Apache Hive總結

作者：大資料學習與分享

連結：https://zhuanlan.zhihu.com/p/134122356

Apache Hive是基于Hadoop的一個資料倉庫工具，可以将結構化的資料檔案映射為一張資料庫表，并提供一種HQL語言進行查詢，具有擴充性好、延展性好、高容錯等特點，多應用于離線數倉建設。

1. Hive架構

存儲：Hive底層存儲依賴于hdfs，是以也支援hdfs所支援的資料存儲格式，如text、json、parquet等。當我們将一個檔案映射為Hive中一張表時，隻需在建表時告訴Hive，資料中的列名、列分隔符、行分隔符等，Hive就可以自動解析資料。

支援多種壓縮格式：bzip2、gzip、lzo、snappy等。通常采用parquet+snappy格式存儲。支援計算引擎：原生支援引擎為MapReduce。但也支援其他計算引擎，如Spark、Tez。

中繼資料存儲：derby是Hive内置的中繼資料存儲庫，但是derby并發性能差且目前不支援多會話。實際生産中，更多的是采用mysql為Hive的中繼資料存儲庫。

HQL語句執行：解析器、編譯器、優化器完成HQL查詢語句從詞法分析、文法分析、編譯、優化以及查詢計劃的生成。生成的查詢計劃存儲在hdfs中，并在随後轉化為MapReduce任務執行。

2.Hive的幾種建表方式

1）create [external] table ...

create [external] table [if not exists] table_name

[(col_name data_type[comment col_comment],...)]

[comment table_comment]

[partitioned by (col_name data_type[comment col_comment],...)]

[clustered by (col_name,col_name,...)

[sorted by (col_name[asc|desc],...)] into num_buckets buckets]

[row format row_format]

[stored as file_format]

[location hdfs_path];

create、if not exists等跟傳統的關系型資料庫含義類似，就不贅述了。筆者這裡主要說一下hive建表時的幾個特殊關鍵字：

external：建立外部表時需要指定該關鍵字，并通過location指定資料存儲的路徑

partitioned by：建立分區表時，指定分區列。

clustered by和sort by：通常連用，用來建立分桶表，下文會具體闡述。

row format delimited [fields terminated by char] [collection items terminated by char] [map keys terminated by char] [lines terminated by char] serde serde_name [with serdeproperties (property_name=property_value, property_name=property_value, ...)]：指定行、字段、集合類型資料分割符、map類型資料key的分隔符等。使用者在建表的時候可以使用Hive自帶的serde或者自定義serde，Hive通過serde确定表具體列的資料。

stored as file_format：指定表資料存儲格式，如TextFile，SequenceFile，RCFile。預設textfile即文本格式，該方式支援通過load方式加載資料。如果資料需要壓縮，則采用sequencefile方式，但這種存儲方式不能通過load方式加載資料，必須從一個表中查詢出資料再寫入到一個表中insert overwrite table t1 select * from t1;

2） create table t_x as select ...即ctas語句，複制資料但不複制表結構，建立的為普通表。如果複制的是分區表則新建立的不是分區表但有分區字段。ctas語句是原子性的，如果select失敗，将不再執行create操作。

3） create table t_x like t_y like允許使用者複制源表結構，但不複制資料。如，create table t2 like t1;

3.Hive的資料類型

Hive内置資料類型主要分為兩類：基礎資料類型和複雜資料類型。基礎資料類型無外乎就是tinyint、smallint、int、bigint、boolean、float、double、string、timestamp、decimal等，筆者這裡主要介紹Hive的複雜資料類型，或者稱之為集合類型。

Hive的複雜資料類型主要分三種：map、array、struct，并且支援複雜類型嵌套，利用好這些資料類型，将有效提高資料查詢效率。目前為止對于關系型資料庫不支援這些複雜類型。

1.首先建立一張表

create table t_complex(id int,

hobby1 map<string,string>,

hobby2 array<string>,

address struct<country:string,city:string>)

row format delimited

fields terminated by ','

collection items terminated by '-'

map keys terminated by ':' ;

2.準備資料檔案

1,唱歌:一般-跳舞:喜歡-遊泳:不喜歡,唱歌-跳舞-遊泳,USA-New York
2,打遊戲:不喜歡-學習:非常喜歡,打遊戲-籃球,CHINA-BeiJing

3.将資料檔案load到建立的表中load data local inpath '/root/complex.txt' into table t_complex;

4.查詢map、array、struct類型資料查詢map和array跟java中是類似的，都是通過key查找map的value或者根據索引查找array中的元素，而struct則通過列名.辨別來通路元素。

查詢map示例：select hobby1['唱歌'] from t_complex;

查詢array示例：select hobby2[0], hobby2[1] from t_complex;

查詢struct示例：select address.country, address.city from t_complex;

4.内部表和外部表

Hive在建立表時預設建立的是内部表（又稱托管表）。當指定external關鍵字時，則建立的為外部表。并可以通過location指定建表的資料存儲的hdfs路徑。

Hive建立内部表時，會将資料複制/移動到資料倉庫指向的路徑；若建立外部表，僅記錄資料所在路徑，不對資料位置做任何改變。在删除表時，内部表的中繼資料和表資料都會被删除，而外部表隻删除中繼資料，不删除表資料。

建議在生産中建立Hive表時采用外部表的方式，這樣在發生誤删表的時，不至于把表資料也删除，利于資料恢複和安全。當然也可以按照下述情況做細分處理：

1）所有資料處理，全部由hive完成，适合用内部表

2）有hive和其他工具共同處理一個資料集即同一資料集有多個應用要處理，适合用外部表

3）從hive中導出資料，供其他應用使用，适合用外部表

4）普遍用法：初始資料集由外部表操作，資料分析中間表使用内部表

5.order/sort/distribute/cluster by

order by：會将所有的資料彙聚到一個reduce上去執行，然後能保證全局有序。但是效率低，因為不能并行執行

sort by：當設定mapred.reduce.tasks>1，則sort by隻保證每個reducer的輸出有序，不保證全局有序。好處是：執行了局部排序之後可以為接下去的全局排序提高不少的效率（其實就是做一次歸并排序就可以做到全局排序。

distribute by：根據指定的字段将資料分到不同的reduce，且分發算法是hash散列。能保證每一個reduce負責的資料範圍不重疊了，但是不保證排序的問題。

cluster by：除了具有distribute by的功能外，還會對該字段進行排序。

隻有一個reduce時，cluster by效果不明顯，可以執行set mapred.reduce.tasks>1來使效果明顯。

當字段相同時，cluster by效果等同于distribute by+sort by。

注意：cluster 和 sort 在查詢(select)時不能共存，建表時可以共存

6. Hive中的分區、分桶以及資料抽樣

對Hive表進行分區、分桶，可以提高查詢效率，抽樣效率

6.1分區

分區，在hdfs中表現為table目錄下的子目錄

6.2分桶

對應建表時bucket關鍵字，在hdfs中表現為同一個表目錄下根據hash散列之後的多個檔案，會根據不同的檔案把資料放到不同的桶中。如果分桶表導入資料沒有生成對應數量的檔案，可通過如下方式解決：

1.開啟自動分桶，設定參數：set hive.enforce.bucketing= true

2.手動設定reduce數量，比如set mapreduce.job.reduces=4。建議對于設計表有分桶需求時，開啟自動分桶。因為一旦reduce數量設定錯了，規劃的分桶數會無效。

注意：要用insert語句或者ctas語句将資料存入分桶表。load語句隻是檔案的移動或複制。

6.3 抽樣（sampling）

6.3.1 按塊抽樣

1）百分比

select * from some_table tablesample(40 percent);

2）按大小

select * from some_table tablesample(20M);

3）按照行數取樣

select * from some_table tablesample(1000 rows);

6.3.2 按桶抽樣

其實就是對分桶表進行抽樣，效率高。

抽樣資料量=總資料量/抽樣分桶數。

示例：select count(1) from tableA Tablesample(bucket 2 out of 8 on user_id)；即Tablesample(bucket 開始取樣的桶 out of 分成多少個桶)。

如果要進行抽樣，建議：

1.如果提前分桶了，表分桶數與抽樣分桶數一緻，那麼隻會掃描那個指定桶的資料

2.如果預先分桶和抽樣分桶數不一緻：重新分桶

3.如果沒分桶：先分桶，在抽樣

7.Hive的嚴格模式和非嚴格模式

通過設定參數hive.mapred.mode來設定是否開啟嚴格模式。目前參數值有兩個：strict（嚴格模式）和nostrict（非嚴格模式，預設）。通過開啟嚴格模式，主要是為了禁止某些查詢（這些查詢可能造成意想不到的壞的結果），目前主要禁止3種類型的查詢：

1）分區表查詢在查詢一個分區表時，必須在where語句後指定分區字段，否則不允許執行。

因為在查詢分區表時，如果不指定分區查詢，會進行全表掃描。而分區表通常有非常大的資料量，全表掃描非常消耗資源。

2）order by 查詢order by語句必須帶有limit 語句，否則不允許執行。

因為order by會進行全局排序，這個過程會将處理的結果配置設定到一個reduce中進行處理，處理時間長且影響性能。

3）笛卡爾積查詢資料量非常大時，笛卡爾積查詢會出現不可控的情況，是以嚴格模式下也不允許執行。

在開啟嚴格模式下，進行上述三種不符合要求的查詢，通常會報類似FAILED: Error in semantic analysis: In strict mode, XXX is not allowed. If you really want to perform the operation,+set hive.mapred.mode=nonstrict+

8.Hive JOIN

寫join查詢時，需要注意幾個關鍵點：1）隻支援等值join，因為非等值連接配接非常難轉化為MapReduce任務

示例：select a.* from a join b on a.id = b.id是正确的，然而:select a.* from a join b on a.id>b.id是錯誤的。

2）可以join多個表，如果join中多個表的join的列是同一個，則join會被轉化為單個MapReduce任務示例：select a.*, b.*, c.* from a join b on a.col= b.col1 join c on c.col= b.col1被轉化為單個MapReduce任務，因為join中隻使用了b.col1作為join列。

但是如下寫法會被轉化為2個MapReduce任務。因為 b.col1用于第一次join條件，而 b.col2用于第二次 join

select a.*, b.*, c.* from a join b on a.col= b.col1 join c on c.col= b.col2;

3）join時，轉換為MapReduce任務的邏輯

reduce會緩存join序列中除了最後一個表的所有表的記錄（具體看啟動了幾個map/reduce任務），再通過最後一個表将結果序列化到檔案系統。這一實作有助于在reduce端減少記憶體的使用量。實踐中，應該把最大的那個表寫在最後（否則會因為緩存浪費大量記憶體）。示例：a.單個map/reduce任務select a.*, b.*, c.* from a join b on a.col= b.col1 join c on c.col= b.col1中所有表都使用同一個join列。reduce端會緩存a表和b表的記錄，然後每次取得一個c表的記錄就計算一次join結果；b.多個map/reduce任務

select a.*, b.*, c.* from a join b on (a.col= b.col1) join c on (c.col= b.col2)。第一次緩存a表，用b表序列化；第二次緩存第一次MapReduce任務的結果，然後用c表序列化。

4）left semi join

經常用來替換 in和exists。

如，select * from a left semi join b on a.id = b.id; 相當于select * from a where a.id exists(select b.id from b);但這種方式在hive中效率極低。

9.Hive中的3種虛拟列

當Hive産生非預期的資料或null時，可以通過虛拟列進行診斷，判斷哪行資料出現問題，主要分3種：

1.INPUT__FILE__NAME

每個map任務輸入檔案名

2.BLOCK__OFFSET__INSIDE__FILE

map任務處理的資料所對應檔案的塊内偏移量，目前全局檔案的偏移量。對于塊壓縮檔案，就是目前塊的檔案偏移量，即目前塊的第一個位元組在檔案中的偏移量

3.ROW__OFFSET__INSIDE__BLOCK行偏移量，預設不可用。需要設定hive.exec.rowoffset=true來啟用

10.Hive條件判斷

Hive中可能會遇到根據判斷不同值，産生對應結果的場景，有三種實作方式：if、coalesce、case when。

1.if( condition, true value, false value)隻能用來判斷單個條件。

示例：select if(col_name='張三',1,0) as x from tab;

2.coalesce( value1,value2,… )擷取參數清單中的首個非空值，若均為null，則傳回null。

示例select coalesce(null,null,5,null,1,0) as x; 傳回5

3.case when

可以與某字段多個比較值的判斷，并分别産生不同結果，與其他語言中case文法相似。

select

case col_name

when "張三" then 1

when "李四" then 0

else 2

end as x

from tab;

或：

select

case

when col_name＝"張三" then 1

when col_name＝"李四" then 0

else 2

end as x

from tab;

11.Hive與傳統的關系型資料庫對比

Apache Hive總結

繼續閱讀

luogu1078 文化之旅

Hadoop離線_Hive的基本操作

Hive中内部表、外部表、分區、分桶以及SQL的執行順序

Hive中的内部表外部表及分區表

Hive---外部分區表的建立

Hive學習筆記 3 Hive的資料模型：内部表、分區表、外部表、桶表、視圖

HiveQL(二):分區表

Hive的分區表入門

Hive的分區表

Hive（二）--分區分桶，内部表外部表

大資料高頻面試題之Hive的小檔案合并

世界因大資料而改變

hive sql通過具體位址解析出行政區劃(省＞市＞區＞縣＞鄉＞鎮＞村)

Hive最全常見錯誤及解決方案hive --service metastore &

《Hive權威指南》第八章：HiveQL索引8 HiveQL：索引

HiveQl語句應用執行個體：WordCount具體步驟如下：