在MySQL中执行脚本根据MySQL表生成hive建表语句

在MySQL中生成hive建表语句

1、保持与MySQL表结构一致；

2、在最后增加数据同步时间字段。

SELECT    CONCAT('create table ',@tbl_name,'(')UNION ALLSELECT    CONCAT(        COLUMN_NAME,        ' ',        CASE    WHEN DATA_TYPE in ('varchar','longtext','char','datetime','timestamp','varbinary','bit','mediumtext','set','longblob','text','blob','time','date') THEN        'string'    WHEN DATA_TYPE = 'decimal' THEN        COLUMN_TYPE   WHEN DATA_TYPE = 'float' THEN        'double'    ELSE         DATA_TYPE    END -- 数据类型转换    ,    ' comment ',    '\'',    CASE    WHEN COLUMN_COMMENT is NULL THEN        COLUMN_NAME    ELSE        replace(COLUMN_COMMENT,';',',')    END,    '\','    )FROM    information_schema. COLUMNS t1WHERE     t1.table_schema = @tbl_schema and t1.TABLE_NAME = @tbl_nameUNION ALLSELECT    concat(        'etl_update string COMMENT \'数据同步时间\') ',        'COMMENT \'',        COALESCE (t2.TABLE_COMMENT ,@tbl_name),        '\'-- PARTITIONED BY (DATE STRING COMMENT \'日期分区\') -- 分区表取消注释ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\' STORED AS TEXTFILE;'        )    FROM        information_schema. TABLES t2    WHERE        t2.table_schema = (@tbl_schema := 'XXX')    and t2.table_name = (@tbl_name := 'XXX')