hive 之 元数据结构(Mysql)

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

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

2 db相关

2.1 DBS

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

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

3 SerDe相关

3.1 SERDES

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

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

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

4 存储相关

4.1 SDS

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

4.2 SD_PARAMS

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

本地环境数据为空。

5 table相关

5.1 TABLE_PARAMS

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

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

5.2 TBLS

记录数据表的信息

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

5.4 COLUMNS_V2

6 分区

6.1 PARTITIONS

6.2 PARTITION_PARAMS

参数可选值:

6.3 PARTITION_KEYS

6.4 PARTITION_KEY_VALS

7 VERSION

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

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 ‘,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) separator ‘,\n‘) as col_string ,group_concat(column_name) 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=543083
-- and t.owner = ‘‘
WHERE se.slib !=‘org.apache.hadoop.hive.ql.io.orc.OrcSerde‘
and t.tbl_name not like ‘stg%‘
-- and t.tbl_name=‘test‘
group by  d.name, t.owner,t.tbl_name
into outfile ‘/tmp/change_table.sql‘;

Author: halberd.lee

Created: 2020-05-31 Sun 23:25

Validate

hive 之 元数据结构(Mysql)

标签：示例   bigdata   Owner   arch   ams   最大的   eric   gray   lambda