CDP中的Hive3系列之Hive3表

大数据杂货铺

发布于 2021-08-20 11:45:25

2K0

发布于 2021-08-20 11:45:25

文章被收录于专栏：大数据杂货铺

表类型的定义和表类型与 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来删除数据。

表存储格式

CRUD表中的数据必须为ORC格式。实现支持AcidInputFormat和AcidOutputFormat的存储处理程序等效于指定ORC存储。

仅插入使用的表支持所有文件格式。

默认情况下，托管表的存储类型为“优化行列”（ORC）。如果在表创建的过程中未指定任何存储来接受默认的设置，或者指定了ORC存储，则将获得具有插入、更新和删除（CRUD）功能的ACID表。如果指定其他任何存储类型，例如text、CSV、AVRO或JSON，则将获得仅插入的ACID表。您不能更新或删除仅插入表中的列。

事务表

事务表是驻留在Hive仓库中的ACID表。为了实现ACID合规性，Hive必须管理表，包括对表数据的访问。只有通过Hive才能访问和更改托管表中的数据。由于Hive可以完全控制托管表，因此Hive可以广泛地优化这些表。

与用作联机分析处理（OLAP）系统相反，Hive旨在支持相对较低的事务率。您可以使用SHOW TRANSACTIONS命令列出未完成和中止的事务。

Hive 3中的事务表与非ACID表相当。Hive 3事务表中不需要分桶或排序。分桶不会影响性能。这些表与原生的云存储兼容。

Hive支持每个事务一个语句，该语句可以包含任意数量的行、分区或表。

外部表

外部表数据不是由Hive拥有或控制的。当您想使用Hive以外的其他工具直接在文件级别访问数据时，通常使用外部表。您还可以使用存储处理程序（例如Druid或HBase）来创建位于Hive元存储之外的表。

在外部表上，Hive 3不支持以下功能：

查询缓存
物化视图，但以受限的方式除外
自动运行时过滤
插入后合并文件

在外部表上运行DROP TABLE时，默认情况下，Hive仅删除元数据（Schema）。如果您希望DROP TABLE命令也删除外部表中的实际数据，就像DROP TABLE在托管表上所做的那样，则需要将external.table.purge属性设置为true。

定位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 菜单更改仓库的位置，如以下步骤所述：

在 Cloudera Manager 中，单击集群> Hive >操作菜单>创建 Hive 仓库目录。

在 Cloudera Manager 中，单击Clusters > Hive（Hive Metastore 服务）> Configuration，并将hive.metastore.warehouse.dir 属性值更改为新 Hive 仓库目录的路径。
单击Hive > Hive >操作菜单>创建 Hive 仓库外部目录。
将hive.metastore.warehouse.external.dir属性值更改为 Hive 仓库外部目录的路径。
配置 Ranger 策略或设置 ACL 权限以访问目录。

原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive-table-location.html

使用点表示法引用表

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

创建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。

原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_create_a_crud_transactional_table.html

创建仅插入的事务表

如果不需要更新和删除功能，则可以使用任何存储格式创建事务表。这种类型的表具有ACID属性，是一个托管表，并且仅接受插入操作。仅插入表的存储格式不限于ORC。

在此任务中，您将创建一个仅插入的事务表来存储文本。在CREATE TABLE语句中，指定ORC以外的其他存储类型（例如文本，CSV，AVRO或JSON）将导致仅插入ACID表。您可以在表属性子句中显式指定“仅插入”。

使用您的用户名从命令行启动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格式。

原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_create_insert_only_transactional_table.html

创建、使用和删除外部表

使用外部表（该表不是Hive所管理的表）将数据从文件系统上的文件导入Hive。与Hive托管表相反，外部表将其数据保留在Hive元存储之外。Hive Metastore仅存储外部表的Schema元数据。Hive不管理或限制对实际外部数据的访问。

您需要使用以下方法之一设置对文件系统中的外部表的访问。

在 Ranger 中设置 Hive HDFS 策略（推荐）以包含外部表数据的路径。
放置一个 HDFS ACL。将逗号分隔值 (CSV) 文件存储在 HDFS 中，该文件将用作外部表的数据源。

在此任务中，您将根据文件系统中存储的CSV（逗号分隔值）数据创建一个外部表，如下图所示。接下来，您希望Hive在元存储中管理和存储实际数据。您创建一个托管表。

您将外部表数据插入到托管表中。

此任务演示了以下Hive原则：

CREATE TABLE中的LOCATION子句指定外部表数据的位置。
外部表和托管（内部）表之间的主要区别是：DROP TABLE语句后，表数据在文件系统上的持久性。
- 外部表删除：Hive仅删除主要由Schema组成的元数据。
- 托管表删除：Hive删除Hive仓库中存储的数据和元数据。

删除外部表后，数据不会消失。要检索它，请发出另一个CREATE EXTERNAL TABLE语句以从文件系统加载数据。

创建一个名为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;

出现托管表名称的结果。

验证外部表Schema定义是否丢失。

SELECT * from names_text;

names_text由于外部表的Schema丢失，因此从中全选不返回任何结果。

2. 检查HDFS或S3上的students.csv文件是否完好无损。

原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_create_an_external_table.html

删除外部表和数据

在外部表上运行DROP TABLE时，默认情况下，Hive仅删除元数据（Schema）。如果您希望DROP TABLE命令也删除外部表中的实际数据，就像DROP TABLE在托管表上一样，则需要相应地配置表属性。

创建一个要在Hive中查询的数据的CSV文件。
启动Hive。
创建一个外部表来存储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');

原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_drop_external_table_data.html

将托管非事务性表转换为外部

您可以使用 ALTER TABLE 语句轻松地将托管表（如果它不是 ACID（事务）表）转换为外部表。从 Hive 1 或 2 升级后，您可能有一个非 ACID 的托管表。

以下伪代码将托管表（如果它不是事务性的）更改为外部。删除表时，数据和元数据也会被删除。

ALTER TABLE ... SET TBLPROPERTIES('EXTERNAL'='TRUE','external.table.purge'='true')

原文链接：https://docs.cloudera.com/cdp-private-cloud-base/latest/using-hiveql/topics/hive_convert_table_to_external.html

使用约束

您可以使用 SQL 约束来强制执行数据完整性并提高性能。使用约束，优化器可以简化查询。约束可以使数据可预测且易于定位。例如，使用约束和支持的修饰符，您可以按照示例将查询限制为唯一值或非空值。

Hive 仅强制执行 DEFAULT、NOT NULL 和 CHECK，而不强制执行 PRIMARY KEY、FOREIGN KEY 和 UNIQUE。

您可以在查询中使用下面列出的约束。Hive 仅强制执行 DEFAULT、NOT NULL 和 CHECK，而不强制执行 PRIMARY KEY、FOREIGN KEY 和 UNIQUE。DEFAULT 即使强制执行，也不支持复杂类型（数组、映射、结构）。约束实施仅限于元数据级别。此限制有助于与第三方工具集成和优化约束声明，例如物化视图重写。

CHECK
- 可以放置在列中的值的范围的限制。
DEFAULT
- 确保存在一个值，该值在数据仓库卸载案例中很有用。
PRIMARY KEY
- 使用唯一标识符标识表中的每一行。
FOREIGN KEY
- 使用唯一标识符标识另一个表中的行。
NOT NULL
- 检查列值未设置为NULL。

支持的修饰符

您可以使用以下可选的修饰符：

ENABLE
- 确保所有传入数据符合约束。
DISABLE
- 不确保所有传入数据都符合约束。
VALIDATE
- 检查表中所有现有数据是否符合约束。
NOVALIDATE
- 不检查表中所有现有数据是否符合约束。
ENFORCED
- 映射到 ENABLE NOVALIDATE。
NOT ENFORCED
- 映射到禁用 NOVALIDATE。
RELY
- 指定遵守约束；优化器使用它来应用进一步的优化。
NORELY
- 指定不遵守约束。

您可以按照以下语法使用修饰符：

默认修改器

以下默认修饰符已就位：

ENABLE 的默认修饰符是 NOVALIDATE RELY。
DISABLE 的默认修饰符是 NOVALIDATE NORELY。
如果在声明约束时未指定修饰符，则默认值为 ENABLE NOVALIDATE RELY。以下约束不支持 ENABLE：
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE KEY

为防止出现错误，请在使用这些约束覆盖默认值时指定修改器。

约束示例

优化器使用约束信息做出明智的决定。以下示例显示了约束的使用。

以下示例显示如何创建声明 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

确定表类型

您可以确定Hive表的类型，是否具有ACID属性，存储格式（例如ORC）和其他信息。出于多种原因，了解表类型非常重要，例如，了解如何在表中存储数据或从集群中完全删除数据。

在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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-08-11，如有侵权请联系 cloudcommunity@tencent.com 删除

hive