Hive基礎語法
1、建立表 – 使用者表
CREATE [EXTERNAL外部表] TABLE [IF NOT EXISTS 是否存在] HUserInfo ( userid int comment ‘使用者Id’, username string comment ‘使用者名稱稱’, userpwd string comment ‘使用者密碼’, createtime string comment ‘建立時間’ ) comment ‘使用者資訊表’ row format delimited fileds terminated by ‘\t’ -- 宣告檔案行分隔符 partitioned by (ds string comment '當前時間，用於分割槽欄位') --表示按什麼欄位進行分割，通常來說是按時間。用於增加分割槽 clustered by userid --要排序的欄位 sorted by username into 32 buckets -- bucket(桶)排，序這裡表示將id按照name進行排序，聚類彙總，然後分割槽劃分到32個雜湊桶中。 stored as rcfile
hive目前支援三種方式：
1）就是最普通的textfile，資料不做壓縮，磁碟開銷大，解析開銷也大
2）SquenceFIle,hadoop api提供的一種二進位制API方式，其具有使用方便、可分割、可壓縮等特點。
3）rcfile行列儲存結合的方式，它會首先將資料進行分塊，保證同一個record在一個分塊上，避免讀一次記錄需要讀多個塊。其次塊資料列式儲存，便於資料儲存和快速的列存取。RCFILE由於採用是的列式儲存，所以載入時候開銷較大，但具有很好的查詢響應、較好的壓縮比。
location '/user/hive/test'; --改變表在hdfs中的位置，可不必先建立。
like Old_UserInfo --複製一個空表，允許使用者複製現有的表結構，但是不復制資料。
2、mysql日期函式：sysdate()
3、ODS與EDW的關係
EDW主要為企業提供分析決策服務。時效：T+1
ODS主要實現企業資料整合、共享和準實時運營監控等功能。時效：實時
ODS是EDW的一個有益的補充和擴充套件。
生產系統中的運營資料透過ETL(抽取、轉換、裝載)過程進人到ODS中，生產系統之間準實時的資料交換由ODS系統完成，ODS系統同時還將整合好的生產系統下的運營資料透過ETL等方式傳送到EDW中，完成運營資料從操作環境進人到分析環境的過程。
ODS作用：
1）對運營資料進行清理整合，提高運營資料質量，是EDW的一個主要資料來源。
2）實現跨系統的近實時報表和查詢統計應用。ODS從不同的運營應用系統中採集資料，整合各個系統的共享交易資料，形成企業級資料的整體檢視。
3）作為其他生產系統的資料同步源。
4、Hive分割槽（partition）簡介
1）在Hive Select查詢中一般會掃描整個表內容，會消耗很多時間做沒必要的工作。有時候只需要掃描表中關心的一部分資料，因此建表時引入了partition概念。
2）分割槽表指的是在建立表時指定的partition的分割槽空間。
3）如果需要建立有分割槽的表，需要在create表的時候呼叫可選引數partitioned by，詳見表建立的語法結構。
4）一個表可以擁有一個或者多個分割槽，每個分割槽以資料夾的形式單獨存在表資料夾的目錄下。
5、Map-side Join和Reduce-side Join的使用場景
1）Map-side Join使用場景是一個大表和一個小表的連線操作，其中，“小表”是指檔案足夠小，可以載入到記憶體中。該演算法可以將join運算元執行在Map端，無需經歷shuffle和reduce等階段，因此效率非常高。
2）Reduce-side Join當兩個檔案/目錄中的資料非常大，難以將某一個存放到記憶體中時，Reduce-side Join是一種解決思路。該演算法需要透過Map和Reduce兩個階段完成，在Map階段，將key相同的記錄劃分給同一個Reduce Task（需標記每條記錄的來源，便於在Reduce階段合併），在Reduce階段，對key相同的進行合併。
6、cast 型別轉化
select cast(20 ad double) from hive;
7、Hive建表：一個表可以擁有一個或多個分割槽，每個分割槽以資料夾的形式單獨存在表資料夾的目錄下。
8、Hive的最佳化思想：
1）儘早儘量過濾資料，減少每個階段的資料量
2）減少job數
3）解決資料傾斜問題
9、列裁剪：HQL最佳化方式及使用技巧：只選擇真實需要的列。
分割槽裁剪：HQL最佳化方式及使用技巧：減少不必要的分割槽。
10、利用hive 的最佳化機制減少JOB數：
HQL最佳化方式及使用技巧：不論是外關聯outer join還是內關聯inner join，如果Join的key相同，不管有多少個表，都會合併為一個MapReduce任務。
SELECT a.val, b.val, c.valFROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key1 ) -> 1個JOB
SELECT a.val, b.val, c.valFROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key2) -> 2個JOB
11、JOB輸入輸出最佳化：
HQL最佳化方式及使用技巧：善用muti-insert、union all，不同表的union all相當於multiple inputs，同一個表的union all，相當map一次輸出多條。
insert overwrite table tmp1 select ... from a where 條件1; insert overwrite table tmp2 select ... from a where 條件2;
from a insert overwrite table tmp1 select ... where 條件1 insert overwrite table tmp2 select ... where 條件2;
12、Local Model：
Select user,itemfrom order_tablelimit 10; Select * from order_tablelimit 10; (不會生成mapreduce程式)
13、HQL最佳化方式及使用技巧：避免笛卡爾積
14、資料過濾：HQL最佳化方式及使用技巧：在JOIN前過濾掉不需要的資料
15、小表放前大表放後原則：
HQL最佳化方式及使用技巧：在編寫帶有join操作的程式碼語句時，應該將條目少的表/子查詢放在Join運算子的左邊。因為在Reduce 階段，位於Join 運算子左邊的表的內容會被載入進記憶體，載入條目較少的表可以有效減少OOM（out of memory）即記憶體溢位。所以對於同一個key來說，對應的value值小的放前，大的放後。
16、Mapjoin()：
HQL最佳化方式及使用技巧：當小表與大表JOIN時，採用mapjoin,即在map端完成。同時也可以避免小表與大表JOIN 產生的資料傾斜。
17、LEFT SEMI JOIN ：
HQL最佳化方式及使用技巧：LEFT SEMI JOIN 是IN/EXISTS 子查詢的一種更高效的實現，0.13版本以前不支援IN/EXISTS。
18、LEFT SEMI JOIN ：
HQL最佳化方式及使用技巧
限制條件：只能在ON 子句中設定過濾條件，在WHERE子句、SELECT 子句或其他地方過濾都不行。
Left semi join 與JOIN 的區別：B表有重複值的情況下left semi join 產生一條，join 會產生多條。
19、輸入輸出最佳化：合理使用動態分割槽
20、輸入輸出最佳化：union all 最佳化
HQL最佳化方式及使用技巧：利用hive對UNION ALL的最佳化的特性(0.13版本可以直接union)hive對union all最佳化只侷限於非巢狀查詢。
21、輸入輸出最佳化：合理使用union all
HQL最佳化方式及使用技巧：不同表太多的union ALL,不推薦使用；通常採用建臨時分割槽表，將不同表的結果insert到不同的分割槽(可並行)，最後再統一處理。
22、輸入輸出最佳化：合理使用UDTF
HQL最佳化方式及使用技巧：select col1,col2,newCol from myTableLATERAL VIEW explode(myCol) adTableAS newCol
說明：執行過程相當於單獨執行了兩次讀取，然後union到一個表裡，但JOB數只有一個。
同樣myCol也需要為陣列型別，但日常中我們多數情況下是string 型別經過split 函式拆分後獲取陣列型別。
23、輸入輸出最佳化：多粒度計算最佳化
HQL最佳化方式及使用技巧：應用UDTF 最佳化：按不同維度進行訂單彙總。
select * from ( select ‘1',province,sum(sales) from order_tablegroup by province union all select ‘2',city,sum(sales) from order_tablegroup by city Union all select ‘3',county,sum(sales) from order_tablegroup by county ) df
24、資料去重與排序：DISTINCT 與GROUP BY
HQL最佳化方式及使用技巧：儘量避免使用DISTINCT 進行排重，特別是大表操作，用GROUP BY 代替。
25、資料去重與排序：排序最佳化
HQL最佳化方式及使用技巧：只有order by 產生的結果是全域性有序的，可以根據實際場景進行選擇排序。
1）Order by 實現全域性排序，一個reduce實現，由於不能併發執行，所以效率偏低，適用於資料量小的場景。
2）Distribute by：控制著在map端如何分割槽，按照什麼欄位進行分割槽，控制map端的均衡分配。
3）Sort by 實現部分有序，每個reduce按照sort by 欄位進行排序，每個reduce輸出的結果是有序的，效率高，通常和DISTRIBUTE BY關鍵字一起使用（DISTRIBUTE BY關鍵字可以指定map 到reduce端的分發key）
例如：distribute by total_sale sort by total_sale desc
4）CLUSTER BY col1 等價於DISTRIBUTE BY col1 SORT BY col1 但不能指定排序規則。
26、資料傾斜：
HQL最佳化方式及使用技巧：任務進度長時間維持在99%（或100%），檢視任務監控頁面，發現只有少量（1個或幾個）reduce子任務未完成。因為其處理的資料量和其他reduce差異過大。單一reduce的記錄數與平均記錄數差異過大，通常可能達到3倍甚至更多。最長時長遠大於平均時長。
27、Hive有用的新特性：
1）指定列之間的分隔符可以用下面的命令實現：
hive> insert overwrite local directory /home/dd_edw/documents/result' hive> row format delimited hive> fields terminated by '\t' hive> select * from test;
2）group by 語法增強，group by除了可以跟column alias，也可以跟column position
比如：
select f1(col1), f2(col2), f3(col3), count(1) \ group by f1(col1), f2(col2), f3(col3);
可以寫成
select f1(col1), f2(col2), f3(col3), count(1) group by 1, 2, 3;
3）ALTER VIEW view_nameAS select_statement
4）SHOW CREATE TABLE ([db_name.]table_name|view_name)
5）Explain dependency 語法, 獲取input table和input partition
6）實現了TRUNCATE，可以刪除HDFS上面相關表格儲存的資料，但是
會保持表和metadata的完整性。
28、Hive建庫使用RCFile格式，反正就是高效，Facebook再用。
29、基於java的框架：資料庫mysql mongodb redis，持久層ibatis，框架springMVC+spring/Autofac，
表現層JDF JQ velocity。
30、Hive動態分割槽設定：set hive.exec.dynamic.partition.mode=nonstrict;
31、python分析常用的模組：numpy, pandas, matplotlib, scipy
32、mapjoin解決資料傾斜
使用場景：語句中B表有30億行記錄，A表只有100行記錄，而且B表中資料傾斜特別嚴重，有一個key上有15億行記錄，在執行過程中特別的慢，而且在reduece的過程中遇有記憶體不夠而報錯。
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key
33、LEFT SEMI JOIN 是IN/EXISTS 子查詢的一種更高效的實現
1）透過left outer join 實現in 查詢
select a.key, a.value from a left outer join b on a.key=b.keywhere b.keyis not null
2）透過left semi join 實現in
SELECT a.key, a.value FROM a LEFT SEMI JOIN b on (a.key= b.key)
注：限制條件只能在ON 子句中設定過濾條件，在WHERE子句、SELECT 子句或其他地方過濾都不行。
Left semi join 與JOIN 的區別：B表有重複值的情況下left semi join 產生一條，join 會產生多條。
34、hive explode函式
——行轉列
35、Hive表還有一個TBLPROPERTIES用來給表新增一些描述資訊，比如最後一次修改資訊，最後一個修改人。
如：TBLPROPERTIES ('creator'='zhoumei', 'created_at'='2012-01-02 10:00:00')
36、Hive distribute by sorl by order by 的區別：
distribute by：控制著在map端如何分割槽，按照什麼欄位進行分割槽，要注意均衡。
sort by：每個reduce按照sort by 欄位進行排序，reduce的數量按照預設的數量來進行，當然可以指定。
最終可以進行歸併排序得出結果。
適用於資料量比較大的排序場景。
order by：reduce只有一個，在一個reduce中完成排序，使用於資料量小的場景。