Hive最新數據操作詳解（超級詳細）
數據操作能力是大數據分析至關重要的能力。數據操作主要包括：更改(exchange)，移動(moving)，排序(sorting)，轉換(transforming)。Hive提供了諸多查詢語句，關鍵字，操作和方法來進行數據操作。
一、 數據更改 數據更改主要包括：LOAD, INSERT, IMPORT, and EXPORT
1. LOAD DATA load關鍵字的作用是將數據移動到hive中。如果是從HDFS加載數據，則加載成功後會刪除源數據；如果是從本地加載，則加載成功後不會刪除源數據。
數據：employee_hr.txt http://pan.baidu.com/s/1c0D9TpI
例：
hive>（不用輸入，在此表示在Hive的shell輸入以下命令，下同） CREATE TABLE IF NOT EXISTS employee_hr( name string, employee_id int, sin_number string, start_date timestamp ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE;
例： hive> LOAD DATA LOCAL INPATH '/apps/ca/yanh/employee_hr.txt' OVERWRITE INTO TABLE employee_hr;
注1：在指令中LOCAL關鍵字用於指定數據從本地加載，如果去掉該關鍵字，默認從HDFS進行加載！ OVERWRITE關鍵字指定使用覆蓋方式進行加載數據，否則使用附加方式進行加載。
注2：如果數據加載到分區表，則必須指定分區列。
2. INSERT 同RDBMS一樣，Hive也支持從其他hive表提取數據插入到指定表，使用INSERT關鍵字。INSERT操作是Hive數據處理中最常用的將已有數據填充進指定表操作。在Hive中，INSERT可以和OVERWRITE一起使用實現覆蓋插入，可以進行多表插入，動態分區插入以及提取數據至HDFS或本地。
例： hive> CREATE TABLE ctas_employee AS SELECT * FROM employee; TRUNCATE TABLE employee； //刪除employee中的數據，保留表結構
例：
hive> INSERT INTO TABLE employee SELECT * FROM ctas_employee;
注：這裡使用Hive提供的beeline工具進行連接，以便清晰的顯示數據表。
例：從CTE插入數據 hive> WITH a AS (SELECT * FROM ctas_employee) FROM a INSERT OVERWRITE TABLE employee SELECT *; //效果和上例相同 注：Hive從0.13.0版本開始支持CTE
例：多表插入 hive> CREATE TABLE employee_internal LIKE employee; FROM ctas_employee INSERT OVERWRITE TABLE employee SELECT * INSERT OVERWRITE TABLE employee_internal SELECT *; SELECT * FROM employee_internal;
Hive除了支持向靜態分區插入靜態數據，還支持插入動態數據，如日期
例：動態分區插入 動態分區默認是關閉的，可通過以下設置開啟：SET hive.exec.dynamic.partition=true; Hive默認至少需要一個partition列是靜態的，可以通過以下設置關閉：SET hive.exec.dynamic.partition.mode=nonstrict; hive> INSERT INTO TABLE employee_partitioned PARTITION(year,month) SELECT name,array('Toronto') AS work_place, named_struct("sex","Male","age",30) AS sex_age, map("Python",90) AS skills_score, map("R&D",array('Developer')) AS depart_title, year(start_date) AS year, month(start_date) AS month FROM employee_hr eh WHERE eh.employee_id = 102;
例：
hive> SELECT * FROM employee_partitioned;
例：提取數據至本地（默認使用^A分離列，換行符分離行） 注：Hive提取數據只能使用OVERWRITE, 不能使用INTO。 注：在一些Hadoop版本中目錄深度只支持到2層，可以使用以下設置修復：SET hive.insert.into.multilevel.dirs=true; hive> INSERT OVERWRITE LOCAL DIRECTORY '/apps/ca' SELECT * FROM employee;
注：默認下Hive會將數據按reducer數量生成多個輸出文件，可以使用以下命令進行合並： hdfs dfs -getmerge hdfs://
:port/user/output/directory
例：使用特定分隔符分隔行 hive> INSERT OVERWRITE LOCAL DIRECTORY '/apps/ca/yanh/data' ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' SELECT * FROM employee; 
例：Hive同樣能多目錄輸出文件 hive> FROM employee INSERT OVERWRITE LOCAL DIRECTORY '/apps/ca/yanh/data1' SELECT * INSERT OVERWRITE LOCAL DIRECTORY '/apps/ca/yanh/data2' SELECT * ; 
3. EXPORT and IMPORT 這兩條命令是Hive用來和HDFS進行數據遷移或者進行數據備份的，從Hive0.8.0開始可用。EXPORT可以導出數據以及元數據到HDFS，元數據被命名為_metadata. 數據被放在命名為data的目錄下
例： hive>
EXPORT TABLE employee TO '/apps/ca/yanh/data'; 
注：輸出目錄不能已存在
例：將輸出的數據導入Hive(將數據導入到已有的表將報錯) hive>
IMPORT FROM '/apps/ca/yanh/data'; 
例：導入到新表（也可以是EXTERNAL表） hive>
IMPORT TABLE employee_imported FROM '/apps/ca/yanh/data'; 
例：partition表導入導出 hive>
EXPORT TABLE employee_partitioned PARTITION (year=2015,month=05) TO '/apps/ca/yanh/data1'; 
例：
hive>
IMPORT TABLE employee_partitioned_imported FROM '/apps/ca/yanh/data1'; 
二、 數據排序 數據排序主要包括：ORDER, and SORT. 該操作同樣經常使用，以便生成已排序表從而進行後面的包括top N, maximum, minimum等取值操作。 主要操作包括ORDER BY (ASC|DESC)、SORT BY(ASC|DESC)、DISTRIBUTE BY、CLUSTER BY
1. ORDER BY (ASC|DESC) 跟RDBMS的ORDER BY操作類似，該操作輸出一個全局排序的結果，因此reducer的輸出結果僅有一個，所以如果大數據量下過程是十分漫長的！這時可以時候LIMIT關鍵字提高輸出效率。如果Hive設置hive.mapred.mode = strict，那LIMIT關鍵字將不可使用（默認是可以使用的）。
例：按名字從大到小排序（如果數據量大，可以在最後加上LIMIT n來顯示前n行） hive>
SELECT name FROM employee ORDER BY name DESC; 
2. SORT BY(ASC|DESC) 與ORDER BY (ASC|DESC)操作不同，SORT BY(ASC|DESC)操作僅輸出局部有序的結果（即多個reducer輸出，每個輸出有序）。如果要輸出全局有序，可以通過SET mapred.reduce.tasks=1;來制定reducer個數為1. 此時效果與ORDER BY (ASC|DESC) 相同。SORT BY指定列排序，可以在數據從mapper端全部傳入之前完成排序（只要該列傳輸完畢）。
例： hive>
SET mapred.reduce.tasks=2; SELECT name FROM employee SORT BY name DESC; 
設置2個reducer，可以看到結果並不是從大到小排列的。
例： hive>
SET mapred.reduce.tasks=1; SELECT name FROM employee SORT BY name DESC; 
設置1個reducer，此時與ORDER BY結果相同！
3. DISTRIBUTE BY 該操作類似於RDBMS中的GROUP BY，根據制定的列將mapper的輸出分組發送至reducer，而不是根據partition來分組數據。 注：如果使用了SORT BY，那麼必須在DISTRIBUTE BY之後，且要分發的列必須出現在已選擇的列中（因為SORT BY的性質）。
例：為選擇employee_id，出錯 hive>
SELECT name FROM employee_hr DISTRIBUTE BY employee_id; 
例： hive> SELECT name,employee_id FROM employee_hr DISTRIBUTE BY employee_id SORT BY name; 
4. CLUSTER BY CLUSTER BY類似於DISTRIBUTE BY和SORT BY的組合作用（作用於相同列）,但不同於ORDER BY的是它僅在每個reducer進行排序，而不是全局排序，且不支持ASC和DESC。如果要實現全局排序，可以先進行CLUSTER BY然後再ORDER BY。
例： hive> SELECT name, employee_id FROM employee_hr CLUSTER BY name; 
ORDER BY和CLUSTER BY不同之處如下圖所示： 
三、 數據操作和方法 為了更進一步的數據操作，我們可以對Hive進行諸如表達式、操作、方法等來對數據進行轉換。在Hive wiki https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF上已經對每一個表達式和方法進行了規范。同時Hive也已經定義了一些關系型的操作、算術運算操作、邏輯運算操作、復雜類型構造器以及復雜類型操作。對於其中的關系型的操作、算術運算操作、邏輯運算操作而言，和SQL/Java中的標准操作比較類似。 Hive中的方法大致可以分為以下幾類：
數學函數：這些方法主要用於數學計算，如RAND()何E().
匯總函數：這些方法主要用於對復雜類型進行size、key、value等的查詢，如SIZE(Array).
類型轉換函數：這些方法主要用於對數據類型進行轉換，如CAST和BINARY.
日期函數：用於對日期相關進行操作，如YEAR(string date)和MONTH(string date).
條件函數：用於返回特定條件過濾後的函數，如COALESCE、IF、和CASE WHEN.
字符串函數：此類函數主要用於字符串相關操作，如UPPER(string A) 和TRIM(string A).
聚合函數：此類函數主要用於數據聚合，如SUM()，COUNT(*).
列表生成函數：此類函數主要用於將單行輸入轉換為多行輸出，如EXPLODE(MAP)和JSON_TUPLE(jsonString, k1, k2, ...).
自定義函數：此類由Java生成的函數作為Hive的擴展函數對Hive功能進行擴展.
可以在Hive CLI使用以下語句進行Hive內建函數查詢： SHOW FUNCTIONS; //列出Hive所有函數 DESCRIBE FUNCTION ; //函數詳細描述 DESCRIBE FUNCTION EXTENDED ; //更多詳細信息
詳細樣例：
1. 復雜數據類型函數提示：SIZE函數用於計算MAP、ARRAY或嵌套MAP/ARRAY。如果size未知則返回-1.
例： hive> SELECT work_place, skills_socre, depart_title FROM employee; 
例： hive> SELECT SIZE(work_place) AS array_size, SIZE(skills_score) AS map_size, SIZE(depart_title) AS complex_size, SIZE(depart_title["Product"]) AS nest_size FROM employee; 
ARRAY_CONTAINS聲明用於使用TRUE或FALSE返回值檢驗指定列是否包含指定值。SORT_ARRAY聲明用於對數組進行升序排序。
例： hive> SELECT ARRAY_CONTAINS(work_place, 'Toronto') AS is_Toronto, SORT_ARRAY(work_place) AS sorted_array FROM employee; 
2. 日期函數提示：FROM_UNIXTIME(UNIX_TIMESTAMP())聲明與Oracle中的SYSDATE函數相同，動態返回Hive服務器的當前時間。
例： hive> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()) AS current_time FROM employee limit 1; 
TO_DATE用於將獲取的系統時間截取日期
例： hive> SELECT TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) AS current_date FROM employee limit 1; 
3. 多種不同數據類型的CASE：在Hive0.13.0版本之前THEN或者ELSE後面的數據類型必須相同。否則可能會產生異常值，如ELSE後的數據類型必須同THEN。此問題在0.13.0之後得到修復。
例：由於數據類型不同造成異常返回 hive> SELECT CASE WHEN 1 IS NULL THEN 'TRUE' ELSE 0 END AS case_result FROM employee LIMIT 1; 
4. 解析和查找：LATERAL VIEW是用來生成用戶自定義表以展開的形式顯示map或array的值，如同EXPLODE()，但其會忽略值為NULL的列，如果要顯示這些列，可以使用LATERAL VIEW OUTER(Hive0.12.0之後版本)
例： hive> INSERT INTO TABLE employee SELECT 'Steven' AS name, array(null) AS work_place, named_struct("sex","Male","age",30) AS sex_age, map("Python", 90) AS skills_score, map("R&D",array('Developer')) AS depart_title FROM employee LIMIT 1; SELECT name, work_place, skills_score FROM employee; 
例： hive> SELECT name, workplace, skills, score FROM employee LATERAL VIEW explode(work_place) wp AS workplace LATERAL VIEW explode(skills_score) ss AS skills, score; 
例： hive> SELECT name, workplace, skills, score FROM employee LATERAL VIEW OUTER explode(work_place) wp AS workplace LATERAL VIEW explode(skills_score) ss AS skills, score; 
REVERSE用於將指定字符串進行反轉，SPLIT用於將字符串按指定分隔符進行分隔。
例： hive> SELECT reverse(split(reverse('/apps/ca/yanh/employee.txt'),'/')[0]) AS linux_file_name FROM employee LIMIT 1; 
REVERSE將輸出轉換為單獨元素，而COLLECT_SET和COLLECT_LIST則是反過來將元素組合成集合進行輸出。COLLECT_SET和COLLECT_LIST的不同在COLLECT_SET返回的集合不含重復元素，而COLLECT_LIST則可以包含重復元素。
例： hive> SELECT collect_set(work_place[0]) AS flat_wprkplace FROM employee; 
例：
hive> SELECT collect_list(work_place[0]) AS flat_wprkplace FROM employee; 
注：Hive0.11.0及以前不支持collect_list
5. 虛擬列：虛擬列是Hive中特殊的列的特殊函數類型。目前為止Hive僅支持2個虛擬列：INPUT_FILE_NAME和BLOCK_OFFSET_INSIDE_FILE。INPUT_FILE_NAME列是mapper的輸入文件名，BLOCK_OFFSET_INSIDE_FILE是當前全部文件位置或當前壓縮文件的塊偏移量。
例： hive> SELECT INPUT_FILE_NAME, BLOCK_OFFSET_INSIDE_FILE AS OFFSIDE FROM employee_id; 注：在Hive0.13.0上測試失敗，沒有該函數。
6. wiki未提到的函數：
例：isnull，用於檢驗值是否為空 hive> SELECT work_place, isnull(work_place) is_null, isnotnull(work_place) is_not_null FROM employee; 
例：assert_true，如果條件為false時拋出異常 hive> SELECT assert_true(work_place IS NULL) FROM employee; 
例：elt，返回第n個字符串 hive> SELECT elt(2, 'New York', 'Beijing', 'Toronto') FROM employee LIMIT 1; 
例：current_database，返回當前數據庫名 hive> SELECT current_database(); 
注：Hive0.11.0及以前沒有此函數
四、 數據轉換 在Hive0.13.0以前不支持行級的數據轉換。因此，數據行的更新、插入、刪除都不能實現。因此數據重寫只能發生在表或者分區，這使得Hive很難處理並發讀寫和數據清洗的情況。但是從0.13.0開始，Hive提供了原子性、一致性、隔離性和持久性（ACID）的行級數據處理功能。如今所有的轉換操作支持ORC（優化排柱狀，從Hive0.11.0開始支持）文件和桶列表中的數據。
以下配置參數需要適當的配置以開啟Hive的轉換功能： SET hive.support.concurrency = true; SET hive.enforce.bucketing = true; SET hive.exec.dynamic.partition.mode = nonstrict; SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; SET hive.compactor.initiator.on = true; SET hive.compactor.worker.threads = 1;
SHOW TRANSACTIONS可以對當前已開啟的轉換操作進行查詢： hive> SHOW TRANSACTIONS;
從Hive0.14.0開始，行級插入數值、更新和刪除可以使用以下語法規則進行實現： INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row …]; UPDATE tablename SET column = value [, column = value…] [WHERE expression]; DELETE FROM tablename [WHERE expression];
結語 以上便是全部Hive的關於數據的具體操作，相信到此為止應該能對Hive的常規數據操作進行較為得心應手的使用了吧。以上有截圖的用例均由本人親測可行，測試環境為Hive0.11.0，部分Hive0.13.0的特性是在0.13.0下進行測試，在截圖下均有說明。
