常用HiveQL
1、在命令行提示符前显示操作的数据库：
hive > set hive.cli.print.current.db=true;
2、查询结果显示字段名称：
hive > set hive.cli.print.header=true;
3、执行shell命令：
hive > ! ls;
4、执行dfs命令：
hive > dfs -ls;
5、hive脚本中，--表示注释，在命令行中不能使用
6、数据类型转换：
hive >cast (s as int);
7、显示表的详细信息：
hive >desribe formatted/extended tablename;
8、将Hive设置为"strict（严格）"模式，这样如果对分区表进行查询而where子句没有加分区过滤的话，将会禁止提交这个任务。
hive> set hive.mapred.mode=strict/nostrict;
9、动态分区插入数据的写法：
hive >insert overwrite table table1 partition (a,b) select ...,c,d from test2;
10、rand()：返回一个0~1随机值
11、了解Hive是如何将查询转化成MapReduce任务的：
hive >explain select sum(a) from test;
hive >explain extended select sum(a) from test;
12、指定日志为DEBUG级别，而且输出到控制台
hive -hiveconf hive.root.logger=DEBUG,console
13、发现和描述函数:
hive >show functions;
hive >describe function concat;
hive >describe function extended concat;
14、标准函数：UDF（一对一）
聚合函数：UDAF（多对一）
表生成函数：UNTF（一对多）
15、宏命令（某些时候比函数方便）
hive >create temporary macro sigmoid (x double) 1.0 / (1.0 + exp(-x));
hive >select sigmoid(2) from src limit 1;
16、关于Hive中的用户，组和角色
hive (default)> set hive.security.authorization.enabled;
hive.security.authorization.enabled=false
hive (default)> set system:user.name;
system:user.name=hadoop
hive (default)> show grant user hadoop;
OK
default hadoop USER ALL false 1522203964000 hive
test hadoop USER ALL false 1522205200000 hive
zb_dwa hadoop USER ALL false 1522203974000 hive
zb_dwd hadoop USER ALL false 1522203972000 hive
zb_src hadoop USER ALL false 1522203972000 hive
zb_src_test hadoop USER ALL false 1522203972000 hive
zb_dwa t_code hadoop USER ALL true 1524211395000 hadoop
zb_dwa t_code_source hadoop USER ALL true 1524204335000 hadoop
Time taken: 2.214 seconds, Fetched: 18 row(s)
hive (default)> show grant user hadoop on database default;
OK
default hadoop USER ALL false 1522203964000 hive
Time taken: 0.026 seconds, Fetched: 1 row(s)
17、什么情况下Hive可以避免MapReduce？
hive > set hive.exec.mode.local.auto=true;
Hive中对于某些情况的查询可以不必使用MapReduce，也就是所谓的本地模式。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employees对应的存储目录下的文件，然后输出格式化后的内容到控制台。对于WHERE语句中过滤条件只是分区字段这种情况，也是无需使用MapReduce过程的。
18、JOIN优化
□ 当对3个或者更多个表进行JOIN连接时，如果每个ON子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
□ Hive同时假定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，它会尝试将其它表缓存起来，然后扫描最后那个表进行计算。因此，用户需要保证连续查询中的表的大小从左到右是依次增加的。
□ Hive还提供了一个"标记"机制来显式的告之查询优化器哪张表是大表，使用方式如下：
hive > SELECT /*+STREAMTABLE(s)*/s.ymd,s.symbol,s.price_close,d.dividend FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol WHERE s.symbol = 'AAPL' ;
19、map-side JOIN
如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程（称为map-side JOIN），这是因为Hive可以和内存中的小表进行逐一匹配，从而省略常规连接操作所需要的reduce过程。即使对于很小的数据量，这个优化也明显的要快于常规的连接操作。其不仅减少了reduce过程，而且有时还可以同时减少map过程的执行步骤。
在Hive v0.7之前的版本，如果想要使用这个优化，需要在查询语句中增加一个标记来进行触发。
hive > SELECT /*+MAPJOIN(d)*/s.ymd,s.symbol,s.price_close,d.dividend FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol WHERE s.symbol = 'AAPL' ;
从Hive v0.7版本开始，废弃了这种标记的方式，不过如果增加这个标记还是有效的。用户需要配置属性hive.auto.convert.join为true，默认情况下这个属性为false。用户也可以配置能够使用这个优化的小表的大小，配置属性为hive.mapjoin.smalltable.filesize，默认值为25000000。
hive > set hive.auto.convert.join = true;
hive > set hive.mapjoin.smalltable.filesize = 25000000;
hive > SELECT s.ymd,s.symbol,s.price_close,d.dividend FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol WHERE s.symbol = 'AAPL' ;
Hive对于右外连接和全外连接不支持这个优化。
如果所有表中的数据是分桶的，那么对于大表，在特定的情况下同样可以使用这个优化。简单地说，表中的数据必须是按照ON语句中的键进行分桶的，其中一张表的分桶个数必须是另外一张表的分桶个数的若干倍。当满足这些条件时，那么Hive可以在map阶段按照分桶数据进行连接。不过这个优化同样默认是没有开启的，需要设置参数hive.optimize.bucketmapjoin为true，默认是false。
hive > set hive.optimize.bucketmapjoin = true;
20、ORDER BY 和 SORT BY
Hive中ORDER BY会对结果进行一个全局排序。也就是说会有一个所有数据都通过一个reducer进行处理的过程。对于大数据集，这个过程可能会消耗太过漫长的时间。如果属性hive.mapred.mode的值是strict的话，那么Hive要求这样的语句必须加有LIMIT语句进行限制。默认情况下，这个属性的值是nostrict。
SORT BY只会在每个reducer中对数据进行排序，也就是执行一个局部排序的过程。这样可以保证每个reducer的输出数据都是有序的（但并非全局有序）。这样可以提高后面进行的全局排序的效率。
如果使用的reducer的个数大于1的话，那么输出结果的排序就大不一样了，既然只保证每个reducer的输出是局部排序的，那么不同reducer的输出就可能会有重叠的。
21、含有SORT BY的DISTRIBUTE BY
DISTRIBUTE BY控制map的输出在reducer中是如何划分的（按照指定的字段对数据进行划分输出到不同的reducer中）。MapReduce job中传输的所有数据都是按照键-值对的方式进行组织的，因此Hive再将用户的查询语句转换成MapReduce job时，其必须在内部使用这个功能。
默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去。这也就意味着当我们使用SORT BY时，不同reducer的输出内容会有明显的重叠，至少对于排列顺序而言是这样，即使每个reducer的输出的数据都是有序的。
DISTRIBUTE BY和GROUP BY在其控制着reducer是如何接受一行行数据进行处理这方面是类似的，而SORT BY则控制着reducer内的数据是如何进行排序的。
需要注意的是，Hive要求DISTRIBUTE BY语句要卸载SORT BY语句之前。
22、CLUSTER BY
CLUSTER BY除了具有DISTRIBUTE BY的功能外还兼具SORT BY的功能。
使用DISTRIBUTE BY......SORT BY语句或其简化版的CLUSTER BY语句会剥夺SORT BY的并行性，然而这样可以实现输出文件的数据是全局排序的。
23、ROW_NUMBER()分组排序取TOP 1
hive > SELECT * FROM (SELECT m.qq_fwbzh,m.xxzjbh,ROW_NUMBER() OVER(PARTITION BY m.qq_fwbzh ORDER BY m.xxrksj DESC) as flag FROM zb_src.zy_sh_qqdjxx m) t WHERE t.flag=1;
24、hive中的替换
select regexp_replace(sfzbh,"'",""),regexp_replace(glkh,"'","") from DWD_Z_SG_BG_MGJCRY limit 10;
25、实现hive里横转纵的功能，使用说明： lateral view explode(split(列名,'分隔符')) ，这个函数必须要有别名
示例：select gmsfhm, gddh2 from zb_dwa.DWA_R_JB_RYDHHMK t lateral view explode(split(t.gddh,','))a as gddh2 where gmsfhm='152301198209100568';
152632196712060315 ,13088573907,13034744906
转化成
152632196712060315 13088573907
152632196712060315 13034744906
26、hive格式转换
cast(c.code as int)=cast(a.mz_dm as int)
27、hive 纵变横
原始:
sys_region (id,name)
1 a
1 b
2 c
2 d
select id,concat_ws(',',collect_set(name))
from sys_region
group by id;
结果:
1 a,b
2 c,d
28、修改表注释
alter table DWA_R_GJ_GLKYGPXX set tblproperties('comment'='公路客运售票信息整合表');
29、一些Hive优化
set hive.groupby.skewindata=true; //当数据出现倾斜时，会自动进行负载均衡
set hive.exec.compress.output=true; //hive最终输出是否压缩
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec; //map的输出压缩方式
set mapred.output.compression.type=BLOCK; //压缩类型，默认为RECORD，压缩单独的记录，BLOCK为块压缩
set mapreduce.map.memory.mb=2049; //每个map的内存大小
set mapreduce.reduce.memory.mb=2049; //每个reduce的内存大小
set hive.exec.parallel=true; //控制同一个sql中的不同的job是否可以同时运行，默认为false
set hive.exec.parallel.thread.number=4; //控制对于同一个sql来说同时可以运行的job的最大值，默认为8
set mapred.max.split.size=256000000; //决定每个map处理的最大的文件大小，单位为B
set mapred.min.split.size.per.node=100000000; //节点中可以处理的最小的文件的大小
set mapred.min.split.size.per.rack=100000000; //机架中可以处理的最小的文件的大小
set hive.merge.mapfiles=true; //在Map-only的任务结束时合并小文件
set hive.merge.mapredfiles=true; //在Map-Reduce的任务结束时合并小文件
set hive.merge.size.per.task=128000000; //合并文件的大小
set hive.meger.smallfiles.avgsize=100000000; //当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; //hive的输入 InputFormat
set hive.hadoop.supports.splittable.combineinputformat=true; //是否支持可切分的combineinputformat
set mapred.reduce.tasks=10;
set hive.exec.compress.output=true; //hive最终输出是否压缩
set mapred.compress.map.output=false; //hadoop参数，map输出是否压缩
set mapred.output.compress=true; //hadoop参数，reduce输出是否压缩
30、查询时间戳
hive > select from_unixtime(unix_timestamp()) from test;
31、一些正则
select gmsfhm from DWD_R_JG_ZDRKXX where gmsfhm not rlike "^[0-9]{15}$" and gmsfhm not rlike "^[0-9]{17}[0-9Xx]{1}$";
select * from dwd_r_jg_zdrkxx where lxdh rlike "^+86[0-9]{11}$";
select * from dwd_r_jg_zdrkxx where lxdh rlike "^[0-9]{4}-[0-9]{7}$";
select * from dwd_r_jg_zdrkxx where lxdh rlike "^+86[0-9]{11}$" or lxdh rlike "^[0-9]{4}-[0-9]{7}$";
获取当前时间：select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss');
匹配姓名： select xm from dwd_r_jg_zdrkxx where xm not rlike "^[\\u4e00-\\u9fa5]+[.]?[\\u4e00-\\u9fa5]+$";
select * from dwd_r_jg_zdrkxx where xm is null;
32、Hive建表并指定分隔符
hive (zb_dim)> create table code_zylb(code string,name string) ROW FORMAT delimited fields terminated by '\t';
OK
Time taken: 0.131 seconds
hive (zb_dim)> load data local inpath '/home/hadoop/code_zylb.txt' into table code_zylb;
Loading data to table zb_dim.code_zylb
Table zb_dim.code_zylb stats: [numFiles=1, totalSize=10765]
OK
Time taken: 0.426 seconds
33、添加字段
alter table civil_aviation_in_port add columns (sfzh string comment '身份证号');
34、DEBUG模式启动hiveserver2，并指定hiveserver2服务器
nohup hive --service hiveserver2 --hiveconf hive.root.logger=DEBUG,console --hiveconf hive.server2.thrift.bind.host=hadoop02 &
