【学习笔记】HQL

HDFS（Hadoop分布式文件系统）管理着集群中的数据，每个数据块（block）被冗余多份。
HQL用于查询存储在Hadoop集群中的数据。
HQL不支持行级插入操作、更新操作和删除操作

MapReduce

MapReduce是一种计算模型，可将大型数据处理任务分解成很多单个的、可以在服务器集群中并行执行的任务，这些任务的计算结果可以合并在一起来计算最终的结果。

map操作会将集合中的元素从一种形式转换成另一种形式。reduce过程的目的是将值的集合转换成一个值，或者转换成另一个集合。job可以不需要reduce过程。

数据类型

基本数据类型

数据类型转换 cast(s AS INT);

集合数据类型

默认分隔符

• \n：分割文本文件
• ^A：分隔字段
• ^B：分隔ARRAY或STRUCT中的元素，或MAP中的键值对
• ^C：分隔MAP中键和值

可以修改默认分隔符

CREATE TABLE employees (
	name			STRING,
	salary			FLOAT,
	subordinates	ARRAY<STRING>,
	deductions		MAP<STRING, FLOAT>,
	address			STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8
9
10
11
12
13

数据库

-- 创建数据库
CREATE DATABASE financials;
-- 创建数据库且检查是否存在
CREATE DATABASE IF NOT EXISTS financials;
-- 查看包含的数据库
SHOW DATABASES;
-- 创建数据库且修改默认位置
CREATE DATABASE financials
LOCATION '/my/other/directory';
-- 切换工作目录
USE financials;
-- 删除数据库,加关键字CASCADE可以删除包含有表的数据库
DROP DATABASES IF EXISTS financials [CASCADE];
-- 修改数据库
ALTER DATABASES financials SET DBPROPERTITIES('edited-by'='Ann')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

表

CREATE TABLE [IF NOT EXISTS] mydb.employees (
	name			STRING COMMENT 'Emplyee name',
	salary			FLOAT COMMENT 'Emplyee salary',
	subordinates	ARRAY<STRING> COMMENT 'Names of subordinates',
	deductions		MAP<STRING, FLOAT> COMMENT 'Keys are deductions names, values are percentages',
	address			STRUCT<street:STRING, city:STRING, state:STRING, zip:INT> COMMENT 'Home address'
)
COMMENT 'Description of the Table'
TBLPROPERTIES ('creator'='me', 'created_at'='2022-01-01')
LOCATION '/user/hive/warehouse/mydb.db/employees';

-- 拷贝已经存在的表的表模式
CREATE TABLE [IF NOT EXISTS] mydb.employees2
LIKE mydb.employees;

-- 查看表的结构信息
DESCRIBE EXTENDED mydb.employees;
-- 只看某列的信息
DESCRIBE mydb.employees.salary;

-- 创建外部表,可以读取location目录下的数据,无法删除该数据
CREATE EXTERNAL TABLE ex_table (
	....
)
LOCATION '/data/ex_directory';

-- 修改列信息,修改列名,类型,注释和位置
ALTER TABLE a
CHANGE COLUMN old_name new_name INT
COMMENT 'new comment'
AFTER column_c;

-- 增加列
ALTER TABLE a ADD COLUMNS (
	new_column STRING COMMENT 'add new column'
);

-- 删除表
DROP TABLE [IF EXISTS] employees;

-- 表重命名
ALTER TABLE a RENAME TO b;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- 创建分区表,改变了数据存储的组织方式,会创建对应子目录,"/pt_table/country=A/state=B"
CREATE TABLE pt_table (
	....
)
PARTITIONED BY (country STRING, state STRING);

-- 增加表分区
ALTER TABLE a IF NOT EXISTS
PARTITION (year=2022, month=1) LOCATION '/user/2022/01'
PARTITION (year=2022, month=2) LOCATION '/user/2022/02';

-- 修改表分区
ALTER TABLE a PARTITION (year=2022, month=1) 
SET LOCATION '/user/2022/01';

-- 删除表分区
ALTER TABLE a DROP IF EXISTS PARTITION (year=2022, month=1) ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

插入数据

-- 覆盖掉原内容,使用INTO则是追加写入
INSERT OVERWRITE TABLE a
PARTITION (year=2022, month=1)
SELECT * FROM b
WHERE b.yr=2022 AND b.mon=1;

-- 动态分区插入,根据最后2列来确定分区字段的值
INSERT OVERWRITE TABLE a
PARTITION (year, month)
SELECT ..., yr, mon FROM b;

-- 混合使用动态和静态分区,镜头分区键必须在动态分区之前
INSERT OVERWRITE TABLE a
PARTITION (year=2022, month)
SELECT ..., yr, mon FROM b
WHERE b.yr=2022;

-- 查询结果载入表
CREATE TABLE a
AS SELECT name, salary, address
FROM employees
WHERE yr=2022;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

查询

-- 限制返回的行数
SELECT * FROM a
LIMIT 2;

-- 列别名
SELECT deductions["Tax"] AS tax FROM employees;

-- 嵌套select语句, 结果集需要起别名
SELECT e.name
FROM (
	SELECT upper(name), salary
	FROM emplyees
) e;

-- case, when, then句式
SELECT name, salary,
	CASE
		WHEN salary < 50000.0 THEN 'low'
		WHEN salary >= 50000.0 AND salary < 70000.0 THEN 'mid'
		WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high'
		ELSE 'very high'
	END AS bracket
FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

谓词操作符

• A=B：A等于B
• A<=>B：A等于B，A和B都为NULL则返回TRUE
• A<>B, A!=B：A不等于B
• AB：A小于/大于B
• A<=B, A>=B：A小于等于/大于等于B
• A [NOT] BETWEEN B AND C：A的值大于等于B且小于等于C
• A IS [NOT] NULL：A为NULL
• A [NOT] LIKE B：A与B匹配，B为简单正则表达式，‘x%‘以x开头，’%x’以x结尾，’%x%'A包含有字母x
• A RLIKE B, A REGEXP B：A与B匹配，B为Java正则表达式

函数

数学函数

聚合函数

表生成函数

其他内置函数

视图

视图可以允许保存一个查询并像对待表一样对这个查询进行操作，但不会像表一样存储数据。可以通过使用视图将查询语句分割成多个小的、更可控的片段来降低复杂度，常见的使用场景是基于一个或多个列的值来限制输出结果。

CREATE VIEW v AS
SELECT * FROM t1 JOIN t2
ON (t1.id=t2.id) WHERE t1.firstname='jack';

SELECT lastname from v WHERE id=3;
1
2
3
4
5

索引

• 索引（Index）用于提高数据表的查询速度。一个表可以创建多个索引，一个索引可以包含一个或者多个字段。
• 索引是根据表的一个或者多个字段生成的子表，该子表中的数据已经进行了排序。子表除了包含指定字段中的数据，还包含一个 rowid 列，用于存储当前记录在原始表中的位置。用户无法看到索引，它只是用来加快查询速度。
• 在表中插入、修改或者删除数据时，数据库引擎也必须维护索引，以保持索引和原始表的同步；也就是说，使用索引是有额外开销的。

CREATE TABLE employees (
	name			STRING,
	salary			FLOAT,
	subordinates	ARRAY<STRING>,
	deductions		MAP<STRING, FLOAT>,
	address			STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

-- 对分区字段country建立索引
CREATE INDEX employees_index
ON TABLE employees (country)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD
INXPROPERTIES ('created_at'='2022')
IN TABLE employees_index_table
PARTITIONED BY (country, name)
COMMENT 'Employees indexed by country and name';

-- Bitmap索引
CREATE INDEX employees_index
ON TABLE employees (country)
AS 'BITMAP'
WITH DEFERRED REBUILD
INXPROPERTIES ('created_at'='2022')
IN TABLE employees_index_table
PARTITIONED BY (country, name)
COMMENT 'Employees indexes';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

注意事项

在严格模式 hive.mapred.mode=strict

• 对于分区表必须含有分区字段过滤条件
• 使用了order by 必须使用LIMIT
• 限制笛卡尔积查询，执行JOIN查询时不能用where代替on