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

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

[[272867]]

一、基礎命令

1、數據庫操作

• show databases; # 查看某個數據庫
• use 數據庫; # 進入某個數據庫
• show tables; # 展示所有表
• desc 表名; # 顯示表結構
• show partitions 表名; # 顯示表名分區
• show create table_name; # 顯示創建表的結構

2、表結構修改

• use xxdb; create table xxx; # 內部表
• create table xxx like xxx; # 創建一個表，結構與其他一樣
• use xxdb; create external table xxx; # 外部表
• use xxdb; create external table xxx (l int) partitoned by (d string); # 分區表
• alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 內部表轉外部表
• alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表轉內部表

3、字段類型

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

二、常用函數

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

三、相關概念

1、hive

hive是基于hadoop的一個數據倉庫工具，可以將結構化的數據文件映射為一張數據庫庫表，并提供類SQL查詢功能。

2、基本組成

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

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

3、table

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

4、partition

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

5、buckets

Buckets 對指定列計算 hash，根據 hash 值切分數據，目的是為了并行，每一個 Bucket 對應一個文件。將 user 列分散至 32 個 bucket，首先對 user 列的值計算 hash，對應 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 指向已經在 HDFS 中存在的數據，可以創建 Partition。它和 Table 在元數據的組織上是相同的，而實際數據的存儲則有較大的差異。

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

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

7、全量數據和增量數據

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

四、HQL和SQL的異同

1、HQL和SQL常見不同，

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

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

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

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

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

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