最容易出错的 HiveSQL 详解

==================================================

正文开始：

前言

在进行数仓搭建和数据分析时最常用的就是 sql，其语法简洁明了，易于理解，目前大数据领域的几大主流框架全部都支持sql语法，包括 hive，spark，flink等，所以sql在大数据领域有着不可替代的作用，需要我们重点掌握。

在使用sql时如果不熟悉或不仔细，那么在进行查询分析时极容易出错，接下来我们就来看下几个容易出错的sql语句及使用注意事项。

正文开始

1. decimal

hive 除了支持 int,double,string等常用类型，也支持 decimal 类型，用于在数据库中存储精确的数值，常用在表示金额的字段上

注意事项：

整数部分超过9位，则这个字段就会变成null，如果整数部分不超过9位，则原字段显示小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入

2. location

注意事项：

指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错。msck  repair   table  score

3. load data 和 load data local

注意事项：

从本地文件系统加载，文件会拷贝到hdfs上

从hdfs文件系统加载，文件会直接移动到hive相关目录下

如果表是分区表，load 时不指定分区会报错

如果加载相同文件名的文件，会被自动重命名

4. drop 和 truncate

注意事项：

hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表。

5. join 连接

注意事项：

join on条件后面可以使用大于小于符号;并且也支持 join on 条件后跟or

如hive执行引擎使用MapReduce，一个join就会启动一个job，一条sql语句中如有多个join，则会启动多个job

注意

它们的执行效率没有区别，只是书写方式不同

6. left semi join

注意事项：

只能在 on 子句中设置过滤条件

只能是等于号

结果只许出现左表

右表重复记录，左表会跳过

7. 聚合函数中 null 值

注意事项：

聚合操作时要注意 null 值

求最小值是不包含 null求平均值也是不包含 null

以上需要特别注意，null 值最容易导致算出错误的结果

8. 运算符中 null 值

注意事项：

每行中的列字段相加或相减，如果含有 null 值，则结果为 null

各字段含义：id (商品id)、price (价格)、dis_amount (优惠金额)

每个商品优惠后实际的价格

得到结果如下：

id=2的商品价格为 null，结果是错误的。

对 null 值进行处理

小于是不包含 null 值

not in 是不包含 null 值的

9. and 和 or

and 的优先级高于 or

注意事项：

例：  还是一张商品表（product）

我想要统计下电器或者家具这两类中价格大于100的商品，sql如下：

得到结果

结果是错误的，把所有的电器类型都查询出来了，原因就是 and 优先级高于 or，上面的sql语句实际执行的是，先找出 classify = ‘家具’ and price>100 的，然后在找出 classify = ‘电器’ 的

正确的 sql 就是加个括号，先计算括号里面的：

–END–