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

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

[[272867]]

一、基礎(chǔ)命令

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

• show databases; # 查看某個(gè)數(shù)據(jù)庫(kù)
• use 數(shù)據(jù)庫(kù); # 進(jìn)入某個(gè)數(shù)據(jù)庫(kù)
• 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)建一個(gè)表，結(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
• 復(fù)合類型：struct, array, map

二、常用函數(shù)

• length() # 返回字符串長(zhǎng)度
• 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，返回分割后的字符串?dāng)?shù)組
• coalesce(v1, v2, v3, ...) # 返回列表中第一個(gè)非空元素,如果所有值都為空，則返回null
• from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') # 返回當(dāng)前時(shí)間
• instr(string str, string search_str) # 返回第二個(gè)參數(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一行中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行

三、相關(guān)概念

1、hive

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

2、基本組成

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

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

3、table

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

4、partition

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

5、buckets

Buckets 對(duì)指定列計(jì)算 hash，根據(jù) hash 值切分?jǐn)?shù)據(jù)，目的是為了并行，每一個(gè) Bucket 對(duì)應(yīng)一個(gè)文件。將 user 列分散至 32 個(gè) bucket，首先對(duì) user 列的值計(jì)算 hash，對(duì)應(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í)際數(shù)據(jù)的存儲(chǔ)則有較大的差異。

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

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

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

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

四、HQL和SQL的異同

1、HQL和SQL常見不同，

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

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

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

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

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

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