HIVE SQL入门与sql小白技巧总结

公司有提供hive 数据开发平台,开发者可以自己写sql来处理数据。

之前对于hive不太熟悉,现在了解下:

1、Hive 由 Facebook 实现并开源

2、是基于 Hadoop 的一个数据仓库工具

3、可以将结构化的数据映射为一张数据库表

4、并提供 HQL(Hive SQL)查询功能

5、底层数据是存储在 HDFS 上

6、Hive的本质是将 SQL 语句转换为 MapReduce 任务运行

7、使不熟悉 MapReduce 的用户很方便地利用 HQL 处理和计算 HDFS 上的结构化的数据,适用于离线的批量数据计算。

Hive 依赖于 HDFS 存储数据,Hive 将 HQL 转换成 MapReduce 执行,所以说 Hive 是基于 Hadoop 的一个数据仓库工具,实质就是一款基于 HDFS 的 MapReduce 计算框架,对存储在 HDFS 中的数据进行分析和管理

找到一张图:

为什么要使用 Hive:

1、更友好的接口:操作接口采用类 SQL 的语法,提供快速开发的能力

2、更低的学习成本:避免了写 MapReduce,减少开发人员的学习成本

3、更好的扩展性:可自由扩展集群规模而无需重启服务,还支持用户自定义函数

优点

1、可扩展性,横向扩展,Hive 可以自由的扩展集群的规模,一般情况下不需要重启服务 横向扩展:通过分担压力的方式扩展集群的规模 纵向扩展:一台服务器cpu i7-6700k 4核心8线程,8核心16线程,内存64G => 128G

2、延展性,Hive 支持自定义函数,用户可以根据自己的需求来实现自己的函数

3、良好的容错性,可以保障即使有节点出现问题,SQL 语句仍可完成执行

缺点

1、Hive 不支持记录级别的增删改操作,但是用户可以通过查询生成新表或者将查询结 果导入到文件中(当前选择的 hive-2.3.2 的版本支持记录级别的插入操作)

2、Hive 的查询延时很严重,因为 MapReduce Job 的启动过程消耗很长时间,所以不能 用在交互查询系统中。

3、Hive 不支持事务(因为不没有增删改,所以主要用来做 OLAP(联机分析处理),而 不是 OLTP(联机事务处理),这就是数据处理的两大级别)。

听起来好像厉害还的样子,那么赶快来试试吧!

但是实际工作中,要查询数据表,表中有个字段存的是json字符串,这个时候如何来查询json中的某个属性呢?

下面是我最近总结的一些小白技巧:

小白技巧1、查询json字符串中的某个key:get_json_object方法的使用

如果数据表这样的:

id name json_data dt
1 jonh {‘name’:’sss’,’type’:1} 20191128
2 tim {‘name’:’aaa’,’type’:1} 20191128
3 paul {‘name’:’bbb’,’type’:2} 20191128
4 kate {‘name’:’ccc’,’type’:1} 20191128
5 lucy {‘name’:’ccsssc’,’type’:1} 20191128
6 Thomas {‘name’:’ddd’,’type’:2} 20191129

 

我现在想查询type=1的总数,那么查询语句:

select count(*) as total,
dt as `日期`
from tab1 WHERE name = ‘name’
AND get_json_object(json_data ,’$.type’)=1
AND dt >= ‘20191128’
GROUP BY `日期`

小白技巧2:get_dt_date(get_date(-10))

该方法返回的是当前时间往前10个自然日,比如当前时间是20191128,方法返回的就是 20191118

在实际使用中,这个方法非常有用。

 

小白技巧3:sql子查询,

(也可以说是sql嵌套)把一个sql当成中间表,一般用来输出需要计算的结果,比如比例的计算,总和的计算,平均数等

SELECT tb1.dt as `日期`,
tb1.success/(tb1.success + tb2.error) as `比率`,
tb2.error as `总数`,
tb1.success as `成功数` FROM (
select count(*) as success,
dt
from tab_name WHERE name = ‘xxx’  AND type = ‘total’ AND dt > get_dt_date(get_date(-10))
GROUP BY dt ) AS tb1 JOIN ( select dt,
count(*) as error
from tab_name WHERE name = ‘xxx’ AND type = ‘success’ AND dt > get_dt_date(get_date(-10))
GROUP BY dt
) as tb2 ON tb1.dt = tb2.dt
ORDER BY `日期`

 

小白技巧4、提供变量输入

使用${变量名} 后,在查询之前会弹出输入框,要求输入变量,然后变量会带入到sql中进行查询。

如下:

 

SELECT tb1.dt as `日期`,
tb1.success/(tb1.success + tb2.error) as `比率`,
tb2.error as `总数`,
tb1.success as `成功数` FROM (
select count(*) as success,
dt
from tab_name WHERE name = 'xxx'  AND type = 'total' AND dt > get_dt_date(get_date(-10))
GROUP BY dt ) AS tb1 JOIN ( select dt,
count(*) as error
from tab_name WHERE name = 'xxx' AND type = 'success' AND dt > get_dt_date(get_date(-10))
						   AND dt <${结束时间}
GROUP BY dt
) as tb2 ON tb1.dt = tb2.dt
ORDER BY `日期`

然后执行的时候就会弹出输入框输入变量:

 

 

 

 

 

这个也非常有用。

在hive 使用sql 还有很多技巧,此文保持陆续更新。

发表评论

记录工作生活点滴。

返回
顶部