Mysql自动生成Hive建表语句

1.在Mysql中创建dim_ddl_convert来存放MYSQL数据类型与HIVE数据类型的映射关系

CREATE TABLE
    dim_ddl_convert
    (
        source VARCHAR(100) NOT NULL,
        data_type1 VARCHAR(100) NOT NULL,
        target VARCHAR(100) NOT NULL,
        data_type2 VARCHAR(100),
        update_time VARCHAR(26),
        PRIMARY KEY (source, data_type1, target)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库表结构转换';

2.添加映射关系数据

INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'bigint', 'hive', 'BIGINT', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'binary', 'hive', 'BINARY', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'char', 'hive', 'STRING', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'datetime', 'hive', 'STRING', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'decimal', 'hive', 'DOUBLE', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'double', 'hive', 'DOUBLE', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'float', 'hive', 'DOUBLE', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'int', 'hive', 'BIGINT', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'json', 'hive', 'MAP', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'mediumtext', 'hive', 'STRING', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'smallint', 'hive', 'BIGINT', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'text', 'hive', 'STRING', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'time', 'hive', 'STRING', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'timestamp', 'hive', 'STRING', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'tinyint', 'hive', 'BIGINT', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'varbinary', 'hive', 'BINARY', '2019-07-31 00:00:00');
INSERT INTO dim_ddl_convert (source, data_type1, target, data_type2, update_time) VALUES ('mysql', 'varchar', 'hive', 'STRING', '2019-07-31 00:00:00');

3.打开SQL查询工具，执行以下转换查询语句

（注意修改下面sql语句中数据库名）

SET SESSION group_concat_max_len = 102400;
SELECT
    a.TABLE_NAME ,
    b.TABLE_COMMENT ,
    concat('use 数据库名;DROP TABLE IF EXISTS ',a.TABLE_NAME,';CREATE EXTERNAL TABLE IF NOT EXISTS ',a.TABLE_NAME ,' (',group_concat(concat(a.COLUMN_NAME,' ',
    c.data_type2," COMMENT '",COLUMN_COMMENT,"'") order by a.TABLE_NAME,a.ORDINAL_POSITION) ,
    ") COMMENT '",b.TABLE_COMMENT ,"' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS orc;") AS col_name
FROM
    (
        SELECt
            TABLE_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            ORDINAL_POSITION,
            DATA_TYPE,
            COLUMN_COMMENT
        FROM
            information_schema.COLUMNS
        WHERe
            TABLE_SCHEMA='数据库名'
        ) AS a
LEFT JOIN
    information_schema.TABLES AS b
ON
    a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_SCHEMA=b.TABLE_SCHEMA
#选择源为mysql，目标为hive
LEFT JOIN
    (
	    select
	    *
	    from dim_ddl_convert
	    where source='mysql' and target='hive'
    ) AS c
ON
    a.DATA_TYPE=c.data_type1
where b.TABLE_TYPE='base TABLE'
  and a.TABLE_NAME not like 'ods_%'
GROUP BY
    a.TABLE_NAME,
    b.TABLE_COMMENT
;

4.生成的建表语句示例如下：