Hive是基于Hadoop生態(tài)的一個重要組件，是對數(shù)據(jù)倉庫進行管理和分析數(shù)據(jù)的工具。她提供了SQL查詢方式來分析存儲在HDFS分布式文件系統(tǒng)中的數(shù)據(jù)，可以將結(jié)構(gòu)化的數(shù)據(jù)文件映射為一張數(shù)據(jù)庫表，并提供完整的SQL查詢功能。

這種SQL就是Hive SQL，她可以將SQL語句轉(zhuǎn)換為Map Reduce任務(wù)運行，通過特殊的SQL去查詢分析需要的內(nèi)容，使不熟悉map reduce的用戶很方便的利用SQL 語言查詢，匯總，分析數(shù)據(jù)。

[[272867]]

一、基礎(chǔ)命令

1、數(shù)據(jù)庫操作

• show databases; # 查看某個數(shù)據(jù)庫
• use 數(shù)據(jù)庫; # 進入某個數(shù)據(jù)庫
• show tables; # 展示所有表
• desc 表名; # 顯示表結(jié)構(gòu)
• show partitions 表名; # 顯示表名分區(qū)
• show create table_name; # 顯示創(chuàng)建表的結(jié)構(gòu)

2、表結(jié)構(gòu)修改

• use xxdb; create table xxx; # 內(nèi)部表
• create table xxx like xxx; # 創(chuàng)建一個表，結(jié)構(gòu)與其他一樣
• use xxdb; create external table xxx; # 外部表
• use xxdb; create external table xxx (l int) partitoned by (d string); # 分區(qū)表
• alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 內(nèi)部表轉(zhuǎn)外部表
• alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表轉(zhuǎn)內(nèi)部表

3、字段類型

• 基本類型： tinyint, smallint, int, bigint, float, decimal, boolean, string
• 復合類型：struct, array, map

二、常用函數(shù)

• length() # 返回字符串長度
• trim() # 去除兩邊空格
• lower(), upper() # 大小寫轉(zhuǎn)換
• reverse() # 反轉(zhuǎn)字符串
• cast(expr as type) # 類型轉(zhuǎn)換
• substring(string A, int start, int len) # 字符串截取
• split(string str, string pat) # 按照pat字符串分割str，返回分割后的字符串數(shù)組
• coalesce(v1, v2, v3, ...) # 返回列表中第一個非空元素,如果所有值都為空，則返回null
• from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') # 返回當前時間
• instr(string str, string search_str) # 返回第二個參數(shù)在待查找字符串中的位置(找不到返回0)
• concat(string A, string B, string C, ...) # 字符串連接
• concat_ws(string sep, string A, string B, string C, ...) # 自定義分隔符sep的字符串連接
• str_to_map(string A, string item_pat, string dict_pat) # 將字符串轉(zhuǎn)為map
• map_keys(map m) # 提取出map的key, 返回key的array
• datediff(date1, date2) # 日期比較函數(shù)，返回相差天數(shù)，datediff('${cur_date},d)
• explode(colname) # explode就是將hive一行中復雜的array或者map結(jié)構(gòu)拆分成多行

三、相關(guān)概念

1、hive

hive是基于hadoop的一個數(shù)據(jù)倉庫工具，可以將結(jié)構(gòu)化的數(shù)據(jù)文件映射為一張數(shù)據(jù)庫庫表，并提供類SQL查詢功能。

2、基本組成

用戶接口：CLI，shell命令行;JDBC/ODBC是hive的java實現(xiàn);webGUI是通過瀏覽器訪問hive;元數(shù)據(jù)存儲：通常是存儲在關(guān)系數(shù)據(jù)庫如mysql, derby中;hive的元數(shù)據(jù)包括表的名字，表的列和分區(qū)及其屬性，表的屬性(是否為外部表)，表的數(shù)據(jù)所在目錄等。

解釋器，編譯器，優(yōu)化器完成HQL查詢語句從詞法分析，語法分析，編譯，優(yōu)化以及查詢計劃的生成。生成的查詢存儲在HDFS中，并隨后有mapreduce調(diào)用執(zhí)行。因此，hive與Hadoop的關(guān)系可以理解為用戶發(fā)出SQL查詢語句，hive將查詢存儲在HDFS中，然后由mapreduce調(diào)用執(zhí)行。

3、table

Hive 中的 Table 和數(shù)據(jù)庫中的 Table 在概念上是類似的，每一個 Table 在 Hive 中都有一個相應(yīng)的目錄存儲數(shù)據(jù)。例如，一個表 pvs，它在 HDFS 中的路徑為：/wh/pvs，其中，wh 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的數(shù)據(jù)倉庫的目錄，所有的 Table 數(shù)據(jù)(不包括 External Table)都保存在這個目錄中。

4、partition

Partition 對應(yīng)于數(shù)據(jù)庫中的 Partition 列的密集索引，但是 Hive 中 Partition 的組織方式和數(shù)據(jù)庫中的很不相同。在 Hive 中，表中的一個 Partition 對應(yīng)于表下的一個目錄，所有的 Partition 的數(shù)據(jù)都存儲在對應(yīng)的目錄中。

5、buckets

Buckets 對指定列計算 hash，根據(jù) hash 值切分數(shù)據(jù)，目的是為了并行，每一個 Bucket 對應(yīng)一個文件。將 user 列分散至 32 個 bucket，首先對 user 列的值計算 hash，對應(yīng) hash 值為 0 的 HDFS 目錄為：/wh/pvs/ds=20090801/ctry=US/part-00000;hash 值為 20 的 HDFS 目錄為：/wh/pvs/ds=20090801/ctry=US/part-00020

6、external table

External Table 指向已經(jīng)在 HDFS 中存在的數(shù)據(jù)，可以創(chuàng)建 Partition。它和 Table 在元數(shù)據(jù)的組織上是相同的，而實際數(shù)據(jù)的存儲則有較大的差異。

Table 的創(chuàng)建過程和數(shù)據(jù)加載過程(這兩個過程可以在同一個語句中完成)，在加載數(shù)據(jù)的過程中，實際數(shù)據(jù)會被移動到數(shù)據(jù)倉庫目錄中;之后對數(shù)據(jù)對訪問將會直接在數(shù)據(jù)倉庫目錄中完成。刪除表時，表中的數(shù)據(jù)和元數(shù)據(jù)將會被同時刪除。

External Table 只有一個過程，加載數(shù)據(jù)和創(chuàng)建表同時完成(CREATE EXTERNAL TABLE ……LOCATION)，實際數(shù)據(jù)是存儲在 LOCATION 后面指定的 HDFS 路徑中，并不會移動到數(shù)據(jù)倉庫目錄中。當刪除一個 External Table 時，僅刪除元數(shù)據(jù)，表中的數(shù)據(jù)不會真正被刪除。

7、全量數(shù)據(jù)和增量數(shù)據(jù)

查看分區(qū)信息：如果分區(qū)的大小隨時間增加而增加，則最新的分區(qū)為全量數(shù)據(jù)。如果分區(qū)的大小隨時間增加而大小上下變化，則每個分區(qū)都是增量數(shù)據(jù)。

四、HQL和SQL的異同

1、HQL和SQL常見不同，

• select distinct 后必須指定字段名
• join 條件僅支持等值關(guān)聯(lián)且不支持or條件
• 子查詢不能在select中使用;
• HQL中沒有UNION，可使用distinct+ union all 實現(xiàn) UNION;
• HQL以分號分隔，必須在每個語句結(jié)尾寫上分號;
• HQL中字符串的比較比較嚴格，區(qū)分大小寫及空格，因此在比較時建議upper(trim(a))=upper(trim(b))
• 日期判斷，建議使用to_date(),如：to_date(orderdate)=‘2016-07-18’
• 關(guān)鍵字必須在字段名上加``符號，如select `exchange` from xxdb.xxtb;
• 數(shù)據(jù)庫和表/視圖之間僅有1個點，如xx_db.xx_tb。

2、HQL不支持update，采用union all + left join (is null)變相實現(xiàn)update。

• 取出增量數(shù)據(jù);
• 使用昨日分區(qū)的全量數(shù)據(jù)通過主鍵左連接增量數(shù)據(jù)，并且只取增量表中主鍵為空的數(shù)據(jù)(即，取未發(fā)生變化的全量數(shù)據(jù));
• 合并1、2的數(shù)據(jù)覆蓋至最新的分區(qū)，即實現(xiàn)了update。

3、HQL不支持delete，采用not exists/left join(is null)的方法變相實現(xiàn)。

• 取出已刪除的主鍵數(shù)據(jù)(表B);
• 使用上一個分區(qū)的全量數(shù)據(jù)(表A)通過主鍵左連接A，并且只取A中主鍵為空的數(shù)據(jù)，然后直接insert overwrite至新的分區(qū)。

對于會SQL的人員，轉(zhuǎn)入Hive SQL還是比較容易的，語法大部分是想通的，少部分函數(shù)不太一致。