【编程学习】Hive分布式数据库HQL基础语法
![](https://gcore.jsdelivr.net/gh/6YoungHome/cdn2@main/cover/13.png)
【编程学习】Hive分布式数据库HQL基础语法
6YoungHive宏参数设置
1 | 表示开启动态分区 |
Hive建表基础语法
1 | CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name |
全部使用默认建表方式
1 | create table students |
指定location
1 | create table students2 |
指定存储格式
1 | create table students3 |
Hive建立分区表
1 | create external table students_pt1 |
1 | alter table students_pt1 add partition(pt='20210904'); // 增加分区 |
1 | insert into table students_pt partition(pt='20210902') |
Hive分桶
什么是数据分桶
我们都知道,Hive是基于Hadoop的一个数据仓库,可将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。其实,Hive的本质是将HiveSQL语句转化成MapReduce任务执行。
Hive中,分区提供了一个隔离数据和优化查询的便利方式,不过并非所有的数据都可形成合理的分区,尤其是需要确定合适大小的分区划分方式(有的数据分区数据过大,有的很少,即我们常说的数据倾斜)
我们可以将Hive中的分桶原理理解成MapReduce中的HashPartitioner的原理。都是基于hash值对数据进行分桶。
MR:按照key的hash值除以reduceTask个数进行取余(reduce_id = key.hashcode % reduce.num)
Hive:按照分桶字段(列)的hash值除以分桶的个数进行取余(bucket_id = column.hashcode % bucket.num)
数据分桶的作用:
进行抽样:在处理大规模数据集时,在开发和修改查询的阶段,可以使用整个数据集的一部分进行抽样测试查询、修改。可以使得开发更高效。
map-side join:获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。
代码
1 | create table students_buks |
分桶抽样查询
1 | select * from students_buks tablesample(bucket 1 out of 12 on class) |
- 解释:tablesample(bucket x out of y on id)
- y表示抽取多少个桶数据,y必须为桶的倍数或者因子
- x表示从哪个桶开始抽取数据,如果有多个分区,以后的分区号 = 当前分区号+y
- x<=y必须成立,否则报错
其他常用函数
空字段赋值nvl
1 | select class, nvl(class,-1) from students_buks; |
如果表中的class字段为null,用-1代替
行转列
将多列数据转换为行,类比聚合函数
CONCAT(string A/col,string B/col,…)
:返回字符串拼接后的结果;- concat(‘a’,’b’,’c’) —- ‘abc’
- concat(‘’a,null,’c’)—-null
CONCAT_WS(separatot,str,str,…)
:使用分隔符连接;- concat_ws(‘/‘,’2018’, ‘12’, ‘19’)—-2018/12/19
- concat_ws(‘:’,’22’,’47’,null)—-22:47
- concat_ws(null,’22’,’47’)—-null
COLLECT_SET(col)
:函数只接受基本数据类型,将某个字段的值进行去重汇总,产生array类型字段;- 分组后,将该组的col行聚合成为一个array
COLLECT_LIST(col)
:同上,但是不去重。
列转行
假设现在有表table1
:
col1 | col2_list |
---|---|
“x” | [1, 2, 3] |
“y” | [3, 4, 5] |
要处理为table2
:
col1 | col2 |
---|---|
“x” | 1 |
“x” | 2 |
“x” | 3 |
“y” | 3 |
“y” | 4 |
“y” | 5 |
lateral view
1 | SELECT |
explode
功能:可以将一个array或者map展开
- explode(array):
将array列表里的每个元素生成一行
- explode(map):
每一对元素作为一行,key为一列,value为一列
Hive中的数据类型
基本数据类型
数值型:
TINYINT
:微整型,只占用1个字节,只能存储0-255的整数。SMALLINT
:小整型,占用2个字节,存储范围–32768 到 32767。INT
:整型,占用4个字节,存储范围-2147483648到2147483647。BIGINT
:长整型,占用8个字节,存储范围-2^63到2^63-1。
布尔型
BOOLEAN
:TRUE/FALSE
浮点型
FLOAT
:单精度浮点数。DOUBLE
:双精度浮点数。
字符串型
STRING
:不设定长度。
日期类型
timestamp
:时间戳date
:日期
复杂数据类型:
Structs
,Maps
,Arrays