最近在將數(shù)據(jù)從mysql 等其他關(guān)系型數(shù)據(jù)庫 抽取到hive 表中時，需要同步mysql表中的注釋，以下腳本可以生成hive表字段注釋修改語句。主要給大家介紹了關(guān)于mysql元數(shù)據(jù)如何生成hive建表語句注釋腳本的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面跟著小編來一起學(xué)習(xí)學(xué)習(xí)吧。
注：其他關(guān)系型數(shù)據(jù)庫如：oracle 可以通過相同的思路，讀取元數(shù)據(jù)，修改腳本語法實現(xiàn)。
使用：
在mysql元數(shù)據(jù)庫：information_schema 中執(zhí)行以下語句
SELECT?CONCAT('alter?table?',?TABLE_NAME,?'?CHANGE?COLUMN?',?COLUMN_NAME,?'?',?COLUMN_NAME,?'?',?DATA_TYPE,?'?comment?',?'"',?COLUMN_COMMENT,?'"',?';') FROM?(SELECT?TABLE_NAME,?COLUMN_NAME,?CASE?WHEN?DATA_TYPE?=?'varchar'?THEN?'string'?WHEN?DATA_TYPE?=?'int'?THEN?'int'?WHEN?DATA_TYPE?=?'tinyint'?THEN?'tinyint'?WHEN?DATA_TYPE?=?'decimal'?THEN?'double'?WHEN?DATA_TYPE?=?'datetime'?THEN?'string'?WHEN?DATA_TYPE?=?'timestamp'?THEN?'string'?WHEN?DATA_TYPE?=?'float'?THEN?'double'?WHEN?DATA_TYPE?=?'double'?THEN?'double'?WHEN?DATA_TYPE?=?'bigint'?THEN?'bigint'?END?AS?DATA_TYPE,?COLUMN_COMMENT FROM?COLUMNS WHERE?TABLE_NAME?=?'o_oms_statistic_profit' )?t;
在將數(shù)據(jù)從Mysql 等其他關(guān)系型數(shù)據(jù)庫 抽取到Hive 表中時，需要同步mysql表中的注釋，以下腳本可以生成hive創(chuàng)建表語句。只是生成了hive表主要的字段信息，其他信息需要手工添加。
在mysql元數(shù)據(jù)庫：information_schema 中執(zhí)行以下語句
SELECT?CONCAT('create?table?',?TABLE_NAME,?'(',?substring(column_info,?1,?length(column_info)?-?1),?')',?'?comment?',?'"',?TABLE_COMMENT,?'"',?';') FROM?(SELECT?TABLE_NAME,?TABLE_COMMENT,?group_concat(CONCAT(COLUMN_NAME,?'?',?DATA_TYPE,?'?comment?',?'"',?COLUMN_COMMENT,?'"'))?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?=?'varchar'?THEN?'string'?WHEN?DATA_TYPE?=?'int'?THEN?'int'?WHEN?DATA_TYPE?=?'tinyint'?THEN?'tinyint'?WHEN?DATA_TYPE?=?'decimal'?THEN?'double'?WHEN?DATA_TYPE?=?'datetime'?THEN?'string'?WHEN?DATA_TYPE?=?'timestamp'?THEN?'string'?WHEN?DATA_TYPE?=?'float'?THEN?'double'?WHEN?DATA_TYPE?=?'double'?THEN?'double'?WHEN?DATA_TYPE?=?'bigint'?THEN?'bigint'?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?=?'o_oms_statistic_profit' )?t3 GROUP?BY?TABLE_NAME,?TABLE_COMMENT )?t4;
相關(guān)推薦：
什么叫MySQL元數(shù)據(jù)？元數(shù)據(jù)的介紹及實例代碼
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
.png)
