Detailed description of how Mysql metadata generates Hive table creation statement annotation scripts, and metadata hive

Detailed description of how Mysql metadata generates Hive table creation statement annotation scripts, and metadata hive

Preface

This article describes how to generate a script for commenting on Hive table creation statements generated by Mysql metadata for your reference. I will not talk about it here. Let's take a look at the detailed introduction:

Recently, when extracting data from other relational databases such as Mysql to Hive tables, you need to synchronize comments in the mysql table. The following script can generate a hive table field comment modification statement.

Note:Other relational databases such as oracle can read metadata and modify the script syntax using the same idea.

Usage:

Execute the following statement in mysql metabase: 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_COMMENTFROM COLUMNSWHERE TABLE_NAME = 'o_oms_statistic_profit') t;

When extracting data from other relational databases such as Mysql to Hive tables, you need to synchronize annotations in the mysql table. The following script can generate hive table creation statements. Only the main field information of the hive table is generated, and other information needs to be manually added.

Execute the following statement in mysql metabase: 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_infoFROM (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_COMMENTFROM COLUMNS t1 JOIN TABLES t2 ON t1.TABLE_NAME = t2.TABLE_NAMEWHERE t1.TABLE_NAME = 'o_oms_statistic_profit') t3GROUP BY TABLE_NAME, TABLE_COMMENT) t4;

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.