Hive sql使用注意事項有哪些

這篇文章給大家分享的是有關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 (商品id)、price (價格)、dis_amount (優惠金額)

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

select id, price - dis_amount as real_amount from product;

得到結果如下：

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）

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

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

得到結果

結果是錯誤的，把所有的電器類型都查詢出來了，原因就是 and 優先級高于 or，上面的sql語句實際執行的是，先找出 classify = '家具' and price>100 的，然后在找出 classify = '電器' 的

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

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

感謝各位的閱讀！關于“Hive sql使用注意事項有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！