How to generate Hive table creation statement comment script in MySQL metadata

How to generate Hive table creation statement comment script in MySQL metadata

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 MySQL's commonly used SQL statements for creating tables, adding fields, modifying fields, and adding indexes
  • Mysql table creation commonly used SQL statements personal experience sharing
  • Detailed summary of mysql sql statements to create tables
  • Summary of commonly used SQL statements for creating MySQL tables

<<:  Summary of some problems encountered when integrating echarts with vue.js

>>:  Detailed explanation of Nginx installation, SSL configuration and common commands under Centos7.x

Recommend

Solution to the problem of MySQL deleting and inserting data very slowly

When a company developer executes an insert state...

js canvas realizes circular water animation

This article example shares the specific code of ...

Semantic web pages XHTML semantic markup

Another important aspect of separating structure ...

Problems encountered in the execution order of AND and OR in SQL statements

question I encountered a problem when writing dat...

Detailed steps for building Portainer visual interface with Docker

In order to solve the problem mentioned last time...

Application of Hadoop counters and data cleaning

Data cleaning (ETL) Before running the core busin...

A brief analysis of the difference between ref and toRef in Vue3

1. ref is copied, the view will be updated If you...

Detailed explanation of mysql transaction management operations

This article describes the MySQL transaction mana...

mysql zip file installation tutorial

This article shares the specific method of instal...

How to achieve the maximum number of connections in mysql

Table of contents What is the reason for the sudd...

How to set up scheduled tasks in Linux and Windows

Table of contents Linux 1. Basic use of crontab 2...

HTML Tutorial: Collection of commonly used HTML tags (5)

Related articles: Beginners learn some HTML tags ...

How to replace all tags in html text

(?i) means do not match case. Replace all uppercas...

How to reset the root password of Mysql in Windows if you forget it

My machine environment: Windows 2008 R2 MySQL 5.6...

MySQL 8.0.20 Installation Tutorial with Pictures and Text (Windows 64-bit)

1: Download from mysql official website https://d...