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事务表的局限性:
不支持BEGIN,COMMIT和ROLLBACK,所有语言操作都是自动提交的。 仅支持ORC文件格式(STORED AS ORC) 默认情况下事务配置关闭,需要配置参数开启使用 表必须是分桶表才可以使用事务功能 表参数transactional必须为true 外部表不能成为ACID表,不允许从非ACID会话读取或写入ACID表 事务表不支持LOAD DATA …语句
事务表实现原理
Hive文件是存储在HDFS上的,而HDFS上又不支持对文件的任意修改,因此可以采取其他手段完成:
用HDFS存储原始数据,用delta保持事务操作的记录增量数据; 正在执行中的事务,是以一个staging开头的文件夹维护的,执行结束就是delta文件夹。 每执行一次事务操作都会有这样一个delta增量文件夹; 访问Hive数据时,HDFS原始文件和delta增量文件合并,查询最新的数据。
INSERT语句会直接创建delta目录; DELETE目录前缀是delete_delta; UPDATE语句采用了split-update特性,即先删除、后插入;
创建使用Hive事务表
事务表创建要素:开启参数、分桶表、存储格式orc、表属性。 step1:开启事务配置(可以使用set设置当前session生效,也可以配置在hive-site.xml中)
SET hive. support. concurrency = true ;
SET hive. enforce. bucketing = true ;
SET hive. exec . dynamic. partition . mode = nonstrict;
SET hive. txn. manager = org. apache. hadoop. hive. ql. lockmgr. DbTxnManager;
SET hive. compactor. initiator. on = true ;
SET hive. compactor. worker. threads = 1 ;
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;
SHOW MATERIALIZED VIEW [ IN database_name] ;
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;