About how Mysql metadata generates Hive table creation statement annotation scripts

Recently, when extracting data from Mysql and other relational databases into a Hive table, it is necessary to synchronize the comments in the MySQL table. The following script can generate hive table field comment modification statements. It mainly introduces to you the relevant information about how Mysql metadata generates Hive table creation statement annotation scripts. The article introduces it in great detail through sample code. It has certain reference learning value for everyone's study or work. Friends who need it can follow the instructions below. Let’s learn together.

Note: Other relational databases such as oracle can be implemented through the same idea, reading metadata and modifying the script syntax.

Use:

Execute the following statement in mysql metadata database: information_schema

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;

When extracting data from other relational databases such as Mysql into a Hive table, the comments in the mysql table need to be synchronized. The following script can generate a hive create table statement. Only the main field information of the hive table is generated, and other information needs to be added manually.

Execute the following statement in the mysql metadata database: information_schema

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;

Related recommendations:

What is MySQL metadata? Introduction to metadata and example code

Installation and simple testing of Hive based on MySQL metadata

[MySQL] Steps to obtain metadata

The above is the detailed content of About how Mysql metadata generates Hive table creation statement annotation scripts. For more information, please follow other related articles on the PHP Chinese website!