Shulou(Shulou.com)06/01 Report--

About MySQL metadata how to generate Hive creation table statement comment script? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Note: other relational databases such as oracle can read metadata and modify script syntax in the same way.

Use:

Execute the following statement in the 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 = 'oasis statistics statistics profit`) t

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

Execute the following statement in the 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 = 'oaths statistics statistics profit`) t3GROUP BY TABLE_NAME, TABLE_COMMENT) T4 Thank you for reading! After reading the above, do you have a general understanding of how MySQL metadata generates Hive table statement comment scripts? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.