九个最容易出错的 Hive sql 详解及使用注意事项 (一)

简介: 在进行数仓搭建和数据分析时最常用的就是 sql,其语法简洁明了,易于理解,目前大数据领域的几大主流框架全部都支持sql语法,包括 hive,spark,flink等,所以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)


  1. 如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月前
|
SQL HIVE
【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率
该文介绍了环比增长率、同比增长率和复合增长率的概念及计算公式,并提供了SQL代码示例来计算商品的月度增长率。环比增长率是相邻两期数据的增长率,同比增长率是与去年同期相比的增长率,复合增长率则是连续时间段内平均增长的速率。文章还包含了一组销售数据用于演示如何运用这些增长率进行计算。
235 4
|
21天前
|
SQL 存储 数据可视化
SQL中文字符旋转90度的处理:技巧、方法与注意事项
在SQL数据库中,直接对文本数据进行90度旋转并不是数据库系统的原生功能
|
23天前
|
SQL 分布式计算 Hadoop
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
42 3
|
22天前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
23 0
|
23天前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
61 0
|
3月前
|
SQL 监控 安全
SQL Server的安全注意事项
将上述注意事项纳入日常的数据库管理中,有助于确保SQL Server数据库的安全稳定运行。除了遵循这些最佳实践外,定期进行安全审计也是确保环境持续安全的关键。
47 7
|
3月前
|
SQL 存储 分布式计算
插入Hive表数据SQL
【8月更文挑战第10天】
|
3月前
|
SQL 物联网 数据处理
"颠覆传统,Hive SQL与Flink激情碰撞!解锁流批一体数据处理新纪元,让数据决策力瞬间爆表,你准备好了吗?"
【8月更文挑战第9天】数据时代,实时性和准确性至关重要。传统上,批处理与流处理各司其职,但Apache Flink打破了这一界限,尤其Flink与Hive SQL的结合,开创了流批一体的数据处理新时代。这不仅简化了数据处理流程,还极大提升了效率和灵活性。例如,通过Flink SQL,可以轻松实现流数据与批数据的融合分析,无需在两者间切换。这种融合不仅降低了技术门槛,还为企业提供了更强大的数据支持,无论是在金融、电商还是物联网领域,都将发挥巨大作用。
56 6
|
4月前
|
SQL 分布式计算 关系型数据库
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
61 2