最容易出錯的 Hive Sql 詳解

前言

在進行數(shù)倉搭建和數(shù)據(jù)分析時最常用的就是 sql，其語法簡潔明了，易于理解，目前大數(shù)據(jù)領(lǐng)域的幾大主流框架全部都支持sql語法，包括 hive，spark，flink等，所以sql在大數(shù)據(jù)領(lǐng)域有著不可替代的作用，需要我們重點掌握。

在使用sql時如果不熟悉或不仔細，那么在進行查詢分析時極容易出錯，接下來我們就來看下幾個容易出錯的sql語句及使用注意事項。

正文開始

1. decimal

hive 除了支持 int,double,string等常用類型，也支持 decimal 類型，用于在數(shù)據(jù)庫中存儲精確的數(shù)值，常用在表示金額的字段上

注意事項：

如：decimal(11,2) 代表最多有11位數(shù)字，其中后2位是小數(shù)，整數(shù)部分是9位； 
如果整數(shù)部分超過9位，則這個字段就會變成null，如果整數(shù)部分不超過9位，則原字段顯示； 
如果小數(shù)部分不足2位，則后面用0補齊兩位，如果小數(shù)部分超過兩位，則超出部分四舍五入； 
也可直接寫 decimal，后面不指定位數(shù)，默認是 decimal(10,0) 整數(shù)10位，沒有小數(shù)

2. location

表創(chuàng)建的時候可以用 location 指定一個文件或者文件夾
create  table stu(id int ,name string)  location '/user/stu2';

注意事項：

創(chuàng)建表時使用location，
當指定文件夾時，hive會加載文件夾下的所有文件，當表中無分區(qū)時，這個文件夾下不能再有文件夾，否則報錯。      
當表是分區(qū)表時，比如 partitioned by (day string)， 則這個文件夾下的每一個文件夾就是一個分區(qū)，且文件夾名為 day=20201123
這種格式，然后使用：msck  repair   table  score; 修復表結(jié)構(gòu)，成功之后即可看到數(shù)據(jù)已經(jī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;

注意事項：

1. 使用 load data local 表示從本地文件系統(tǒng)加載，文件會拷貝到hdfs上

2. 使用 load data 表示從hdfs文件系統(tǒng)加載，文件會直接移動到hive相關(guān)目錄下，注意不是拷貝過去，因為hive認為hdfs文件已經(jīng)有3副本了，沒必要再次拷貝了

3. 如果表是分區(qū)表，load 時不指定分區(qū)會報錯  

4. 如果加載相同文件名的文件，會被自動重命名

4. drop 和 truncate

刪除表操作
drop table score1;

清空表操作
truncate table score2;

注意事項：

如果 hdfs 開啟了回收站，drop 刪除的表數(shù)據(jù)是可以從回收站恢復的，表結(jié)構(gòu)恢復不了，需要自己重新創(chuàng)建；truncate 清空的表是不進回收站的，所以無法恢復truncate清空的表。  
所以 truncate 一定慎用，一旦清空除物理恢復外將無力回天

5. join 連接

INNER JOIN 內(nèi)連接：只有進行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來
select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略

LEFT OUTER JOIN 左外連接：左邊所有數(shù)據(jù)會被返回，右邊符合條件的被返回
select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略

RIGHT OUTER JOIN 右外連接：右邊所有數(shù)據(jù)會被返回，左邊符合條件的被返回、
select * from techer t right join course c on t.t_id = c.t_id;

FULL OUTER JOIN 滿外(全外)連接: 將會返回所有表中符合條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

注意事項：

1. hive2版本已經(jīng)支持不等值連接，就是 join on條件后面可以使用大于小于符號;并且也支持 join on 條件后跟or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)

2. 如hive執(zhí)行引擎使用MapReduce，一個join就會啟動一個job，一條sql語句中如有多個join，則會啟動多個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標準，join 是sql 92標準。用逗號連接后面過濾條件用 where ，用 join 連接后面過濾條件是 on。

6. left semi join

為什么把這個單獨拿出來說，因為它和其他的 join 語句不太一樣，
這個語句的作用和 in/exists 作用是一樣的，是 in/exists 更高效的實現(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

上述兩個 sql 語句執(zhí)行結(jié)果完全一樣，只不過第二個執(zhí)行效率高

注意事項：

1. left semi join 的限制是：join 子句中右邊的表只能在 on 子句中設(shè)置過濾條件，在 where 子句、select 子句或其他地方過濾都不行。

2. left semi join 中 on 后面的過濾條件只能是等于號，不能是其他的。

3. left semi join 是只傳遞表的 join key 給 map 階段，因此left semi join 中最后 select 的結(jié)果只許出現(xiàn)左表。

4. 因為 left semi join 是 in(keySet) 的關(guān)系，遇到右表重復記錄，左表會跳過

7. 聚合函數(shù)中 null 值

hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函數(shù)

注意事項：

聚合操作時要注意 null 值：

count(*) 包含 null 值，統(tǒng)計所有行數(shù)； 
count(id) 不包含id為 null 的值； 
min 求最小值是不包含 null，除非所有值都是 null； 
avg 求平均值也是不包含 null。

以上需要特別注意，null 值最容易導致算出錯誤的結(jié)果

8. 運算符中 null 值

hive 中支持常用的算術(shù)運算符(+,-,*,/)  
比較運算符(>, <, =)
邏輯運算符(in, not in)

以上運算符計算時要特別注意 null 值

注意事項：

1. 每行中的列字段相加或相減，如果含有 null 值，則結(jié)果為 null  
   例：有一張商品表（product）

id	price	dis_amount
1	100	20
2	120	null

各字段含義：id (商品id)、price (價格)、dis_amount (優(yōu)惠金額)

我想算每個商品優(yōu)惠后實際的價格，sql如下：

select id, price - dis_amount as real_amount from product;

得到結(jié)果如下：

id	real_amount
1	80
2	null

id=2的商品價格為 null，結(jié)果是錯誤的。

我們可以對 null 值進行處理，sql如下：

select id, price - coalesce(dis_amount,0) as real_amount from product;

使用 coalesce 函數(shù)進行 null 值處理下，得到的結(jié)果就是準確的

coalesce 函數(shù)是返回第一個不為空的值
如上sql：如果dis_amount不為空，則返回dis_amount，如果為空，則返回0

2. 小于是不包含 null 值，如 id \< 10；是不包含 id 為 null 值的。

3. not in 是不包含 null 值的，如 city not in ('北京','上海')，這個條件得出的結(jié)果是 city 中不包含 北京，上海和 null 的城市。

9. and 和 or

在sql語句的過濾條件或運算中，如果有多個條件或多個運算，我們都會考慮優(yōu)先級，如乘除優(yōu)先級高于加減，乘除或者加減它們之間優(yōu)先級平等，誰在前就先算誰。那 and 和 or 呢，看似 and 和 or 優(yōu)先級平等，誰在前先算誰，但是，and 的優(yōu)先級高于 or。

注意事項：

例： 
還是一張商品表（product）

id	classify	price
1	電器	70
2	電器	130
3	電器	80
4	家具	150
5	家具	60
6	食品	120

我想要統(tǒng)計下電器或者家具這兩類中價格大于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é)果是錯誤的，把所有的電器類型都查詢出來了，原因就是 and 優(yōu)先級高于 or，上面的sql語句實際執(zhí)行的是，先找出 classify = '家具' and price>100 的，然后在找出 classify = '電器' 的

正確的 sql 就是加個括號，先計算括號里面的：

select * from product where (classify = '電器' or classify = '家具') and price>100

--END--