最容易出錯(cuò)的 Hive Sql 詳解
前言
在進(jìn)行數(shù)倉搭建和數(shù)據(jù)分析時(shí)最常用的就是 sql，其語法簡潔明了，易于理解，目前大數(shù)據(jù)領(lǐng)域的幾大主流框架全部都支持sql語法，包括 hive，spark，flink等，所以sql在大數(shù)據(jù)領(lǐng)域有著不可替代的作用，需要我們重點(diǎn)掌握。
在使用sql時(shí)如果不熟悉或不仔細(xì)，那么在進(jìn)行查詢分析時(shí)極容易出錯(cuò)，接下來我們就來看下幾個(gè)容易出錯(cuò)的sql語句及使用注意事項(xiàng)。
正文開始
1. decimal
hive 除了支持 int,double,string等常用類型，也支持 decimal 類型，用于在數(shù)據(jù)庫中存儲(chǔ)精確的數(shù)值，常用在表示金額的字段上
注意事項(xiàng)：
如：decimal(11,2) 代表最多有11位數(shù)字，其中后2位是小數(shù)，整數(shù)部分是9位；
如果整數(shù)部分超過9位，則這個(gè)字段就會(huì)變成null，如果整數(shù)部分不超過9位，則原字段顯示；
如果小數(shù)部分不足2位，則后面用0補(bǔ)齊兩位，如果小數(shù)部分超過兩位，則超出部分四舍五入；
也可直接寫 decimal，后面不指定位數(shù)，默認(rèn)是 decimal(10,0) 整數(shù)10位，沒有小數(shù)
2. location
表創(chuàng)建的時(shí)候可以用 location 指定一個(gè)文件或者文件夾
create table stu(id int ,name string) location '/user/stu2';
注意事項(xiàng)：
創(chuàng)建表時(shí)使用location，
當(dāng)指定文件夾時(shí)，hive會(huì)加載文件夾下的所有文件，當(dāng)表中無分區(qū)時(shí)，這個(gè)文件夾下不能再有文件夾，否則報(bào)錯(cuò)。
當(dāng)表是分區(qū)表時(shí)，比如 partitioned by (day string)， 則這個(gè)文件夾下的每一個(gè)文件夾就是一個(gè)分區(qū)，且文件夾名為 day=20201123
這種格式，然后使用：msck repair table score; 修復(fù)表結(jié)構(gòu)，成功之后即可看到數(shù)據(jù)已經(jīng)全部加載到表當(dāng)中去了
3. load data 和 load data local
從hdfs上加載文件
load data inpath '/hivedatas/techer.csv' into table techer;
從本地系統(tǒng)加載文件
load data local inpath '/user/test/techer.csv' into table techer;
注意事項(xiàng)：
使用 load data local 表示從本地文件系統(tǒng)加載，文件會(huì)拷貝到hdfs上
使用 load data 表示從hdfs文件系統(tǒng)加載，文件會(huì)直接移動(dòng)到hive相關(guān)目錄下，注意不是拷貝過去，因?yàn)閔ive認(rèn)為hdfs文件已經(jīng)有3副本了，沒必要再次拷貝了
如果表是分區(qū)表，load 時(shí)不指定分區(qū)會(huì)報(bào)錯(cuò)
如果加載相同文件名的文件，會(huì)被自動(dòng)重命名
4. drop 和 truncate
刪除表操作
drop table score1;
清空表操作
truncate table score2;
注意事項(xiàng)：
如果 hdfs 開啟了回收站，drop 刪除的表數(shù)據(jù)是可以從回收站恢復(fù)的，表結(jié)構(gòu)恢復(fù)不了，需要自己重新創(chuàng)建；truncate 清空的表是不進(jìn)回收站的，所以無法恢復(fù)truncate清空的表。
所以 truncate 一定慎用，一旦清空除物理恢復(fù)外將無力回天
5. join 連接
INNER JOIN 內(nèi)連接：只有進(jìn)行連接的兩個(gè)表中都存在與連接條件相匹配的數(shù)據(jù)才會(huì)被保留下來
select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略
LEFT OUTER JOIN 左外連接：左邊所有數(shù)據(jù)會(huì)被返回，右邊符合條件的被返回
select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略
RIGHT OUTER JOIN 右外連接：右邊所有數(shù)據(jù)會(huì)被返回，左邊符合條件的被返回、
select * from techer t right join course c on t.t_id = c.t_id;
FULL OUTER JOIN 滿外(全外)連接: 將會(huì)返回所有表中符合條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;
注意事項(xiàng)：
hive2版本已經(jīng)支持不等值連接，就是 join on條件后面可以使用大于小于符號;并且也支持 join on 條件后跟or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)
如hive執(zhí)行引擎使用MapReduce，一個(gè)join就會(huì)啟動(dòng)一個(gè)job，一條sql語句中如有多個(gè)join，則會(huì)啟動(dòng)多個(gè)job
注意：表之間用逗號(,)連接和 inner join 是一樣的，例：
select tableA.id, tableB.name from tableA , tableB where tableA.id=tableB.id;
和
select tableA.id, tableB.name from tableA join tableB on tableA.id=tableB.id;
它們的執(zhí)行效率沒有區(qū)別，只是書寫方式不同，用逗號是sql 89標(biāo)準(zhǔn)，join 是sql 92標(biāo)準(zhǔn)。用逗號連接后面過濾條件用 where ，用 join 連接后面過濾條件是 on。
6. left semi join
為什么把這個(gè)單獨(dú)拿出來說，因?yàn)樗推渌?nbsp;join 語句不太一樣，
這個(gè)語句的作用和 in/exists 作用是一樣的，是 in/exists 更高效的實(shí)現(xiàn)
SELECT A.* FROM A where id in (select id from B)
SELECT A.* FROM A left semi join B ON A.id=B.id
上述兩個(gè) sql 語句執(zhí)行結(jié)果完全一樣，只不過第二個(gè)執(zhí)行效率高
注意事項(xiàng)：
left semi join 的限制是：join 子句中右邊的表只能在 on 子句中設(shè)置過濾條件，在 where 子句、select 子句或其他地方過濾都不行。
left semi join 中 on 后面的過濾條件只能是等于號，不能是其他的。
left semi join 是只傳遞表的 join key 給 map 階段，因此left semi join 中最后 select 的結(jié)果只許出現(xiàn)左表。
因?yàn)?left semi join 是 in(keySet) 的關(guān)系，遇到右表重復(fù)記錄，左表會(huì)跳過
7. 聚合函數(shù)中 null 值
hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函數(shù)
注意事項(xiàng)：
聚合操作時(shí)要注意 null 值：
count(*) 包含 null 值，統(tǒng)計(jì)所有行數(shù)；
count(id) 不包含id為 null 的值；
min 求最小值是不包含 null，除非所有值都是 null；
avg 求平均值也是不包含 null。
以上需要特別注意，null 值最容易導(dǎo)致算出錯(cuò)誤的結(jié)果
8. 運(yùn)算符中 null 值
hive 中支持常用的算術(shù)運(yùn)算符(+,-,*,/)
比較運(yùn)算符(>, <, =)
邏輯運(yùn)算符(in, not in)
以上運(yùn)算符計(jì)算時(shí)要特別注意 null 值
注意事項(xiàng)：
每行中的列字段相加或相減，如果含有 null 值，則結(jié)果為 null
例：有一張商品表（product）
| id | price | dis_amount |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 120 | null |
各字段含義：id (商品id)、price (價(jià)格)、dis_amount (優(yōu)惠金額)
我想算每個(gè)商品優(yōu)惠后實(shí)際的價(jià)格，sql如下：
select id, price - dis_amount as real_amount from product;
得到結(jié)果如下：
| id | real_amount |
|---|---|
| 1 | 80 |
| 2 | null |
id=2的商品價(jià)格為 null，結(jié)果是錯(cuò)誤的。
我們可以對 null 值進(jìn)行處理，sql如下：
select id, price - coalesce(dis_amount,0) as real_amount from product;
使用 coalesce 函數(shù)進(jìn)行 null 值處理下，得到的結(jié)果就是準(zhǔn)確的
coalesce 函數(shù)是返回第一個(gè)不為空的值
如上sql：如果dis_amount不為空，則返回dis_amount，如果為空，則返回0
小于是不包含 null 值，如 id \< 10；是不包含 id 為 null 值的。
not in 是不包含 null 值的，如 city not in ('北京','上海')，這個(gè)條件得出的結(jié)果是 city 中不包含 北京，上海和 null 的城市。
9. and 和 or
在sql語句的過濾條件或運(yùn)算中，如果有多個(gè)條件或多個(gè)運(yùn)算，我們都會(huì)考慮優(yōu)先級，如乘除優(yōu)先級高于加減，乘除或者加減它們之間優(yōu)先級平等，誰在前就先算誰。那 and 和 or 呢，看似 and 和 or 優(yōu)先級平等，誰在前先算誰，但是，and 的優(yōu)先級高于 or。
注意事項(xiàng)：
例：
還是一張商品表（product）
| id | classify | price |
|---|---|---|
| 1 | 電器 | 70 |
| 2 | 電器 | 130 |
| 3 | 電器 | 80 |
| 4 | 家具 | 150 |
| 5 | 家具 | 60 |
| 6 | 食品 | 120 |
我想要統(tǒng)計(jì)下電器或者家具這兩類中價(jià)格大于100的商品，sql如下：
select * from product where classify = '電器' or classify = '家具' and price>100
得到結(jié)果
| id | classify | price |
|---|---|---|
| 1 | 電器 | 70 |
| 2 | 電器 | 130 |
| 3 | 電器 | 80 |
| 4 | 家具 | 150 |
結(jié)果是錯(cuò)誤的，把所有的電器類型都查詢出來了，原因就是 and 優(yōu)先級高于 or，上面的sql語句實(shí)際執(zhí)行的是，先找出 classify = '家具' and price>100 的，然后在找出 classify = '電器' 的
正確的 sql 就是加個(gè)括號，先計(jì)算括號里面的：
select * from product where (classify = '電器' or classify = '家具') and price>100
--END--