这是CDP中Apache Hive3用户指南系列之一，之前的文章请参考<CDP的Hive3系列之Hive Metastore介绍>，<CDP中的Hive3系列之Apache Hive3的特性>和<CDP中的Hive3系列之启动Apache Hive3>
1 Apache Hive 3表
表类型的定义和表类型与 ACID 属性的关系图使得 Hive 表变得清晰。表的位置取决于表的类型。您可以根据其支持的存储格式选择表的类型。
您可以创建ACID（原子性，一致性，隔离性和持久性）表用于不受限制的事务或仅插入的事务。这些表是Hive托管表。数据与Schema一起位于Hive metastore中。或者，您可以创建一个外部表用于非事务性使用。数据位于Hive Metastore外部。模式元数据位于Hive Metastore内部。因为外部表受Hive的控制很弱，所以该表不符合ACID。
下图描述了Hive表的类型。
以下矩阵包括可以使用Hive创建的表的类型、是否支持ACID属性、所需的存储格式以及关键的SQL操作。
表类型 |
ACID |
文件格式 |
插入 |
更新/删除 |
托管表：CRUD事务 |
是 |
ORC |
是 |
是 |
托管表：仅插入式事务 |
是 |
任意格式 |
是 |
否 |
托管表：临时 |
没有 |
任意格式 |
是 |
否 |
外部表 |
没有 |
任意格式 |
是 |
否 |
虽然不能使用SQL UPDATE或DELETE语句删除某些类型的表中的数据，但是可以对任何类型的表使用DROP PARTITION来删除数据。
1.1 表存储格式
CRUD表中的数据必须为ORC格式。实现支持AcidInputFormat和AcidOutputFormat的存储处理程序等效于指定ORC存储。
仅插入使用的表支持所有文件格式。
默认情况下，托管表的存储类型为“优化行列”（ORC）。如果在表创建的过程中未指定任何存储来接受默认的设置，或者指定了ORC存储，则将获得具有插入、更新和删除（CRUD）功能的ACID表。如果指定其他任何存储类型，例如text、CSV、AVRO或JSON，则将获得仅插入的ACID表。您不能更新或删除仅插入表中的列。
1.2 事务表
事务表是驻留在Hive仓库中的ACID表。为了实现ACID合规性，Hive必须管理表，包括对表数据的访问。只有通过Hive才能访问和更改托管表中的数据。由于Hive可以完全控制托管表，因此Hive可以广泛地优化这些表。
与用作联机分析处理（OLAP）系统相反，Hive旨在支持相对较低的事务率。您可以使用SHOW TRANSACTIONS命令列出未完成和中止的事务。
Hive 3中的事务表与非ACID表相当。Hive 3事务表中不需要分桶或排序。分桶不会影响性能。这些表与原生的云存储兼容。
Hive支持每个事务一个语句，该语句可以包含任意数量的行、分区或表。
1.3 外部表
外部表数据不是由Hive拥有或控制的。当您想使用Hive以外的其他工具直接在文件级别访问数据时，通常使用外部表。您还可以使用存储处理程序（例如Druid或HBase）来创建位于Hive元存储之外的表。
在外部表上，Hive 3不支持以下功能：
· 查询缓存
· 物化视图，但以受限的方式除外
· 自动运行时过滤
· 插入后合并文件
在外部表上运行DROP TABLE时，默认情况下，Hive仅删除元数据（Schema）。如果您希望DROP TABLE命令也删除外部表中的实际数据，就像DROP TABLE在托管表上所做的那样，则需要将external.table.purge属性设置为true。
1.4 定位Hive表并更改位置
您需要知道 Hive 在 HDFS 上存储表的位置以及安装服务后如何更改仓库位置。
您在 CDP 中创建的新表存储在托管表的Hive 仓库或外部表的 Hive 仓库中。以下默认仓库位置位于 HDFS 文件系统中：
· /warehouse/tablespace/managed/hive
· /warehouse/tablespace/external/hive
托管表驻留在托管表空间中，只有 Hive 可以访问。默认情况下，Hive 假定外部表驻留在外部表空间中。
要确定托管或外部表类型，您可以运行 DESCRIBE EXTENDED table_name 命令。
您需要在Ranger中设置 HDFS 策略以访问外部表，或设置 HDFS ACL。
更改 Hive 仓库位置的功能旨在在安装服务后立即使用。您可以使用 Cloudera Manager中的 Hive Metastore Action 菜单更改仓库的位置，如以下步骤所述：
1) 在 Cloudera Manager 中，单击集群> Hive >操作菜单>创建 Hive 仓库目录。
2) 在 Cloudera Manager 中，单击Clusters > Hive（Hive Metastore 服务）> Configuration，并将hive.metastore.warehouse.dir 属性值更改为新 Hive 仓库目录的路径。
3) 单击Hive > Hive >操作菜单>创建 Hive 仓库外部目录。
4) 将hive.metastore.warehouse.external.dir属性值更改为 Hive 仓库外部目录的路径。
5) 配置 Ranger 策略或设置 ACL 权限以访问目录。
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive-table-location.html
1.5 使用点表示法引用表
Hive 3.1 使用点表示法对表引用的更改可能需要对 Hive 脚本进行更改。
CDP 中的 Hive 3.1 包括 SQL 兼容性 (Hive-16907)，它拒绝 `db.table`SQL查询。表名中不允许使用点 (.)。要在表名中引用数据库和表，请将两者括在反引号中，如下所示：
`db`.`table`
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_refer_to_table.html
1.6 创建CRUD事务表
当需要可更新、删除和合并的托管表时，可以创建具有ACID（原子性，一致性，隔离性和持久性）属性的CRUD事务表。默认情况下，表数据以优化行列（ORC）文件格式存储。
在此任务中，您将创建一个CRUD事务表。您无法对这种类型的表进行排序。要创建 CRUD 事务表，您必须接受默认的 ORC 格式，方法是在表创建期间不指定任何存储，或明确指定 ORC 存储。
1) 启动Beeline以启动Hive。例如：
beeline -u jdbc:hive2://myhiveserver.com:10000 -n hive -p
2) 输入您的用户名和密码。
出现Hive3连接消息，然后出现Hive提示符，用于在命令行中输入SQL查询。
3) 创建一个名为T的CRUD事务表，该表具有两个整数列a和b：
CREATE TABLE T(a int, b int);
4) 确认您创建了一个托管的ACID表。
DESCRIBE FORMATTED T;
表类型显示MANAGED_TABLE，transactional = true。
1.7 创建仅插入的事务表
如果不需要更新和删除功能，则可以使用任何存储格式创建事务表。这种类型的表具有ACID属性，是一个托管表，并且仅接受插入操作。仅插入表的存储格式不限于ORC。
在此任务中，您将创建一个仅插入的事务表来存储文本。在CREATE TABLE语句中，指定ORC以外的其他存储类型（例如文本，CSV，AVRO或JSON）将导致仅插入ACID表。您可以在表属性子句中显式指定“仅插入”。
1. 使用您的用户名从命令行启动Hive，并替换HiveServer主机的名称或IP地址，如下所示。
beeline -u jdbc:hive2://myhiveserver.com:10000 -n <your user name> -p
2. 输入您的用户名和密码。
出现Hive 3连接消息，然后出现Hive提示符，用于在命令行中输入查询。
3. 创建名为T2的仅插入事务表，该表具有两个整数列a和b：
CREATE TABLE T2(a int, b int) STORED AS ORC TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only');
将'transactional_properties'='insert_only'是必需的; 否则，将生成CRUD表。STORED AS ORC子句是可选的（默认= ORC）。
4. 创建用于文本数据的仅插入事务表。
CREATE TABLE T3(a int, b int) STORED AS TEXTFILE;
该'transactional_properties'='insert_only'不是必需的，因为存储格式不是ORC格式。
1.8 创建、使用和删除外部表
使用外部表（该表不是Hive所管理的表）将数据从文件系统上的文件导入Hive。与Hive托管表相反，外部表将其数据保留在Hive元存储之外。Hive Metastore仅存储外部表的Schema元数据。Hive不管理或限制对实际外部数据的访问。
您需要使用以下方法之一设置对文件系统中的外部表的访问。
· 在 Ranger 中设置 Hive HDFS 策略（推荐）以包含外部表数据的路径。
· 放置一个 HDFS ACL。将逗号分隔值 (CSV) 文件存储在 HDFS 中，该文件将用作外部表的数据源。
在此任务中，您将根据文件系统中存储的CSV（逗号分隔值）数据创建一个外部表，如下图所示。接下来，您希望Hive在元存储中管理和存储实际数据。您创建一个托管表。
您将外部表数据插入到托管表中。
此任务演示了以下Hive原则：
· CREATE TABLE中的LOCATION子句指定外部表数据的位置。
· 外部表和托管（内部）表之间的主要区别是：DROP TABLE语句后，表数据在文件系统上的持久性。
n 外部表删除：Hive仅删除主要由Schema组成的元数据。
n 托管表删除：Hive删除Hive仓库中存储的数据和元数据。
删除外部表后，数据不会消失。要检索它，请发出另一个CREATE EXTERNAL TABLE语句以从文件系统加载数据。
1) 创建一个名为students.csv的文本文件，其中包含以下几行。
1,jane,doe,senior,mathematics 2,john,smith,junior,engineering
2) 将文件移动到名为andrena的目录/存储桶中的HDFS / S3中，然后将students.csv放入目录中。
3) 启动Hive Shell。
例如，替换您的HiveServer的URI：
beeline -u jdbc:hive2://myhiveserver.com:10000 -n hive -p
4) 创建一个指定文本格式的外部表的Schema定义，并从s3a:// andrena中的students.csv中加载数据。
CREATE EXTERNAL TABLE IF NOT EXISTS names_text( student_ID INT, FirstName STRING, LastName STRING, year STRING, Major STRING) COMMENT 'Student Names' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 's3a://andrena';
5) 验证Hive仓库是否将学生姓名存储在外部表中。
SELECT * FROM names_text;
6) 为托管表创建Schema。
CREATE TABLE IF NOT EXISTS Names( student_ID INT, FirstName STRING, LastName STRING, year STRING, Major STRING) COMMENT 'Student Names';
7) 将外部表数据移动到托管表。
INSERT OVERWRITE TABLE Names SELECT * FROM names_text;
8) 验证外部表中的数据是否位于托管表中，然后删除外部表，并验证数据是否仍在托管表中。
SELECT * from Names; DROP TABLE names_text; SELECT * from Names;
出现托管表名称的结果。
9) 验证外部表Schema定义是否丢失。
SELECT * from names_text;
names_text由于外部表的Schema丢失，因此从中全选不返回任何结果。
10) 检查HDFS或S3上的students.csv文件是否完好无损。
1.9 删除外部表和数据
在外部表上运行DROP TABLE时，默认情况下，Hive仅删除元数据（Schema）。如果您希望DROP TABLE命令也删除外部表中的实际数据，就像DROP TABLE在托管表上一样，则需要相应地配置表属性。
1) 创建一个要在Hive中查询的数据的CSV文件。
2) 启动Hive。
3) 创建一个外部表来存储CSV数据，并配置该表，以便将其与数据一起删除。
CREATE EXTERNAL TABLE IF NOT EXISTS names_text( a INT, b STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 's3a://andrena' TBLPROPERTIES ('external.table.purge'='true');
4) 在外部表上运行DROP TABLE。
DROP TABLE names_text;
该表将从Hive Metastore中删除，并且数据存储在外部。例如，names_text将其从Hive Metastore中删除，并将存储数据的CSV文件也从HDFS中删除。
5) 防止外部表中的数据被DROP TABLE语句删除。
ALTER TABLE addresses_text SET TBLPROPERTIES ('external.table.purge'='false');
1.10 将托管非事务性表转换为外部
您可以使用 ALTER TABLE 语句轻松地将托管表（如果它不是 ACID（事务）表）转换为外部表。从 Hive 1或 2 升级后，您可能有一个非 ACID 的托管表。
以下伪代码将托管表（如果它不是事务性的）更改为外部。删除表时，数据和元数据也会被删除。
ALTER TABLE ... SET TBLPROPERTIES('EXTERNAL'='TRUE','external.table.purge'='true')
1.11 使用约束
您可以使用 SQL 约束来强制执行数据完整性并提高性能。使用约束，优化器可以简化查询。约束可以使数据可预测且易于定位。例如，使用约束和支持的修饰符，您可以按照示例将查询限制为唯一值或非空值。
Hive 仅强制执行 DEFAULT、NOT NULL 和 CHECK，而不强制执行 PRIMARY KEY、FOREIGN KEY 和 UNIQUE。
您可以在查询中使用下面列出的约束。Hive 仅强制执行 DEFAULT、NOT NULL 和 CHECK，而不强制执行 PRIMARY KEY、FOREIGN KEY 和 UNIQUE。DEFAULT 即使强制执行，也不支持复杂类型（数组、映射、结构）。约束实施仅限于元数据级别。此限制有助于与第三方工具集成和优化约束声明，例如物化视图重写。
· CHECK
n 可以放置在列中的值的范围的限制。
· DEFAULT
n 确保存在一个值，该值在数据仓库卸载案例中很有用。
· PRIMARY KEY
n 使用唯一标识符标识表中的每一行。
· FOREIGN KEY
n 使用唯一标识符标识另一个表中的行。
· NOT NULL
n 检查列值未设置为NULL。
1.11.1支持的修饰符
您可以使用以下可选的修饰符：
· ENABLE
n 确保所有传入数据符合约束。
· DISABLE
n 不确保所有传入数据都符合约束。
· VALIDATE
n 检查表中所有现有数据是否符合约束。
· NOVALIDATE
n 不检查表中所有现有数据是否符合约束。
· ENFORCED
n 映射到 ENABLE NOVALIDATE。
· NOT ENFORCED
n 映射到禁用 NOVALIDATE。
· RELY
n 指定遵守约束；优化器使用它来应用进一步的优化。
· NORELY
n 指定不遵守约束。
您可以按照以下语法使用修饰符：
( ( ( (ENABLE | DISABLE) (VALIDATE | NOVALIDATE) ) | ( ENFORCED | NOT ENFORCED ) ) (RELY | NORELY) )
默认修改器
以下默认修饰符已就位：
· ENABLE 的默认修饰符是 NOVALIDATE RELY。
· DISABLE 的默认修饰符是 NOVALIDATE NORELY。
· 如果在声明约束时未指定修饰符，则默认值为 ENABLE NOVALIDATE RELY。以下约束不支持 ENABLE：
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE KEY
为防止出现错误，请在使用这些约束覆盖默认值时指定修改器。
1.11.2约束示例
优化器使用约束信息做出明智的决定。以下示例显示了约束的使用。
以下示例显示如何创建声明 NOT NULL 内联约束以约束列的表。
CREATE TABLE t(a TINYINT, b SMALLINT NOT NULL ENABLE, c INT);
受约束的列 b 接受一个 SMALLINT 值，如第一个 INSERT 语句中所示。
INSERT INTO t values(2,45,5667); ... ---------------------------------------------------------- 1 row affected ...
受约束的列 b不接受 NULL 值。
INSERT INTO t values(2,NULL,5667); Error: Error running query: org.apache.hadoop.hive.ql.exec.errors.DataConstraintViolationError: / Either CHECK or NOT NULL constraint violated! (state=,code=0)
以下示例显示如何声明外键约束。您可以在外部约束中指定约束名称，在本例中为 fk
CREATE TABLE Persons ( ID INT NOT NULL, Name STRING NOT NULL, Age INT, Creator STRING DEFAULT CURRENT_USER(), CreateDate DATE DEFAULT CURRENT_DATE(), PRIMARY KEY (ID) DISABLE NOVALIDATE); CREATE TABLE BusinessUnit ( ID INT NOT NULL, Head INT NOT NULL, Creator STRING DEFAULT CURRENT_USER(), CreateDate DATE DEFAULT CURRENT_DATE(), PRIMARY KEY (ID) DISABLE NOVALIDATE, CONSTRAINT fk FOREIGN KEY (Head) REFERENCES Persons(ID) DISABLE NOVALIDATE );
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_constraints.html
1.12 确定表类型
您可以确定Hive表的类型，是否具有ACID属性，存储格式（例如ORC）和其他信息。出于多种原因，了解表类型非常重要，例如，了解如何在表中存储数据或从集群中完全删除数据。
1) 在Hive Shell中，获取对该表的扩展描述。
例如： DESCRIBE EXTENDED mydatabase.mytable;
2) 滚动到命令输出的底部以查看表类型。
以下输出包括对表类型的管理，并 transaction=true指示该表具有ACID属性：
... | Detailed Table Information | Table(tableName:t2, dbName:mydatabase, owner:hdfs, createTime:1538152187, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:a, type:int, comment:null), FieldSchema(name:b, type:int, comment:null)], ...
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_table_type.html
2 HMS表存储
您需要了解在运行CREATE TABLE语句或将表迁移到Cloudera Data Platform时HMS如何存储Hive表。语句的成功或失败，结果表类型和表位置取决于许多因素。
2.1 HMS表的转换
HMS包含以下有关您创建的表的Hive元数据：
· 表定义
· 列名
· 数据类型
· 中央Schema存储库中的注释
在CREATE TABLE语句中使用EXTERNAL关键字时，HMS将表存储为外部表。当您省略EXTERNAL关键字并创建托管表或提取托管表时，HMS可能会将表转换为外部表，否则表创建可能失败，具体取决于表属性。影响表转换的重要表属性是ACID或Non-ACID表类型：
· 非ACID
n 如果表属性不包含任何与ACID相关的属性，则此属性为true 。例如，该表不包含此类属性 transactional=true或insert_only=true。
· ACID
n 如果表属性确实包含一个或多个ACID属性，则此属性为true。
· 完全的ACID
n 此属性为true，如果表属性包含 transactional=true但不是 insert_only=true。
· 仅插入的ACID
n 表属性包含insert_only=true。
以下矩阵显示了表类型以及是否支持location属性。
ACID |
受管理 |
位置属性 |
注释 |
行动 |
Non-ACID |
是 |
是 |
迁移到CDP，例如从HDP或CDH群集 |
表存储为外部 |
非ACID，ACID，完整ACID，仅插入ACID |
是 |
否 |
表位置为空 |
表存储在子目录中 |
HMS检测用于与HMS进行交互的客户端类型，例如Hive或Spark，并将客户端的功能与表要求进行比较。HMS根据比较结果执行以下操作：
表要求 |
客户端符合要求 |
托管表 |
ACID表类型 |
行动 |
客户端可以写入任何类型的ACID表 |
没有 |
是 |
是 |
创建表失败 |
客户端可以写入完整的ACID表 |
没有 |
是 |
insert_only = true |
创建表失败 |
客户端可以写入仅插入的ACID表 |
没有 |
是 |
insert_only = true |
创建表失败 |
例如，如果Spark客户端不具备所需的功能，则会出现以下类型的错误消息：
Spark has no access to table `mytable`. Clients can access this table only if they have the following capabilities: CONNECTORREAD,HIVEFULLACIDREAD, HIVEFULLACIDWRITE, HIVEMANAGESTATS, HIVECACHEINVALIDATE, ...
3 Hive 3 ACID事务
Hive 3实现对事务表的原子性和隔离性操作是通过使用涉及增量文件的写入、读取、插入、创建、删除和更新操作的技术来实现，这些技术可以提供查询状态信息并帮助您解决查询问题。
3.1 读写操作
Hive 3的读写操作提高了事务表的ACID的质量和性能。事务表的性能与其他表一样。Hive支持所有TPC Benchmark DS（TPC-DS）查询。
Hive 3和更高版本将原子操作从简单的写入和插入扩展为支持以下操作：
· 写入多个分区
· 在单个SELECT语句中使用多个insert子句
一条语句可以写入多个分区或多个表。如果操作失败，则用户看不到部分写入或插入。即使数据经常更改，例如每小时更改百分之一，操作仍然保持快速。Hive 3和更高版本不会覆盖整个分区以执行更新或删除操作。
Hive自动压缩ACID事务文件，而不会影响并发查询。当查询许多小的分区文件时，自动压缩可提高查询性能和元数据占用量。
读取语义包括快照隔离。当读取操作开始时，Hive在逻辑上锁定仓库的状态。读操作不受操作期间发生的更改的影响。
3.2 仅插入表中的原子性和隔离性
当仅插入事务开始时，事务管理器将获得事务ID。对于每次写入，事务管理器都会分配一个写入ID。此ID确定实际写入数据的路径。以下代码显示创建仅插入事务表的语句示例：
CREATE TABLE tm (a int, b int) TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only')
假设发生了三个插入操作，而第二个失败：
INSERT INTO tm VALUES(1,1); INSERT INTO tm VALUES(2,2); // Fails INSERT INTO tm VALUES(3,3);
对于每个写入操作，Hive都会创建一个增量目录，事务管理器将在该目录中写入数据文件。Hive将所有数据写入由写入ID指定的增量文件，并映射到表示原子操作的事务ID。如果发生故障，该事务将标记为已中止，但它是原子的：
tm ___ delta_0000001_0000001_0000 └── 000000_0 ___ delta_0000002_0000002_0000 //Fails └── 000000_0 ___ delta_0000003_0000003_0000 └── 000000_0
在读取过程中，事务管理器维护每个事务的状态。当读取器启动时，它会请求快照信息，该信息由高水印表示。水印标识系统中最高的事务ID，后跟代表仍在运行或中止的事务的异常列表。
读取器查看增量，并过滤出或跳过任何已中止或仍在运行的事务ID。读者可以将此技术与参与事务的任意数量的分区或表一起使用，以实现原子性和对事务表的操作隔离。
3.3 CRUD表中的原子性和隔离
使用以下SQL语句创建完整的CRUD（创建，检索，更新，删除）事务表：
CREATE TABLE acidtbl (a INT, b STRING);
运行SHOW CREATE TABLE acidtbl提供有关默认值的信息：事务性（ACID）和ORC数据存储格式：
+----------------------------------------------------+ | createtab_stmt | +----------------------------------------------------+ | CREATE TABLE `acidtbl`( | | `a` int, | | `b` string) | | ROW FORMAT SERDE | | 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' | | STORED AS INPUTFORMAT | | 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' | | OUTPUTFORMAT | | 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' | | LOCATION | | 's3://myserver.com:8020/warehouse/tablespace/managed/hive/acidtbl' | | TBLPROPERTIES ( | | 'bucketing_version'='2', | | 'transactional'='true', | | 'transactional_properties'='default', | | 'transient_lastDdlTime'='1555090610') | +----------------------------------------------------+
支持更新和删除的表需要稍微不同的技术来实现原子性和隔离性。Hive在仅追加模式下运行，这意味着Hive不执行就地更新或删除。在就地更新或删除存在的情况下，无法隔离读取器和写入器。在这种情况下，需要使用锁管理器或其他机制进行隔离。这些机制为长期运行的查询带来了问题。
代替就地更新，Hive用行ID装饰每一行。行ID是一个 struct，由以下信息组成：
· 映射到创建行的事务的写ID
· 创建行的物理写入器的存储区ID（具有若干位信息的位支持整数）
· 行ID，在将行写入数据文件时对行进行编号
当发生删除时，Hive会将更改附加到表中，而不是就地删除。删除的数据将不可用，压缩过程将在以后处理垃圾回收。
3.4 创建操作
下面的示例将几行数据插入完整的CRUD事务表中，创建一个增量文件，并将行ID添加到数据文件中。
INSERT INTO acidtbl (a,b) VALUES (100, "oranges"), (200, "apples"), (300, "bananas");
此操作将生成目录和文件delta_00001_00001 / bucket_0000，它们具有以下数据：
ROW_ID |
a |
b |
{1,0,0} |
100 |
"oranges" |
{1,0.1} |
200 |
"apples" |
{1,0,2} |
300 |
"bananas" |
3.5 删除操作
与单行匹配的delete语句也会创建一个增量文件，称为delete-delta。该文件为与您的查询匹配的行存储了一组行ID。在读取时，读取器会查看此信息。当找到与行匹配的删除事件时，它将跳过该行，并且该行不包括在运算符管道中。下面的示例从事务表中删除数据：
DELETE FROM acidTbl where a = 200;
此操作将生成目录和文件delete_delta_00002_00002 / bucket_0000，它们具有以下数据：
ROW_ID |
a |
b |
{1,0,1} |
null |
null |
3.6 更新操作
更新结合了新数据的删除和插入。下面的示例更新事务表：
UPDATE acidTbl SET b = "pears" where a = 300;
一个增量文件包含delete事件，另一个包含insert事件：
要求AcidInputFormat的读取器将应用所有插入事件，并封装所有逻辑以处理删除事件。读取操作首先从事务管理器获取快照信息，并根据快照信息选择与该读取操作相关的文件。接下来，该流程将每个数据文件拆分为每个流程必须处理的片段数。相关的删除事件被本地化到每个处理任务。删除事件存储在已排序的ORC文件中。压缩后的存储数据极少，这是Hive 3的显着优势。您不再需要担心增量文件中的插入事件会使网络饱和。
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_3_internals.html
4 计划查询
如果您需要一种简单但强大且安全的方式来创建、管理和监控计划作业，您可以使用 Apache Hive 计划查询。您可以使用计划查询替换操作系统级别的调度程序，例如 cron、Apache Oozie 或 Apache Airflow。
使用 SQL 语句，您可以安排 Hive查询重复运行、监控查询进度并可选择禁用查询计划。您可以执行查询以定期摄取数据、刷新物化视图、复制数据以及执行其他重复性任务。例如，您可以每 10 分钟将流中的数据插入到事务表中，每小时刷新一次用于 BI 报告的物化视图，并每天将数据从一个集群复制到另一个集群。
Hive 计划查询由以下部分组成：
· 调度器中的唯一名称
· 要执行的 SQL 语句
· 由 Quartz cron 表达式定义的执行计划。
Quartz cron 表达式富有表现力且灵活。例如，表达式可以描述简单的计划，例如每 10 分钟一次，但也可以描述在 2021 年 1 月和 2021 年 2 月的第一个星期日上午 10 点执行。您可以用易于理解的格式描述常见的计划，例如例如每 20 分钟或每天“3:25:00”。
4.1 操作
计划查询属于命名空间，它是负责执行查询的 HiveServer (HS2) 实例的集合。计划查询存储在 Hive 元存储中。Metastore 存储计划查询、正在进行和先前执行的语句的状态以及其他信息。HiveServer 会定期轮询Metastore 以检索将要执行的预定查询。如果您在单个部署中运行多个 HiveServer 实例，则元存储保证在任何给定时间只有其中一个执行某个预定查询。
您可以使用专用 SQL 语句创建、更改和删除计划查询。
4.2 启用计划查询
您需要知道如何启用和禁用计划查询，并了解默认状态如何防止您无意中运行查询。
默认情况下，计划查询在 CDP 中以禁用模式创建。此默认设置有助于防止您无意中运行新的计划查询。您必须显式启用新的计划查询。调度查询可以在错误的时间保持集群处于唤醒状态。要启用特定计划，例如 schedule1，请执行 ALTER SCHEDULED QUERY 语句：
ALTER SCHEDULED QUERY schedule1 ENABLE;
要禁用此计划：
ALTER SCHEDULED QUERY schedule1 DISABLE;
4.3 启用所有计划查询
您可以使用 SQL 命令启用单个计划查询，但要启用多个计划查询，您需要在 Cloudera Manager 中执行几个步骤。
要在CDP Private Cloud Base 中启用所有新创建的计划，请执行以下步骤：
1) 在 Cloudera Manager 中，单击集群> Hive on TEZ >配置
2) 在搜索中，输入安全。
3) 在hive-site.xml HIVE_ON_TEZ-1 (Service-Wide) 的 Hive 服务高级配置片段（安全阀）中，单击+并添加以下属性： hive.scheduled.queries.create.as.enabled
4) 将值设置为true。
5) 在 Tez 上保存并重新启动 Hive。
4.4 定期重建物化视图
使用物化视图可以提高查询性能。当新数据添加到基础表时，您需要刷新物化视图内容。您可以安排此任务，而不是手动重建实体化视图。重建会定期发生，并且对用户是透明的。
在此任务中，您将创建一个用于存储员工信息的架构。想象一下，您将许多员工的数据添加到表中。假设您的数据库的许多用户发出查询以访问有关去年雇用的员工的数据，包括他们所属的部门。您创建表的物化视图来处理这些查询。想象一下，招聘了新员工，您将他们的记录添加到表中。这些更改使物化视图内容过时。您需要刷新其内容。您创建计划查询来执行此任务。除非输入表发生更改，否则不会发生计划的重建。您可以通过绕过计划并立即执行计划来测试计划查询。最后，您更改计划以减少重建频率。
1) 为员工数据创建数据库Schema。
CREATE TABLE emps ( empid INTEGER, deptno INTEGER, name VARCHAR(256), salary FLOAT, hire_date TIMESTAMP); CREATE TABLE depts ( deptno INTEGER, deptname VARCHAR(256), locationid INTEGER);
2) 要处理许多查询以访问最近雇用的员工和部门数据，创建物化视图。
CREATE MATERIALIZED VIEW mv_recently_hired AS SELECT empid, name, deptname, hire_date FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2020-01-01 00:00:00';
3) 通过查询员工数据使用物化视图。
SELECT empid, name FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2020-03-01 00:00:00' AND deptname = 'finance';
4) 假设发生了新招聘并且您向emps表中添加了新记录，则重建物化视图。
ALTER MATERIALIZED VIEW mv_recently_hired REBUILD;
重建会刷新物化视图的内容。
5) 创建计划查询以每 10 分钟调用一次重建语句。
CREATE SCHEDULED QUERY scheduled_rebuild EVERY 10 MINUTES AS ALTER MATERIALIZED VIEW mv_recently_hired REBUILD;
除非emps表没有更改，否则每 10 分钟执行一次重建。如果物化视图可以增量重建，则除非输入表发生更改，否则不会发生计划的重建。
6) 要测试计划，请立即执行计划查询。
ALTER SCHEDULED QUERY scheduled_rebuild EXECUTE;
7) 改变重建的频率。
ALTER SCHEDULED QUERY scheduled_rebuild EVERY 20 MINUTES;
4.5 获取预定查询信息并监控查询
创建计划查询后，您可以在 Hive 信息架构表中访问有关scheduled_queries 的信息。您还可以使用信息架构来监控计划的查询执行。
1) 查询信息模式以获取有关计划的信息。
SELECT * FROM information_schema.scheduled_queries WHERE schedule_name = 'scheduled_rebuild';
将显示有关计划查询的以下信息：
schedule_query_id
计划查询的唯一数字标识符。
Schema_name
计划查询的名称。
Enabled
当前是否启用了计划查询。
cluster_namespace
预定查询所属的命名空间。
Schedule
调度描述为 Quartz cron 表达式。
User
计划查询的所有者。
Query
要执行的 SQL 查询。
Next_execution
当此预定查询的下一次执行到期时。
2) 监视最近的计划查询执行。
SELECT * FROM information_schema.scheduled_executions;
您可以在 Hive 元存储中配置此信息的保留期。
schedule_execution_id
计划查询执行的唯一数字标识符。
Schedule_name
与此执行关联的计划查询的名称。
executor_query_id
分配给 HiveServer (HS2) 执行的查询 ID。
state
以下执行阶段之一。
· STARTED。预定查询到期并且 HiveServer 实例已检索其信息。
· EXECUTING。HiveServer 正在以可配置的时间间隔执行查询和报告进度。
· FAILED。由于错误或异常，查询执行已停止。
· FINISHED。查询执行成功。
· TIME_OUT。HiveServer 未提供超过可配置超时的查询状态更新。
Start_time
开始执行时间。
End_time
执行结束时间。
elapsed
开始时间和结束时间之间的差异。
Error_message
如果计划查询失败，则它包含与其失败相关的错误消息。
Last_update_time
HiveServer 上次更新查询状态的时间。
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_scheduling.html
5 使用物化视图
Apache Hive与Apache Calcite一起使用您创建的物化视图自动优化查询。
如果您的查询具有重复性，您可以通过使用物化视图来减少延迟和资源消耗。您可以使用您创建的物化视图自动优化您的查询。
使用物化视图，优化器可以比较新旧表，重写查询以加速处理，并在发生数据更新时管理物化视图的维护。优化器可以使用物化视图完全或部分重写投影、过滤器、连接和聚合。Hive 将物化视图存储在 Hive 仓库中。您可以执行以下与物化视图相关的操作：
· 创建查询或子查询的物化视图
· 删除物化视图
· 显示物化视图
· 描述一个物化视图
· 基于物化视图启用或禁用查询重写
· 基于任何物化视图全局启用或禁用重写
· 使用分区可以改善物化视图的性能。
5.1 创建和使用物化视图
您可以创建查询的物化视图来计算和存储重复执行的昂贵操作（例如特定连接）的结果。当您发出由该物化视图指定的查询时，优化器会根据它重写查询。此操作可节省重新处理。查询性能提高。
在此任务中，您将创建并填充示例表。您创建表联接的物化视图。随后，当您运行查询以联接表时，查询计划将利用预先计算的联接来加快处理速度。该任务过分简化，旨在显示物化视图的语法和输出，而不是演示导致现实世界中任务加速的处理，该任务将处理大量数据。
1) 在Hive Shell或其他Hive UI中，创建两个表：
CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary FLOAT, hire_date TIMESTAMP); CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid INT);
2) 出于示例目的，将一些数据插入表中：
INSERT INTO TABLE emps VALUES (10001,101,'jane doe',250000,'2018-01-10'); INSERT INTO TABLE emps VALUES (10002,100,'somporn klailee',210000,'2017-12-25'); INSERT INTO TABLE emps VALUES (10003,200,'jeiranan thongnopneua',175000,'2018-05-05'); INSERT INTO TABLE depts VALUES (100,'HR',10); INSERT INTO TABLE depts VALUES (101,'Eng',11); INSERT INTO TABLE depts VALUES (200,'Sup',20);
3) 创建一个物化视图以连接表：
CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2017-01-01';
4) 执行一个利用物化视图执行的预计算的查询：
SELECT empid, deptname FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2017-01-01' AND hire_date <= '2019-01-01';
输出为：
+--------+-----------+ | empid | deptname | +--------+-----------+ | 10003 | Sup | | 10002 | HR | | 10001 | Eng | +--------+-----------+
5.2 验证查询重写的使用
您可以使用 Apache Hive 解释日志记录来检查物化视图是否使用了查询重写。
默认情况下，解释日志记录在 CDP 中设置为 false。
1) 在Cloudera Manager中，启用解释日志记录：导航到Clusters > HIVE_ON_TEZ-1 > Configuration，搜索hive.log.explain.output，选中HiveServer2 Default Group，然后单击Save Changes。
2) 通过运行EXPLAIN EXTENDED语句，验证查询重写是否使用了物化视图：
EXPLAIN EXTENDED SELECT empid, deptname FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2017-01-01' AND hire_date <= '2019-01-01'; 输出显示default.mv1该计划的TableScan部分中的物化视图的别名。 OPTIMIZED SQL: SELECT `empid`, `deptname` FROM `default`.`mv1` WHERE TIMESTAMP '2019-01-01 00:00:00.000000000' >= `hire_date` STAGE DEPENDENCIES: Stage-0 is a root stage STAGE PLANS: Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: TableScan alias: default.mv1 filterExpr: (hire_date <= TIMESTAMP'2019-01-01 00:00:00') (type: boolean) | GatherStats: false Filter Operator isSamplingPred: false predicate: (hire_date <= TIMESTAMP'2019-01-01 00:00:00') (type: boolean) Select Operator expressions: empid (type: int), deptname (type: varchar(256)) outputColumnNames: _col0, _col1 ListSink
5.3 在子查询中使用物化视图
您可以创建一个查询，该查询具有优化器基于物化视图重写的子查询。您创建一个物化视图，然后运行 Hive 使用该物化视图以优化查询。
在此任务中，您将创建一个物化视图，并在子查询中使用它来返回目标-起源对的数量。假设数据驻留在一个flights_hdfs具有以下数据的表中：
c_id |
dest |
origin |
1 |
Chicago |
Hyderabad |
2 |
London |
Moscow |
... |
1) 为目的地和原始数据创建一个名为flights_hdfs的表的Schema定义。
CREATE TABLE flights_hdfs( c_id INT, dest VARCHAR(256), origin VARCHAR(256));
2) 创建一个包含目的地和起点的物化视图。
CREATE MATERIALIZED VIEW mv1 AS SELECT dest, origin, count(*) FROM flights_hdfs GROUP BY dest, origin;
3) 当您必须再次计算目的地和起点时，利用物化视图加快查询速度。
例如，使用子查询来选择目标-源对的数量，如物化视图。
SELECT count(*)/2 FROM( SELECT dest, origin, count(*) FROM flights_hdfs GROUP BY dest, origin ) AS t;
显然，Hive 使用自创建物化视图以来已经存在的工作，而不是重新处理。
5.4 删除物化视图
您必须了解何时删除物化视图才能成功删除相关表。
在相关表上执行DROP TABLE操作之前，请删除物化视图。Hive不支持删除与物化视图有关系的表。
在此任务中，从my_database数据库中删除名为mv1的物化视图。
将物化视图放入名为mv1的my_database中。
DROP MATERIALIZED VIEW my_database.mv1;
5.5 Show 物化视图
您可以列出当前数据库或另一个数据库中的所有物化视图。您可以使用正则表达式通配符过滤指定数据库中的物化视图列表。
您可以使用正则表达式通配符来过滤要查看的物化视图的列表。支持以下通配符：
· 星号（*）
n 代表一个或多个字符。
· 管道符号（|）
n 代表一个选择。
例如，mv_q *和* mv | q1 *匹配物化视图mv_q1。未找到匹配项不会导致错误。
1) 列出当前数据库中的物化视图。
SHOW MATERIALIZED VIEWS;
2) 列出特定数据库中的物化视图。
SHOW MATERIALIZED VIEWS IN my_database;
3) 显示名称以mv开头的物化视图。
SHOW MATERIALIZED VIEWS mv*;
5.6 Describe物化视图
您可以获取有关物化视图的摘要，详细信息和带格式的信息。
此任务建立在创建名为mv1的物化视图的任务的基础上。
1) 获取有关名为mv1的物化视图的摘要信息。
DESCRIBE mv1; +------------+---------------+----------+ | col_name | data_type | comment | +------------+---------------+----------+ | empid | int | | | deptname | varchar(256) | | | hire_date | timestamp | | +------------+---------------+----------+
2) 获取有关名为mv1的物化视图的详细信息。
DESCRIBE EXTENDED mv1; +-----------------------------+---------------------------------... | col_name | data_type ... +-----------------------------+---------------------------------... | empid | int ... | deptname | varchar(256) ... | hire_date | timestamp ... | | NULL ... | Detailed Table Information |Table(tableName:mv1, dbName:default, owner:hive, createTime:1532466307, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:empid, type:int, comment:null), FieldSchema(name:deptname, type:varchar(256), comment:null), FieldSchema(name:hire_date, type:timestamp, comment:null)], location:hdfs://myserver.com:8020/warehouse/tablespace/managed/hive/mv1, inputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.ql.io.orc.OrcSerde, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=488, numRows=4, rawDataSize=520, COLUMN_STATS_ACCURATE={\"BASIC_STATS\":\"true\"}, numFiles=1, transient_lastDdlTime=1532466307, bucketing_version=2}, viewOriginalText:SELECT empid, deptname, hire_date\nFROM emps2 JOIN depts\nON (emps2.deptno = depts.deptno)\nWHERE hire_date >= '2017-01-17', viewExpandedText:SELECT `emps2`.`empid`, `depts`.`deptname`, `emps2`.`hire_date`\nFROM `default`.`emps2` JOIN `default`.`depts`\nON (`emps2`.`deptno` = `depts`.`deptno`)\nWHERE `emps2`.`hire_date` >= '2017-01-17', tableType:MATERIALIZED_VIEW, rewriteEnabled:true, creationMetadata:CreationMetadata(catName:hive, dbName:default, tblName:mv1, tablesUsed:[default.depts, default.emps2], validTxnList:53$default.depts:2:9223372036854775807::$default.emps2:4:9223372036854775807::, materializationTime:1532466307861), catName:hive, ownerType:USER)
3) 获取有关名为mv1的物化视图的格式化详细信息。
DESCRIBE FORMATTED mv1; +-------------------------------+--------------------------------... | col_name | data_type ... +-------------------------------+--------------------------------... | # col_name | data_type ... | empid | int ... | deptname | varchar(256) ... | hire_date | timestamp ... | | NULL ... | # Detailed Table Information | NULL ... | Database: | default ... | OwnerType: | USER ... | Owner: | hive ... | CreateTime: | Tue Jul 24 21:05:07 UTC 2019 ... | LastAccessTime: | UNKNOWN ... | Retention: | 0 ... | Location: | hdfs://myserver... | Table Type: | MATERIALIZED_VIEW ... | Table Parameters: | NULL ... | | COLUMN_STATS_ACCURATE ... | | bucketing_version ... | | numFiles ... | | numRows ... | | rawDataSize ... | | totalSize ... | | transient_lastDdlTime ... | | NULL ... | # Storage Information | NULL ... | SerDe Library: | org.apache.hadoop.hive.ql.io.or... | InputFormat: | org.apache.hadoop.hive.ql.io.or... | OutputFormat: | org.apache.hadoop.hive.ql.io.or... | Compressed: | No ... | Num Buckets: | -1 ... | Bucket Columns: | [] ... | Sort Columns: | [] ... | # View Information | NULL ... | View Original Text: | SELECT empid, deptname, hire_da... | View Expanded Text: | SELECT `emps2`.`empid`, `depts`... | View Rewrite Enabled: | Yes ...
5.7 管理查询重写
您可以使用Hive查询来停止或启动优化器以基于物化视图重写查询，并且作为管理员，您可以基于物化视图全局启用或禁用所有查询重写。
默认情况下，优化器可以基于物化视图重写查询。如果要在不考虑物化视图的情况下执行查询（例如，测量执行时间差），则可以禁用重写，然后再次启用它。
1) 禁用基于默认数据库中名为mv1的物化视图的查询重写。
ALTER MATERIALIZED VIEW default.mv1 DISABLE REWRITE;
2) 启用基于物化视图mv1的查询重写。
ALTER MATERIALIZED VIEW default.mv1 ENABLE REWRITE;
3) 通过设置全局属性，全局禁用基于物化视图的查询重写。
SET hive.materializedview.rewriting=true;
5.8 创建和使用分区物化视图
创建物化视图时，可以对选定的列进行分区以提高性能。分区会将表的视图分为多个部分，这通常可以改善对物化视图与表或其他物化视图的分区联接的查询重写。
此任务假定您创建了emps和 depts表的物化视图，并假定您创建了这些表。该 emps表包含以下数据：
empid |
deptno |
name |
salary |
hire_date |
10001 |
101 |
jane doe |
250000 |
2018-01-10 |
10005 |
100 |
somporn klailee |
210000 |
2017-12-25 |
10006 |
200 |
jeiranan thongnopneua |
175000 |
2018-05-05 |
该depts表包含以下数据：
deptno |
deptname |
locationid |
100 |
HR |
10 |
101 |
Eng |
11 |
200 |
Sup |
20 |
在此任务中，您将创建两个物化视图：一个在部门上对数据进行分区；一个在部门上进行分区。另一个按雇用日期划分数据。您从原始表中选择按部门过滤的数据，而不是从任何一个物化视图中选择数据。解释计划表明，Hive重写了查询以提高效率，以便从按部门对数据进行分区的物化视图中选择数据。在此任务中，您还将看到重建物化视图的效果。
1) 创建emps表的物化视图，该视图将数据划分为多个部门。
CREATE MATERIALIZED VIEW partition_mv_1 PARTITIONED ON (deptno) AS SELECT hire_date, deptno FROM emps WHERE deptno > 100 AND deptno < 200;
2) 创建第二个物化视图，该视图按雇用日期而不是部门编号对数据进行分区。
CREATE MATERIALIZED VIEW partition_mv_2 PARTITIONED ON (hire_date) AS SELECT deptno, hire_date FROM emps where deptno > 100 AND deptno < 200;
3) 通过直接从emps表中选择部门101的数据来生成扩展的解释计划，而无需使用物化视图。
EXPLAIN EXTENDED SELECT deptno, hire_date FROM emps_a where deptno = 101;
解释计划表明，Hive使用作业的两个物化视图中最好的一个来重写查询，以提高效率：partition_mv_1。
+----------------------------------------------------+ | Explain | +----------------------------------------------------+ | OPTIMIZED SQL: SELECT CAST(101 AS INTEGER) AS `deptno`, `hire_date` | | FROM `default`.`partition_mv_1` | | WHERE 101 = `deptno` | | STAGE DEPENDENCIES: | | Stage-0 is a root stage ...
4) 将Jane Doe的聘用日期更改为2018年2月12日，重新构建一个物化视图，而不是另一个，并比较两个物化视图的内容。
INSERT INTO emps VALUES (10001,101,'jane doe',250000,'2018-02-12'); ALTER MATERIALIZED VIEW partition_mv_1 REBUILD; SELECT * FROM partition_mv_1 where deptno = 101; SELECT * FROM partition_mv_2 where deptno = 101;
选择重新构建的partition_mv_1的输出包括原始行和新插入的行，因为INSERT不执行就地更新（覆盖）。
+---------------------------+------------------------+ | partition_mv_1.hire_date | partition_mv_1.deptno | +---------------------------+------------------------+ | 2018-01-10 00:00:00.0 | 101 | | 2018-02-12 00:00:00.0 | 101 | +---------------------------+------------------------+
另一个分区的输出是过时的，因为您没有重建它：
+------------------------+---------------------------+ | partition_mv_2.deptno | partition_mv_2.hire_date | +------------------------+---------------------------+ | 101 | 2018-01-10 00:00:00.0 | +------------------------+---------------------------+
5) 创建第二个雇员表，并在部门编号上连接这些表的物化视图。
CREATE TABLE emps2 TBLPROPERTIES AS SELECT * FROM emps; CREATE MATERIALIZED VIEW partition_mv_3 PARTITIONED ON (deptno) AS SELECT emps.hire_date, emps.deptno FROM emps, emps2 WHERE emps.deptno = emps2.deptno AND emps.deptno > 100 AND emps.deptno < 200;
6) 生成一个解释计划，该计划使用忽略分区物化视图的查询将表emps和emps2连接到部门编号。
EXPLAIN EXTENDED SELECT emps.hire_date, emps.deptno FROM emps, emps2 WHERE emps.deptno = emps2.deptno AND emps.deptno > 100 AND emps.deptno < 200;
输出显示Hive重写查询以使用分区的物化视图partition_mv_3，即使您的查询省略了物化视图。
7) 验证partition_mv_3为partition_mv_3的deptno = 101设置了分区。
SHOW PARTITIONS partition_mv_3;
输出为：
+-------------+ | partition | +-------------+ | deptno=101 | +-------------+
6 Apache Hive查询基础
使用 Apache Hive，您可以查询分布式数据存储。您需要了解 ANSI SQL 才能查看、维护或分析 Hive 数据。基础示例（例如如何从表中插入、更新和删除数据）可帮助您开始使用 Hive。
Hive支持ANSI SQL和原子性、一致性、隔离性和持久性（ACID）事务。对于更新数据，您可以使用符合ACID 标准的 MERGE 语句。物化视图根据访问模式优化查询。Hive 支持高达 300PB 的优化行列式 (ORC) 格式的表。还支持其他文件格式。您可以创建类似于传统关系数据库中的表。您可以使用熟悉的插入、更新、删除和合并 SQL 语句来查询表数据。insert 语句将数据写入表。更新和删除语句修改和删除已写入 Hive 的值。merge 语句通过利用共存表来简化更新、删除和更改数据捕获操作。
6.1 查询information_schema数据库
Hive支持ANSI标准的information_schema数据库，您可以在该数据库中查询有关表、视图、列和Hive特权的信息。information_schema数据显示了系统状态，类似于sys数据库数据，但是以一种用户友好的只读方式显示。您可以在information_schema查询中使用联接、聚合、过滤器和投影。
以下步骤之一涉及更改HiveServer与策略之间同步的时间间隔。HiveServer会在此时间间隔内响应任何策略更改。您只能在information_schema数据库中查询自己的特权信息。
1) 打开Ranger Access Manager，并检查是否为public组启用了预加载default database tables columns和information_schema database策略。
信息架构数据库默认每半小时同步一次。
2) 导航到服务> 配置单元> 配置> 高级> 自定义配置单元站点。
3) 添加hive.privilege.synchronizer.interval键并将其值设置为1。
此设置将同步从默认的半小时更改为一分钟。
4) 在Beeline shell中，启动Hive，并检查是否安装了information_schema数据库：
SHOW DATABASES; ... +---------------------+ | database_name | +---------------------+ | default | | information_schema | | sys | +---------------------+
5) 使用information_schema数据库列出数据库中的表。
USE information_schema; ... SHOW TABLES; ... +--------------------+ | tab_name | +--------------------+ | column_privileges | | columns | | schemata | | table_privileges | | tables | | views | +--------------------+
6) 查询information_schema数据库以查看例如有关可在其中插入值的表的信息。
SELECT * FROM information_schema.tables WHERE is_insertable_into='YES' limit 2; ... +--------------------+-------------------+----------------- |tables.table_catalog|tables.table_schema|tables.table_name +--------------------+-------------------+----------------- |default |default |students2 |default |default |t3
6.2 向表中插入数据
要将数据插入表中，您可以使用熟悉的 ANSI SQL 语句。一个简单的例子表明你必须完成这个基本任务。
要将数据插入 ACID 表，请使用优化行列式(ORC) 存储格式。要将数据插入非ACID 表，您可以使用其他Hive 支持的格式。您可以按以下语法指定分区：
INSERT INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] VALUES values_row [, values_row...]
其中
values_row是(value [, value]) 。
值可以为NULL或任何SQL文字。
1) 创建一个包含学生信息的ACID表。
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3,2));
2) 在表中插入几个学生的姓名、年龄和gpa值。
INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
3) 创建一个名为pageviews的表，并为您不想分配值的列分配空值。
CREATE TABLE pageviews (userid VARCHAR(64), link STRING, from STRING) PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS; INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23') VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null); INSERT INTO TABLE pageviews PARTITION (datestamp) VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
ACID 数据驻留在 Hive 仓库中。
6.3 更新表中的数据
您可以使用UPDATE语句修改已经存储在Apache Hive表中的数据。
使用以下语法构造UPDATE语句：
UPDATE tablename SET column = value [, column = value ...] [WHERE expression];
根据可选WHERE子句中指定的条件，UPDATE语句可能会影响表中的每一行。WHERE子句中的表达式必须是Hive SELECT子句支持的表达式。SET语句的右侧不允许子查询。分区和存储桶字段无法更新。
您必须具有SELECT和UPDATE特权才能使用UPDATE语句。
创建一条语句来更改gpa列的值为1.0的所有行的name列中的值。
UPDATE students SET name = null WHERE gpa <= 1.0;
6.4 合并表中的数据
示例语句显示了如何使用 ACID MERGE 语句有条件地将现有数据插入 Hive 表中。提到了额外的合并操作。您可以使用ACID MERGE语句有条件地在Hive表中插入，更新或删除现有数据。
MERGE语句基于ANSI标准SQL。
1) 构造一个查询以更新customer表中的客户名称和状态，以匹配new_customer_stage表中具有相同ID的客户的名称和状态。
2) 如果没有数据，则增强查询以将new_customer_stage 表中的数据插入到 customer 表中。
以类似的方式使用 MERGE 更新或删除数据。
MERGE INTO customer USING (SELECT * FROM new_customer_stage) sub ON sub.id = customer.id WHEN MATCHED THEN UPDATE SET name = sub.name, state = sub.new_state WHEN NOT MATCHED THEN INSERT VALUES (sub.id, sub.name, sub.state);
6.5 从表中删除数据
您可以使用DELETE语句删除已经写入ACID表的数据。
使用以下语法从Hive表中删除数据。DELETE FROM tablename [WHERE expression];
如果gpa列的值为1或0，请从学生表中删除所有数据行。
DELETE FROM students WHERE gpa <= 1,0;
6.6 创建临时表
在CDP Private Cloud Base中，您可以创建一个临时表来提高性能，方法是临时存储数据以供复杂查询中间使用或重用。
临时表数据仅在当前的Apache Hive会话期间持续存在。Hive在会话结束时会删除该表。如果使用永久表的名称来创建临时表，则在会话期间无法访问该永久表，除非您删除或重命名该临时表。您可以创建一个与其他用户的临时表同名的临时表，因为用户会话是独立的。临时表不支持分区的列和索引。
1) 创建一个具有一个字符串列的临时表。
CREATE TEMPORARY TABLE tmp1（tname varchar（64））;
2) 使用CREATE TABLE AS SELECT（CTAS）语句创建一个临时表。
CREATE TEMPORARY TABLE tmp2 AS SELECT c2, c3, c4 FROM mytable;
3) 使用CREATE TEMPORARY TABLE LIKE语句创建一个临时表。
CREATE TEMPORARY TABLE tmp3 LIKE tmp1;
6.7 配置临时表存储
在CDP Private Cloud Base中，您可以更改临时表数据的存储以满足系统要求。
默认情况下，Apache Hive将临时表数据存储在默认用户暂存目录/ tmp / hive- <用户名>中。通常，默认情况下不会将此位置设置为容纳大量数据，例如临时表产生的数据。
仅限CDP Private Cloud Base
1) 通过设置hive.exec.temporary.table.storage，将Hive配置为将临时表数据存储在内存中或SSD上。
n 将数据存储在内存中。hive.exec.temporary.table.storage=memory
n 将数据存储在SSD上。hive.exec.temporary.table.storage=ssd
2) 创建和使用临时表。
n Hive在会话结束时删除临时表。
6.8 使用子查询
Hive 支持 FROM 子句和 WHERE 子句中的子查询，您可以将这些子查询用于许多Apache Hive 操作，例如根据另一个表的内容过滤一个表中的数据。
子查询是内部查询中的SQL表达式，它将结果集返回到外部查询。从结果集中，评估外部查询。外部查询是包含内部子查询的主查询。WHERE子句中的子查询包含查询谓词和谓词运算符。谓词是计算为布尔值的条件。子查询中的谓词还必须包含谓词运算符。谓词运算符指定在谓词查询中测试的关系。
如果表中的year列的值与us_census表中的年份匹配，则从transfer_payments表中选择所有state和net_payments值。
SELECT state, net_payments FROM transfer_payments WHERE transfer_payments.year IN (SELECT year FROM us_census);
谓词以第一个WHERE关键字开头。谓词运算符是IN关键字。
如果us_census表的至少一行中的年值与transfer_payments表中的年值匹配，则谓词对于transfer_payments表中的一行返回true。
6.9 子查询限制
为了有效地构造查询，您必须了解WHERE子句中的子查询的限制。
· 子查询必须出现在表达式的右侧。
· 不支持嵌套子查询。
· 单个查询只能有一个子查询表达式。
· 子查询谓词必须显示为顶级连接词。
· 子查询在查询谓词中支持四个逻辑运算符：IN，NOT IN，EXISTS和NOT EXISTS。
· IN和NOT IN逻辑运算符只能在WHERE子句子查询中选择一列。
· EXISTS和NOT EXISTS运算符必须至少具有一个相关谓词。
· 子查询的左侧必须限定对表列的所有引用。
· 仅在子查询的WHERE子句中允许引用父查询中的列。
· 引用父查询中列的子查询谓词必须使用equals（=）谓词运算符。
· 子查询谓词可能不只引用父查询中的列。
· 带有隐含GROUP BY语句的相关子查询可能仅返回一行。
· 子查询中对列的所有不合格引用都必须解析为子查询中的表。
· 相关子查询不能包含窗口子句。
6.10 聚合和分组数据
您可以使用AVG，SUM或MAX函数来聚合数据，并使用GROUP BY子句将数据查询结果分组在一个或多个表列中。
GROUP BY子句显式对数据进行分组。Hive支持隐式分组，这在完全聚合表时会发生。
1) 构造一个查询，该查询返回按年份分组的工程部门中所有员工的平均工资。
SELECT year, AVG(salary) FROM Employees WHERE Department = 'engineering' GROUP BY year;
2) 构造一个隐式分组查询以获取薪水最高的员工。
SELECT MAX(salary) as highest_pay, AVG(salary) as average_pay FROM Employees WHERE Department = 'engineering';
6.11 查询关联数据
您可以查询一个表相对于另一表中的数据。
关联查询包含带有等于（=）运算符的查询谓词。运算符的一侧必须引用父查询中的至少一列，而另一侧必须引用子查询中的至少一列。不相关的查询不会引用父查询中的任何列。
在transfer_payments表中的state列的值与us_census表中的state列的值匹配的年份中，从transfer_payments表中选择所有state和net_payments值。
SELECT state, net_payments FROM transfer_payments WHERE EXISTS (SELECT year FROM us_census WHERE transfer_payments.state = us_census.state);
此查询是关联查询，因为子查询中的 = 谓词运算符的一侧引用了父查询中transfer_payments 表中的 state 列，而运算符的另一侧引用了 us_census 表中的 state 列。
该语句在WHERE子句中包含一个连词。
合取条件等于AND条件，而分离条件等于OR条件。以下子查询包含一个合取条件：
... WHERE transfer_payments.year = "2018" AND us_census.state = "california"
以下子查询包含一个分离条件：
... WHERE transfer_payments.year = "2018" OR us_census.state = "california"
6.12 使用公用表表达式
使用公用表表达式（common table expression：CTE），您可以创建一个重复引用子查询的临时视图。
CTE 是一组查询结果，该查询结果是从紧接在SELECT 或 INSERT 关键字之前的 WITH 子句中指定的简单查询中获得的。CTE 仅存在于单个 SQL 语句的范围内，而不存储在 Metastore 中。您可以在以下 SQL 语句中包含一个或多个 CTE：
· SELECT
· INSERT
· CREATE TABLE AS SELECT
· CREATE VIEW AS SELECT
子查询块中不支持递归查询，并且不支持WITH子句。
6.13 在查询中使用CTE
您可以使用公用表表达式（CTE）简化创建视图或表，选择数据或插入数据的过程。
1) 使用CTE基于您使用CREATE TABLE AS SELECT（CTAS）子句选择的另一个表创建表。
CREATE TABLE s2 AS WITH q1 AS (SELECT key FROM src WHERE key = '4') SELECT * FROM q1;
2) 使用CTE创建视图。
CREATE VIEW v1 AS WITH q1 AS (SELECT key FROM src WHERE key='5') SELECT * from q1;
3) 使用CTE选择数据。
WITH q1 AS (SELECT key from src where key = '5') SELECT * from q1;
4) 使用CTE插入数据。
CREATE TABLE s1 LIKE src; WITH q1 AS (SELECT key, value FROM src WHERE key = '5') FROM q1 INSERT OVERWRITE TABLE s1 SELECT *;
6.14 转义非法标识符
当您需要在列或分区名称中使用保留字，特殊字符或空格时，请将其括在反引号（`）中。
SQL中的标识符是用反引号括起来的字母数字和下划线（_）字符的序列。在Hive中，这些标识符称为带引号的标识符，并且不区分大小写。您可以使用标识符而不是列或表分区名称。
您已在hive-site.xml文件中将以下参数设置为column，以启用带引号的标识符：
在hive-site.xml 文件中将hive.support.quoted.identifiers 配置参数设置为column，在列名称中启用带引号的标识符，该参数的有效值为null 和column。例如：
hive.support.quoted.identifiers = column
1) 创建一个名为test的表，该表具有由带引号的标识符指定的两列字符串：
CREATE TABLE test (`x+y` String, `a?b` String);
2) 创建一个表，该表使用带引号的标识符和区域号来定义分区：
CREATE TABLE partition_date-1 (key string, value string) PARTITIONED BY (`dt+x` date, region int);
3) 创建一个使用加引号的标识符定义聚类的表：
CREATE TABLE bucket_test(`key?1` string, value string) CLUSTERED BY (`key?1`) into 5 buckets;
6.15 CHAR数据类型支持
在迁移过程中，了解Hive与其他数据库相比如何支持CHAR数据类型至关重要。
Data Type |
Hive |
Oracle |
SQL Server |
MySQL |
Teradata |
CHAR |
Ignore |
Ignore |
Ignore |
Ignore |
Ignore |
VARCHAR |
Compare |
Compare |
Configurable |
Ignore |
Ignore |
STRING |
Compare |
N/A |
N/A |
N/A |
N/A |
6.16 CDP 中的 ORC 与 Parquet
理解用于存储 Hive 数据的优化行列式(ORC) 文件格式和用于存储Impala 数据的 Parquet 之间的差异很重要。当您为应用程序使用适当的格式时，查询性能会提高。
6.17 ORC 和 Parquet 功能比较
下表比较了 Hive 和 Impala 对 ORC 和 Parquet 的支持。CDP 服务列显示支持的服务：
· Hive
· Hive metastore (HMS)
· Impala
· Spark
能力 |
数据仓库 |
ORC |
Parquet |
CDP服务 |
读取非事务性数据 |
Apache Hive |
✓ |
✓ |
Hive & HMS |
读取非事务性数据 |
Apache Impala |
✓ |
✓ |
Impala & HMS |
完整的 ACID 事务 |
Apache Hive |
✓ |
|
Hive & HMS |
只读插入事务 |
Apache Impala |
✓ |
✓ |
Impala & HMS |
Hive 仓库连接器读取 |
Apache Hive |
✓ |
✓ |
Hive & Spark & HMS |
Hive 仓库连接器写入 |
Apache Hive |
✓ |
|
Hive & Spark & HMS |
列索引 |
Apache Hive |
✓ |
✓ |
Hive & HMS |
列索引 |
Apache Impala |
|
✓ |
Impala & HMS |
CBO 使用列元数据 |
Apache Hive |
✓ |
|
Hive & HMS |
推荐的文件格式 |
Apache Hive |
✓ |
|
Hive & HMS |
推荐的文件格式 |
Apache Impala |
|
✓ |
Impala & HMS |
矢量化读取 |
Apache Hive |
✓ |
✓ |
Hive & HMS |
读取复杂类型 |
Apache Impala |
✓ |
✓ |
Impala & HMS |
读/写复杂类型 |
Apache Hive |
✓ |
✓ |
Hive & HMS |
7 为托管表创建默认目录
您可以在创建 Hive 数据库时为托管表指定顶级目录。
仅在将 CREATE DATABASE 和 ALTER DATABASE 语句且限制为具有 Admin 角色且具有hive服务用户权限的用户后，才能为托管表创建默认目录。对托管目录的权限必须仅限于hive服务用户。除了限制hive用户的权限之外，您还可以使用 Ranger 细粒度权限进一步保护托管表，例如行级过滤和列屏蔽。
作为管理员，您可以在hive.metastore.warehouse.dir配置属性指定一个托管位置来作为默认位置，以便为托管表提供治理策略的公共位置。托管位置为所有租户表（托管和外部）指定一个根目录。
若将该metastore.warehouse.tenant.colocation属性设置为 true，则允许托管表的公共位置在仓库根目录之外，提供基于租户的公共根来设置配额和其他策略。要设置此属性，请在 Cloudera Manager 中为 hive-site.xml 使用 Hive on Tez 安全阀，如下所示。
使用以下语法创建指定托管表位置的数据库:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION external_table_path] [MANAGEDLOCATION managed_table_directory_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
不要设置LOCATION和MANAGEDLOCATION指向相同的HDFS路径。
使用以下语法设置或更改托管表的位置。
ALTER (DATABASE|SCHEMA) database_name MANAGEDLOCATION [managed_table_directory_path];
1) 创建一个数据库mydatabase，为托管表指定名为sales 的顶级目录。
CREATE DATABASE mydatabase MANAGEDLOCATION '/warehouse/tablespace/managed/hive/sales';
2) 将 abc_sales 数据库位置更改为与 mydatabase 相同的位置。
ALTER DATABASE abc_sales MANAGEDLOCATION '/warehouse/tablespace/managed/hive/sales';
7.1 在仓库根目录外配置表位置
1) 在 Cloudera Manager 中，单击集群> Tez 上的 Hive >配置。
2) 在范围内，单击Hive on Tez (Service-Wide)。
3) 在 hive-site.xml 的 Hive 服务高级配置片段（安全阀）中，单击+。
4) 在名称中，输入metastore.warehouse.tenant.colocation。
5) 在值中，输入true。
6) 保存更改。
7) 在 Cloudera Manager Home 中，在 Tez 上重新启动 Hive。
8 使用 ANY/SOME/ALL 比较表
根据 SQL 标准，Apache Hive 支持非相关子查询中的量化比较谓词（ANY/SOME/ALL）。SOME 是 ANY 的任何别名。
Hive 支持将以下运算符之一与比较谓词一起使用：
· >
· <
· >=
· <=
· <>
· =
ALL：
· 如果表为空，或者子查询表中每一行的比较都为真，则该谓词的谓词为真。
· 如果至少一行的比较结果为假，则谓词为假。
SOME或ANY：
· 如果子查询表中至少一行的比较结果为真，则该谓词对该谓词也为真。
· 如果表为空或子查询表中每一行的比较为假，则谓词为假。
如果比较既不是真也不是假，则结果未定义。
例如，您运行以下查询以匹配 tbl 的 c2 中的任何值等于同一 tbl 中的 c1 中的任何值：
select c1 from tbl where c1 = ANY (select c2 from tbl);
您运行以下查询以匹配 tbl 的 c1 中不等于同一 tbl 中 c2 中任何值的所有值。
select c1 from tbl where c1 <> ALL (select c2 from tbl);
9 将通配符与 SHOW DATABASES 一起使用
在 SHOW DATABASES LIKE 语句中，您可以使用通配符，并且在此版本的Hive 中，指定任何字符或单个字符。
SHOW DATABASES 或 SHOW SCHEMAS 列出了 Hive Metastore 中定义的所有数据库。您可以使用以下通配符：
· * 或％
匹配任何单个字符或多个字符
· _
匹配任何单个字符
· |
匹配管道左侧或右侧的模式部分。
例如，'students'、'stu%'、'stu_ents' 匹配名为students 的数据库。
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_show_databases.html
10 分区介绍
简要说明分区和性能的优势包括创建分区时必须避免的字符。创建分区和在分区中插入数据的示例介绍了基本的分区语法。也提到了分区的最佳实践。
您创建的没有分区的表将数据放在一个目录中。分区将数据划分到多个目录中，基于目录的一列或多列查询可以更快地执行。因为它避免了冗长的全表扫描，而仅扫描相关目录中的数据。例如，按year列分区的表school_records，将按年份将值分隔到单独的目录中。一个 WHERE条件，例如 YEAR=2020，YEAR IN (2020,2019)或YEAR BETWEEN 2001 AND 2010扫描只在适当的目录中的数据来解析查询。使用分区通常可以提高查询性能。
在 SQL 查询中，定义分区，如下例所示：
CREATE TABLE sale(id in, amount decimal) PARTITIONED BY (xdate string, state string);
要将数据插入此表，请指定用于快速加载的分区键：
INSERT INTO sale (xdate='2016-03-08', state='CA') SELECT * FROM staging_table WHERE xdate='2016-03-08' AND state='CA';
您不需要指定动态分区列。如果你启用了动态分区，Hive 会生成一个分区规范。
10.1 分区数据查询示例
INSERT INTO sale (xdate, state) SELECT * FROM staging_table;
进行表分区和查询分区表时，请遵循以下最佳实践：
· 永远不要在唯一 ID 上分区。
· 将分区的大小平均为大于或等于1 GB。
· 设计查询以处理不超过1000 个分区。
10.2 分区名称中的非法字符
创建分区时，请勿在分区名称中使用以下字符：
· 冒号
· 问号
· 百分号
如果您在分区名称中使用这些字符，您的目录将使用这些字符的 URL 编码命名，如“为什么不应在 Hive/Impala 的分区名称中使用某些特殊字符”中所述。
11 动态创建分区
您可以将Hive配置为动态创建分区，然后运行查询以在文件系统或对象存储上创建相关目录。Hive然后将数据分离到目录中。
本示例假定您具有以下命名employees.csv的CSV文件作为数据源：
1,jane doe,engineer,service 2,john smith,sales rep,sales 3,naoko murai,service rep,service 4,somporn thong,ceo,sales 5,xi singh,cfo,finance
1) 将CSV文件上传到文件系统。
2) 启动Beeline，然后在Hive Shell中创建一个包含所有数据的未分区表。
CREATE EXTERNAL TABLE employees (eid int, name string, position string, dept string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 's3://user/hive/dataload/employee';
3) 检查数据是否已加载到employees表中。
SELECT * FROM employees;
格式化为适合该板式的输出显示如下：
+------+---------------+-------------+-------+---------+ | eid | name | position | dept | | +------+---------------+-------------+-------+---------| | 1 | jane doe | engineer | service | | 2 | john smith | sales rep | sales | | 3 | naoko murai | service rep | service | | 4 | somporn thong | ceo | sales | | 5 | xi singh | cfo | finance | +------+---------------+-------------+-----------------+
4) 创建一个分区表。
CREATE EXTERNAL TABLE EMP_PART (eid int, name string, position string) PARTITIONED BY (dept string);
5) 接受默认的动态分区模式（非严格），在插入数据时动态创建数据的分区目录，或者如果更改了默认值，请按如下方式重置模式：
SET hive.exec.dynamic.partition.mode=nonstrict;
6) 将未分区表中的数据（所有数据）插入分区表中，从而动态创建分区。
INSERT INTO TABLE EMP_PART PARTITION (DEPT) SELECT eid,name,position,dept FROM employees;
分区是动态创建的。
7) 检查分区是否已创建。
SHOW PARTITIONS emp_part; +----------------+ | partition | +----------------+ | dept=finance | | dept=sales | | dept=service | +----------------+
12 管理分区
您可以发现分区更改并自动同步Hive元数据。与手动执行相反，自动执行同步可以节省大量时间，尤其是在分区数据（例如日志）频繁更改时。您还可以配置将分区数据和元数据保留多长时间。
创建分区表后，Hive不会更新有关您添加或删除的文件系统上相应对象或目录的元数据。添加或删除相应的对象/目录后，Hive元存储中的分区元数据变得陈旧。您需要同步元存储和文件系统。
您可以手动或自动刷新Hive Metastore分区信息。
· 手动
您运行MSCK（元存储一致性检查）Hive命令： MSCK REPAIR TABLE table_name SYNC PARTITIONS每次需要将分区与文件系统同步时。
· 自动
您将分区发现设置为定期发生。
为外部分区表自动创建和启用discover.partitions 表属性。当discover.partitions 对一个表被启用，Hive如下执行自动刷新：
· 将在文件系统中但不在metastore中的相应分区添加到metastore。
· 如果您从文件系统中删除了相应的分区，则从元存储中删除分区的Schema信息。
分区保留
您可以配置保留分区元数据和数据多长时间，并在保留期限过后将其删除。
限制
通常，不建议在托管表上使用分区发现和保留。Hive元存储在表上获取排他锁，启用分区发现，从而会减慢其他查询的速度。
12.1 自动分区发现和修复
自动分区发现和修复对于处理 Spark 和 Hive 目录中的日志数据和其他数据非常有用。您将了解如何设置分区发现参数以适合您的用例。积极的分区发现和修复配置可能会延迟升级过程。
Hive可以自动并定期发现Hive元存储中分区元数据中以及文件系统上相应目录或对象中的差异。发现差异后，Hive执行同步。
表属性discover.partitions启用，并与分区的文件系统禁用同步。在外部分区表中，创建表时默认启用此属性 (true )。对于遗留的外部表（使用不支持此功能的 Hive 版本创建），您需要添加discover.partitions到表属性以启用分区发现。
默认情况下，分区的发现和同步每 5 分钟发生一次。如果您正在升级，这种情况过于频繁，可能会导致每隔几毫秒查询一次 Hive 数据库，从而导致性能下降。在升级期间，批处理例程的高频率要求不经常运行发现和同步，可能每小时甚至每天一次。您可以配置频率，如本任务中所示。
启用压缩（请参见下面的链接）作为解决以下已知问题的解决方法：除非启用压缩，否则发现不会开始。
1) 假设您有一个使用不支持分区发现的Hive版本创建了一个外部表，请对该表启用分区发现。
ALTER TABLE exttbl SET TBLPROPERTIES ('discover.partitions' = 'true');
2) 在 Cloudera Manager 中，单击Clusters > Hive > Configuration，搜索Hive Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml.
3) 将以下属性和值添加到hive-site.xml：属性： metastore.partition.management.task.frequency。值：600。
此操作将分区同步设置为每 10 分钟发生一次，以秒为单位。如果您要升级，请考虑将值设置为 86,400 秒，每 24 小时运行一次发现和同步。
12.2 使用MSCK repair修复分区
MSCK REPAIR TABLE命令旨在手动添加在Hive元存储中不存在的分区，这些分区是添加到文件系统或从文件系统中删除过的。
此任务假定您创建了一个分区的外部表emp_part，用于存储仓库外的分区。您删除文件系统上的分区目录之一。此操作使元存储与文件系统不一致。您可以手动修复差异以将元存储与文件系统（例如HDFS）同步。
1) 从文件系统中删除dept=sales对象。
2) 在Hive命令行中，查看emp_part表分区。
SHOW PARTITIONS emp_part;
分区列表是陈旧的；它仍然包含dept = sales目录。
+----------------+ | partition | +----------------+ | dept=finance | | dept=sales | | dept=service | +----------------+
3) 手动修复分区。
MSCK REPAIRTABLE emp_part DROP PARTITIONS;
12.3 管理分区保留时间
您可以通过设置数据的保留期，将 Apache Hive 元数据和为日志处理和其他活动积累的数据的大小保持在可管理的大小。
该表必须配置为自动将分区元数据与文件系统上的目录或对象同步。
如果您指定分区元数据保留期，Hive 会删除保留期之后创建的任何分区中的元数据和相应数据。您可以使用数字和以下一个或多个字符来表示保留时间：
· ms（毫秒）
· s（秒）
· m（分钟）
· d（天）
在此任务中，您将配置文件系统分区与metastore的自动同步以及分区保留期。假设您已经创建了一个分区的外部表employees如前所述。
1) 如有必要，启用employees表的分区自动发现。
ALTER TABLE employees SET TBLPROPERTIES ('discover.partitions'='true');
默认情况下，外部分区表已将此表属性设置为true。
2) 配置分区保留期为一周。
ALTER TABLE employees SET TBLPROPERTIES ('partition.retention.period'='7d');
一周后，employees分区元数据以及Hive中的实际数据将自动删除。
13 生成代理键
您可以使用内置的SURROGATE_KEY用户自定义函数（UDF）在将数据输入表中时自动为行生成数字ID。生成的代理键可以替换多个复合键。
Hive仅在ACID表上支持代理键，如以下表类型矩阵中所述：
表类型 |
ACID |
代理键 |
文件格式 |
插入 |
更新/删除 |
托管表：CRUD事务 |
是 |
是 |
ORC |
是 |
是 |
托管表：仅插入事务 |
是 |
是 |
任何 |
是 |
否 |
托管：临时表 |
没有 |
否 |
任何 |
是 |
否 |
外部表 |
没有 |
否 |
任何 |
是 |
否 |
您要使用代理键联接的表不能具有需要强制转换的列类型。这些数据类型必须是原生类型，例如INT或STRING。
使用生成的键进行联接比使用字符串进行联接要快。使用生成的键不会通过行号将数据强制进入单个节点。您可以生成键作为自然键的抽象。代理键比UUID具有优势，后者速度较慢且概率较高。
SURROGATE_KEY UDF为您插入表中的每一行生成唯一的ID。它基于分布式系统中的执行环境生成键，其中包括许多因素，例如内部数据结构、表的状态和最后的事务ID。代理键生成不需要计算任务之间的任何协调。
UDF要么不带参数，要么带两个参数：
· Write Id bit
· Task ID bit
1) 以具有ACID属性的默认ORC格式创建一个学生表。
CREATE TABLE students (row_id INT, name VARCHAR(64), dorm INT);
2) 将数据插入表中。例如：
INSERT INTO TABLE students VALUES (1, 'fred flintstone', 100), (2, 'barney rubble', 200);
3) 使用SURROGATE_KEY UDF创建学生表的版本。
CREATE TABLE students_v2 (`ID` BIGINT DEFAULT SURROGATE_KEY(), row_id INT, name VARCHAR(64), dorm INT, PRIMARY KEY (ID) DISABLE NOVALIDATE);
4) 插入数据，它会自动为主键生成代理键。
INSERT INTO students_v2 (row_id, name, dorm) SELECT * FROM students;
5) 看一下代理键。
SELECT * FROM students_v2; +-----------------+---------------------+-------------------+-------------------+ | students_v2.id | students_v2.row_id | students_v2.name | students_v2.dorm | +-----------------+---------------------+-------------------+-------------------+ | 1099511627776 | 1 | fred flintstone | 100 | | 1099511627777 | 2 | barney rubble | 200 | +-----------------+---------------------+-------------------+-------------------+
6) 将代理键作为外键添加到另一个表（例如student_grades表）中，以加快表的后续联接。
ALTER TABLE student_grades ADD COLUMNS (gen_id BIGINT); MERGE INTO student_grades g USING students_v2 s ON g.row_id = s.row_id WHEN MATCHED THEN UPDATE SET gen_id = s.id;
现在，您可以在代理键上实现快速连接。
原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_surrogate_keys.html
14 使用JdbcStorageHandler查询RDBMS
使用JdbcStorageHandler，可以将Hive连接到MySQL、PostgreSQL、Oracle、DB2或Derby数据源。然后，您可以创建一个外部表来表示数据，并查询该表。
此任务假定您是CDP Private Cloud Base用户。您创建一个外部表，该表使用JdbcStorageHandler连接到并读取本地的JDBC数据源。
1) 将数据加载到受支持的SQL数据库（如MySQL）中，或者集群中的某个节点上，或使自己根据现有数据熟悉数据库。
2) 使用JdbcStorageHandler和指定最小信息的表属性创建一个外部表：数据库类型，驱动程序，数据库连接字符串，用于查询hive的用户名和密码，表名以及与Hive的活动连接数。
CREATE EXTERNAL TABLE mytable_jdbc( col1 string, col2 int, col3 double ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( "hive.sql.database.type" = "MYSQL", "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver", "hive.sql.jdbc.url" = "jdbc:mysql://localhost/sample", "hive.sql.dbcp.username" = "hive", "hive.sql.dbcp.password" = "hive", "hive.sql.table" = "MYTABLE", "hive.sql.dbcp.maxActive" = "1" );
3) 查询外部表。
SELECT * FROM mytable_jdbc WHERE col2 = 19;
15 使用函数
您可以调用内置的Hive函数来执行多种操作之一，而不必执行多个步骤。您可以使用SHOW FUNCTIONS搜索或列出可用功能。当无法使用内置函数来执行所需的操作时，可以创建用户定义函数（UDF）。您可能需要重新加载函数以更新在另一个会话中创建的函数的可用性。
15.1 重新加载，查看和过滤函数
若要确定可用的Hive函数和运算符，请重新加载函数，然后使用SHOW FUNCTIONS语句。语句中的可选模式将过滤语句返回的功能列表。
在此任务中，您首先需要重新加载函数以使会话开始后在Hive会话中注册的所有用户定义函数可用。语法为：
RELOAD (FUNCTION|FUNCTIONS);
接下来，使用SHOW FUNCTIONS语句。该语句的语法为：
SHOW FUNCTIONS [LIKE "<pattern>"];
表示可以包含正则表达式通配符的搜索字符。
最后，您可以通过发出DESCRIBE FUNCTION语句来获得有关使用的更多信息。
1) 在集群节点的命令行上打开Hive Shell，例如：
beeline -u jdbc:hive2://mycloudhost-3.com:10000 -n <your user name> -p
2) 重新加载功能以确保您的会话中所有注册的UDF均可用。
RELOAD FUNCTIONS;
使用命令的复数形式。RELOAD FUNCTION是为了向后兼容。
3) 生成可用的内置和用户定义函数（UDF）的列表。
SHOW FUNCTIONS;
出现内置函数、运算符和UDF的列表。
+------------------------------+ | tab_name | +------------------------------+ | ! | | != | | $sum0 | | % | ...
4) 使用正则表达式通配符%生成功能过滤列表。
SHOW FUNCTIONS LIKE "a%";
出现以字符a开头的所有可用函数。
+------------------------------+ | tab_name | +------------------------------+ | abs | | acos | | add_months | ...
5) 获取有关特定函数的更多信息。
DESCRIBE FUNCTION abs; +-------------------------------------------+ | tab_name | +-------------------------------------------+ | ABS(x) - returns the absolute value of x | +-------------------------------------------+
6) 获取有关该函数的更多信息。
DESCRIBE FUNCTION EXTENDED abs; +----------------------------------------------------+ | tab_name | +----------------------------------------------------+ | ABS(x) - returns the absolute value of x | | Synonyms: abs | | Example: | | > SELECT ABS(0) FROM src LIMIT 1; | | 0 | | > SELECT ABS(-5) FROM src LIMIT 1; | | 5 | | Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs | | Function type:BUILTIN | +----------------------------------------------------+
15.2 创建用户自定义函数
您可以从与Hadoop和Hive兼容的Java项目中将用户自定义函数（UDF）导出到JAR，然后将JAR存储在集群或对象存储中。使用Hive命令，您可以基于JAR注册UDF，然后从Hive查询中调用UDF。
· 您具有将JAR上载到集群或集群或对象存储的访问权限。
· Cloudera Manager的最低要求角色：配置器（也由Cluster Administrator，Full Administrator提供）。
· Hive on Tez在集群上运行。
· 您已在要创建UDF的计算机或虚拟机上安装了Java和Java集成开发环境（IDE）工具。
1. 设置开发环境例如，
您可以使用IntelliJ 在开发环境中创建Hive UDF，并使用从Cloudera集群下载的Hive和Hadoop JARS构建UDF。
2. 创建UDF类
您可以在新类中定义UDF逻辑，该类将返回表中所选列的数据类型。
3. 生成项目并上载JAR
您可以将UDF代码编译成JAR，然后将JAR添加到群集上的类路径中。您需要使用直接引用或为Hive配置群集以找到JAR。
4. 注册UDF
在群集中，您登录到Hive，然后从Beeline运行命令以使UDF在Hive查询中起作用。在HiveServer重新启动之间，UDF仍然存在。
5. 在查询中调用UDF
注册UDF之后，无需在查询中使用UDF之前重新启动Hive。在此示例中，您调用在SELECT语句中创建的UDF，Hive返回您指定的列的数据类型。
