表类型的定义和表类型与 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 在 HDFS 上存储表的位置以及安装服务后如何更改仓库位置。
您在 CDP 中创建的新表存储在托管表的 Hive 仓库或外部表的 Hive 仓库中。以下默认仓库位置位于 HDFS 文件系统中:
托管表驻留在托管表空间中,只有 Hive 可以访问。默认情况下,Hive 假定外部表驻留在外部表空间中。
要确定托管或外部表类型,您可以运行 DESCRIBE EXTENDED table_name 命令。
您需要在Ranger中设置 HDFS 策略以访问外部表,或设置 HDFS ACL。
更改 Hive 仓库位置的功能旨在在安装服务后立即使用。您可以使用 Cloudera Manager 中的 Hive Metastore Action 菜单更改仓库的位置,如以下步骤所述:
原文链接: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
当需要可更新、删除和合并的托管表时,可以创建具有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表。您可以在表属性子句中显式指定“仅插入”。
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不管理或限制对实际外部数据的访问。
您需要使用以下方法之一设置对文件系统中的外部表的访问。
在此任务中,您将根据文件系统中存储的CSV(逗号分隔值)数据创建一个外部表,如下图所示。接下来,您希望Hive在元存储中管理和存储实际数据。您创建一个托管表。
您将外部表数据插入到托管表中。
此任务演示了以下Hive原则:
删除外部表后,数据不会消失。要检索它,请发出另一个CREATE EXTERNAL TABLE语句以从文件系统加载数据。
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;
出现托管表名称的结果。
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在托管表上一样,则需要相应地配置表属性。
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 即使强制执行,也不支持复杂类型(数组、映射、结构)。约束实施仅限于元数据级别。此限制有助于与第三方工具集成和优化约束声明,例如物化视图重写。
您可以使用以下可选的修饰符:
您可以按照以下语法使用修饰符:
( ( ( (ENABLE | DISABLE) (VALIDATE | NOVALIDATE) ) | ( ENFORCED | NOT ENFORCED ) ) (RELY | NORELY) )
默认修改器
以下默认修饰符已就位:
为防止出现错误,请在使用这些约束覆盖默认值时指定修改器。
优化器使用约束信息做出明智的决定。以下示例显示了约束的使用。
以下示例显示如何创建声明 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)和其他信息。出于多种原因,了解表类型非常重要,例如,了解如何在表中存储数据或从集群中完全删除数据。
例如:
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