hive 之元数据表结构（Mysql)

hive 元数据表结构

1. CDS
2. db相关
- 2.1. DBS
- 2.2. DATABASE_PARAMS
3. SerDe相关
- 3.1. SERDES
- 3.2. SERDES_PARAMS
4. 存储相关
- 4.1. SDS
- 4.2. SD_PARAMS
5. table相关
6. 分区
7. VERSION
8. 根据元数据拼写表的创建语句

1 CDS

该表内容非常简单，只有一个字段：cd_id,这个字段不明白是什么意思，但是有一点可以肯定的是cd_id 并不是tbls.tbl_id.

比如在partition_keys.tbls_id ，如果与sds.cd_id 关联，查出来的结果，可能是错误的。

2 db相关

2.1 DBS

该表存储着数据库的基本信息。

字段	说明
DB_ID	数据库的编号，作为主键
DESC	对于该数据库的说明
DB_LOCATION_URI	数据库在hdfs中的位置
NAME	库名
OWNER_NAME	库的所有者。
OWNER_TYPE	库拥有者的类型

db_id 可以与tbls 表关联，查询库里有哪些表。或者某张表属于哪个库。见 TBLS.

mysql> select db_id,db_location_uri,name from DBS;
+-------+----------------------------------------------------+---------+
| db_id | db_location_uri                                    | name    |
+-------+----------------------------------------------------+---------+
|     1 | hdfs://nameservice1/user/hive/warehouse            | default |
|  3132 | hdfs://nameservice1/user/hive/warehouse/bigdata.db | bigdata |
+-------+----------------------------------------------------+---------+

2.2 DATABASE_PARAMS

字段	说明
DB_ID	数据库ID
PARAM_KEY	参数名称
PARAM_VALUE	参数值

3 SerDe相关

3.1 SERDES

字段	说明
serde_id	主键,每张表一个编号
NAME	默认为NULL
slib	序列化使用的库名

slib 是建表时指定的或者根据存储格式自动指定的。

CREATE EXTERNAL TABLE IF NOT EXISTS my_table (
id string, name string,...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'  STORED AS textfile -- hive 3.0使用Json格式读写textfile
LOCATION '/usr/hive/text/my_table';

示例如下：

mysql> select * from SERDES limit 5;
+----------+------+------------------------------------------------------+
| SERDE_ID | NAME | SLIB                                                 |
+----------+------+------------------------------------------------------+
|    18005 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe   |
|    82367 | NULL | org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe |
+----------+------+------------------------------------------------------+

3.2 SERDES_PARAMS

字段	说明
serde_id	主键，序列化的编号
param_key	参数名
param_value	参数值

参数的可选值有如下几项:

mysql> select distinct param_key from SERDE_PARAMS;
+----------------------+
| param_key            |
+----------------------+
| field.delim          |
| serialization.format |
| escapeChar           |
| quoteChar            |
| separatorChar        |
+----------------------+

4 存储相关

4.1 SDS

该表存储着表的存储信息。比如inputformat,outputformat,location 等。

字段	说明
SD_ID	主键，没什么意义
CD_ID	tbls.tbl_id或者 cds.cd_id
INPUT_FORMAT	数据输入格式
IS_COMPRESSED	是否对数据进行压缩
IS_STOREDASSUBDIRECTORIES	是否存储在子目录
LOCATION	数据在hdfs中的存放位置
NUM_BUCKETS	分桶的数量
OUTPUT_FORMAT	数据输出格式
SERDE_ID	SERDES.SERDE_ID

4.2 SD_PARAMS

该表存储Hive存储的属性信息，在创建表时候使用. 通过STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)指定

字段	说明
SD_ID	配置信息ID
param_key	存储属性名
param_value

本地环境数据为空。

5 table相关

5.1 TABLE_PARAMS

这张表存储着表相关的统计信息。比如有多少个文件，有多少行数据，当前大小，最近一次操作时间。

字段	说明
TBL_ID	数据的编号
PARAM_KEY	参数
PARAM_VALUE	参数的值

一般param_key 包含如下几个统计项:

项	说明
COLUMN_STATS_ACCURATE	是否精确统计列，布尔值
numFiles	文件个数
numRows	行数
rawDataSize	原始数据大小，未压缩前的数据大小
totalSize	占用HDFS空间大小
transient_lastDdlTime	最近一次操作的时间戳
external	是否外部表，布尔值
comment	表说明，字符串

5.2 TBLS

记录数据表的信息

字段	解释
TBL_ID	在hive中创建表的时候自动生成的一个id，用来表示，主键
CREATE_TIME	创建的数据表的时间，使用的是时间戳
DBS_ID	这个表是在那个数据库里面
LAST_ACCESS_TIME	最后一次访问的时间戳
OWNER	数据表的所有者
RETENTION	保留时间
SD_ID	标记物理存储信息的id
TBL_NAME	数据表的名称
TBL_TYPE	数据表的类型,MANAGED_TABLE, EXTERNAL_TABLE, VIRTUAL_VIEW, INDEX_TABLE
VIEW_EXPANDED_TEXT	展开视图文本，非视图为null
VIEW_ORIGINAL_TEXT	原始视图文本，非视图为null

TBLS的SD_ID与SDS的SD_ID进行关联,可以查询存储信息,TBLS的DB_ID与DBS的DB_ID进行关联，可以查询库信息。

下面语句可以查看所有表的所属的数据库，用户和表类型。

select b.name as db_name,a.tbl_id,a.owner as tbl_owner,a.tbl_name,a.tbl_type from TBLS a, DBS b
where a.db_id = b.db_id;

下面语句可以查看所有表的存储信息:

select a.owner,a.tbl_name, b.input_format,b.output_format,b.location,
b.is_compressed,b.IS_STOREDASSUBDIRECTORIES
from TBLS a,SDS b
where a.sd_id = b.sd_id;

5.3 TAB_COL_STATS

字段	说明
CS_ID	列统计编号
AVG_COL_LEN	数据的平均长度
MAX_COL_LEN	数据的最大长度
COLUMN_NAME	列的名字
COLUMN_TYPE	列的类型
DB_NAME	数据库的名称
BIG_DECIMAL_HIGH_VALUE	数据中最大的Decimal值
BIG_DECIMAL_LOW_VALUE	数据中最小的Decimal值
DOUBLE_HIGH_VALUE	数据中最大的Double值
DOUBLE_LOW_VALUE	数据中最小的Double值
LAST_ANALYZED	最新一次解析的时间戳
LONG_HIGH_VALUE	数据中最大的Long值
LONG_LOW_VALUE	数据中最小的Long值
NUM_DISTINCTS	不同记录的数量
NUM_FALSES	为false的数量
NUM_NULLS	为null的数量
NUM_TRUES	为true的数量
TBL_ID	表的ID
TABLE_NAME	数据表的名称

5.4 COLUMNS_V2

字段	说明
CD_ID	关联cds.cd_id,与tbls.tb_id一致
comment	字段注释
column_name	字段名
type_name	字段类型
integer_idx	字段在表中的顺序

6 分区

6.1 PARTITIONS

字段	说明
PART_ID	分区的编号
CREATE_TIME	创建分区的时间
LAST_ACCESS_TIME	最近一次访问时间
PART_NAME	分区的名字
SD_ID	关联SDS.SD_ID
TBL_ID	数据表的id,TBLS.tbl_id

6.2 PARTITION_PARAMS

字段	说明
PART_ID	分区的编号
PARAM_KEY	参数
PARAM_VALUE	参数的值

参数可选值:

param_key	说明
COLUMN_STATS_ACCURATE	是否精确统计，布尔值，默认TRUE
numFiles	有多少个文件
numRows	有多少行数据
rawDataSize	原始文件大小，未压缩前的数据占用空间大小
totalSize	hdfs中占用空间大小
transient_lastDdlTime	最后一次执行ddl的时间，timestamp类型
last_modified_by	执行ddl的用户
last_modified_time	最后一次执行修改的时间，timestamp类型

6.3 PARTITION_KEYS

字段	说明
TBL_ID	数据表的编号,TBLS.tbl_id
PKEY_COMMENT	分区字段的描述
PKEY_NAME	分区字段的名称
PKEY_TYPE	分区字段的类型

6.4 PARTITION_KEY_VALS

字段	说明
PART_ID	分区编号
PART_KEY_VAL	分区字段的值

7 VERSION

这个表是记录Hive的版本，这个表里面只能有一条记录，这样Hive才能启动。在创建metadata表的时候，自动写入的信息。

字段	说明
VER_ID	版本id
SCHEMA_VERSION
VERSION_COMMENT	一般就是简单的说明

mysql> select * from VERSION;
+--------+----------------+----------------------------+
| VER_ID | SCHEMA_VERSION | VERSION_COMMENT            |
+--------+----------------+----------------------------+
|      1 | 1.1.0          | Hive release version 1.1.0 |
+--------+----------------+----------------------------+

8 根据元数据拼写表的创建语句

下面是基本

select concat('create table ',
t.tbl_name,' (\n',c.col_string,')',
case pk.partition_string
WHEN NULL then NULL
ELSE concat('\npartition by (',pk.partition_string,')')
end,
case se.slib
when null then null
else concat('\nrow format serde\n''',se.slib,'''\n')
end,
case sep.serde_id
when null then null
else concat('WITH SERDEPROPERTIES (\n',sep.params,')\n')
end,
'stored as inputformat\n''',
s.input_format,'''\noutputformat\n''',
s.output_format,'\nlocation\n''',s.location,''';'
-- '\n stored as orc\n LOCATION ''',s.location
-- ,'''\nTBLPROPERTIES(\n ''orc.compression=''SNAPPY'');'
)
from TBLS t left join (select tbl_id,group_concat(concat_ws(' ',pkey_name,pkey_type)) as partition_string from PARTITION_KEYS group by tbl_id order by integer_idx) pk on t.tbl_id = pk.tbl_id
left join DBS d on t.db_id = d.db_id
left join SDS s on t.sd_id = s.sd_id
left join SERDES se on s.serde_id = se.serde_id
left join (select serde_id,group_concat(concat_ws('=',concat('''',param_key,''''),concat('''',param_value,'''\n'))) params from SERDE_PARAMS group by serde_id) sep on se.serde_id = sep.serde_id
left join (select cd_id, group_concat(concat_ws(' ',column_name,type_name) separator ',\n') as col_string from COLUMNS_V2 group by cd_id order by integer_idx) c on s.cd_id = c.cd_id
where t.tbl_id=33374
-- and t.owner = ''
-- and d.name = ''
group by  d.name, t.owner,t.tbl_name;

下面是通过元数据，生成将表改为ORC的SQL:

select concat('create table ',
t.tbl_name,'_orc (\n',c.col_string,')',
case
WHEN pk.partition_string is NULL then ' '
ELSE concat('\npartitioned by (',pk.partition_string,')')
end,
'\n stored as orc\n LOCATION ''',s.location,'_orc',
'''\nTBLPROPERTIES(\n ''orc.compress''=''SNAPPY'');',
'\n\ninsert into ',t.tbl_name,'_orc ',
case
WHEN pk.partition_string is NULL then ' '
ELSE concat('partition (',pk.pkeys,')')
end,
'\nselect ',c.cols,
case
WHEN pk.partition_string is NULL then ' '
ELSE concat(',',pk.pkeys)
end,
'\n from ',t.tbl_name,';'
'\n\nalter table ',t.tbl_name,' rename to ',t.tbl_name,'b;\n',
'alter table ',t.tbl_name,'_orc rename to ',t.tbl_name,';\n\n') as contents
from TBLS t left join (select tbl_id,group_concat(concat_ws(' ',pkey_name,pkey_type)) as partition_string,group_concat(pkey_name) as pkeys from PARTITION_KEYS group by tbl_id order by integer_idx) pk on t.tbl_id = pk.tbl_id
left join (select * from DBS where db_id=3132) d on t.db_id = d.db_id
left join SDS s on t.sd_id = s.sd_id
left join SERDES se on s.serde_id = se.serde_id
left join (select serde_id,group_concat(concat_ws('=',concat('''',param_key,''''),concat('''',param_value,'''\n'))) params from SERDE_PARAMS group by serde_id) sep on se.serde_id = sep.serde_id
left join (select cd_id, group_concat(concat_ws(' ',column_name,type_name) order by integer_idx separator ',\n') as col_string ,group_concat(column_name order by integer_idx) as cols from COLUMNS_V2 group by cd_id order by integer_idx) c on s.cd_id = c.cd_id
where -- t.tbl_id=28883 and
-- t.owner = '' and
se.slib !='org.apache.hadoop.hive.ql.io.orc.OrcSerde' and
t.tbl_name not like 'stg%'
-- and t.tbl_name='test'
and t.tbl_name not like 'new%'
group by  d.name, t.owner,t.tbl_name
into outfile '/tmp/change_table_0603.sql';

Author: halberd.lee

Created: 2020-06-02 Tue 23:41

Validate

posted @ 2020-05-31 23:24 halberd.lee 阅读(2437) 评论(0) 编辑收藏举报

刷新页面返回顶部

halberd's blog

hive 之元数据表结构（Mysql)

hive 元数据表结构

Table of Contents

1 CDS

2 db相关

2.1 DBS

2.2 DATABASE_PARAMS

3 SerDe相关

3.1 SERDES

3.2 SERDES_PARAMS

4 存储相关

4.1 SDS

4.2 SD_PARAMS

5 table相关

5.1 TABLE_PARAMS

5.2 TBLS

5.3 TAB_COL_STATS

5.4 COLUMNS_V2

6 分区

6.1 PARTITIONS

6.2 PARTITION_PARAMS

6.3 PARTITION_KEYS

6.4 PARTITION_KEY_VALS

7 VERSION

8 根据元数据拼写表的创建语句

公告

halberd's blog

hive 之 元数据表结构（Mysql)

hive 元数据表结构

Table of Contents

1 CDS

2 db相关

2.1 DBS

2.2 DATABASE_PARAMS

3 SerDe相关

3.1 SERDES

3.2 SERDES_PARAMS

4 存储相关

4.1 SDS

4.2 SD_PARAMS

5 table相关

5.1 TABLE_PARAMS

5.2 TBLS

5.3 TAB_COL_STATS

5.4 COLUMNS_V2

6 分区

6.1 PARTITIONS

6.2 PARTITION_PARAMS

6.3 PARTITION_KEYS

6.4 PARTITION_KEY_VALS

7 VERSION

8 根据元数据拼写表的创建语句

公告

hive 之元数据表结构（Mysql)