查询mysql元数据转换成hive语句

SELECT
	c.table_name,
	c.TABLE_COMMENT,
	#拼接hive建表语句
	concat(
		'DROP TABLE IF EXISTS ',c.TABLE_NAME,';
		CREATE EXTERNAL TABLE IF NOT EXISTS ',c.TABLE_NAME,' (',
	  group_concat( concat( c.COLUMN_NAME, ' ', c.hive_type, " COMMENT '", c.COLUMN_COMMENT, "'" ) ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION),
	") COMMENT '",c.TABLE_COMMENT,
	"PARTITIONED BY (`dt` STRING)",
	"' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' 
	STORED AS orc 
	LOCATION ;" ) AS col_name 
FROM
	(SELECT 
		a.*,
		b.TABLE_COMMENT
		FROM 
		(SELECT
			TABLE_SCHEMA,
			TABLE_NAME,
			COLUMN_NAME,
			ORDINAL_POSITION,
			DATA_TYPE,
			CASE DATA_TYPE
			WHEN 'bigint' THEN
				'bigint'
			WHEN 'int' THEN
				'bigint'
			WHEN 'smallint' THEN
				'bigint'
			WHEN 'tinyint' THEN
				'bigint'
			WHEN 'decimal' THEN
				'decimal'
			WHEN 'double' THEN
				'DOUBLE'
			WHEN 'float' THEN
				'DOUBLE'
			WHEN 'char' THEN
				'STRING'
			WHEN 'varchar' THEN
				'STRING'
			WHEN 'mediumtext' THEN
				'STRING'
			WHEN 'text' THEN
				'STRING'
			WHEN 'datetime' THEN
				'STRING'
			WHEN 'time' THEN
				'STRING'
			WHEN 'timestamp' THEN
				'STRING'
			WHEN 'json' THEN
				'MAP<STRING,STRING>'
			ELSE
				'无'
			END 'HIVE_TYPE',
			COLUMN_COMMENT 
	FROM
		information_schema.COLUMNS 
	WHERE
		TABLE_SCHEMA = '库名' AND TABLE_NAME = '表名') AS a
	LEFT JOIN information_schema.TABLES AS b 
	ON a.TABLE_NAME=b.TABLE_NAME
	AND a.TABLE_SCHEMA=b.TABLE_SCHEMA) AS c 
GROUP BY
	c.table_name,
	c.TABLE_COMMENT

1、mysql类型转换成hive类型
2、group by根据库名和表名分组，group_concat拼接
如果需要查询一个数据库的多个表
去掉 “AND TABLE_NAME = ‘表名’”