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

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

2.1 DBS

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.1 SERDES

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.1 SDS

4.2 SD_PARAMS

5.1 TABLE_PARAMS

5.2 TBLS

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.1 PARTITIONS

6.2 PARTITION_PARAMS

6.3 PARTITION_KEYS

6.4 PARTITION_KEY_VALS

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

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

下面是基本

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‘;