《hive程式設計指南》閱讀筆記摘要（五）

第6章 HiveQL：查詢

查詢字段是數組類型的元素資料

select name, subordinates[0] from employees;

查詢字段是map類型的元素資料

select name, deductions["state taxes"] from employees;

查詢字段是struct類型的元素資料

select name, address.city from employees;

使用正規表達式訓示列

select symbol, `price.*` from stocks;

hive中支援所有典型的算術運算符。

函數：數學函數、聚合函數、 表生成函數（要有别名，否則無法表明含義，因為是從集合類型字段導出的資料，沒有單獨的字段名）、其他内置函數（用于處理字元串、map、數組、json和時間戳）

進行資料類型轉換時，floor、round、ceil是首選的處理方式，而不是cast類型轉換操作符。

set hive.map.aggr=true; 可以提高聚合性能

case......when......then......

select name,salary,

  case

    when salary < 5000 then 'low'

    when salary >= 5000 and salary < 7000 then 'middle'

    when salary >= 7000 and salary < 10000 then 'high'

    else 'very high'

  end as bracket from employees;

hive本地模式：不觸發mapreduce job。

什麼情況下可以避免進行mapreduce？

1、select * from employees;      //簡單讀取employees對應的存儲目錄下的檔案

2、where子句中過濾條件隻是分區字段，無論是否使用limit，都不會觸發mapreduce

select * from employees

where country='US' and state='CA'

limit 100;

3、set hive.exec.mode.local.auto=true; hive會嘗試使用本地模式執行

否則，其他所有查詢都會觸發mapreduce job

where子句中不能引用平層的select中定義的别名，但可以引用嵌套查詢裡定義的别名

浮點數比較的解決辦法：

1、和錢相關的數值都避免使用浮點數；

2、使用cast

select * from employees where deductions['federal taxes] > cast(0.2 as float);

deductions['federal taxes]是float型的，0.2在hive中是double型的，使用cast(0.2 as float)讓0.2是float型的

RLIKE是hiveQL的擴充，可以通過java的正規表達式指定比對條件

group by語句通常會和聚合函數一起使用，按照一個或多個列對結果進行分組，然後對每個組執行聚合操作。

having子句讓使用者通過一個簡單的文法完成原本需要通過子查詢才能對group by子句産生的分組進行條件篩選的任務。

having 和where 都是用來篩選用的 ： having 是篩選組  而where是篩選記錄

用having就一定要和group by連用，用group by不一有having （它隻是一個篩選條件用的）

select ... from ... where ... group by ... having...

WHERE語句在GROUPBY語句之前；SQL會在分組之前計算WHERE語句。

HAVING語句在GROUPBY語句之後；SQL會在分組之後計算HAVING語句。

join語句

hive支援通常的sql join操作，但是隻支援等值連接配接。

hive不支援在on子句中的謂詞間使用or

大多數情況下，hive會對每個join連接配接啟動一個mapreduce任務。如

a join b

   join c

會縣啟動一個mapreduce job對a和b進行連接配接操作，然後再啟動一個mapreduce job對第一個mapreduce job的輸出和表c進行連接配接操作。

當對3個或者更多個表進行join連接配接時，如果每個on子句都使用相同的連接配接鍵，那麼隻會産生一個mapreduce job。

hive會假設查詢中最後一個表是最大的表，會嘗試将其他表緩存起來。是以，使用者需要保證連續查詢中的表的大小從左到右是依次增加的。

幸運的是，使用者并非總是要将最大的表放在查詢語句最後，因為hive提供了 标記 機制，顯式地告知哪張表是大表

select s.ymd,s.symbol,s.price_close,d.dividend

from stocks s join dividends d on s.ymd=d.ymd and s.symbol = d.symbol

where s.symbol='AAPL';

select ... from ...join ...on... where ...

會先執行join語句，再将結果通過where語句進行過濾

on語句中的分區過濾條件在外連結中是無效的，在内連接配接中是有效的

hive不支援where s.ymd,s.symbol in (select d.ymd, d.symbol from dividends d)種種操作，而是以left semi join實作此功能

select s.ymd,s.symbol,s.price_close

from stocks left semi join dividends d on s.ymd=d.ymd and s.symbol=d.symbol;

注意：select和where子句中不能引用右邊表中的字段。

semi join比通常的inner join高效

如果join on語句寫錯了，可能會導緻一個笛卡爾積join查詢，如果設定hive.mapred.mode=strict，hive會阻止使用者執行笛卡爾積查詢

map-side join：hive可以在map端執行連接配接過程（稱為map-side join），即hive可以和記憶體中的小表進行逐一比對，進而省略掉正常連接配接操作所需要的reduce過程。有時還可以同時減少map過程的執行步驟。

set hive.auto.convert.join=true;   hive會在必要的時候啟動map-side join優化

使用者還可以配置能夠使用這個優化的小表的大小

hive.mapjoin.smalltable.filesize=25000000   （機關是位元組，即25M）

hive對right outer join 和 full outer join 不支援這個優化

order by 和sort by

distribute by 控制map的輸出在reducer中是如何劃分的。

cluster by s.symbol = distribute by s.symbol sort by s.symbol asc

cluster by剝奪了sort by 的并行性，但可以實作輸出檔案的資料是全局排序的。

類型轉換函數cast(field as type)，type是具體的字段類型，如cast(salary as FLOAT),如果轉換失敗，會傳回NULL

将浮點數轉換為整形的推薦方式是round()、floor()

抽樣查詢、資料塊抽樣

抽樣會掃描表中所有的資料，然後在每N行中抽取一行資料。

union all可以将2個或多個表進行合并。每一個union子查詢都必須具有相同的列，而且對應的每個字段的字段類型必須是一緻的。

*****************************

微信公衆号：IT人成長關注

大資料技術QQ群：485681776