常用HiveQL

1、在命令行提示符前顯示操作的數(shù)據(jù)庫：

hive > set hive.cli.print.current.db=true;

2、查詢結(jié)果顯示字段名稱：

hive > set hive.cli.print.header=true;

3、執(zhí)行shell命令：

hive > ! ls;

4、執(zhí)行dfs命令：

hive > dfs -ls;

5、hive腳本中，--表示注釋，在命令行中不能使用

6、數(shù)據(jù)類型轉(zhuǎn)換：

hive >cast (s as int);

7、顯示表的詳細(xì)信息：

hive >desribe formatted/extended tablename;

8、將Hive設(shè)置為“strict（嚴(yán)格）”模式，這樣如果對(duì)分區(qū)表進(jìn)行查詢而where子句沒有加分區(qū)過濾的話，將會(huì)禁止提交這個(gè)任務(wù)。

hive> set hive.mapred.mode=strict/nostrict;

9、動(dòng)態(tài)分區(qū)插入數(shù)據(jù)的寫法：

hive >insert overwrite table table1 partition (a,b) select ...,c,d from test2;

10、rand()：返回一個(gè)0~1隨機(jī)值

11、了解Hive是如何將查詢轉(zhuǎn)化成MapReduce任務(wù)的：

hive >explain select sum(a) from test;

hive >explain extended select sum(a) from test;

12、指定日志為DEBUG級(jí)別，而且輸出到控制臺(tái)

hive -hiveconf hive.root.logger=DEBUG,console

13、發(fā)現(xiàn)和描述函數(shù):

hive >show functions;

hive >describe function concat;

hive >describe function extended concat;

14、標(biāo)準(zhǔn)函數(shù)：UDF（一對(duì)一）

? ? ? ? 聚合函數(shù)：UDAF（多對(duì)一）

? ? ? ? 表生成函數(shù)：UNTF（一對(duì)多）

15、宏命令（某些時(shí)候比函數(shù)方便）

hive >create temporary macro sigmoid (x double) 1.0 / (1.0 + exp(-x));

hive >select sigmoid(2) from src limit 1;

16、關(guān)于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中對(duì)于某些情況的查詢可以不必使用MapReduce，也就是所謂的本地模式。例如：SELECT * FROM?employees;在這種情況下，Hive可以簡單地讀取employees對(duì)應(yīng)的存儲(chǔ)目錄下的文件，然后輸出格式化后的內(nèi)容到控制臺(tái)。對(duì)于WHERE語句中過濾條件只是分區(qū)字段這種情況，也是無需使用MapReduce過程的。

18、JOIN優(yōu)化

?□ 當(dāng)對(duì)3個(gè)或者更多個(gè)表進(jìn)行JOIN連接時(shí)，如果每個(gè)ON子句都使用相同的連接鍵的話，那么只會(huì)產(chǎn)生一個(gè)MapReduce?job。

?□? Hive同時(shí)假定查詢中最后一個(gè)表是最大的那個(gè)表。在對(duì)每行記錄進(jìn)行連接操作時(shí)，它會(huì)嘗試將其它表緩存起來，然后掃描最后那個(gè)表進(jìn)行計(jì)算。因此，用戶需要保證連續(xù)查詢中的表的大小從左到右是依次增加的。

?□ Hive還提供了一個(gè)“標(biāo)記”機(jī)制來顯式的告之查詢優(yōu)化器哪張表是大表，使用方式如下：

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的時(shí)候?qū)⑿”硗耆诺絻?nèi)存中。Hive可以在map端執(zhí)行連接過程（稱為map-side JOIN），這是因?yàn)镠ive可以和內(nèi)存中的小表進(jìn)行逐一匹配，從而省略常規(guī)連接操作所需要的reduce過程。即使對(duì)于很小的數(shù)據(jù)量，這個(gè)優(yōu)化也明顯的要快于常規(guī)的連接操作。其不僅減少了reduce過程，而且有時(shí)還可以同時(shí)減少map過程的執(zhí)行步驟。

?? ?在Hive?v0.7之前的版本，如果想要使用這個(gè)優(yōu)化，需要在查詢語句中增加一個(gè)標(biāo)記來進(jìn)行觸發(fā)。

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版本開始，廢棄了這種標(biāo)記的方式，不過如果增加這個(gè)標(biāo)記還是有效的。用戶需要配置屬性hive.auto.convert.join為true，默認(rèn)情況下這個(gè)屬性為false。用戶也可以配置能夠使用這個(gè)優(yōu)化的小表的大小，配置屬性為hive.mapjoin.smalltable.filesize，默認(rèn)值為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對(duì)于右外連接和全外連接不支持這個(gè)優(yōu)化。

?? ?如果所有表中的數(shù)據(jù)是分桶的，那么對(duì)于大表，在特定的情況下同樣可以使用這個(gè)優(yōu)化。簡單地說，表中的數(shù)據(jù)必須是按照ON語句中的鍵進(jìn)行分桶的，其中一張表的分桶個(gè)數(shù)必須是另外一張表的分桶個(gè)數(shù)的若干倍。當(dāng)滿足這些條件時(shí)，那么Hive可以在map階段按照分桶數(shù)據(jù)進(jìn)行連接。不過這個(gè)優(yōu)化同樣默認(rèn)是沒有開啟的，需要設(shè)置參數(shù)hive.optimize.bucketmapjoin為true，默認(rèn)是false。

hive > set hive.optimize.bucketmapjoin = true;

20、ORDER BY?和 SORT BY

?? ?Hive中ORDER BY會(huì)對(duì)結(jié)果進(jìn)行一個(gè)全局排序。也就是說會(huì)有一個(gè)所有數(shù)據(jù)都通過一個(gè)reducer進(jìn)行處理的過程。對(duì)于大數(shù)據(jù)集，這個(gè)過程可能會(huì)消耗太過漫長的時(shí)間。如果屬性hive.mapred.mode的值是strict的話，那么Hive要求這樣的語句必須加有LIMIT語句進(jìn)行限制。默認(rèn)情況下，這個(gè)屬性的值是nostrict。

?? ?SORT BY只會(huì)在每個(gè)reducer中對(duì)數(shù)據(jù)進(jìn)行排序，也就是執(zhí)行一個(gè)局部排序的過程。這樣可以保證每個(gè)reducer的輸出數(shù)據(jù)都是有序的（但并非全局有序）。這樣可以提高后面進(jìn)行的全局排序的效率。

?? ?如果使用的reducer的個(gè)數(shù)大于1的話，那么輸出結(jié)果的排序就大不一樣了，既然只保證每個(gè)reducer的輸出是局部排序的，那么不同reducer的輸出就可能會(huì)有重疊的。

21、含有SORT BY的DISTRIBUTE BY

?? ?DISTRIBUTE BY控制map的輸出在reducer中是如何劃分的（按照指定的字段對(duì)數(shù)據(jù)進(jìn)行劃分輸出到不同的reducer中）。MapReduce?job中傳輸?shù)乃袛?shù)據(jù)都是按照鍵-值對(duì)的方式進(jìn)行組織的，因此Hive再將用戶的查詢語句轉(zhuǎn)換成MapReduce?job時(shí)，其必須在內(nèi)部使用這個(gè)功能。

? ? 默認(rèn)情況下，MapReduce計(jì)算框架會(huì)依據(jù)map輸入的鍵計(jì)算相應(yīng)的哈希值，然后按照得到的哈希值將鍵-值對(duì)均勻分發(fā)到多個(gè)reducer中去。這也就意味著當(dāng)我們使用SORT BY時(shí)，不同reducer的輸出內(nèi)容會(huì)有明顯的重疊，至少對(duì)于排列順序而言是這樣，即使每個(gè)reducer的輸出的數(shù)據(jù)都是有序的。

?? ?DISTRIBUTE BY和GROUP BY在其控制著reducer是如何接受一行行數(shù)據(jù)進(jìn)行處理這方面是類似的，而SORT BY則控制著reducer內(nèi)的數(shù)據(jù)是如何進(jìn)行排序的。

? ? 需要注意的是，Hive要求DISTRIBUTE BY語句要卸載SORT BY語句之前。

22、CLUSTER BY

?? ?CLUSTER BY除了具有DISTRIBUTE BY的功能外還兼具SORT BY的功能。

?? ?使用DISTRIBUTE BY......SORT BY語句或其簡化版的CLUSTER BY語句會(huì)剝奪SORT BY的并行性，然而這樣可以實(shí)現(xiàn)輸出文件的數(shù)據(jù)是全局排序的。

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、實(shí)現(xiàn)hive里橫轉(zhuǎn)縱的功能，使用說明： lateral view explode(split(列名,'分隔符')) ，這個(gè)函數(shù)必須要有別名

示例：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????

轉(zhuǎn)化成???????????????????

152632196712060315????13088573907

152632196712060315????13034744906

26、hive格式轉(zhuǎn)換

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;

結(jié)果:

1 a,b

2 c,d

28、修改表注釋

alter table DWA_R_GJ_GLKYGPXX set tblproperties('comment'='公路客運(yùn)售票信息整合表');

29、一些Hive優(yōu)化

set hive.groupby.skewindata=true;???????????????????????????????????????????????????//當(dāng)數(shù)據(jù)出現(xiàn)傾斜時(shí)，會(huì)自動(dòng)進(jìn)行負(fù)載均衡

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;???????????????????????????????????????????//壓縮類型，默認(rèn)為RECORD，壓縮單獨(dú)的記錄，BLOCK為塊壓縮

set mapreduce.map.memory.mb=2049;???????????????????????????????????????????????????//每個(gè)map的內(nèi)存大小

set mapreduce.reduce.memory.mb=2049;????????????????????????????????????????????????//每個(gè)reduce的內(nèi)存大小

set hive.exec.parallel=true;????????????????????????????????????????????????????????//控制同一個(gè)sql中的不同的job是否可以同時(shí)運(yùn)行，默認(rèn)為false

set hive.exec.parallel.thread.number=4;?????????????????????????????????????????????//控制對(duì)于同一個(gè)sql來說同時(shí)可以運(yùn)行的job的最大值，默認(rèn)為8

set mapred.max.split.size=256000000;????????????????????????????????????????????????//決定每個(gè)map處理的最大的文件大小，單位為B

set mapred.min.split.size.per.node=100000000;???????????????????????????????????????//節(jié)點(diǎn)中可以處理的最小的文件的大小

set mapred.min.split.size.per.rack=100000000;???????????????????????????????????????//機(jī)架中可以處理的最小的文件的大小

set hive.merge.mapfiles=true;???????????????????????????????????????????????????????//在Map-only的任務(wù)結(jié)束時(shí)合并小文件

set hive.merge.mapredfiles=true;????????????????????????????????????????????????????//在Map-Reduce的任務(wù)結(jié)束時(shí)合并小文件

set hive.merge.size.per.task=128000000;?????????????????????????????????????????????//合并文件的大小

set hive.meger.smallfiles.avgsize=100000000;????????????????????????????????????????//當(dāng)輸出文件的平均大小小于該值時(shí)，啟動(dòng)一個(gè)獨(dú)立的map-reduce任務(wù)進(jìn)行文件合并

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參數(shù)，map輸出是否壓縮

set mapred.output.compress=true;????????????????????????????????????????????????????//hadoop參數(shù)，reduce輸出是否壓縮

30、查詢時(shí)間戳

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}$";

獲取當(dāng)前時(shí)間：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 '身份證號(hào)');

34、DEBUG模式啟動(dòng)hiveserver2，并指定hiveserver2服務(wù)器

nohup hive --service hiveserver2 --hiveconf hive.root.logger=DEBUG,console --hiveconf hive.server2.thrift.bind.host=hadoop02 &