2019 小土刀再启程

0%

【HIVE 编程指南】查大数

发表于 更新于 分类于

查大数，做大事，但是从小事做起。

更新历史

  • 2020.02.18：重新上线
  • 2019.05.09：完成读后感
  • 2018.05.18：开始阅读

读后感

在微众开始比较多的使用 Hive，才意识到大数据生态的重要性，因为数据量太大，如何合理找到自己所需的数据，也是一门技术和艺术，值得我在实践中不断学习。

查大数，做大事，但是从小事做起。

阅读笔记

Hive 是 Hadoop 生态系统中必不可少的一个工具，它提供了一种 SQL 方言，可以查询存储在 Hadoop 分布式文件系统（及其他类似文件系统）中的数据。大多数数据仓库应用程序都是使用关系数据库进行实现的，并使用 SQL 作为查询语言。Hive 降低了将这些应用程序转移到 Hadoop 系统上的难度。注：本文的 Hive 是 0.9 版本，可能跟 1.2 版本（中信版本）有差别。

第 1 章 基础知识

Hive 最适合于数据仓库应用程序，使用该应用程序进行相关的静态数据分析，不需要快速响应给出结果，而且数据本身不会频繁变化，用户可以对数据进行挖掘，然后形成意见和报告。

Hive 不是一个完整的数据库。Hadoop 以及 HDFS 的设计本身约束和局限性地限制了 Hive 所能胜任的工作。其中最大的限制就是 Hive 不支持记录级别的更新、插入或者删除操作。但是用户可以通过查询生成新表或将查询结果导入到文件中。Hive 不支持事务。

Hive 的替代工具中最有名的的就是 Pig，但是它是一种数据流语言，而不是一种查询语言，并不是基于 SQL。

HBase 同样基于 HDFS，但可以提供行级别的数据更新和快速查询。现在 Hive 也可以和 HBase 结合使用。

第 2 章 基础操作

Hive 的 Thrift 服务提供了可远程访问其他进程的功能，也提供使用 JDBC 和 ODBC 访问 Hive 的功能。所有的 Hive 客户端都需要一个 metastoreservice(元数据服务)，Hive 使用这个服务来存储表模式信息和其他元数据信息。通常情况下会使用一个关系型数据库中的表来存储这些信息。

第 3 章 数据类型和文件格式

Hive 支持关系型数据库中的大多数基本数据类型，同时也支持关系型数据库中很少出现的 3 种集合数据类型。

基本数据类型

数据类型 长度 例子
TINYINT 1 byte 有符号整数 20
SMALINT 2 byte 有符号整数 20
INT 4 byte 有符号整数 20
BIGINT 8 byte 有符号整数 20
BOOLEAN true / false TRUE
FLOAT 单精度浮点数 3.1415926
DOUBLE 双精度浮点数 3.1415926
STRING 字符序列，可以指定字符集，可以使用单/双引号 “now is the time”
TIMESTAMP 整数，浮点数或字符串 unix timestamp utc
BINARY 字节数组 -

集合数据类型

数据类型 长度 例子
STRUCT 类似一个对象，可以通过 . 访问 struct('John', "Doe")
MAP 键值对，通过方括号访问 map('first', 'John', 'last', 'Doe')
ARRAY 数组，通过下标访问 Array('John', 'Doe')

在大数据系统中，不遵循标准格式的一个好处就是可以提供更高吞吐量的数据。当处理的数据级是 T 或者 P 时，以最少的“头部寻址”来从磁盘上扫描数据是非常必要的。按数据集进行封装的话可以通过减少寻址次数来提高查询的速度。注意，STRUCT可以混合多种不同的数据，但是 STRUCT 中一旦声明好结构，那么其位置就不可以再改变

Hive 中并没有键的概念。但是，用户可以对表建立索引。

文本文件数据编码

Hive 默认使用了几个控制字符，这些字符很少出现在字段值中，具体如下。

分隔符 描述
\n 换行符分割
^A 用于分割字段（列），在 CREATE TABLE 语句中可以使用八进制编码 \001 表示
^B 用于分割 ARRAY 或者 STRUCT 中的元素，或用于 MAP 中键值对的分隔，在 CREATE TABLE 语句中可以使用八进制编码 \002 表示
^C 用于 MAP 中键值对的分隔，在 CREATE TABLE 语句中可以使用八进制编码 \003 表示

读时模式

传统数据库是写时模式(schema on write)，数据在写入数据库时对模式进行检查。Hive 会在查询时进行数据加载时进行验证，即读时模式(schema on read)

第 4 章 HiveQL: 数据定义

Hive 不支持行级插入操作、更新操作和删除操作。Hive 也不支持事务。

数据库操作

Hive 中的数据库的概念本质上仅仅是表的一个目录或者命名空间。然而，对于具有很多组和用户的大集群来说，这是非常有用的，因为这样可以避免表名冲突。通常会使用数据库来将生产表组织成逻辑组。

如果用户没有显式指定数据库，那么将会使用默认的数据库 default。其他常用命令（以脚本形式展现）。Hive 会为每个数据库创建一个目录。数据库中的表将会以这个数据库目录的子目录形式存储。有一个例外就是 default 数据库中的表，因为这个数据库本身没有自己的目录。

默认情况下，Hive 是不允许用户删除一个包含有表的数据库的。要么先删除数据库中的表，然后再删除数据库；要么在删除命令的后面加上关键字 CASCADE。

在所有的数据库相关命令中，都可以使用 SCHEMA 关键字来替代关键字 TABLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 创建数据库
CREATE DATABASE IF NOT EXISTS financials;
# 为数据库增加描述信息
COMMENT 'Holds all financial tables'
# 查看 Hive 中包含的数据库
SHOW DATABASES;
# 可以使用正则来筛选数据库
SHOW DATABASES LIKE 'h.*';
# 显示数据库的信息
DESCRIBE DATABASE financials;
# 设置当前的工作数据库
USE financials;
# 显示数据库中的表
SHOW TABLES;
# 删除数据库
DROP DATABASE IF EXISTS financials;
# 删除数据库（同时删除对应的表）
DROP DATABASE IF EXISTS financials CASCADE;

# 修改数据库，设置键值对属性，没有方法可以删除或者重置数据库属性
ALTER DATABASE financials SET DBPROPERTIES('edited-by'='wdxtub')

表操作

创建表类似于类声明，比如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE IF NOT EXSITS mydb.employees (
  name STRING COMMENT 'Employee name',
  salary FLOAT COMMENT 'Employee salary',
  subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
  deductions MAP<STRING, FLOAT> COMMNET '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'='2012-01-02 10:00:00', ...)
LOCATION '/user/hive/warehouse/mydb.db/employees';

# 拷贝一个已经存在的表模式
CREATE TABLE IF NOT EXISTS mydb.employees2 LIKE mydb.employees;
# 查看数据库里的表
SHOW TABLES IN mydb;
# 查看表的详细结构信息
DESCRIBE mydb.employees;
DESCRIBE EXTENDED mydb.employees;
DESCRIBE FORMATTED mydb.employees;

分区表、管理表

数据分区的一般概念存在已久。其可以有多种形式，但是通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的。

Hive 中有分区表的概念。我们可以看到分区表具有重要的性能优势，而且分区表还可以将数据以一种符合逻辑的方式进行组织，比如分层存储。

1
2
3
4
5
6
7
8
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);

分区表改变了 Hive 对数据存储的组织方式，现在 Hive 会创建好可以反映分区结构的子目录。当我们在 WHERE 子句中增加谓词来按照分区值进行过滤时，这些谓词被称为分区过滤器。

外部表同样可以使用分区。事实上，用户可能会发现，这是管理大型生产数据集最常见的情况。

删除表

Hive 支持和 SQL 中 DROP TABLE 命令类似的操作

1
DROP TABLE IF EXISTS employees;

修改表

大多数的表属性可以通过 ALTER TABLE 语句来进行修改。这种操作会修改元数据，但不会修改数据本身。

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
# 表重命名
ALTER TABLE log_messages RENAME TO logmsgs;
# 增加分区
ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year = 2011, month = 1, day = 1) LOCATION '/logs/2011/01/01';
# 修改分区
ALTER TABLE log_messages PARTITION(year = 2011, month = 12, day = 2)
SET LOCATION 's3n://ourbucket/logs/2011/01/02';
# 删除分区
ALTER TABLE log_messages DROP IF EXISTS PARTITION(year = 2011, month = 12, day = 2);
# 对于外部表，分区内数据不会被删除

# 修改列信息
ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours, minutes, and seconds part of the timestamp'
AFTER severity;

# 增加列
ALTER TABLE log_messages ADD COLUMN (
  app_name STRING COMMENT 'Application name',
  session_id LONG COMMENT 'The current session id');

# 删除或者替换列
ALTER TABLE log_messages REPLACE COLUMNS (
  hours_mins_secs INT COMMENT 'hour, minute, seconds from timestamp',
  severity STRING COMMENT 'The message severity',
  message STRING COMMENT 'The rest of the message');

第 5 章 HiveQL: 数据操作

向管理表中装载数据

往表中装载数据的唯一途径就是使用一种“大量”的数据装载操作。

1
2
3
4
# 如果分区目录不存在，这个命令会先创建分区目录，然后再拷贝到该目录下
LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
OVERWRITE INTO TABLE employees
PARTITION (country = 'US', state = 'CA');

通过查询语句向表中插入数据

1
2
3
4
INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state = 'OR')
SELECT * FROM staged_employees se
WHERE se.cnty = 'US' AND se.st = 'OR';

导出数据

如果数据文件恰好是用户需要的格式，那么只需要简单拷贝文件夹即可 hadoop fs -cp source_path target_path

第 6 章 HiveQL: 查询

一些查询操作，基本上看看就能明白

1
2
3
4
SELECT name, salary FROM employees;
# STRUCT, MAP, ARRAY 会以 JSON 形式进行展示
SELECT count(*), avg(salary) FROM employees;
SELECT upper(name), salary, deductions["Federal Taxes"] FROM employees LIMIT 2;

抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结构而不是全部结果。Hive 可以通过对表进行分桶抽样来满足这个需求。

1
2
3
4
5
6
7
# 分桶语句中的分母表示的是数据将会被散列的桶的个数，而分子表示将会选择的桶的个数
# 可以使用 rand() 函数进行抽样，每次结果不同
SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;
# 如果我们按照指定的列进行分桶，那么每次结果相同
SELECT * from numbers TABLESAMPLE(BUCKET 5 OUT OF 10 ON number) s;

# 数据块抽样

Hive 提供了另外一种按照抽样百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样。这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个 HDFS 数据块。因此，如果表的数据大小小于普通的块大小 128MB 的话，那么将会返回所有行。

第 7 章 HiveQL: 视图

视图可以允许保存一个查询对象并像对待表一样对这个查询进行操作。这是一个逻辑结构，因为它不像一个表会存储数据。

当一个查询引用一个视图时，这个视图所以定义的查询语句将和用户的查询语句组合在一起，然后供 Hive 制定查询计划。从逻辑上讲，可以想象为 Hive 先执行这个视图，然后使用这个结果进行余下后序的查询。

使用视图来降低查询复杂度

当查询变得长或复杂时，通过使用视图将这个查询语句分割成多个小的、更可控的片段可以降低这种复杂度。

视图操作的命令

1
2
3
4
5
6
7
8
# 创建视图
CREATE VIEW IF NOT EXISTS shipments(time, part)
COMMENT 'Time and parts for shipments.'
TBLPROPERTIES ('creator' = 'me')
AS SELECT ...;

# 删除视图
DROP VIEW IF EXISTS shipments;

第 8 章 HiveQL: 索引

Hive 只有有限的索引功能。Hive 中没有普通关系型数据库中键的概念，但是还是可以对一些字段建立索引来加速某些操作的。一张表的索引数据存储在另外一张表中。

bitmap 索引普遍应用于排重后值较小的列。

1
2
3
4
5
6
7
8
9
10
11
# 重建索引
ALTER INDEX employees_index
ON TABLE employees
PARTITION (country = 'US')
REBUILD;

# 显示索引
SHOW FORMATTED INDEX ON employees;

# 删除索引
DROP INDEX IF EXISTS employees_index ON TABLE employees;

第 9 章 模式设计

重点介绍 Hive 中哪些模式

按天划分的表

1
2
3
4
5
6
7
8
9
10
# 错误的方法（正确应该使用分区表）
CREATE TABLE supply_2011_01_02(id int, part string, quantity int);
CREATE TABLE supply_2011_01_03(id int, part string, quantity int);

# 正确的方法（使用分区表）
CREATE TABLE supply (id int, part string, quantity int)
PARTITIONED BY (int day);

ALTER TABLE supply add PARTITION (day=20110102);
ALTER TABLE supply add PARTITION (day=20110103);

唯一键和标准化

如果可以的话，应避免对非标准化数据进行连接(JOIN)操作。复杂的数据类型，如 array, map 和 struct，有助于实现在单行中存储一对多数据（其实 HBASE 的思路也是如此）。这并不是说不应该进行标准化，但是星形架构类型设计并非最优的。

避免标准化的主要原因是为了最小化磁盘寻道，比如那些通常需要外键关系的情况。非标准化数据允许被写入到大的、连续的磁盘存储区域，从而优化磁盘驱动器的 IO 性能。

同一份数据多种处理

Hive 本身提供一个独特的语法，它可以从一个数据源产生多个数据聚合，而无需每次聚合都要重新扫描一次。

分桶表数据存储

分区提供一个隔离数据和优化查询的便利的方式。不过，并非所有的数据集都可形成合力的分区，要注意合适的划分大小。

总是使用压缩

可以无缝使用很多压缩类型

第 10 章 调优

HiveQL 是一种声明式语言，用户会提交声明式的查询，而 Hive 会将其转换成 MapReduce job。

  • 使用 EXPLAIN

第 13 章 函数

用户自定义函数(UDF)是一个允许用户扩展 HiveQL 的强大功能。

  • 标准函数：表示以一行数据中的一列或多列数据作为参数然后返回结果是一个值的函数。大多数函数是属于这类的。
  • 聚合函数(UDAF)：接受从零行到多行的的零个到多个列，然后返回单一值，通常和 GROUP BY 语句组合使用
  • 表生成函数(UDTF): 接受零个或多个输入，然后产生多列或多行输出。
1
2
3
4
5
6
7
8
9
10
11
# 显示所有的函数
SHOW FUNCTIONS;

# 显示函数的文档
DESCRIBE FUNCTION concat;

# 显示详细的文档
DESCRIBE FUNCTION EXTENDED concat;

# 调用函数
SELECT concat(column1, column2) AS x FROM table;

第 16 章 Hive 的 Thrift 服务

Thrift 是一个软件框架，其用于跨语言的服务开发。

相关文章