九個最容易出錯的 Hive sql 詳解及使用注意事項

閱讀本文小建議：本文適合細嚼慢咽，不要一目十行，不然會錯過很多有價值的細節。

文章首發於公眾號：五分鐘學大數據

前言

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

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

正文開始

1. decimal

hive 除了支援 int,double,string等常用類型，也支援 decimal 類型，用於在資料庫中存儲精確的數值，常用在表示金額的欄位上

注意事項：

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

2. location

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

注意事項：

創建表時使用location，
當指定文件夾時，hive會載入文件夾下的所有文件，當表中無分區時，這個文件夾下不能再有文件夾，否則報錯。
當表是分區表時，比如 partitioned by (day string)， 則這個文件夾下的每一個文件夾就是一個分區，且文件夾名為 day=20201123
這種格式，然後使用：msck repair table score; 修復表結構，成功之後即可看到數據已經全部載入到表當中去了

3. load data 和 load data local

從hdfs上載入文件
load data inpath '/hivedatas/techer.csv' into table techer;

從本地系統載入文件
load data local inpath '/user/test/techer.csv' into table techer;

注意事項：

  1. 使用 load data local 表示從本地文件系統載入，文件會拷貝到hdfs上
  2. 使用 load data 表示從hdfs文件系統載入，文件會直接移動到hive相關目錄下，注意不是拷貝過去，因為hive認為hdfs文件已經有3副本了，沒必要再次拷貝了
  3. 如果表是分區表，load 時不指定分區會報錯
  4. 如果載入相同文件名的文件，會被自動重命名

4. drop 和 truncate

刪除表操作
drop table score1;

清空表操作
truncate table score2;

注意事項：

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

5. join 連接

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

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

RIGHT OUTER JOIN 右外連接：右邊所有數據會被返回，左邊符合條件的被返回、
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版本已經支援不等值連接，就是 join on條件後面可以使用大於小於符號;並且也支援 join on 條件後跟or (早前版本 on 後只支援 = 和 and，不支援 > < 和 or)
  2. 如hive執行引擎使用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;

它們的執行效率沒有區別，只是書寫方式不同，用逗號是sql 89標準，join 是sql 92標準。用逗號連接後面過濾條件用 where ，用 join 連接後面過濾條件是 on。

6. left semi join

為什麼把這個單獨拿出來說，因為它和其他的 join 語句不太一樣，
這個語句的作用和 in/exists 作用是一樣的，是 in/exists 更高效的實現
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 語句執行結果完全一樣，只不過第二個執行效率高

注意事項：

  1. left semi join 的限制是：join 子句中右邊的表只能在 on 子句中設置過濾條件，在 where 子句、select 子句或其他地方過濾都不行。
  2. left semi join 中 on 後面的過濾條件只能是等於號，不能是其他的。
  3. left semi join 是只傳遞表的 join key 給 map 階段，因此left semi join 中最後 select 的結果只許出現左表。
  4. 因為 left semi join 是 in(keySet) 的關係，遇到右表重複記錄，左表會跳過

7. 聚合函數中 null 值

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

注意事項：

聚合操作時要注意 null 值：

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

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

8. 運算符中 null 值

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

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

注意事項：

  1. 每行中的列欄位相加或相減，如果含有 null 值，則結果為 null
    例：有一張商品表（product）
id price dis_amount
1 100 20
2 120 null

各欄位含義： id (商品id)、price (價格)、dis_amount (優惠金額)

我想算每個商品優惠後實際的價格，sql如下：

select id, price - dis_amount as real_amount from product;

得到結果如下：

id real_amount
1 80
2 null

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

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

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

使用 coalesce 函數進行 null 值處理下，得到的結果就是準確的

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

  1. 小於是不包含 null 值，如 id < 10；是不包含 id 為 null 值的。

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

9. and 和 or

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

注意事項：

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

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

我想要統計下電器或者傢具這兩類中價格大於100的商品，sql如下：

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

得到結果

id classify price
1 電器 70
2 電器 130
3 電器 80
4 傢具 150

結果是錯誤的，把所有的電器類型都查詢出來了，原因就是 and 優先順序高於 or，上面的sql語句實際執行的是，先找出 classify = ‘傢具’ and price>100 的，然後在找出 classify = ‘電器’ 的

正確的 sql 就是加個括弧，先計算括弧裡面的：

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

最後

第一時間獲取最新大數據技術，盡在公眾號：五分鐘學大數據

搜索公眾號：五分鐘學大數據，學更多大數據技術！

Tags:

VirMach 便宜 VPS

QNews

QNews