How to generate a HIVE table statement note script detailed
Foreword
This article mainly introduces you to the relevant content of the Mysql metadata generation Hive table statement comment script, and share it for your reference and study.I won’t say much, 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 mysql For comments in the table, the following script can generate hive table field comment modification statements.
Note: Other relational databases, such as Oracle, can be achieved through the same idea of reading metadata and modifying script syntax.
Use:
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_COMMENT FROM COLUMNS WHERE TABLE_NAME ='o_oms_statistic_profit' ) t;
When extracting data from Mysql and other relational databases to Hive tables, you need to synchronize the comments 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 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;
Summary
The above is the entire content of this article.I hope that the content of this article will be helpful to your study or work.If you have any questions, you can leave a message and communicate.Thank you for your support.
purchase lipitor online cheap & lt;a href="https://lipiws.top/"& gt;purchase lipitor sale& lt;/a& gt; buy atorvastatin 20mg for sale
Tinrgw
2024-03-07