hive 通过mysql 生成建表语句

自行修改字段

SELECT
    a.TABLE_NAME ,
    b.TABLE_COMMENT ,
    concat('DROP TABLE IF EXISTS ', 'ods_', a.TABLE_NAME, ';', char(13),
'CREATE EXTERNAL TABLE IF NOT EXISTS ', 'ods_', a.TABLE_NAME , ' (', char(13),
group_concat(
concat("`",a.COLUMN_NAME,"`"," ",
(case when a.data_type = 'bigint' then 'bigint'
when a.data_type = 'binary' then 'binary'
when a.data_type = 'char' then 'string'
when a.data_type = 'date' then 'string'
when a.data_type = 'datetime' then 'string'
when a.data_type = 'decimal' then concat('decimal', '(', a.NUMERIC_PRECISION, ',', a.NUMERIC_SCALE, ')')
when a.data_type = 'double' then 'double'
when a.data_type = 'enum' then 'string'
when a.data_type = 'float' then 'double'
when a.data_type = 'int' then 'int'
when a.data_type = 'json' then 'map<string,string>'
when a.data_type = 'longtext' then 'string'
when a.data_type = 'mediumtext' then 'string'
when a.data_type = 'smallint' then 'int'
when a.data_type = 'text' then 'string'
when a.data_type = 'time' then 'string'
when a.data_type = 'timestamp' then 'string'
when a.data_type = 'tinyint' then 'int'
when a.data_type = 'varbinary' then 'binary'
when a.data_type = 'varchar' then 'string'
else '未知类型'
end), " COMMENT '", COLUMN_COMMENT, "'" ), char(13)
order by a.TABLE_NAME, a.ORDINAL_POSITION) ,
") COMMENT '", b.TABLE_COMMENT , "'", char(13), "PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''  LOCATION '/warehouse/edu/ods/xxxxxxxxxx/';", char(13)) AS ods_ddl
FROM
    (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME,
        ORDINAL_POSITION,
        DATA_TYPE,
        NUMERIC_PRECISION,
        NUMERIC_SCALE,
        COLUMN_COMMENT
    FROM
        information_schema.COLUMNS
    WHERE
        TABLE_SCHEMA = 'edu22'
       -- AND TABLE_NAME = 'base_category_info'
        # 该条件限制单张张表的转换，可以注释掉，注释后为整个库的转换


) AS a
LEFT JOIN
information_schema.TABLES AS b
ON
    a.TABLE_NAME = b.TABLE_NAME
    AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
where
    b.TABLE_TYPE = 'BASE TABLE'
GROUP BY
    a.TABLE_NAME,
    b.TABLE_COMMENT


;