HiveQL是一種類似SQL的語言, 它與大部分的SQL語法相容, 但是並不完全支援SQL標準, 如HiveQL不支援更新操作, 也不支援索引和事務, 它的子查詢和join操作也很侷限, 這是因其底層依賴於Hadoop雲平臺這一特性決定的, 但其有些特點是SQL所無法企及的。例如多表查詢、支援create table as select和整合MapReduce指令碼等, 本節主要介紹Hive的資料型別和常用的HiveQL操作。
1.hive client命令
a.hive命令引數
-e: 命令列sql語句 -f: SQL檔案 -h, --help: 幫助 --hiveconf: 指定配置檔案 -i: 初始化檔案 -S, --silent: 靜態模式（不將錯誤輸出） -v,--verbose: 詳細模式
b.互動模式
hive> show tables; #檢視所有表名 hive> show tables 'ad*' #檢視以'ad'開頭的表名 hive> set 命令 #設定變數與檢視變數； hive> set -v #檢視所有的變數 hive> set hive.stats.atomic #檢視hive.stats.atomic變數 hive> set hive.stats.atomic=false #設定hive.stats.atomic變數 hive> dfs -ls #檢視hadoop所有檔案路徑 hive> dfs -ls /user/hive/warehouse/ #檢視hive所有檔案 hive> dfs -ls /user/hive/warehouse/ptest #檢視ptest檔案 hive> source file <filepath> #在client裡執行一個hive指令碼檔案 hive> quit #退出互動式shell hive> exit #退出互動式shell hive> reset #重置配置為預設值 hive> !ls #從Hive shell執行一個shell命令
2.操作及函式
檢視函式： hive> show functions; 正則檢視函式名： show functions 'xpath.*'; 檢視具體函式內容： describe function xpath; | desc function xpath;
3.欄位型別
Hive支援基本資料型別和複雜型別, 基本資料型別主要有數值型別(INT、FLOAT、DOUBLE)、布林型和字串, 複雜型別有三種:ARRAY、MAP 和 STRUCT。
a.基本資料型別
TINYINT: 1個位元組
SMALLINT: 2個位元組
INT: 4個位元組
BIGINT: 8個位元組
BOOLEAN: TRUE/FALSE
FLOAT: 4個位元組，單精度浮點型
DOUBLE: 8個位元組，雙精度浮點型STRING 字串
b.複雜資料型別
ARRAY: 有序欄位
MAP: 無序欄位
STRUCT: 一組命名的欄位
4.表型別
hive表大致分為普通表、外部表、分割槽表三種。
a.普通表
建立表 hive> create table tb_person(id int, name string); 建立表並建立分割槽欄位ds hive> create table tb_stu(id int, name string) partitioned by(ds string); 檢視分割槽 hive> show partitions tb_stu; 顯示所有表 hive> show tables; 按正規表示式顯示錶， hive> show tables 'tb_*'; 表新增一列 hive> alter table tb_person add columns (new_col int); 新增一列並增加列欄位註釋 hive> alter table tb_stu add columns (new_col2 int comment 'a comment'); 更改表名 hive> alter table tb_stu rename to tb_stu; 刪除表(hive只能刪分割槽，不能刪記錄或列 ) hive> drop table tb_stu; 對於託管表, drop 操作會把後設資料和資料檔案刪除掉, 對於外部表, 只是刪除後設資料。如果只要刪除表中的資料, 保留表名可以在 HDFS 上刪除資料檔案: hive> dfs –rmr /user/hive/warehouse/mutill1/*
將本地/home/hadoop/ziliao/stu.txt檔案中的資料載入到表中, stu.txt檔案資料如下：
1 zhangsan
2 lisi
3 wangwu
將檔案中的資料載入到表中
hive> load data local inpath '/home/hadoop/ziliao/stu.txt' overwrite into table tb_person;
載入本地資料，同時給定分割槽資訊
hive> load data local inpath '/home/hadoop/ziliao/stu.txt' overwrite into table tb_stu partition (ds='2008-08-15');
備註：如果匯入的資料在 HDFS 上,則不需要 local 關鍵字。託管表匯入的資料檔案可在資料倉儲目錄“user/hive/warehouse/<tablename>”中看到。
檢視資料
hive> dfs -ls /user/hive/warehouse/tb_stu hive> dfs -ls /user/hive/warehouse/tb_person
b.外部表
external關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑(location)，hive建立內部表時，會將資料移動到資料倉儲指向的路徑；若建立外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變。在刪除表的時候，內部表的後設資料和資料會被一起刪除，而外部表只刪除後設資料，不刪除資料。
eg. 建立外部表：
create external table tb_record(col1 string, col2 string) row format delimited fields terminated by '\t' location '/user/hadoop/input';
這樣表tb_record的資料就是hdfs://user/hadoop/input/* 的資料了。
c.分割槽表
分割槽是表的部分列的集合, 可以為頻繁使用的資料建立分割槽, 這樣查詢分割槽中的資料時就不需要掃描全表, 這對於提高查詢效率很有幫助。
建立分割槽：create table log(ts bigint,line string) partitioned by(name string);
插入分割槽：insert overwrite table log partition(name='xiapi') select id from userinfo where name='xiapi';
檢視分割槽：show partitions log;
刪除分割槽: alter table ptest drop partition (name='xiapi')
備註:通常情況下需要先預先建立好分割槽,然後才能使用該分割槽。還有分割槽列的值要轉化為資料夾的儲存路徑,所以如果分割槽列的值中包含特殊值,如 '%', ':', '/', '#',它將會被使用%加上 2 位元組的 ASCII 碼進行轉義。
5. sql操作及桶
1). 建立表
首先建立三張測試表:
userinfo表中有兩列,以tab鍵分割,分別儲存使用者的id和名字name;
classinfo表中有兩列,以tab鍵分割,分別儲存課程老師teacher和課程名classname;
choice表中有兩列,以tab鍵分割,分別儲存使用者的userid和選課名稱classname(類似中間表)。
建立測試表:
hive> create table userinfo(id int,name string) row format delimited fields terminated by '\t'; hive> create table classinfo(teacher string,classname string) row format delimited fields terminated by '\t'; hive> create table choice(userid int,classname string) row format delimited fields terminated by '\t';
注意：'\t'相當於一個tab鍵盤。
顯示剛才建立的資料表:
hive> show tables;
2). 匯入資料
建表後,可以從本地檔案系統或 HDFS 中匯入資料檔案,匯入資料樣例如下:
userinfo.txt內容如下(資料之間用tab鍵隔開)：
1 xiapi
2 xiaoxue
3 qingqing
classinfo.txt內容如下(資料之間用tab鍵隔開)：
jack math
sam china
lucy english
choice.txt內容如下(資料之間用tab鍵隔開)：
1 math
1 china
1 english
2 china
2 english
3 english
首先在本地“/home/hadoop/ziliao”下按照上面建立三個檔案, 並新增如上的內容資訊。
3. 按照下面匯入資料。
hive> load data local inpath '/home/hadoop/ziliao/userinfo.txt' overwrite into table userinfo; hive> load data local inpath '/home/hadoop/ziliao/classinfo.txt' overwrite into table classinfo; hive> load data local inpath '/home/hadoop/ziliao/choice.txt' overwrite into table choice;
查詢表資料
hive> select * from userinfo; hive> select * from classinfo; hive> select * from choice;
4. 分割槽
a.建立分割槽 hive> create table ptest(userid int) partitioned by (name string) row format delimited fields terminated by '\t'; b.準備匯入資料 xiapi.txt內容如下(資料之間用tab鍵隔開)： 1 c.匯入資料 hive> load data local inpath '/home/hadoop/ziliao/xiapi.txt' overwrite into table ptest partition (name='xiapi'); d.檢視分割槽 hive> dfs -ls /user/hive/warehouse/ptest/name=xiapi; e.查詢分割槽 hive> select * from ptest where name='xiapi'; f.顯示分割槽 hive> show partitions ptest; g.對分割槽插入資料(每次都會覆蓋掉原來的資料): hive> insert overwrite table ptest partition(name='xiapi') select id from userinfo where name='xiapi'; h.刪除分割槽 hive> alter table ptest drop partition (name='xiapi')
5.桶
可以把表或分割槽組織成桶, 桶是按行分開組織特定欄位, 每個桶對應一個 reduce 操作。在建立桶之前, 需要設定“hive.enforce.bucketing”屬性為 true, 使 Hive 能夠識別桶。在表中分桶的操作如下:
hive> set hive.enforce.bucketing=true; hive> set hive.enforce.bucketing; hive.enforce.bucketing=true; hive> create table btest2(id int, name string) clustered by(id) into 3 buckets row format delimited fields terminated by '\t';
向桶中插入資料, 這裡按照使用者 id 分了三個桶, 在插入資料時對應三個 reduce 操作,輸出三個檔案。
hive> insert overwrite table btest2 select * from userinfo;
檢視資料倉儲下的桶目錄,三個桶對應三個目錄。
hive> dfs -ls /user/hive/warehouse/btest2;
Hive 使用對分桶所用的值進行 hash,並用 hash 結果除以桶的個數做取餘運算的方式來分桶,保證了每個桶中都有資料,但每個桶中的資料條數不一定相等,如下所示。
hive>dfs -cat /user/hive/warehouse/btest2/*0_0; hive>dfs -cat /user/hive/warehouse/btest2/*1_0; hive>dfs -cat /user/hive/warehouse/btest2/*2_0;
分桶可以獲得比分割槽更高的查詢效率,同時分桶也便於對全部資料進行取樣處理。下面是對桶取樣的操作。
hive>select * from btest2 tablesample(bucket 1 out of 3 on id);
6. 多表插入
多表插入指的是在同一條語句中, 把讀取的同一份後設資料插入到不同的表中。只需要掃描一遍後設資料即可完成所有表的插入操作, 效率很高。多表操作示例如下。
hive> create table mutill as select id,name from userinfo; #有資料 hive> create table mutil2 like mutill; #無資料，只有表結構 hive> from userinfo insert overwrite table mutill select id,name insert overwrite table mutil2 select count(distinct id),name group by name;
7. 連線
連線是將兩個表中在共同資料項上相互匹配的那些行合併起來, HiveQL 的連線分為內連線、左向外連線、右向外連線、全外連線和半連線 5 種。
a. 內連線(等值連線)
內連線使用比較運算子根據每個表共有的列的值匹配兩個表中的行。
例如, 檢索userinfo和choice表中標識號相同的所有行。
hive> select userinfo.*, choice.* from userinfo join choice on(userinfo.id=choice.userid);
b. 左連線
左連線的結果集包括“LEFT OUTER”子句中指定的左表的所有行, 而不僅僅是連線列所匹配的行。如果左表的某行在右表中沒有匹配行, 則在相關聯的結果集中右表的所有選擇列均為空值。
hive> select userinfo.*, choice.* from userinfo left outer join choice on(userinfo.id=choice.userid);
c. 右連線
右連線是左向外連線的反向連線,將返回右表的所有行。如果右表的某行在左表中沒有匹配行,則將為左表返回空值。
hive> select userinfo.*, choice.* from userinfo right outer join choice on(userinfo.id=choice.userid);
d. 全連線
全連線返回左表和右表中的所有行。當某行在另一表中沒有匹配行時,則另一個表的選擇列表包含空值。如果表之間有匹配行,則整個結果集包含基表的資料值。
hive> select userinfo.*, choice.* from userinfo full outer join choice on(userinfo.id=choice.userid);
e. 半連線
半連線是 Hive 所特有的, Hive 不支援 IN 操作,但是擁有替代的方案; left semi join, 稱為半連線, 需要注意的是連線的表不能在查詢的列中,只能出現在 on 子句中。
hive> select userinfo.* from userinfo left semi join choice on (userinfo.id=choice.userid);
8. 子查詢
標準 SQL 的子查詢支援巢狀的 select 子句,HiveQL 對子查詢的支援很有限,只能在from 引導的子句中出現子查詢。如下語句在 from 子句中巢狀了一個子查詢(實現了對教課最多的老師的查詢)。
hive>select teacher,MAX(class_num) from (select teacher, count(classname) as class_num from classinfo group by teacher) subq group by teacher;
9. 檢視操作
目前,只有 Hive0.6 之後的版本才支援檢視。
Hive 只支援邏輯檢視, 並不支援物理檢視, 建立檢視可以在 MySQL 後設資料庫中看到建立的檢視表, 但是在 Hive 的資料倉儲目錄下沒有相應的檢視表目錄。
當一個查詢引用一個檢視時, 可以評估檢視的定義併為下一步查詢提供記錄集合。這是一種概念的描述, 實際上, 作為查詢優化的一部分, Hive 可以將檢視的定義與查詢的定義結合起來,例如從查詢到檢視所使用的過濾器。
在檢視建立的同時確定檢視的架構,如果隨後再改變基本表(如新增一列)將不會在檢視的架構中體現。如果基本表被刪除或以不相容的方式被修改,則該檢視的查詢將被無效。
檢視是隻讀的,不能用於 LOAD/INSERT/ALTER。
檢視可能包含 ORDER BY 和 LIMIT 子句,如果一個引用了檢視的查詢也包含這些子句,那麼在執行這些子句時首先要檢視檢視語句,然後返回結果按照檢視中的語句執行。
以下是建立檢視的例子:
hive> create view teacher_classsum as select teacher, count(classname) from classinfo group by teacher;
刪除檢視：
hive>drop view teacher_classnum;
10. 函式
建立函式
hive> create temporary function function_name as class_name
該語句建立一個由類名實現的函式。在 Hive 中使用者可以使用 Hive 類路徑中的任何類,使用者通過執行 add files 語句將函式類新增到類路徑,並且可持續使用該函式進行操作。
刪除函式
登出使用者定義函式的格式如下:
hive> drop temporary function function_name;