Hive最新資料操作詳解（超級詳細），hive最新資料

Hive最新資料操作詳解（超級詳細），hive最新資料
資料操作能力是大資料分析至關重要的能力。資料操作主要包括：更改(exchange)，移動(moving)，排序(sorting)，轉換(transforming)。Hive提供了諸多查詢語句，關鍵字，操作和方法來進行資料操作。



一、 資料更改資料更改主要包括：LOAD, INSERT, IMPORT, and EXPORT

1. LOAD DATAload關鍵字的作用是將資料移動到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 DELIMITEDFIELDS 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 monthFROM 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://<host_name>: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 employeeINSERT 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 BYCLUSTER 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<T>).
• 類型轉換函式：這些方法主要用於對資料類型進行轉換，如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 <function_name>;        //函數詳細描述DESCRIBE FUNCTION EXTENDED <function_name>;        //更多詳細資料

詳細範例：
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下進行測試，在下均有說明。