一、前言
Hive是Hadoop上的資料倉儲框架，其設計目的是讓精通SQL技能（但Java程式設計技能相對較弱）的分析師能夠在存放到HDFS大規模資料集上執行查詢。提出Hive的主要原因是SQL並不是所有的“大資料”的理想工具。
Hive在工作站上執行，它把SQL轉換為一系列在Hadoop叢集上執行的MapReduce作業，即用MapReduce操作HDFS資料。Hive把資料組織為表，透過這種方式為儲存在HDFS上的資料賦予結構。後設資料——如表模式——儲存在名為metastore的資料庫中。
Hive的metastore預設儲存在本地機器上，這樣就無法和其他使用者共享這些定義。後面將會詳細講述如何在生產環境中設定遠端共享metastore。
二、HiveQL
使用者透過在Hive的直譯器互動，發出HiveQL命令，HiveQL是Hive的查詢語言，它是SQL的一種方言，和mysql有很大的相似之處。
1、基本命令：
a、顯示metastore資料庫中的表：
hive> show tables; OK Time taken: 1.528 seconds hive> show tables; OK Time taken: 0.126 seconds hive>
系統採用”懶“策略，第一次比較慢，當建立metastore資料庫後載入的就比較快了。該資料庫存放在你執行hive命令所在位置下名為metastore_db的目錄中。
我的Hive metastore_db的目錄位置如下：
[root@master admin]# find / -name metastore_db /usr/lib64/R/metastore_db
b.執行Hive指令碼
對於較長的指令碼，通常儲存在.q檔案中，在shell環境下用如下命令執行：
% hive -f script.q
如果對於較短的指令碼，可以使用-e命令在行內嵌入執行，這兩種情況下都不需要加上表示結束的分號。
% hive -e "select * from dummy"
下面是一個生成單行表的方法：
[root@master admin]# echo 'X' > /tmp/dummy.txt [root@master admin]# cat /tmp/dummy.txt X [root@master admin]# hive -e "create table dummy(value STRING);\ > load data local inpath '/tmp/dummy.txt' \ > overwrite into table dummy" Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hive/lib/hive-common-0.10.0-cdh4.3.0.jar!/hive-log4j.properties Hive history file=/tmp/root/hive_job_log_450335d5-274c-4082-81a7-0b4bbe8d1c0c_944836217.txt OK Time taken: 2.321 seconds Copying data from file:/tmp/dummy.txt Copying file: file:/tmp/dummy.txt Loading data to table default.dummy rmr: DEPRECATED: Please use 'rm -r' instead. Moved: 'hdfs://master:8020/user/hive/warehouse/dummy' to trash at: hdfs://master:8020/user/root/.Trash/Current Table default.dummy stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 2, raw_data_size: 0] OK Time taken: 1.369 seconds [root@master admin]#
無論是在shell命令還是在互動式環境下，Hive都會把操作執行的時間列印到標準錯誤輸出，可以在啟動程式的時候使用- S選項強制不限時這條訊息，其結果只是查詢輸出結果。
帶-S的和不帶的對比結果如下：
[root@master admin]# hive -S -e 'select * from dummy' X [root@master admin]# hive -e 'select * from dummy' Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hive/lib/hive-common-0.10.0-cdh4.3.0.jar!/hive-log4j.properties Hive history file=/tmp/root/hive_job_log_44207314-384b-4f29-a590-834c723b96ad_444463715.txt OK X Time taken: 2.173 seconds [root@master admin]#
備註：
其他有用的Hive互動式程式的特性有：使用a!字首來執行宿主作業系統的命令；使用dfs來訪問hadoop檔案系統。
c、示例
和RDMS一樣，Hive把資料組織成表，下面我們用create table語句為氣象資料建立一個表格（各行換行符分隔，用'\t'分隔欄位）：
create table records (year string, temperature int, quality int) row format delimited fields terminated by '\t';
建立表格完成後，我們可以向Hive中輸入資料，overwrite關鍵字告訴Hive刪除表所對應的目錄下的所有檔案，如果省略overwrite，Hive就簡單的把新檔案加入目錄，如果有同名檔案就替換掉，其他的不作處理。
load data local inpath 'input/ncdc/micro-tab/sample.txt' overwrite into table records;
這樣命令告訴Hive把指定的本地檔案放到它的儲存目錄中，這僅僅是一個簡單的檔案系統操作，不解析檔案，也不會將其轉換為內部格式，這是因為hive並不強制星星某種特定的檔案格式。檔案以原樣子逐字儲存，Hive對檔案沒有做任何修改。
Hive的表儲存在HDFS中，由（fs.default.name 設為預設值file:///），在Hive的倉庫目錄中，表儲存為目錄。倉庫錄由hive.metastore.warehouse.dir設定，預設值為/user/hive/warehouse，在HDFS的根目錄下。
Hive的查詢語句：
hive> select year, max(temperature) > from records > where temperature!=9999 > and (quality=0 or quality=1 or quality=4 or quality=5 or quality=9) > group by year;
d、 多個Hive 共享hadoop叢集
如果準備讓多個Hive使用者共享一個Hadoop叢集，則需要更改Hive所使用目錄的許可權，對所有使用者可寫。用以下命令建立，並設定合適的許可權：
% hadoop -mkdir /tmp % hadoop -chmod a+w /tmp % hadoop -mkdir /user/hive/warehouse % hadoop -chmod a+w /user/hive/warehouse
如果所有使用者在同一個使用者組中，把倉庫目錄許可權設定為g+w即可。
e、在一個會話中使用SET命令更改設定
hive> set hive.enforce.bucketing=true;
可以只使用帶屬性名的SET命令檢視屬性的當前值：
hive> set hive.enforce.bucketing;
設定屬性的優先順序，數值越小，優先順序越高。
1、Hive SET命令
2、命令列-hiveconf選項
3、hive-site.xml
4、hive-default.xml
5、hadoop-site.xml（或等價的core-site.xml、hdfs-site.xml、mapred-site.xml）
6、hadoop-default.xml（或等價的core-default.xml、hdfs-default.xml、mapred-default.xml）
可以對日誌的配置進行設定，下面的語句可以方便的將除錯資訊傳送到控制檯:
% hive -hiveconf hive.root.logger=DEBUG, console
e、metastore
metastore是Hive的後設資料的集中存放地，metastore包括兩部分：後臺和資料庫的儲存。預設情況下，metastore服務和hive服務執行在同一個JVM中，它包含一個內嵌的以本地磁碟作為儲存的Derby資料庫例項，使用內嵌資料庫是Hive入門最簡單的方法，在侷限是一次只能訪問一個磁碟上的資料檔案，這就意味著一次只能為每一個metastore開啟一個hive會話，如果啟動兩個會話時就會報如下錯誤：
Failed to start database 'metastore_db'
若果要支援多會話，就要配置使用一個獨立的資料庫，這種配置稱為"本地metastore"。
f、HiveQL和SQL的比較
SQL的延遲級別為秒級，而HiveQL延遲為分鐘級別。HiveSQL支援create table as select語法，而SQL不支援。SQL支援儲存過程，而HiveSQL支援使用者定義函式，MapReduce指令碼。
Hive的string類似其他資料庫中的VARCHAR，但不能宣告儲存長度，最長可以儲存2GB字元數（理論上）。當然這樣做效率較低，可以使用Sqoop對大物件的處理。
可以使用CAST進行資料型別轉換，例如CAST('1' AS INT)，可以把字串'1'轉換為整數1。如果轉換失敗，那麼表示式會返回空（CAST ('X' AS INT)）。
g、複雜型別
Hive有三種複雜資料型別：array、map、struct，複雜資料型別必須用尖括號"<>"指明其中資料欄位的型別。
如下表所示的表定義有三列，每一種對應一種複雜的資料型別：
create table complex( col1 arrary<int>, col2 map<string, int>, col3 struct<a:string, b:int, c:double> );
下面是展示每種資料型別的訪問操作：
hive>select col1[0], col2['b'], col3.c from complex;
h、操作與函式
可以透過hive shell下面鍵入show functions獲取函式列表，用describe function length獲取函式幫助。
提供普通的SQL操作：關係操作（x='a'，空值判斷x is null，模式匹配 x like 'A%'），算數操作（x+1），以及邏輯或（or），如x or y。MySql和Hive中字串連線使用concat函式。
i、表
Hive表格邏輯上由儲存的資料和描述表格中資料形式的相關資料組成。資料一般存放在HDFS中，當然也可以放在本地檔案系統中，而把後設資料放在關聯式資料庫中。
資料庫支援命令空間，0.90的hive也支援名稱空間，提供了create database dbname， use dbname以及drop database dbname這樣的語句。
託管表和外部表
這兩種表的區別表現在load和drop命令的語義上。
載入託管表時，Hive把資料移動到倉庫目錄，例如;
create table managed_table(dummy string); load data inpath '/user/tom/data.txt' into table managed_table;
把檔案從hdf://user/tom/data.txt 移動到hive的資料倉儲目錄managed_table表的目錄，即hdfs://user/hive/warehouse/managed_table。
如果隨後要刪除一個表格，可以用
drop table managed_table;
它的表（包括資料和後設資料）會一起被刪除，這就是hive所謂的”資料託管的含義“。
而對於外部表而言，這兩個操作結果就不一樣了，使用者來控制資料的建立和刪除。外部資料的位置要在建立表格的時候說明：
create external table external_table(dummy string) location '/user/tom/external_table'; load data inpath '/user/tom/data.txt' into table external_table;
使用external關鍵字之後，hive知道資料並不由自己管理，因此不會把資料移動到自己的倉庫目錄。丟棄外部表時，Hive不會碰資料，僅刪除後設資料。
經驗法則：所有的資料都在hive中完成，使用託管表，如果使用Hive和其他工具共同處理一個資料集，應該使用外部表。普遍的做法是把存放在hdfs（由其他程式建立）的初始資料集用作外部表，然後使用hive的變換功能把資料移動到託管的hive表，可以使用hive匯出資料供其他應用程式使用。
j、分割槽和桶
hive把表組織成分割槽（partition），根據分割槽列（partition column，如日期）對錶進行粗略劃分的機制。使用分割槽可以加快資料分片（slice）的查詢速度。分割槽可以進一步劃分為桶（bucket）。它會為資料提供額外的結構以獲得更搞笑的查詢處理。例如，透過根據使用者ID來劃分桶，我們可以在所有使用者集合的隨機樣本上快速計算基於使用者的查詢。
使用分割槽並不會影響大範圍查詢的執行，我們依然可以查詢跨多個分割槽的整個資料集合。
對於假想的日誌檔案，在根據日期對日誌進行分割槽外，還可以能根據國家對每個分割槽進行子分割槽（subpartition），以加速根據地理位置進行查詢。分割槽在建立表格的時候用partitioned by子句定義，該子句需要定義列的列表。我們可能要把表記錄定義為由時間戳和日誌行構成：
create table logs(ts bigint, line string) partitioned by (dt string, country string);
在我們把資料載入到分割槽表的時候要顯示指定分割槽值：
load data local inpath 'input/hive/partitions/file1' into table logs partition (dt='2010-01-01', country='GB');
在檔案系統級別，分割槽只是表示目錄下巢狀的子目錄。把更多檔案載入到日誌表以後，目錄結構可能像下面這樣：
/user/hive/warehouse/logs/dt=2010-01-01/country=GB/file1 /file2 /country=US/file3 /user/hive/warehouse/logs/dt=2010-01-02/country=GB/file4 /country=US/file5 /file6
可以使用show partitions 表名 命令查詢表中有哪些分割槽：
hive> show partitions logs; dt=2010-01-01/country=GB dt=2010-01-01/country=US dt=2010-01-02/country=GB dt=2010-01-02/country=US
注意：partitioned by 子句中的列定義是表中正式的列，稱為“分割槽列”（partition column）。但是資料檔案並不包含這些列的值，因為它們源於目錄名。
實際使用的過程中可以以普通方式使用分割槽列。Hive會對輸入進行修剪，從而只掃描相關分割槽。例如:
select ts, dt, line from logs where country='GB';
將只掃描file1、file2、file4。還要注意，這個查詢也返回dt分割槽列的值。這個值是hive從目錄名中讀取的，因為他們在資料檔案中並不存在。
桶：
把表（或分割槽）組織成桶（bucket）有兩個理由。第一理由是獲得更高效的查詢處理效率，第二個理由是取樣更高效。
使用clustered by子句來指定劃分桶所在的列和要劃分為桶的個數：
create table bucketed_users (id int, name string) clustered by (id) into 4 buckets;
可以使用HiveQL對兩個劃分了桶的表進行連線。
桶中的資料可以根據一個或者多個列另外進行排序，所以這樣對每個桶的連線就程式設計了高效的合併排序，因此可以進一步提升map端連線的效率。以下語法說明一個連線使其使用排序桶。
create table bucketed_users(id int, name string) clustered by (id) sorted by (id asc) into 4 buckets;
有兩種方式將表中的資料劃分成桶：一是將Hive外的資料載入到劃分成桶的表中，二是針對已有的表可以用Hive來劃分桶。
建議用hive來劃分桶，以為hive不檢查資料檔案中的桶是否和表定義中的桶一致。
有個沒有劃分桶的表格：
hive> select * from user; OK 0 Nat 2 Joe 3 Kay 4 Ann Time taken: 2.178 seconds
要將桶內填充成員，需要將hive.enforce.bucketing的屬性設定為true。
這樣hive就知道使用表定義中生命的變數來建立桶。然後使用insert命令建立即可：
hive> insert overwrite table bucketed_users > select * from user;
物理上每個桶就是表（或分割槽）裡的一個檔案。但是桶n是按照字典排列的第n個檔案。事實上，桶對應於MapReduce的輸出檔案分割槽，一個作業產生的桶（輸出檔案）和reduce任務個數相等。從下面執行的MapReduce程式中可以看出：
Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 4 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number> In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number> In order to set a constant number of reducers: set mapred.reduce.tasks=<number> Starting Job = job_201306210458_0022, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201306210458_0022 Kill Command = /opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/bin/hadoop job -kill job_201306210458_0022 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 4
檢視bucketed_users表的佈局可以得到
hive> dfs -ls /user/hive/warehouse/bucketed_users; Found 4 items -rw-r--r-- 2 admin supergroup 12 2013-06-22 22:14 /user/hive/warehouse/bucketed_users/000000_0 -rw-r--r-- 2 admin supergroup 0 2013-06-22 22:14 /user/hive/warehouse/bucketed_users/000001_0 -rw-r--r-- 2 admin supergroup 6 2013-06-22 22:14 /user/hive/warehouse/bucketed_users/000002_0 -rw-r--r-- 2 admin supergroup 6 2013-06-22 22:14 /user/hive/warehouse/bucketed_users/000003_0
4個新建的檔案
具體桶內的資料如下所示：
hive> dfs -cat /user/hive/warehouse/bucketed_users/*0_0; 0Nat 4Ann hive> dfs -cat /user/hive/warehouse/bucketed_users/*1_0; hive> dfs -cat /user/hive/warehouse/bucketed_users/*2_0; 2Joe hive> dfs -cat /user/hive/warehouse/bucketed_users/*3_0; 3Kay
使用tablesample子句對錶進行取樣，我們可以獲得相同的結果。這個子句會把查詢限定在表的一部分桶內，而不是整個表：
hive> select * from bucketed_users > tablesample(bucket 1 out of 4 on id); ........... OK 0 Nat 4 Ann Time taken: 7.289 seconds
1/4 第一個桶，下面的查詢會會返回1/2桶：
hive> select * from bucketed_users > tablesample(bucket 1 out of 2 on ; ............. OK 0 Nat 4 Ann 2 Joe Time taken: 10.367 seconds
當然，可以用其他比例對若干個桶進行取樣，因為取樣並不是一個精確的操作，因此這個比例一定是桶的整數倍。
用此取樣分桶表是非常高效的操作，如果使用rand()函式對沒有劃分成桶的表進行取樣，及時只需要讀取很小的一部分樣本，也要輸入整個資料集。所以rand的特點是掃描次數多，效率低，用下面查詢結果耗時和上面對比可以得出。
hive> select * from bucketed_users > tablesample(bucket 1 out of 4 on rand()); ...................... OK Time taken: 10.379 seconds
k、儲存格式
最簡單的是文字格式，同時支援面向行的和麵向列的二進位制格式。
分隔符的問題，create table ……語句等價於下面的語句：
create table row format delimited fields terminated by '\001' collection iterms terminated by '\002' map keys terminated by '\003' lines terminated by '\n' stored as textfile;
注意，可以使用八進位制表示分隔符，例如001表示Control-A。
支援順序檔案Sequence File和RCFile，按列記錄檔案。
在Hvie中可以使用下面的句子還啟用面向列的儲存：
create table ........... row format serde 'org.apache.hadoop.hive.seder2.columnar.ColumnarSerDe' store as RCFile;
示例：利用正規表示式從一個文字檔案中讀取定長的觀測站資料
create table station (usaf string, wban, name string) row format serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' with serdeproperties ( "input.regex"="(\\d{6}) (\\d{5}) (.{29}) .*");
SerDe要設定相應的屬性值，在這裡要設定RegexSerDe特有的input.regex屬性。
用load data向表中輸入資料：
load data local inpath "input/ncdc/metadata/stations-fixed-with.txt" into table station;
載入操作並不適用表的SerDe。
從表中檢索資料時，用簡單的查詢所示，反序列化會呼叫SerDe解析這個欄位：
hive> select * from stations limit 4; 10000 99999 BOGUS NORWAY 010003 99999 BOGUS NORWAY 010010 99999 JAN MAYEN 010013 99999 ROST
l、匯入資料
如果想把資料從關聯式資料庫直接匯入hive，請參考Sqoop。
insert overwrite table
insert overwrite table target select col1, col2 from source;
對於分割槽的表，可以使用partition子句來指明
insert overwrite table target partition (dt='2010-01-01') select col1, col2 from source;
使用overwrite會替換掉目標，而如果要向已經填充了內容的表新增記錄，可以使用不帶overwrite 關鍵字的load data操作。
從hive 0.6.0開始，可以在select 語句中透過使用分割槽值來動態指明分割槽：
insert overwrite table target partition dt select col1, col2, dt from source;
這種方法稱為動態分割槽插入法，這一特性預設是關閉的，可以透過命令set 命令檢視開啟，將hive.exec.dynamic.partition=true。
當前hive不支援使用insert into values (.......)的形式。
多表插入：
在hive中，可以把insert語句倒過來，把from語句放在最前面，查詢的效果是一樣的：
from source insert overwrite table target select col1, col2;
在一個表查詢中，可以使用多個insert的語句，只要掃描一遍表，就可以生成多個不相交的輸出。
FROM records2 INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;
這裡有一個源表，三個目標表。
create table ......as select.......
create table target as select col1, col2 from source;
m、表的修改
重新命名錶
alter table source rename to target;
新增新的列
alter table target add columns (col3 string);
n、表的丟棄
drop table刪除表的後設資料和資料
也可以僅僅刪除資料檔案，保留表結構，預設為空表
hive > dfs -rmr /user/hive/warehouse/my_table;
這時候，可以使用like關鍵字建立一個與第一個表模式相同的新表：
create table new_table like existing_table;
o、查詢資料
排序和聚集
可以用order by子句對資料進行全域性排序，但是它只是用一個reducer完成的。在多數情況下不需要全域性排序的情況下，可以使用sort by區域性排序，為每一個reduce產生一個排序檔案。有些時候需要控制某個特定行應該到哪個reducer，通常是為了後續的聚集操作，這就是hive的 distribute by所做的事情，下面的例子根據年份和氣溫對氣象資料進行排序，以確保所有年份所在的行，最終都在一個reduce分割槽中。
hive> from records2 >select year, temperature > distribute by year > sort by year asc, temperature desc;
1949 111
1949 78
1950 22
1950 0
1950 11
如果sort by 和distribute by中所用到的列相同，可以縮寫為cluster by一遍同時制定兩者相同的列。
使用Hadoop Streaming 、transform 、map、reduce子句這樣的方法，便可以在hive中呼叫外部指令碼。
p、連線
同mapreduce相比，hive的好處是簡化了常用操作。
內連線：
hive> SELECT * FROM sales; Joe 2 Hank 4 Ali 0 Eve 3 Hank 2 hive> SELECT * FROM things; 2 Tie 4 Coat 3 Hat 1 Scarf
sales:人名及所購商品的id，sales:上平id及名稱
hive> SELECT sales.*, things.*
> FROM sales JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
hive只支援等值連線，條件是兩個表的id必須相等。
與資料庫不同，hive不支援where子句，且只允許在from子句中出現一個表。
用explain 關鍵字可以查詢連線的過程中使用多少個MapReduce，可以用explain extended檢視更詳細的資訊。
EXPLAIN SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);
外連線：
使用left outer join，查詢會返回左側表（sales）中的每一個資料行，及時這些行與這各表索要連線的表（things）中的任何資料對應。
hive> SELECT sales.*, things.*
> FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
Ali 0 NULL NULL
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
hive也支援right outer join右外連線
hive> SELECT sales.*, things.*
> FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
NULL NULL 1 Scarf
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
全外連線，full outer join
hive> SELECT sales.*, things.*
> FROM sales FULL OUTER JOIN things ON (sales.id = things.id);
Ali 0 NULL NULL
NULL NULL 1 Scarf
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
半連線：
下面的in 子句能夠查詢things表中在sales表中出現過的所有商品：
select *
from things
where things.id in (select id from sales);
但是hive不支援in子句查詢，所以要對其進行改寫，改寫後如下：
hive> select * from things; OK 2 Tie 4 Coat 3 Hat 1 Scarf Time taken: 0.282 seconds hive> select * from sales; OK Joe 2 Hank 4 Ali 0 Eve 3 Hank 2 Time taken: 0.227 seconds
對上述表操作，左半連線，只要左半自身。
hive> select * > from things left semi join sales on (sales.id=things.id); .................. OK 2 Tie 3 Hat 4 Coat Time taken: 14.528 seconds
map連線：
如果要指定使用map連線，需要在sql中使用C語言風格的註釋，從而給出提示：
select /* mapjoin(things) */ sales.*, things.* from sales join things on (sales.id=things.id);
執行這個查詢不適用reducer，只在所有輸入上進行聚集，使用下面的語法啟用最佳化選項：set hive.optimize.bucketmapjoin=true;
q、子查詢
hive對子查詢支援有限，只允許出現在select 語句的from子句中。
下面的語句可以查出每年每個氣象站最高氣溫的均值：
SELECT station, year, AVG(max_temperature) FROM ( SELECT station, year, MAX(temperature) AS max_temperature FROM records2 WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = GROUP BY station, year ) mt GROUP BY station, year;
內層查詢查詢出每個氣象站的最高氣溫，外層查詢使用AVG聚集函式計算這些最高讀數的均值。