关于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

最初

第一工夫获取最新大数据技术，尽在公众号：五分钟学大数据

搜寻公众号：五分钟学大数据，学更多大数据技术！