【编程学习】Hive分布式数据库HQL基础语法

Hive宏参数设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 表示开启动态分区
set hive.exec.dynamic.partition=true;

# 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict

# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nostrict;

# 表示支持的最大的分区数量为1000，可以根据业务自己调整
set hive.exec.max.dynamic.partitions.pernode=1000;

# 开启分桶
set hive.enforce.bucketing=true;

Hive建表基础语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  // 定义字段名，字段类型
  [(col_name data_type [COMMENT col_comment], ...)]
  // 给表加上注解
  [COMMENT table_comment]
  // 分区
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  // 分桶
  [CLUSTERED BY (col_name, col_name, ...) 
  // 设置排序字段 升序、降序
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [
  	// 指定设置行、列分隔符 
   [ROW FORMAT row_format] 
   // 指定Hive储存格式：textFile、rcFile、SequenceFile 默认为：textFile
   [STORED AS file_format]
   
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  // 指定储存位置
  [LOCATION hdfs_path]
  // 跟外部表配合使用，比如：映射HBase表，然后可以使用HQL对hbase数据进行查询，当然速度比较慢
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

全部使用默认建表方式

1
2
3
4
5
6
7
8
9
create table students
(
    id bigint,
    name string,
    age int,
    gender string,
    class string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 指定了逗号作为列分隔符

指定location

1
2
3
4
5
6
7
8
9
10
11
12
create table students2
(
    id bigint,
    name string,
    age int,
    gender string,
    class string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; 
// 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，
// 通常Locaion会跟外部表一起使用，内部表一般使用默认的location

指定存储格式

1
2
3
4
5
6
7
8
9
10
11
12
13
create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    class string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; 
// 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，
// 如果不指定，默认为textfile，
// 注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

Hive建立分区表

1
2
3
4
5
6
7
8
9
10
create external table students_pt1
(
    id bigint,
    name string,
    age int,
    gender string,
    class string
)
PARTITIONED BY(pt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
1
2
3
alter table students_pt1 add partition(pt='20210904'); // 增加分区
alter table students_pt drop partition(pt='20210904'); // 删除分区
show partitions students_pt; // 查看某个表的所有分区
1
2
3
4
5
insert into table students_pt partition(pt='20210902') 
select * from students;

insert into table
select *, '20210902' as pt from students; // 不用partition，直接insert写入时包含pt可以自动分区

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
2
3
4
5
6
7
8
9
10
create table students_buks
(
    id bigint,
    name string,
    age int,
    gender string,
    class string
)
CLUSTERED BY (class) into 12 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

分桶抽样查询

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
2
3
4
5
6
7
8
9
10
11
SELECT 
	col1, col2
FROM table1 .
LATERAL VIEW explode(col2_list) tabel2 AS col2;

-- 也可以直接进行聚合统计
SELECT 
	col2, count(1) 
FROM table1 
LATERAL VIEW explode(col2_list) tabel2 AS col2
GROUP BY col2;

explode

功能：可以将一个array或者map展开

  • explode(array)：
    将array列表里的每个元素生成一行
  • explode(map)：
    每一对元素作为一行，key为一列，value为一列

Hive中的数据类型

  1. 基本数据类型

    • 数值型：

      • 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：不设定长度。

  2. 日期类型

    • timestamp：时间戳

    • date：日期

  3. 复杂数据类型：Structs，Maps，Arrays