Hive SQL DDL事务表、视图、物化视图

Hive SQL DDL建表语法树

• “[]”中的语法可选
• “|”表示使用时，语法需要二选一
• 建表语句中语法顺序要和语法树规则保持一直
• 整体分为原生数据类型和复杂数据类型
• 原生数据类型包括：数值类型、字符串类型、时间日期类型、杂项数据类型。
• 复杂数据类型包括：array数组、map映射、struct结构、union联合体。

CREATE [TEMPORARY][EXTERNAL] TABLE [IF NOT EXISTS][db_name.]table_name
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type[COMMENT col_comment],...)]
[CLUSTERED BY (col_name,col_name,...)[SORTED BY (col_name[ASC|DESC],...)]INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED|SERDE serde_name 	WITH SERDEPROPERTIES(property_name=property_value,...)]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES(property_name=property_value,...)];

Hive事务表

• 解决流式传输数据可能造成的脏读问题，Hive只能每十五分钟到一小时添加一次分区。
• 解决尺寸变化缓慢问题，即修改局部数据。
• 解决数据重述问题，即更正不正确的数据。
• Hive事务表的局限性：
  1. 不支持BEGIN，COMMIT和ROLLBACK，所有语言操作都是自动提交的。
  2. 仅支持ORC文件格式（STORED AS ORC）
  3. 默认情况下事务配置关闭，需要配置参数开启使用
  4. 表必须是分桶表才可以使用事务功能
  5. 表参数transactional必须为true
  6. 外部表不能成为ACID表，不允许从非ACID会话读取或写入ACID表
  7. 事务表不支持LOAD DATA …语句

事务表实现原理

• Hive文件是存储在HDFS上的，而HDFS上又不支持对文件的任意修改，因此可以采取其他手段完成：

1. 用HDFS存储原始数据，用delta保持事务操作的记录增量数据；
2. 正在执行中的事务，是以一个staging开头的文件夹维护的，执行结束就是delta文件夹。
3. 每执行一次事务操作都会有这样一个delta增量文件夹；
4. 访问Hive数据时，HDFS原始文件和delta增量文件合并，查询最新的数据。

• INSERT语句会直接创建delta目录；
• DELETE目录前缀是delete_delta；
• UPDATE语句采用了split-update特性，即先删除、后插入；

创建使用Hive事务表

• 事务表创建要素：开启参数、分桶表、存储格式orc、表属性。
• step1：开启事务配置(可以使用set设置当前session生效，也可以配置在hive-site.xml中)

SET hive.support.concurrency = true; --Hive是否支持并发
SET hive.enforce.bucketing = true; --从Hive2.0开始不再需要（是否开启分桶）
SET hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式 非严格
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
SET hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程

• step2：创建Hive事务表

CREATE TABLE trans_student(
	id INT,
	name STRING,
	age INT
)CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES('transactional'='true');

Hive Views 视图

• 和MySQL视图相似，是虚拟表，可以用于简化操作，提高数据安全性。
• Hive视图不支持插入数据

视图相关语法

• 创建视图

# 从已有表查询结果中创建视图
CREATE VIEW v_usa_covid19 AS SELECT count_date,county,state,deaths FROM t_usa_covid19 LIMIT 5;
# 从已有视图中创建视图
CREATE VIEW v_usa_covid19_from_view AS SELECT * FROM v_usa_covid19 LIMIT 2;

• 显示已有的视图(SHOW TABLE会返回表和视图)

SHOW VIEW;

• 查看视图定义

SHOW CREATE TABLE v_usa_covid19;

• 删除视图

DROP VIEW v_usa_covid19;

• 更改视图属性

ALTER VIEW v_usa_covid19 SET TBLPROPERTIES('comment'='This is a view');

• 更改视图定义

ALTER VIEW v_usa_covid19 AS SELECT county,deaths FROM v_usa_covid19 LIMIT 2;

物化视图 - Hive3.0特性

• 物化视图是一个包括查询结果的数据库对象，可以用于预先计算并保存计算结果，可以提高查询性能。
• Hive3.0提供物化视图的查询自动重写机制。
• 物化视图还提供了物化视图存储选择机制，可以本地存储在Hive，也可以通过用户自定义storage handlers 存储在其他系统(如Druid)。
• Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。

物化视图语法：

• 创建语法：

CREATE MATERIALIZED VIEW [IF NOT EXISTS][db_name.]materialized_view_name
	[DISABLE REWRITE]
	[COMMENT materialized_view_comment]
	[PARTITIONED ON (col_name,...)]
	[CLUSTERED ON(col_name,...)|DISTRIBUTED ON(col_name,...)SORTED ON(col_name,...)]
	[
		[ROW FORMAT row_format]
		[STORED AS file_format]
		|[STORED BY 'storage.handler.class.name'[WITH SERDEPROPERTIES(...)]]
	]
	[LOCATION hdfs_path]
	[TBLPROPERTIES(property_name=property_value,...)]
AS SELECT ...;

• 删除语法：

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

• 查看视图语法：

# shows materialized views(with optional filters)
SHOW MATERIALIZED VIEW [IN database_name];
# shows information about a specific materialized view
DESCRIBE [EXTENDED|FORMATTED][db_name.]materialized_view_name;

• 保持数据一致性：
  当数据源变更时，物化视图需要更新以保持数据一致，目前需要用户主动触发rebuild重构。

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

物化视图的查询重写

• 提交查询语句时，若命中物化视图的查询语句，则直接返回物化视图的数据，不用额外进行计算，查询速度加快。
• 是否重写查询使用物化视图可以通过全局参数控制，默认为true：
  hive.materializedview.rewriting=true;
• 用户可控制指定物化视图查询重写机制：

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;