[Hadoop] Hive metadata - Githubissues

Hive 版本：hive-1.1.0-cdh5.7.0-bin

Hive 元数据的表结构

Hive在MySQL上的相关元数据表关系图：

1. 版本表

1.1 VERSION表，存储hive的版本

元数据表字段	说明	示例数据
VERSION_ID	ID主键	1
SCHEMA_VERSION	Hive版本	1.1.0
VERSION_COMMENT	版本说明	Set by MetaStore hadoop@192.168.1.8

注意：VERSION表有且只能有一条数据，Hive-Clie才能正常进入。生产中应监控表数据条数，大于一则删除新进来的数据（crontab或者trigger）。

2. 数据库信息表

2.1 DBS表，存储Hive中所有数据库的基本信息。

元数据表字段	说明	示例数据
DB_ID	数据库ID	1
DESC	数据库描述	Default Hive database
DB_LOCATION_URI	数据库HDFS存储路径	hdfs://192.168.1.8:9000/user/hive/warehouse
NAME	数据库名	default
OWNER_NAME	数据库所有者	public
OWNER_TYPE	所有者类型	ROLE

2.2 DATABASE_PARAMS表，存储数据库的相关参数，在CREATE DATABASE时调用。

元数据表字段	说明	示例数据
DB_ID	数据库ID	2
PARAM_KEY	参数名称	createdby
PARAM_VALUE	参数值	cindy

DBS和DATABASE_PARAMS这两张表通过DB_ID字段外键关联。

3. hive表和视图描述表

3.1 TBLS表，存储Hive表、视图、索引表的基本信息

元数据表字段	说明	示例数据
TBL_ID	数据表ID	12
CREATE_TIME	创建时间	1528092265
DB_ID	数据库ID	8
LAST_ACCESS_TIME	上次访问时间	1528092265
OWNER	所有者	hadoop
RETENTION	保留字段	0
SD_ID	序列化配置信息	12，与SDS表中的SD_ID对应
TBL_NAME	表名	person
TBL_TYPE	表类型	MANAGED_TABLE EXTERNAL_TABLE INDEX_TABLE VIRTUAL_VIEW
VIEW_EXPANDED_TEXT	视图的详细HQL语句	select name,age from person
VIEW_ORIGINAL_TEXT	视图的原始HQL语句	select * from person

3.2 TABLE_PARAMS表，存储表、视图的属性信息

元数据表字段	说明	示例数据
TBL_ID	表ID	12
PARAM_KEY	属性名	totalSize numRows EXTERNAL
PARAM_VALUE	属性值	this is person

3.3 TBL_PRIVS，存储表、视图的授权信息

元数据表字段	说明	示例数据
TBL_GRANT_ID	授权ID	1
CREATE_TIME	授权时间	1528092265
GRANT_OPTION	授权选项
GRANTOR	授权用户	hadoop
GRANTOR_TYPE	授权用户类型	USER
PRINCIPAL_NAME	被授权用户	username
PRINCIPAL_TYPE	被授权用户类型	USER
TBL_PRIV	表权限	Select、Alter
TBL_ID	表ID	12

这三张表通过TBL_ID关联。

4. 文件存储类型信息表

这些表主要跟hive文件存储类型相关，HDFS支持各种文件格式，hive建表的时候也可以指定文件格式。这样Hive在将HQL解析成MapReduce的时候，可以指定去哪里，使用哪种格式去读写HDFS文件。

4.1 SDS表记录文件存储的基本信息，如输入格式、输出格式，是否压缩等。

元数据表字段	说明	示例数据
SD_ID	存储信息ID	12
CD_ID	字段信息ID	12
INPUT_FORMAT	文件输入格式	org.apache.hadoop.mapred.TextInputFormat
IS_COMPRESSED	是否压缩	0
IS_STOREDASSUBDIRECTORIES	是否以子目录存储	0
LOCATION	HDFS路径	hdfs://192.168.1.8:9000 /user/hive/warehouse/hive.db/person
NUM_BUCKETS	分桶数量	-1
OUTPUT_FORMAT	文件输出格式	org.apache.hadoop.hive.ql.io .HiveIgnoreKeyTextOutputFormat
SERDE_ID	序列化类ID	12，对应SERDES表

4.2 SD_PARAMS表，记录hive存储的相关属性，在创建表时使用

元数据表字段	说明	示例数据
SD_ID	存储配置ID	1
PARAM_KEY	存储属性名
PARAM_VALUE	存储属性值

4.3 SERDES表，记录存储序列号使用的类信息

元数据表字段	说明	示例数据
SERDE_ID	序列化类配置ID	1
NAME	序列化类别名
SLIB	序列化类	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

4.4 SERDE_PARAMS表，记录序列化的一些属性和格式信息，比如行、分隔符等

元数据表字段	说明	示例数据
SERDE_ID	序列化类配置ID	12
PARAM_KEY	属性名	serialization.format
PARAM_VALUE	属性值	1

5. 表字段信息表

5.1 COLUMNS_V2

元数据表字段	说明	示例数据
CD_ID	字段信息ID	12
COMMENT	字段注释	this is name
COLUMN_NAME	字段名	name
TYPE_NAME	字段类型	string
INTEGER_IDX	字段顺序	1

6. 分区信息表

6.1 PARTITIONS，分区基本信息表

元数据表字段	说明	示例数据
PART_ID	分区ID	12
CREATE_TIME	分区创建时间	1530030077
LAST_ACCESS_TIME	最后一次访问时间	1530030077
PART_NAME	分区名	create_date=2016-05-05
SD_ID	分区存储ID	53
TBL_ID	表ID	41

6.2 PARTITION_KEYS，分区字段信息表

元数据表字段	说明	示例数据
TBL_ID	表ID	24
PKEY_COMMENT	分区字段说明
PKEY_NAME	分区字段名	event_month
PKEY_TYPE	分区字段类型	string
INTEGER_IDX	分区字段顺序	0

6.3 PARTITION_KEY_VALS，分区字段值表

元数据表字段	说明	示例数据
PART_ID	分区ID	12
PART_KEY_VAL	分区字段值	2016-05-05
INTEGER_IDX	分区字段值顺序	0

6.4 PARTITION_PARAMS，分区属性信息表

元数据表字段	说明	示例数据
PART_ID	分区ID	12
PARAM_KEY	分区属性名	COLUMN_STATS_ACCURATE
PARAM_VALUE	分区属性值	true

7. 其他不常用的表

DB_PRIVS：数据库权限信息表。通过GRANT语句对数据库授权后，将会在这里存储； IDXS：索引表； INDEX_PARAMS：索引参数/属性表； TAB_COL_STATS：表字段的统计信息表。使用ANALYZE语句对标字段分析后记录在这里； TBL_COL_PRIVS：表字段的授权信息表； PART_PRIVS：分区授权信息表； PART_COL_STATS：分区字段的统计信息表； PART_COL_PRIVS：分区字段的权限信息表； FUNCS：用户注册的函数信息表； FUNCS_RU：用户注册函数的资源信息表。

8. 示例

8.1 Hive中新建一张表

hive> use hive;
hive> create table order_partition_sample(
    > ordernumber string,
    > eventtime string
    > )
    > comment 'this is order partition'
    > partitioned by (event_month string)
    > row format delimited 
    > fields terminated by '\t';
OK
Time taken: 0.411 seconds
hive>

建表语句执行雨后，metadaba发生了如下变化 TBLS

# TBLS表里产生了表order_partition_sample的基本信息
mysql> SELECT * FROM TBLS WHERE TBL_NAME = 'order_partition_sample' \G
*************************** 1. row ***************************
            TBL_ID: 56
       CREATE_TIME: 1532948299
             DB_ID: 8
  LAST_ACCESS_TIME: 0
             OWNER: hadoop
         RETENTION: 0
             SD_ID: 71
          TBL_NAME: order_partition_sample
          TBL_TYPE: MANAGED_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL
1 row in set (0.00 sec)

mysql>

TABLE_PARAMS

# 其中，TBL_ID来自上面的TBLS表。注意这里是产生了两条记录
mysql> SELECT * FROM TABLE_PARAMS WHERE TBL_ID = 56 \G
*************************** 1. row ***************************
     TBL_ID: 56
  PARAM_KEY: comment
PARAM_VALUE: this is order partition
*************************** 2. row ***************************
     TBL_ID: 56
  PARAM_KEY: transient_lastDdlTime
PARAM_VALUE: 1532948299
2 rows in set (0.00 sec)

mysql>

SDS

# 其中，SD_ID来自于TBLS表刚查询的结果
mysql> SELECT * FROM SDS WHERE SD_ID = 71 \G
*************************** 1. row ***************************
                    SD_ID: 71
                    CD_ID: 56
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:  
IS_STOREDASSUBDIRECTORIES:  
                 LOCATION: hdfs://192.168.1.8:9000/user/hive/warehouse/hive.db/order_partition_sample
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 71
1 row in set (0.00 sec)

mysql>

SERDE_PARAMS

# 其中，SERDE_ID来自于TBLS表刚查询的结果SD_ID，这里查询结果是也是有两条
mysql> SELECT * FROM SERDE_PARAMS WHERE SERDE_ID = 71 \G
*************************** 1. row ***************************
   SERDE_ID: 71
  PARAM_KEY: field.delim
PARAM_VALUE:    
*************************** 2. row ***************************
   SERDE_ID: 71
  PARAM_KEY: serialization.format
PARAM_VALUE:    
2 rows in set (0.00 sec)

mysql>

COLUMNS_V2

# CD_ID来自于SDS查询结果
mysql> SELECT * FROM COLUMNS_V2 WHERE CD_ID = 56 \G
*************************** 1. row ***************************
      CD_ID: 56
    COMMENT: NULL
COLUMN_NAME: eventtime
  TYPE_NAME: string
INTEGER_IDX: 1
*************************** 2. row ***************************
      CD_ID: 56
    COMMENT: NULL
COLUMN_NAME: ordernumber
  TYPE_NAME: string
INTEGER_IDX: 0
2 rows in set (0.01 sec)

mysql>

PARTITION_KEYS

mysql> SELECT * FROM PARTITION_KEYS WHERE TBL_ID = 56 \G
*************************** 1. row ***************************
      TBL_ID: 56
PKEY_COMMENT: NULL
   PKEY_NAME: event_month
   PKEY_TYPE: string
 INTEGER_IDX: 0
1 row in set (0.00 sec)

mysql>

8.2 再向刚建的表里导入数据

hive> load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_partition_sample partition(event_month='2018-05');
Loading data to table hive.order_partition_sample partition (event_month=2018-05)
Partition hive.order_partition_sample{event_month=2018-05} stats: [numFiles=1, numRows=0, totalSize=208, rawDataSize=0]
OK
Time taken: 0.815 seconds
hive> select * from order_partition_sample;
OK
10703007267488  2014-05-01 06:01:12.334+01  2018-05
10101043505096  2014-05-01 07:28:12.342+01  2018-05
10103043509747  2014-05-01 07:50:12.33+01   2018-05
10103043501575  2014-05-01 09:27:12.33+01   2018-05
10104043514061  2014-05-01 09:03:12.324+01  2018-05
Time taken: 0.298 seconds, Fetched: 5 row(s)
hive>

数据导入以后，下面的metadata会发生变化： PARTITIONS

mysql> SELECT * FROM PARTITIONS WHERE TBL_ID = 56 \G
*************************** 1. row ***************************
         PART_ID: 16
     CREATE_TIME: 1532949369
LAST_ACCESS_TIME: 0
       PART_NAME: event_month=2018-05
           SD_ID: 72
          TBL_ID: 56
1 row in set (0.01 sec)

mysql>

SDS

mysql> SELECT * FROM SDS WHERE SD_ID = 71 \G
*************************** 1. row ***************************
                    SD_ID: 71
                    CD_ID: 56
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:  
IS_STOREDASSUBDIRECTORIES:  
                 LOCATION: hdfs://192.168.1.8:9000/user/hive/warehouse/hive.db/order_partition_sample
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 71
1 row in set (0.00 sec)

mysql>

注意：这里的存储和表的存储是不同的，因为每个分区也会有一个SD

PARTITION_KEY_VALS

# PART_ID是刚才从PARTITIONS表查来的
mysql> SELECT * FROM PARTITION_KEY_VALS WHERE PART_ID = 16 \G
*************************** 1. row ***************************
     PART_ID: 16
PART_KEY_VAL: 2018-05
 INTEGER_IDX: 0
1 row in set (0.00 sec)

mysql>

PARTITION_PARAMS

# 在分区的属性里面，生成该分区的统计信息。
mysql> SELECT * FROM PARTITION_PARAMS WHERE PART_ID = 16;
+---------+-----------------------+-------------+
| PART_ID | PARAM_KEY             | PARAM_VALUE |
+---------+-----------------------+-------------+
|      16 | COLUMN_STATS_ACCURATE | true        |
|      16 | numFiles              | 1           |
|      16 | numRows               | 0           |
|      16 | rawDataSize           | 0           |
|      16 | totalSize             | 208         |
|      16 | transient_lastDdlTime | 1532949369  |
+---------+-----------------------+-------------+
6 rows in set (0.00 sec)

mysql>

8.3 通过删除metadata来删除hive中的表

第一步，查看要删除的表的基本信息，确定是一张分区表

hive> desc formatted order_partition_sample;
OK
# col_name              data_type               comment             

ordernumber             string                                      
eventtime               string                                      

# Partition Information      
# col_name              data_type               comment             

event_month             string                                      

# Detailed Table Information         
Database:               hive                     
Owner:                  hadoop                   
CreateTime:             Mon Jul 30 18:58:19 CST 2018     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://192.168.1.8:9000/user/hive/warehouse/hive.db/order_partition_sample   
Table Type:             MANAGED_TABLE            
Table Parameters:        
    comment                 this is order partition
    transient_lastDdlTime   1532948299          

# Storage Information        
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe   
InputFormat:            org.apache.hadoop.mapred.TextInputFormat     
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat   
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:         
    field.delim             \t                  
    serialization.format    \t                  
Time taken: 0.081 seconds, Fetched: 34 row(s)
hive>

第二步：根据表名字，通过查询TBLS表获得TBL_ID和SD_ID

mysql> SELECT TBL_ID,SD_ID FROM TBLS WHERE TBL_NAME = 'order_partition_sample';
+--------+-------+
| TBL_ID | SD_ID |
+--------+-------+
|     56 |    71 |
+--------+-------+
1 row in set (0.00 sec)

mysql>

第三步：拿SD_ID去SDS表里查询得到CD_ID和SERDE_ID

mysql> SELECT SD_ID,CD_ID,SERDE_ID FROM SDS WHERE SD_ID = 71;
+-------+-------+----------+
| SD_ID | CD_ID | SERDE_ID |
+-------+-------+----------+
|    71 |    56 |       71 |
+-------+-------+----------+
1 row in set (0.00 sec)

mysql>

第四步：拿TBL_ID去PARTITIONS表里拿到PART_ID

mysql> SELECT SD_ID,TBL_ID,PART_ID FROM PARTITIONS WHERE TBL_ID = 56;
+-------+--------+---------+
| SD_ID | TBL_ID | PART_ID |
+-------+--------+---------+
|    72 |     56 |      16 |
+-------+--------+---------+
1 row in set (0.00 sec)

mysql>

这样一共得到了五个ID：

TBL_ID=56
SD_ID=71
CD_ID =56
PART_ID=16
SERDE_ID=71

最后一步，删除表

# 1. 写删除脚本，一共需要删除10张表
[root@hadoop01 tmp]# vi delete_hive_table.sh
#! /bin/bash
mysql -ucindy -p123 hive -e"
delete FROM PARTITION_PARAMS WHERE PART_ID=$4;
delete FROM PARTITION_KEY_VALS WHERE PART_ID=$4;
delete FROM PARTITIONS WHERE TBL_ID=$1;

delete FROM PARTITION_KEYS WHERE TBL_ID=$1;
delete FROM TABLE_PARAMS WHERE TBL_ID=$1;
delete FROM TBLS WHERE TBL_ID=$1;
delete FROM SERDE_PARAMS WHERE SERDE_ID=$5;
delete FROM SERDES WHERE SERDE_ID=$5;

delete FROM SDS WHERE SD_ID=$2;
delete FROM COLUMNS_V2 WHERE CD_ID=$3;"

# 2. 给脚本添加可执行权限
[root@hadoop01 tmp]# chmod +x delete_hive_table.sh 

# 3. 执行删除脚本，传入前面拿到的五个ID
[root@hadoop01 tmp]# ./delete_hive_table.sh 56 71 56 16 71

删除完成以后，再去执行上面8.1和8.2步骤中的查询语句，依次检查，metadata里面已经没有刚创建表的任何数据了，删除完成。

参考：https://www.cnblogs.com/1130136248wlxk/articles/5517909.html

cindysz110 / blog

[Hadoop] Hive metadata #32

Hive 元数据的表结构

1. 版本表

1.1 VERSION表，存储hive的版本

2. 数据库信息表

2.1 DBS表，存储Hive中所有数据库的基本信息。

2.2 DATABASE_PARAMS表，存储数据库的相关参数，在CREATE DATABASE时调用。

3. hive表和视图描述表

3.1 TBLS表，存储Hive表、视图、索引表的基本信息

3.2 TABLE_PARAMS表，存储表、视图的属性信息

3.3 TBL_PRIVS，存储表、视图的授权信息

4. 文件存储类型信息表

4.1 SDS表记录文件存储的基本信息，如输入格式、输出格式，是否压缩等。

4.2 SD_PARAMS表，记录hive存储的相关属性，在创建表时使用

4.3 SERDES表，记录存储序列号使用的类信息

4.4 SERDE_PARAMS表，记录序列化的一些属性和格式信息，比如行、分隔符等

5. 表字段信息表

5.1 COLUMNS_V2

6. 分区信息表

6.1 PARTITIONS，分区基本信息表

6.2 PARTITION_KEYS，分区字段信息表

6.3 PARTITION_KEY_VALS，分区字段值表

6.4 PARTITION_PARAMS，分区属性信息表

7. 其他不常用的表

8. 示例

8.1 Hive中新建一张表

8.2 再向刚建的表里导入数据

8.3 通过删除metadata来删除hive中的表

cindysz110 / blog

[Hadoop] Hive metadata #32

Hive 元数据的表结构

1. 版本表

1.1 VERSION表，存储hive的版本

2. 数据库信息表

2.1 DBS表，存储Hive中所有数据库的基本信息。

2.2 DATABASE_PARAMS表，存储数据库的相关参数，在CREATE DATABASE时调用。

3. hive表和视图描述表

3.1 TBLS表，存储Hive表、视图、索引表的基本信息

3.2 TABLE_PARAMS表，存储表、视图的属性信息

3.3 TBL_PRIVS，存储表、视图的授权信息

4. 文件存储类型信息表

4.1 SDS表 记录文件存储的基本信息，如输入格式、输出格式，是否压缩等。

4.2 SD_PARAMS表，记录hive存储的相关属性，在创建表时使用

4.3 SERDES表，记录存储序列号使用的类信息

4.4 SERDE_PARAMS表，记录序列化的一些属性和格式信息，比如行、分隔符等

5. 表字段信息表

5.1 COLUMNS_V2

6. 分区信息表

6.1 PARTITIONS，分区基本信息表

6.2 PARTITION_KEYS，分区字段信息表

6.3 PARTITION_KEY_VALS，分区字段值表

6.4 PARTITION_PARAMS，分区属性信息表

7. 其他不常用的表

8. 示例

8.1 Hive中新建一张表

8.2 再向刚建的表里导入数据

8.3 通过删除metadata来删除hive中的表

4.1 SDS表记录文件存储的基本信息，如输入格式、输出格式，是否压缩等。