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