Hive總結及優化

1.hive是什么？

hive是基于Hadoop的一個資料倉庫工具，可以將結構化的資料檔案映射為一張資料庫表，并提供簡單的sql查詢功能，可以將sql陳述句轉換為MapReduce任務進行運行， 其優點是學習成本低，可以通過類SQL陳述句快速實作簡單的MapReduce統計，不必開發專門的MapReduce應用，十分適合資料倉庫的統計分析，
Hive是建立在 Hadoop 上的資料倉庫基礎構架，它提供了一系列的工具，可以用來進行資料提取轉化加載（ETL），這是一種可以存盤、查詢和分析存盤在 Hadoop 中的大規模資料的機制，Hive 定義了簡單的類 SQL 查詢語言，稱為 HQL，它允許熟悉 SQL 的用戶查詢資料，同時，這個語言也允許熟悉 MapReduce 開發者的開發自定義的 mapper 和 reducer 來處理內建的 mapper 和 reducer 無法完成的復雜的分析作業，

2.Hive的三種模式
資料庫:access,virtutal fox,sqlserver,mysql,sqlite,postgresql,oracle;
Local模式.此模式連接到一個In-Memory的資料庫Derby,一般用于UnitTest.
單用戶模式.通過網路連接到一個資料庫中,是最常使用到的模式.
多用戶模式:遠程服務器模式.用于非java客戶端訪問元資料庫(metastore),在服務器端啟動metaStoreServer,客戶端利用thrift協議通過metaStoreServer訪問元資料庫

3.hive的組成部分
Metastore:元資料的存盤;元資料(資料庫和表結構,列);mysql中
一個檔案:除了檔案內容以外的叫做元資料;(放到了namenode上)
Cli:client:客戶端;hive的黑視窗
Jdbc:連接的
Webgui:

用戶介面主要有三個:Cli,jdbc和WebGUI,其中最常用的是cli,cli啟動的時候會同時啟動一個hive副本,client是hive客戶端,用戶連接到hiveServer.在啟動client模式的時候,需要指出hiveServer所在的節點,并且在該節點啟動hiveServer.WUI是通過瀏覽器也能訪問Hive
Hive將元資料(資料庫,表)存盤在資料庫表(真實的資料庫,mysql)中,如mysql,derby.hive中元資料包含表的名字,表的列和磁區及其屬性,表的屬性(是否為外部表等),表的資料所在的目錄等.資料庫(Mysql)中并不存盤Hive的記錄;
解釋器,編譯器,優化器完成sql查詢陳述句從詞法分析,語法分析,編譯,優化以及查詢計劃的生成.生成的查詢計劃存盤在hdfs中,并在隨后由mapreduce呼叫執行.
Hive資料存盤在hdfs中,大部分資料查詢,計算由mapreduce完成(包含*的查詢,比如select* from tbl不會生成mapreduce任務)
元資料(hive中看到的庫,表)存盤到真實的資料庫(mysql);記錄存盤到了hdfs上; 經常使用的mysql:表,記錄,庫都存盤到檔案系統中(NTFS)
hive的客戶端連接服務器走的是thrift協議;====http==https;傳輸的內容大小比較小;

4.型別

5.CRUD
修改表名：
alter table 表名 rename to 新表名
# 修改列名
alter table change 列名 新列名 資料型別
# 添加新列,和替換列
alter table 表名
add columns
(
sex smallint,
updateTime timestamp
);

6.檔案格式
mysql底層存盤的檔案格式
Ibd:表引擎是innerdb;默認是支持事務的;
Myd,myi,sdi:引擎是myism;不支持事務;
# 開啟事務;
start transaction ;
# 可以寫一堆的sql陳述句(更新操作,添加操作,洗掉操作);
#提交;確認木有問題,提交
Commit;
# 回滾;(撤銷)
rollback

7.hive底層的檔案格式
Avro:
ORC:
Parquet:目錄結構(父子)

總結：
壓縮比越高，越省空間（磁盤空間）；但是解壓和壓縮的時候浪費了CPU+記憶體（機器性能），浪費了時間
壓縮比越低或者不壓縮；浪費空間，但是不需要解壓和壓縮，省了機器性能;

Load data命令只適用于表的檔案格式是textFile;

如果表的格式為textFile,直接將txt上傳到表指定的目錄中,就可以直接查詢出來;
可以先創建目錄,上傳檔案,再創建表;

8.查看表結構
Desc 表名;
desc extended 表名 ;
desc formatted 表名;

9.創建表--as
10.創建表--like
# 使用like創建的表,目標表和源表的結構一樣,木有資料;
11.Truncate
截斷表:團滅;表里面的記錄全部干掉;;直接洗掉檔案
Delete;相當于洗掉檔案中的某個內容;
12.創建型別復雜表
-- 地址;容器,泛型
address array<string>,
-- map愛好;容器,泛型
hobby map<string,string>,

-- 陣列的拆分
collection items terminated by '-'
-- map
map keys terminated by ':'

陣列的拆分和map的拆分,并木有指定是哪一列,是所有的陣列都是用-拆分,所有的map都是用:拆分
Map:鍵值對,使用:隔開,多個鍵值對使用集合-;

13.磁區
磁區,磁區的規則,指定列進行磁區,磁區的列不允許出現在小括號中;(磁區可以有多個)
partitioned by (sex string,adress string)

14.視窗函式
-- 將查詢出的陳述句,對記錄的內容發生了變化
-- 查詢的結果,要分組;(partition);
-- 這一列的值,lead:往下;當前行的值,請填寫下一行;
-- lag:默認往前走一個,數字可以指定;
select *,lag(name,2) over (partition by dynastyId) from a_king ;
Over:條件;拿著當前執行的sql陳述句執行結果拍一張照片;在這些結果上進行處理;
分析函式

-- 需求
-- select dynastyId,count(*) from a_king group by dynastyId ;
-- row_number:查詢的結果,進行分組;對每一組的結果進行編號;
select *, row_number() over (partition by dynastyId) from a_king ;
-- 每一個朝代,只列出前兩個皇上;(topn)
select * from (select *, row_number() over (partition by dynastyId) as bh from a_king ) t
where bh < 3 ;
select *, row_number() over (partition by dynastyId order by id desc ) as bh from a_king

15.特殊的查詢
Grouping sets;
Cubes:(立方體)
Rollups:(筒)
Grouping__ID function
對于每一列，如果該列已在該行中聚合，則為結果集中的一行生成“1”值，否則值為“0”
當我們沒有統計某一列時，它的值顯示為null，這可能與列本身就有null值沖突，這就需要一種方法區分是沒有統計還是值本來就是null，（寫一個排列組合的演算法，就馬上理解了，grouping_id其實就是所統計各列二進制和）
假設表里面有三列；a,b,c;在寫sql陳述句統計的時候,只統計了a,b;group by a , b;c這一列木有管,在hive中c這一列的值為null;
問題:c本身就是nu ll值（hive統計的時候變成Null值，不是對的）；c它原來有值，直接就變成了null值，這不公平；相當于把原來的資料給覆寫掉;

16.Udf-udtf-udaf
UDF:User Defined Function;(用戶自定義函式);常見的函式
UDAF:User Defined Aggregate(聚合) Functions:count,sum,avg;輸入的資料是多條的,輸出的資料是一條的;
UDTF:User Defined Table-Generating(表格生成) Functions ;輸入是一條,輸出是多條;

如果要執行多個sql陳述句咋辦?將多個sql陳述句寫到一個sql檔案中
bin/hive -f '/root/demo.sql'
-S:不把sql陳述句執行的結果輸出出來;
# 執行hdfs上面的sql檔案;
bin/hive -f hdfs://jh/hw/hive_sql.sql

hive（ETL提取轉化加載）優化：
一、hql陳述句優化：
1.strict mode
嚴格查詢開啟后，會限制3個查詢：
1.磁區查詢，不加磁區欄位過濾條件，不能執行
2.limit
3.order by：不使用limit陳述句，不能執行
4.限制笛卡爾積的查詢，不使用where時，join用on
2.要學會看explain 和 explain extended，后者會額外列印出hql的抽象語法樹
決議器要做的事情就是將hql陳述句變成抽象語法樹
一個stage是一個mapreduce任務，一個任務可以有多個map和reduce
一個hql陳述句包含有一個或多個stage，多個stage之間是相互依賴的，
hive默認只執行一個stage，也可以設定無依賴的并行運行
3.limit性能調優：
是否開啟使用limit優化
4.join優化：
1.永遠是小表驅動大表
2.join笛卡爾積查詢時，盡量加where過濾（嚴格模式必加）
3.磁區表查詢時，where條件中加上磁區欄位過濾
5.group by：分組，一般和聚合函式搭配使用
having是對結果集進一步的過濾
order by：全域排序
sort by：區域排序
6.distinct：去重
7.union all：將多個結果集連接在一起，不去重排序
union：將多個結果集連接在一起，去重排序
8.job個數優化：
hive 默認一個查詢或子查詢或 group by 等陳述句生成一個 job
job 數量盡量少
9.盡量少排序：
排序操作會消耗較多的 CPU，會影響 sql 的回應時間

10.盡量避免 select *：
用什么欄位取什么欄位，減少不必要的資源浪費
11.盡量用 join 代替子查詢：
雖然 join 性能并不佳，但比起 mysql 的子查詢好的多
12.優先優化高并發的 sql，而不是執行頻率低的“大”sql：
對于破壞性來說，高并發的 sql 總是會比低頻率的來的快，它不會給任何喘息機會就會把系統擊垮，
而頻率低的，至少會給我們緩沖的機會
13.從全域出發優化，而不是片面調整：
sql 優化不能單獨針對某一個進行，要考慮所有的 sql
14.盡量少 or：
當 where 子句中存在多個條件以或并存時，mysql 優化器也沒有很好的解決其執行計劃問題，
必要時用 union all 或 union 代替
15.永遠為每張表設定ID：
設定 ID 為主鍵，最好是int型的，并設定自動增加的 auto_increment 標志
16.多配置幾臺服務器

二、mapreduce優化：
17.本地模式：
hive運行模式就需要mr，mr分為韋分布式，單機版，分布式
也有一個本地模式：開啟本地模式計算，more是128m；允許輸入的最大模式檔案數量是4
18.并行執行：
開啟并行執行job，在job之間沒有依賴關系時可以同時執行，并行數另設定，并行執行的最大執行緒數是8，
開啟并行會消耗更多的集群資源來提高執行速度，對特定作業并行執行合適

19.mapreducer的數量：
靠合并小檔案減少map的數量；
設定屬性，減少分片數，當單個檔案很大時，適當增加map的數量
設定reduce的個數，如果不控制，會根據map階段的輸出資料大小來確定它的個數
20、合并小檔案：
檔案數目小，容易在檔案存盤端造成瓶頸，給 HDFS 帶來壓力，從而影響處理效率，
對此，可以通過合并Map和Reduce的結果檔案來消除這樣的影響，
21.jvm的重用：
默認運行1個task，默認jvm的任務數也是1個

三、資料傾斜：
表現：任務進度長時間維持在99%或100%，查看任務監控頁面，發現只有少量（1個或2個）
reduce子任務未完成，因為它處理的資料量和其他的差異過大
1.由于key分布不均勻造成hql陳述句過長
解決方式：將key分布開計算再合并
2.group by 很容易造成資料傾斜：
#是否開啟對group by查詢時出現資料傾斜進行優化
hive.groupby.skewindata=flase;
#是否開啟group by 使用map端的join優化
hive.map.aggr=true;
3.空值產生的資料傾斜：
解決方法：賦予空值分新的key值
4.不同資料型別關聯產生資料傾斜：
解決方法：把數字型別轉換成字串型別
5.表格中出現null值時，建表時加is not null

四、資料方面：
1.hive默認是內部表，洗掉時會洗掉元資料和hdfs上的資料內容
洗掉外部表只會洗掉元資料

2.磁區是為了避免hive暴力掃描大表查詢
磁區欄位不在元資料中存在，真實資料也不在hdfs中存在
將大表分散到多個小表中，提高查詢效率
3.磁區使用表外欄位（paritioned by），分桶使用表內欄位（clustered by）
磁區分桶都是為了將大資料分散到多個檔案中
4.磁區裁剪：可以查詢的程序中減少不必要的磁區，

5. hive的儲存和壓縮：儲存格式一般用orcfile / parquet；壓縮用‘ SNAPPY ’;
建表之前設定壓縮格式：set parquet.compression=SNAPPY;
建表程序中設定檔案格式和壓縮格式：stored as orc tblproperties（'orc.compression'='SNAPPY'）