Hive从入门到放弃——Hive元数据简介（六）

元数据概念

  在之前安装Hive的博客Hive从入门到放弃——Hive安装和基本使用（二）,提到了有一步骤是初始化Hive，其实就是把Hive的元数据表创建部署在一个叫hive的MySQL库里，那么什么是元数据呢？
  元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（data about data），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。
  解释解释就是你在Hive内建的库，建的表，表有哪些字段，字段类型是什么，用的什么序列化，表建了什么分区等等，这些描述Hive内数据库、表、分区、字段等对象的数据，就是Hive的元数据，细心的你可能觉得好像遇到过这些玩意，没错，一般常见的关系型数据库如SQL Server，MySQL会在自己实力本身建一个类似于sys的库和表来存这些描述库或者表的数据，但是Hive本身没有这样的库，而且经常涉及元数据本身的增删查改，还要支持多人同时多事务的增删查改这些配置元数据信息，Hive本身存这样的库不适合，所以选用了能较好支持的MySQL作为元数据库，当然Oracle和SQL Server也是可以作为选择的，但是因为MySQL免费嘛，所以用的多。

元数据表E-R图

  可以自己登录到自己Hive元数据库hive内，查看一下元数据表和E-R图，hive-2.3.5版本有57张元数据表，当然也不是所有的表都能关联到整个元数据的E-R图内，也有游离在外面的独立的表的，有些是Hive预留的，等着你去开发实践，如果你要修改Hive源码，那么必须要要对这些Hive元数据表滚瓜烂熟。

图1 查看Hive元数据表的E-R图

  纵然整个E-R图，确实有点大，截图截不全，但是最重要的是两个表是表SDS(Hive序列化反序列化表)和表TBLS(Hive表描述)，这两个表贯穿了整个E-R图，详细我们在下文会具体说明。

图2 Hive元数据表的E-R图节选

常用元数据表简介

Hive版本相关的表

• VERSION 查看版本信息，结构相对简单，但是很重要，缺失会在启动Hive连接时触发异常table ‘hive.version’ doesn’t exist,表结构数据字典如表1。

表1 VERSION数据字段

字段名	类型	是否为空	约束	描述	样例数据
VER_ID	bigint(20)	N	主键	版本主键id	1
SCHEMA_VERSION	varchar(127)	N	-	Hive版本	2.3.0
VERSION_COMMENT	varchar(255)	Y	-	Hive版本说明id	Hive release version 2.3.0

Hive数据库相关的表

• DBS 查看所有数据库的信息，表结构数据字典如表2；

表2 DBS数据字段

字段名	类型	是否为空	约束	描述	样例数据
DB_ID	bigint(20)	N	主键	数据库主键id	1
DESC	varchar(4000)	Y	-	数据库描述	数据仓库
DB_LOCATION_URI	varchar(4000)	N	-	数据库hdfs位置	hdfs://dw-test-cluster-007/hive/warehouse
NAME	varchar(128)	Y	-	数据库名字	dw
OWNER_NAME	varchar(128)	Y	-	数据库属主(即谁的库)	hadoop
OWNER_TYPE	varchar(10)	Y	-	数据库类型	ROLE或USER

• DATABASE_PARAMS 存储数据库的属性相关参数，对应CREATE DATABASE时候用WITH DBPROPERTIES (property_name=property_value, …)指定的参数,具体如表3；

表3 DATABASE_PARAMS数据字段

字段名	类型	是否为空	约束	描述	样例数据
DB_ID	bigint(20)	N	主键	数据库主键id	1
PARAM_KEY	varchar(180)	N	-	数据库属性参数名	creater或date或edited-by
PARAM_VALUE	varchar(4000)	Y	-	数据库属性参数值	rowyet或20200520

Hive表和视图相关的表

• TBLS 划重点，重中之重，记录Hive表，视图和索引等基本信息描述，表结构字典如表4；

表4 TBLS数据字段

字段名	类型	是否为空	约束	描述	样例数据
TBL_ID	bigint(20)	N	主键	数据库主键id	1
CREATE_TIME	int(11)	N	-	创建时间(时间戳)	1570604212
DB_ID	bigint(20)	Y	DBS表外键	数据库ID	1
LAST_ACCESS_TIME	int(11)	N	-	最后一次访问时间（时间戳）	1570604212
OWNER	varchar(767)	Y	-	表属主	rowyet
RETENTION	int(11)	N	-	表保留字段	0
SD_ID	bigint(20)	Y	SDS表外键	序列化表ID	1，对应SDS表的SD_ID=1
TBL_NAME	varchar(256)	Y	-	表名	ods_rs_basic_area_txt
TBL_TYPE	varchar(128)	Y	-	表类型(内部表，外部表)	MANAGED_TABLE
VIEW_EXPANDED_TEXT	mediumtext	Y	-	视图的详细HQL语句	select * from dw.ods_rs_basic_area_txt
VIEW_ORIGINAL_TEXT	mediumtext	Y	-	视图的原始HQL语句	select * from ods_rs_basic_area_txt
IS_REWRITE_ENABLED	bit(1)	N	-	是否重写	0

• TABLE_PARAMS 表相关属性参数，对应Hive建表语句中的TBLPROPERTIES ( 'parquet.compression'='snappy', 'spark.sql.create.version'='2.2 or prior', 'spark.sql.sources.schema.numPartCols'='3', 'transient_lastDdlTime'='1590565081')以及表的comment的属性描述，具体表结构字典如表5，

表5 TABLE_PARAMS数据字段

字段名	类型	是否为空	约束	描述	样例数据
DB_ID	bigint(20)	N	主键	数据库主键id	1
PARAM_KEY	varchar(256)	N	-	表属性参数名	comment 或 parquet.compression或transient_lastDdlTime等
PARAM_VALUE	mediumtext	Y	-	表属性参数值	ods describe表 或 snappy 或 1571225030

• TBL_PRIVS 记录Hive表、视图授权情况，具体如表6；

表6 TBL_PRIVS数据字段

字段名	类型	是否为空	约束	描述	样例数据
TBL_GRANT_ID	bigint(20)	N	主键	授权表主键id	1
CREATE_TIME	int(11)	N	-	创建时间(时间戳)	1570604212
GRANT_OPTION	smallint(6)	N	-	权限可选项	0
GRANTOR	varchar(128)	Y	-	授权执行用户	hadoop
GRANTOR_TYPE	varchar(128)	Y	-	授权用户类型	USER
PRINCIPAL_NAME	varchar(128)	Y	-	被授权用户	hadoop
PRINCIPAL_TYPE	varchar(128)	Y	-	被授权用户类型	USER
TBL_PRIV	varchar(128)	Y	-	授予权限	SELECT 或 UPDATE 或 DELETE 或INSERT
TBL_ID	bigint(20)	Y	TBLS表外键	TBLS表的TBL_ID	1 对应TBLS的TBL_ID=1

Hive序列化和反序列化相关的表

• SDS 保存表指定文件的存储相关的信息，如输入、输出格式，是否压缩，文件存储在hdfs上的位置等,数据字典具体如表7；

表7 SDS数据字段

字段名	类型	是否为空	约束	描述	样例数据
SD_ID	bigint(20)	N	主键	主键id	1
CD_ID	bigint(20)	Y	CDS表外键	CDS表外键	1
INPUT_FORMAT	varchar(4000)	Y	-	输入格式	org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat
IS_COMPRESSED	bit(1)	N	-	是否压缩	0,1
IS_STOREDASSUBDIRECTORIES	bit(1)	N	-	是否以子目录存储	0,1
LOCATION	varchar(4000)	Y	-	文件在HDFS的存储位置	hdfs://dw-test-cluster-007/hive/warehouse/ods/compose_plan/kuma/ods_kuma_compose_plan/event_week=40/event_day=20191010/event_hour=00
NUM_BUCKETS	int(11)	N	-	分桶个数	7
OUTPUT_FORMAT	varchar(4000)	Y	-	输出格式	org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat
SERDE_ID	bigint(20)	Y	SERDES外键	SERDES外键	1

• SD_PARAMS 存储SDS表的一些属性，由STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)指定，这个表不常见，我查询了下我的生产Hive元数据库，该目前是无记录的，说明这些属性实际用处不大，数据字典如表8；

表8 SD_PARAMS数据字段

字段名	类型	是否为空	约束	描述	样例数据
SD_ID	bigint(20)	N	主键	表SDS主键id	1
PARAM_KEY	varchar(256)	N	-	表SDS属性参数名	-
PARAM_VALUE	mediumtext	Y	-	表SDS属性参数值	-

• SERDES 存储Hive表的序列化，反序列化调用的类，非常重要，具体数据结构如表9；

表9 SERDES数据字段

字段名	类型	是否为空	约束	描述	样例数据
SERDE_ID	bigint(20)	N	主键	表SERDES主键id	1
NAME	varchar(128)	Y	-	调用序列化类的别名	-
SLIB	varchar(4000)	Y	-	调用序列化的类	org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe

• SERDE_PARAMS 存储Hive序列化的一些属性，如文本格式的行列分隔符，二进制文件的序列化格式等，数据字典如表10； 表10 SERDE_PARAMS数据字段

字段名	类型	是否为空	约束	描述	样例数据
SERDE_ID	bigint(20)	N	主键	表SERDE_PARAMS主键id	1
PARAM_KEY	varchar(256)	N	-	表SERDE_PARAMS属性参数名	field.delim 或 line.delim 或 serialization.format
PARAM_VALUE	mediumtext	Y	-	表SERDE_PARAMS属性参数值	或 换行符 或 1

Hive表字段相关的表

• COLUMNS_V2 存储字段相关的信息，很重要，数据字典如表11； 表11 COLUMNS_V2数据字段

字段名	类型	是否为空	约束	描述	样例数据
CD_ID	bigint(20)	N	主键	表COLUMNS_V2主键id	1
COMMENT	varchar(256)	Y	-	Hive表字段描述	账号类型
COLUMN_NAME	varchar(767)	N	-	Hive表字段名称	account_type
TYPE_NAME	mediumtext	Y	-	Hive表字段类型	string
INTEGER_IDX	int(11)	N	-	Hive表字段顺序	13

  扩展：元数据表内获取表字段的方法，可以通过上面图2的E-R图关系获得，表TBLS的字段SD_ID关联表SDS，然后表SDS的字段CD_ID关联表CDS，然后表CDS的字段CD_ID关联表COLUMNS_V2得到，可参看以下sql语句；

select t1.TBL_NAME,t4.* 
from TBLS t1
inner join SDS t2 on t1.SD_ID=t2.SD_ID
inner join CDS t3 on t2.CD_ID=t3.CD_ID
inner join COLUMNS_V2 t4 on t3.CD_ID=t4.CD_ID
where TBL_NAME='ods_fso_tb_e_sales_publish'

  运行结果如图3；

图3 元数据实现查看某表的字段

Hive表分区相关表

• PARTITIONS 存储Hive表的分区信息，数据字典如表12； 表12 PARTITIONS数据字段

字段名	类型	是否为空	约束	描述	样例数据
PART_ID	bigint(20)	N	主键	表PARTITIONS主键id	1
CREATE_TIME	int(11)	N	-	分区创建时间	1570775393
LAST_ACCESS_TIME	int(11)	N	-	最后一次访问时间	0
PART_NAME	varchar(767)	Y	-	分区名	event_week=40/event_day=20191010/event_hour=00
SD_ID	bigint(20)	Y	SDS表外键	分区存储ID	62
TBL_ID	bigint(20)	Y	TBLS表外键	表ID	28

PARTITION_KEYS 存储Hive表的分区字段名字，类型，数据字典如表13；

表13 PARTITION_KEYS数据字段

字段名	类型	是否为空	约束	描述	样例数据
TBL_ID	bigint(20)	N	主键	表COLUMNS_V2主键id	1
PKEY_COMMENT	varchar(4000)	Y	-	分区字段说明	天
PKEY_NAME	varchar(128)	N	-	分区字段名	event_day
PKEY_TYPE	varchar(767)	N	-	分区字段类型	string
INTEGER_IDX	int(11)	N	TBLS表外键	分区字段顺序	1

• PARTITION_KEY_VALS 存储Hive表分区字段的值，数据字典如表14； 表14 PARTITION_KEY_VALS数据字段

字段名	类型	是否为空	约束	描述	样例数据
PART_ID	bigint(20)	N	主键	表PARTITION_KEY_VALS主键id	1
PART_KEY_VAL	varchar(256)	Y	-	分区字段的值	20191010
INTEGER_IDX	int(11)	N	-	分区字段顺序	1

• PARTITION_PARAMS 存储Hive表分区的属性，数据字典如表15； 表15 PARTITION_PARAMS数据字段

字段名	类型	是否为空	约束	描述	样例数据
PART_ID	bigint(20)	N	主键	表PARTITION_PARAMS主键id	1
PARAM_KEY	varchar(256)	N	主键	分区属性名	numFiles 或 numRows
PARAM_VALUE	varchar(4000)	Y	主键	分区属性值	15 或 502195

  扩展：元数据表内获取表分区的方法，可以通过上面图2的E-R图关系获得，表TBLS的字段TBL_ID关联表PARTITIONS和表PARTITION_KEYS，然后表PARTITION_KEY_VALS的字段PART_ID关联表PARTITION_KEY_VALS，关联表PARTITION_PARAMS是为了取属性，可选，一般不用，可参看以下sql语句；

select t1.TBL_NAME,t2.PKEY_COMMENT,t2.PKEY_NAME,t2.PKEY_TYPE,t2.INTEGER_IDX,t3.PART_NAME,t3.PART_ID 
 ,t4.PART_ID,t4.PART_KEY_VAL,t4.INTEGER_IDX-- ,t5.PARAM_KEY,t5.PARAM_VALUE
from TBLS t1
 inner join PARTITION_KEYS t2 on t1.TBL_ID=t2.TBL_ID
 inner join PARTITIONS t3 on t1.TBL_ID=t3.TBL_ID
 inner join PARTITION_KEY_VALS  t4 on t3.PART_ID=t4.PART_ID  and t2.INTEGER_IDX=t4.INTEGER_IDX
-- inner join PARTITION_PARAMS  t5 on t3.PART_ID=t5.PART_ID 
where t1.TBL_ID=28

  运行结果如图4；

图4 元数据实现查看某表的分区信息

其他不常用元数据节选

  hive-2.3.5版本有57张元数据表,以上列出的是比较常用的一些，剩下的就不详细列举了，大家还是要学会利用图2的E-R图来盘清楚这些元数据表的关系，同时对于有修改Hive源码的小伙伴们，也是需要先理清楚这些元数据表的。

• DB_PRIVS 数据库权限信息表。通过GRANT语句对数据库授权后，将会在这里存储。

• IDXS 索引表，存储Hive索引相关的元数据

• INDEX_PARAMS 索引相关的属性信息。

• TAB_COL_STATS 表字段的统计信息。使用ANALYZE语句对表字段分析后记录在这里。

• TBL_COL_PRIVS 表字段的授权信息

• PART_PRIVS 分区的授权信息

• PART_COL_STATS 分区字段的统计信息。

• PART_COL_PRIVS 分区字段的权限信息。

• FUNCS 用户注册的函数信息

• FUNC_RU 用户注册函数的资源信息