关于Mysql元数据如何生成Hive建表语句注释脚本-tutorial mysql-php.cn

关于Mysql元数据如何生成Hive建表语句注释脚本

小云云

Lepaskan： 2017-12-22 14:18:11

asal

2380 orang telah melayarinya

最近在将数据从Mysql 等其他关系型数据库抽取到Hive 表中时，需要同步mysql表中的注释，以下脚本可以生成hive表字段注释修改语句。主要给大家介绍了关于Mysql元数据如何生成Hive建表语句注释脚本的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面跟着小编来一起学习学习吧。

注：其他关系型数据库如：oracle 可以通过相同的思路，读取元数据，修改脚本语法实现。

使用：

在mysql元数据库：information_schema 中执行以下语句

SELECT CONCAT(&#39;alter table &#39;, TABLE_NAME, &#39; CHANGE COLUMN &#39;, COLUMN_NAME, &#39; &#39;, COLUMN_NAME, &#39; &#39;, DATA_TYPE, &#39; comment &#39;, &#39;"&#39;, COLUMN_COMMENT, &#39;"&#39;, &#39;;&#39;)
FROM (SELECT TABLE_NAME, COLUMN_NAME, CASE WHEN DATA_TYPE = &#39;varchar&#39; THEN &#39;string&#39; WHEN DATA_TYPE = &#39;int&#39; THEN &#39;int&#39; WHEN DATA_TYPE = &#39;tinyint&#39; THEN &#39;tinyint&#39; WHEN DATA_TYPE = &#39;decimal&#39; THEN &#39;double&#39; WHEN DATA_TYPE = &#39;datetime&#39; THEN &#39;string&#39; WHEN DATA_TYPE = &#39;timestamp&#39; THEN &#39;string&#39; WHEN DATA_TYPE = &#39;float&#39; THEN &#39;double&#39; WHEN DATA_TYPE = &#39;double&#39; THEN &#39;double&#39; WHEN DATA_TYPE = &#39;bigint&#39; THEN &#39;bigint&#39; END AS DATA_TYPE, COLUMN_COMMENT
FROM COLUMNS
WHERE TABLE_NAME = &#39;o_oms_statistic_profit&#39;
) t;

Salin selepas log masuk

在将数据从Mysql 等其他关系型数据库抽取到Hive 表中时，需要同步mysql表中的注释，以下脚本可以生成hive创建表语句。只是生成了hive表主要的字段信息，其他信息需要手工添加。

在mysql元数据库：information_schema 中执行以下语句

SELECT CONCAT(&#39;create table &#39;, TABLE_NAME, &#39;(&#39;, substring(column_info, 1, length(column_info) - 1), &#39;)&#39;, &#39; comment &#39;, &#39;"&#39;, TABLE_COMMENT, &#39;"&#39;, &#39;;&#39;)
FROM (SELECT TABLE_NAME, TABLE_COMMENT, group_concat(CONCAT(COLUMN_NAME, &#39; &#39;, DATA_TYPE, &#39; comment &#39;, &#39;"&#39;, COLUMN_COMMENT, &#39;"&#39;)) AS column_info
FROM (SELECT t1.TABLE_NAME, CASE WHEN t2.TABLE_COMMENT = NULL THEN t1.TABLE_NAME ELSE t2.TABLE_COMMENT END AS TABLE_COMMENT, COLUMN_NAME, CASE WHEN DATA_TYPE = &#39;varchar&#39; THEN &#39;string&#39; WHEN DATA_TYPE = &#39;int&#39; THEN &#39;int&#39; WHEN DATA_TYPE = &#39;tinyint&#39; THEN &#39;tinyint&#39; WHEN DATA_TYPE = &#39;decimal&#39; THEN &#39;double&#39; WHEN DATA_TYPE = &#39;datetime&#39; THEN &#39;string&#39; WHEN DATA_TYPE = &#39;timestamp&#39; THEN &#39;string&#39; WHEN DATA_TYPE = &#39;float&#39; THEN &#39;double&#39; WHEN DATA_TYPE = &#39;double&#39; THEN &#39;double&#39; WHEN DATA_TYPE = &#39;bigint&#39; THEN &#39;bigint&#39; END AS DATA_TYPE, CASE WHEN COLUMN_COMMENT = NULL THEN COLUMN_NAME ELSE COLUMN_COMMENT END AS COLUMN_COMMENT
FROM COLUMNS t1 JOIN TABLES t2 ON t1.TABLE_NAME = t2.TABLE_NAME
WHERE t1.TABLE_NAME = &#39;o_oms_statistic_profit&#39;
) t3
GROUP BY TABLE_NAME, TABLE_COMMENT
) t4;

Salin selepas log masuk