常用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 &