Preface This article mainly introduces the relevant content about the MySQL metadata generation Hive table creation statement comment script, which is shared for your reference and learning. Let's take a look at the detailed introduction together: Recently, when extracting data from Mysql and other relational databases into Hive tables, it is necessary to synchronize the comments in the MySQL table. The following script can generate hive table field comment modification statements. Note: Other relational databases such as Oracle can be implemented using the same idea, by reading metadata and modifying the script syntax. use: Execute the following statement in the 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 to Hive tables, you need to synchronize the comments in the MySQL table. The following script can generate the 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; Summarize The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM. You may also be interested in:
|
<<: Summary of some problems encountered when integrating echarts with vue.js
1. Prerequisites We use the require.context metho...
Preface Since the most important data structure i...
Due to work requirements, I recently spent some t...
1. Introduction to Macvlan Before the emergence o...
This article shares the specific code of Vue to r...
Table of contents Difference between MVC and MVVM...
Introduction react-i18next is a powerful internat...
Today I had some free time to write a website for...
Understanding object.defineProperty to achieve re...
Preface: In Vue, props can be used to connect ori...
A hyperlink URL in Vm needs to be concatenated wit...
Table of contents 1. typeof 2. instanceof 3. Cons...
The command format for mysql login is: mysql -h [...
Table of contents 1. Problem 2. Solution 1. Decla...
Preface In order to ensure the consistency and in...