1.set hive.cli.print.header=true; //打印表格頭部

2.set hive.mapred.mode=strict;? //設(shè)置hive為“嚴(yán)格”模式储笑，這樣如果對分區(qū)表進行查詢而where子句沒有加分區(qū)過濾的話吉殃，將禁止提交這個任務(wù)

3.select * from new_cnt膳帕；//顯示表格

4.limit 20;

5.crate database? name； // 創(chuàng)建數(shù)據(jù)庫

6.show databases税手；

7.show database like “h.*"? //顯示“h”開頭的database

8.crate external table name? //建立外部表

9.crate table name（? ）partition by（ county string，state string）? //建立表并分區(qū)

10.drop table tablename褐桌；//刪除表格

11.alter table log_messages rename to logmsgs书幕；//將表log_massages重命名為logmsgs

12.alter table log_massages add? ? //增加表的新分區(qū)

? ? ? partition（year = 2011，month = 1凡橱，day = 1） location"/logs/2011/01/01"

? ? ? partition（year = 2011小作，month = 1，day = 2） location"/logs/2011/01/02"

? ? ? partition（year = 2011稼钩，month = 1顾稀，day = 3） location"/logs/2011/01/03"

? .....;

13..alter table log_massages? partition（year = 2011，month = 1变抽，day = 2）

set location"s3n://ourbucket/logs/2011/01/02" ; //移動分區(qū)

14.alter table? log_massages drop partition（year = 2011础拨，month = 1，day = 2）

//刪除分區(qū)

15.insert overwrite(into) table employees? partition (county = "US",state="OR")

? ? select * from staged_employees se where se.cnty = "US" and se.st = "OR";

? ? ? //把表 “se” 中的數(shù)據(jù)插入到表“s”中? ? ----靜態(tài)分區(qū)插入

16.insert overwrite(into) table employees partition (county,state)

? ? select ...,se.cnty,se.st from staged_employees se绍载；

? ? //把表“se”中的數(shù)據(jù)插入到表“s" -------動態(tài)分區(qū)插入

17.insert overwrite(into) table employees? partition (county = "US",state)

? ? select ...,se.cnty,se.st from staged_employees se where se.cnty = "US" ；

? ? //把表“se”中的數(shù)據(jù)動態(tài)和靜態(tài)結(jié)合插入到表“s”中

屬性名稱缺省值描述

hive.exec.dynamic.partitionfalse設(shè)置成true滔蝉，表示開啟動態(tài)分區(qū)功能

hive.exec.dynamic.partition.modestrict設(shè)置成nonstrict击儡，表示允許所有分區(qū)都是動態(tài)的

hive.exec.max.dynamic.partitions.pernode100每個mapper或reducer可以創(chuàng)建的最大動態(tài)分區(qū)個數(shù)。如果某個mapper或reducer嘗試創(chuàng)建大于這個值得分區(qū)的話則會拋出一個致命錯誤信息

hive.exec.max.dynamic.partitions+1000一個動態(tài)分區(qū)創(chuàng)建語句可以創(chuàng)建的最大動態(tài)分區(qū)個數(shù)蝠引。如果超過這個值則會拋出一個致命錯誤信息

hive.exec.max.created.files10000全局可以創(chuàng)建的最大文件個數(shù)阳谍。有一個Hadoop計數(shù)器會跟蹤記錄創(chuàng)新了多少個文件蛀柴，如果超過這個值則會拋出一個致命錯誤信息

sethive.exec.dynamic.partition=true；

set hive.exec.dynamic.partition.mode=nonstrict矫夯；

set hive.exec.max.dynamic.partitions.pernode=1000

18.Hadoop fs -cp source_path target_path,? ? //導(dǎo)出數(shù)據(jù)

? ? insert overwrite local directory"/tmp/ca_employees"?

? ? select name,salary,address from employees where se.state = "CA";

19.Hive distribute by sorl by order by 的區(qū)別

distribute by：控制著在map端如何分區(qū)鸽疾，按照什么字段進行分區(qū)，要注意均衡

sort by：每個reduce按照sort by 字段進行排序训貌，reduce的數(shù)量按照默認(rèn)的數(shù)量來進行制肮，當(dāng)然可以指定。

????????? ? ? ?最終可以進行歸并排序得出結(jié)果递沪。

????????????????適用于數(shù)據(jù)量比較大的排序場景豺鼻。

order by：reduce只有一個，在一個reduce中完成排序款慨，使用于數(shù)據(jù)量小的場景儒飒。

20.都是kill子任務(wù)：

-- kill job：

hadoop job? -kill job_1537547243219_0058

-- kill application：

yarn application -kill application_1537547243219_0056

HiveQL之Sort by、Distribute by檩奠、Cluster by桩了、Order By詳解

在這里解釋一下select語法中的order by、sort by埠戳、distribute by圣猎、cluster by、order by語法乞而。

一送悔、order by語法

在hiveQL中Order by語法類似于sql語言中的order by語法。

colOrder: ( ASC | DESC )

colNullOrder: (NULLS FIRST | NULLS LAST)?????????? -- (Note: Available in Hive 2.1.0 and later)

orderBy: ORDER BY colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*

query: SELECT expression (',' expression)* FROM src orderBy

有一些限制在order by子句中爪模，在嚴(yán)格模式下（strict）欠啤，order by子句必須帶有以limit子句。Limit子句不是必須的屋灌，如果你設(shè)置hive.mapred.mode是非嚴(yán)格模式（nostrict）洁段。原因是為了增強全局排序的結(jié)果，這里只有一個reduce去進行最終的排序輸出共郭，如果輸出結(jié)果集行集太大祠丝，這單獨的reduce可能需要花費非常長的時間去處理。

注意除嘹，這列必須使用的名字写半，而不能指定位置號，然而在hive 0.11.0以后尉咕，在做了下面配置之后列可以指定序列號叠蝇，如下：

1）從hive0.11.0到2.1.x，設(shè)置hive.groupby.orderby.position.alias?是true（默認(rèn)值是false）年缎。

2）從hive2.2.0以后悔捶，hive.orderby.position.alias默認(rèn)是ture铃慷。

Order by默認(rèn)排序是asc。

在hive 2.1.0以后蜕该，被選擇排序的每個列是null在order by子句是支持的犁柜。默認(rèn)null排序規(guī)則是asc（升序）是nulls first，當(dāng)默認(rèn)的排序規(guī)則是desc（時）是nulls last

二堂淡、sort by語法

在hiveQL中sort by語法類似于sql語言中的order by語法馋缅。

colOrder: ( ASC | DESC )

sortBy: SORT BY colName colOrder? (',' colName colOrder?)*

query: SELECT expression (',' expression)* FROM src sortBy

Hive中被用來sort by排序的行的排序操作發(fā)生在發(fā)送這些行到reduce之前。排序的次序依賴于排序列的類型淤齐，如果列是數(shù)值類型股囊，那么排序次序按照數(shù)值排序，如果列式字符串類型更啄，那么排序次序?qū)凑兆值渑判颉?/p>

1稚疹、sort by和order by的不同點

Hive sort by的排序發(fā)生在每個reduce里，order by和sort by之間的不同點是前者保證在全局進行排序祭务，而后者僅保證在每個reduce內(nèi)排序内狗，如果有超過1個reduce，sort by可能有部分結(jié)果有序义锥。

注意：它也許是混亂的作為單獨列排序?qū)τ趕ort by和cluster by柳沙。不同點在于cluster by的分區(qū)列和sort by有多重reduce，reduce內(nèi)的分區(qū)數(shù)據(jù)時一致的拌倍。

通常赂鲤，數(shù)據(jù)在每個reduce排序通過用戶指定的規(guī)則，如下示例：

SELECT key, value FROM src SORT BY key ASC, value DESC

查詢有兩個reduce柱恤，查詢結(jié)果如下：

0?? 5

0?? 3

3?? 6

9?? 1

0?? 4

0?? 3

1?? 1

2?? 5

2数初、設(shè)置排序類型

轉(zhuǎn)換后，參數(shù)類型一般認(rèn)為是string類型梗顺，意味著數(shù)值類型將按照字典排序方式進行排序泡孩，克服這一點，第二個select語句可以在sort by之前被使用寺谤。

FROM (FROM (FROM src

??????????? SELECT TRANSFORM(value)

??????????? USING 'mapper'

??????????? AS value, count) mapped

????? SELECT cast(value as double) AS value, cast(count as int) AS count

????? SORT BY value, count) sorted

SELECT TRANSFORM(value, count)

USING 'reducer'

AS whatever

三仑鸥、Cluster By 和Distribute By語法

Cluster by和distribute by主要用在進行Transform/Map-Reduce腳本。但是变屁，他有時可以應(yīng)用在select語句眼俊，如果有一個子查詢需要分區(qū)和排序?qū)τ谳敵鼋Y(jié)果集。

Cluster by是一個捷徑對于包含distribute by和sort by的語句敞贡。

Hive使用distribute by中的列去分發(fā)行到每個reduce中泵琳，所有同樣的distribute by列的行將發(fā)送到同一個reduce。然而誊役，分發(fā)并不保證clustering和sorting的屬性在分發(fā)關(guān)鍵字获列。

例如，我們按照x進行distribute by在5行數(shù)據(jù)到2個reduce中：

x1

x2

x4

x3

x1

Reduce 1 得到的數(shù)據(jù)：

x1

x2

x1

Reduce 2 得到的數(shù)據(jù)：

x4

x3

注意：這所有制是x1的行確保被分發(fā)到通一個reduce中蛔垢，但是击孩，他們不能保證在集合中會在相鄰的位置。

相比之下鹏漆，如果我們使用cluster by x巩梢，這兩個reduce將進一步在x上進行排序。

Reduce 1得到數(shù)據(jù)：

x1

x1

x2

Reduce 2得到數(shù)據(jù)：

x3

x4

不能替代cluster by艺玲，用戶可以指定distribute by和sort by括蝠，同樣，分區(qū)列和排序列可以是不同的饭聚，通常情況忌警，分區(qū)類是排序類的前綴，但是他并不是必須的秒梳。

SELECT col1, col2 FROM t1 CLUSTER BY col1

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1 SORT BY col1 ASC, col2 DESC

FROM (

FROM pv_users

MAP ( pv_users.userid, pv_users.date )

USING 'map_script'

AS c1, c2, c3

DISTRIBUTE BY c2

SORT BY c2, c1) map_output

INSERT OVERWRITE TABLE pv_users_reduced

REDUCE ( map_output.c1, map_output.c2, map_output.c3 )

USING 'reduce_script'

AS date, count;

20.count（1）和count（*）的區(qū)別

count(*)表示檢索表中所有列法绵，并統(tǒng)計記錄數(shù)，包括重復(fù)列和空值酪碘。

count(1)中1表示表中的第一列朋譬，即檢索表中第一列，并統(tǒng)計第一列不為空的記錄數(shù)

21.concat兴垦、concat_ws徙赢、group_concat函數(shù)用法

一、concat()函數(shù)可以連接一個或者多個字符串

　　CONCAT(str1,str2,…)?返回結(jié)果為連接參數(shù)產(chǎn)生的字符串探越。如有任何一個參數(shù)為NULL?狡赐，則返回值為?NULL。

　　select?concat('11','22','33'); ? ??112233

二扶关、CONCAT_WS(separator,str1,str2,...)?

　　是CONCAT()的特殊形式阴汇。第一個參數(shù)是其它參數(shù)的分隔符。分隔符的位置放在要連接的兩個字符串之間节槐。分隔符可以是一個字符串搀庶，也可以是其它參數(shù)。

　 ? select?concat_ws(',','11','22','33'); ??　11,22,33

三铜异、group_concat()分組拼接函數(shù)

　　group_concat([DISTINCT]?要連接的字段?[Order?BY?ASC/DESC?排序字段]?[Separator?'分隔符'])

　　對下面的一組數(shù)據(jù)使用?group_concat()

　　| id |name

　　|1?|?10|

　　|1?|?20|

　　|1?|?20|

　　|2?|?20|

　　|3?|?200???|

　　|3?|?500???|

　　1哥倔、select?id,group_concat(name)?from?aa?group?by?id;

　　|1?|?10,20,20|

　　|2?|?20?|

　　|3?|?200,500|

　　2、select?id,group_concat(name?separator?';')?from?aa?group?by?id;

　　|1?|?10;20;20?|

　　|2?|?20|

　　|3?|?200;500???|

　　3揍庄、select?id,group_concat(name?order?by?name?desc)?from?aa?group?by?id;

　　|1?|?20,20,10???|

　　|2?|?20|

　　|3?|?500,200|

　　4咆蒿、select?id,group_concat(distinct?name)?from?aa?group?by?id;

　　|1?|?10,20|

　　|2?|?20???|

　　|3?|?200,500?|