[大数据]hive基础相关操作

<一>.hive中的基本操作
1.1.DDL相关语句汇总

alter table table01 rename to table02 --修改表名table01为table02
alter table table01 add columns(dep string); --在table01表中添加dep字段列
alter table table01 change colume dep desc int; --在table01表中更新dep字段列
alter table table01 change dep lol string; --在table01表中修改dep字段名为lol字段名
alter table table01 replace columes(dep string,lol string); --在table01表中dep字段列替换成lol字段列
desc database extended ods_hkvb_dw; --显示ods_hkvb_dw库的详细信息
desc table; --显示表结构
desc formatted table01; --查看table01表的详细信息
drop database if exists dbname[cascade]; --若存在dbname库就删除,加cascade表示强制删除
show databases like ‘ods_hkvb_*’; --过滤显示查询ods_hkvb开头的库
show tables in ods_hkvb_dw; --查看非当前使用库中的表
show partitions table01; --查看table01表中所有分区
show create table table01; --查看table01表中详细信息
show columns in table01; --查看table01表中所有表字段
select current_database(); --查看正在使用的库
truncate table table01; --清空table01表中的数据
1.2.DML相关语句汇总

show functions； --显示函数列表
describe function function_name； --函数的简单描述
describe function extended function_name； --函数的详细描述
!quit --退出beeline客户端
(a).load语句操作，将文件复制到hive对应的存储路径下。
(b).insert语句操作，将查询到的结果插入到表中，
overwrite会覆盖表中原有数据，into会在原有数据基础上追加。
?.select语句操作中，like与rlike语句的区别在于，rlike可以通过java正则表达式的方式进行匹配；
group by+having语句表示分组后进行条件过滤的任务。
1.3.创建语句
(a).当表数据量大时需要创建表分区(一般都要分区)，hadoop数据存储方式，
推荐加入if not exists语句，一般创表语句如下：

CREATE TABLE IF NOT EXISTS lszuu_01(
uuid STRING,
month STRING,
counts STRING
)
COMMENT ‘测试表’
PARTITIONED BY (fpart STRING)
STORED as orc tblproperties(“ORC.compress”=“SNAPPY”);
(b).当在创建表语句中加入external关键字，则该表为外部表，反之该表为内部表。
?.在已经创建的表后可以通过like关键字，复制表结构，语句如下：create table lszuu_01 like student;
(d).新增分区与删除分区的操作语句如下：
alter talbe lszuu_01 add partition(fpart=“time”);
alter table lszuu_01 drop partition(fpart=“time”);
(e).也可以创建以文件形式属性的表，如下语句：

CREATE TABLE IF NOT EXISTS lszuu_01(
uuid STRING,
month STRING,
counts STRING
)
COMMENT ‘测试表’
PARTITIONED BY (fpart STRING)
row format delimited fields terminated by ‘\t’;
1.4.hive常见的四种排序
(a).order by：会对多列做全局排序，默认按asc升排，也可以指定倒排desc。
order by通过只用一个reduce来进行排序，在数据量大的情况下效率低。
(b).sort by：每个mapreduce内部排序，与order by不同在与不受严格模式的限制。
sort by 通过每个reducer产生一个排序文件，可以设置reducer的个数，如下：
set mapreduce.job.reduces=3;
set mapreduce.job.reduces;
数据量大的情况下使用sort by进行排序，当数据量小的情况下使用order by进行排序。
?.distribute by：可以根据指定字段将数据分到不同的reducer，分发算法为hash散列，
常与sort by连用统计每个用户进行排序。
用于解决map输出的文件大小不均，reduce输出文件大小不均，小文件过多，超大文件等情况。
(d).cluster by：就是distrbute by和sort by相结合，除了具有distribute by的功能外还兼具sort by的功能。
但是排序只能是升序排序，不能指定排序规则为asc或者desc。如下语句等价：
select mid, money, name from store cluster by mid;
select mid, money, name from store distribute by mid sort by mid;
1.5.where子句的关系型运算符
关系型运算符优先级高到低的顺序为：not–>and–>or，可以通过’()'符号来改变执行顺序。
(a).关键字含义如下：
and：表示和，全部为真；(A and B)，若A和B值都为null，则返回null，反之返回该值。
or：表示或，只有一个为真；(A or B)，若A或B值同为null，则返回null，反之返回该值。
ont：表示非，与逻辑值相反；(not A <—> !A)，若A为null或false，则返回ture，否则返回false。
(b).语句1：
select * from lsztt_01 where not (uuid=1 or uuid=2);
uuid=1或者uuid=2存在的数据过滤掉不包含，只显示剩余的数据。

    (c).语句2:
     select * from lsztt_01 where not (uuid=1 and uuid=2);    
     uuid=1和uuid=2存在数据则就包含，否则就过滤掉。

(d).语句3:
        select * from lsztt_01 where not uuid=2;    
        显示非uuid=2的数据，即不包含uuid=2的数据。

1.6.sum与count关键字的区别

create table guanwm (a string, b int);

insert into table guanwm values (“a”, 1);
insert into table guanwm values (“b”, 2);
insert into table guanwm values (“c”, 3);

select count(*),sum(1),sum(2) from guanwm; --结果：3,3,3,6

insert into table guanwm values (NULL, 4);
insert into table guanwm values (‘d’, NULL);

select count(),count(1),count(2),sum(1),sum(2) from guanwm; --结果：5,5,5,5,10
select count(a),count(b),sum(a),sum(b) from guanwm; --结果：4,4,0.0,10
结论：count(数字)与count()的逻辑一致都是计算多少行，会包括null值的行
sum(数字)在string列与count(*)逻辑一致，会包括null值的行
sum(数字)在string列与count逻辑一致，但是在数字列则是正常求和逻辑
count(列名)不会包括null值的行
sum(列名)在string列会返回0.0，但是在数字列则是正常求和逻辑

<二>.hive中的启动参数与优化策略
2.1.hive中的系统优化
(a).选用高效的计算引擎，提高hive的执行job的效率
set hive.execution.engine; --查看hive的计算引擎
set hive.execution.engine=spark; --切换spark为计算引擎
2.2.hive语句的优化
(a).在语句中避免频繁使用split函数。
(b).出现多个union all 语句，可以改为小段sql语句，通过insert into table …语句插入到表中。
?.避免全表扫描，select具体到表的某个字段，切勿使用"*"；每张表都要指定加入分区。
(d).可以多使用临时表，或者使用with…as…语句，将语句存发在内存中，达到重复语句复用的效果。
2.3.Beeline常用的启动参数选项
(a).一般情况下的beeline启动命令如下：
beeline -u jdbc:hive2://9.121.12.3:10000 -n root -p fb2021 -hivevar date=20210213 -f 01.hql

-u --要连接的JDBC URL
-n --连接的用户名
-p --连接的密码
-f --应执行的脚本文件(hql文件)
–hivevar name=value --配置变量名和值，并在hive命令和查询中引用值参数。
(b).其他常用参数选项

-e --可以执行hql语句查询，双引号或单引号引起来的查询字符串.
–color=[true/false] --是否使用绿色进行显示hql语句输出结果，默认为false.
–showHeader=[true/false] --是否显示输出结果的列名称，默认为true.
–silent=[true/false] --减少显示查询日志消息，默认为false.
–outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2] --显示结果的格式化模式，默认为表格.
?.–outputformats参数选项详解
该参数选项会将结果以不同的格式显示。
注意该参数要与–silent、–showHeader两个参数联用，才能显示不同格式。
table格式：结果显示在表格中，默认显示格式。
vertical格式：结果以键值格式的块显示(垂直显示)，其中键是列的名称。
xmlattr格式： 结果以xml格式显示， 属性的名称是列的名称。
xmlelements格式： 结果以xml格式显示， 行的值显示为结果元素的子元素。
json格式： 结果以json格式显示，hive4.0版本以上支持。
jsonfile格式：结果以json格式显示，hive4.0版本以上支持， 其中每一行都是一个不同的json对象。
csv2格式：结果以逗号分隔符。
tsv2格式：结果以制表符分隔符。
dsv格式：可以使用delimiterForDSV选项设置定界符，单个特殊字符。默认分隔符为"|"。

beeline --silent=true --showHeader=false --outputformat=dsv --delimiterForDSV=’${}^{\prime }?u"jdbc:hive2://9.121.12.3:10000"?nroot?pfb2021?e"select1,2,3;"beeline??silent=true??showHeader=false??outputformat=csv2??delimiterForDSV{=}^{\prime }$’ -u “jdbc:hive2://9.121.12.3:10000” -n root -p fb2021 -e “select 1,2,3;”
beeline --silent=true --showHeader=false --outputformat=xmlattr --delimiterForDSV=’$’ -u “jdbc:hive2://9.121.12.3:10000” -n root -p fb2021 -e “select 1,2,3;”
beeline --silent=true --color=ture -u “jdbc:hive2://9.121.12.3:10000” -n root -p fb2021 -e “select 1,2,3;”

<三>.hive中的理论知识点
3.1.数据迁移中的日期与表
(a).数据迁移日期
会计日：一般是指分区日期fpart为分区名称，需要指定一个日期进行执行该报表并产生数据，
这个日期会将指定分区的数据进行迁移。
自然日：指报表中的增量日期，每天新增的数据报表中需要指定日期。
快照日：指数据迁移的上个分区日期（上一次抽数日期）。
(b).数据仓库中的相关表
全量表：指将全部的数据迁移到一个分区中保存。
增量表：指每天新增的数据迁移到一个分区中。
快照表：指上一次抽数全量数据放到表中。
拉链表：指通过start_date和end_date进行筛选出固定某一天的数据；
例如我想取截止到20190813的全量数据，
其where过滤条件就是where start_date<=‘20190813’ and end_date>20190813。
3.2.高级查询
(a).当一个表中有多个字段，对表字段特殊处理，可以如下操作:

set hive.support.quoted.identifiers=None;
//需要排除个别字段外的剩余所有字段
select (fpart)?+.+ from tableName;
//查询出所有以字母o打头的列
select ^o.* from tableName;

(b).增量表的数据量大，需建立一个中间表，在这个中间表中获取业务数据达到优化效果。
      获取增量表中的指定相关业务数据，然后通过合并（union去重合并）快照表的，
      通过窗口函数获取最新数据，最后排除分区和rn列。

set hive.support.quoted.identifiers=None;
INSERT INTO TABLE ads_t_switch_push_mid_report partition(fpart=${hivevar:batchDateStr})
SELECT (fpart|rn)?+.+ FROM --排除fpart和rn两列
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY gid,device_id ORDER BY time DESC ) rn
FROM(
SELECT
gid,
platform,
num,
device_id,
time
FROM ods_hkvb_dw.ods_elk_t_vb_app_log_di
WHERE fpart=20210603
and evtName=’/NotificationSwitch’
and page_id=‘app’
UNION
SELECT
gid,
platform,
state,
device_id,
time
FROM ads_t_switch_push_mid_report
WHERE fpart=date_format(DATE_SUB(from_unixtime(unix_timestamp(‘20210603’,‘yyyyMMdd’),‘yyyy-MM-dd’),1),‘yyyyMMdd’)
) a
)b where b.rn=1;