转发：mysql2hive 表结构转换工具

转发自：https://blog.csdn.net/qq_36039236/article/details/118890193

-- 从mysql转odps存在bug，修复为：
SET SESSION group_concat_max_len = 102400;
SELECT
    a.TABLE_NAME ,
    b.TABLE_COMMENT ,
    concat('DROP TABLE IF EXISTS ',a.TABLE_NAME,';CREATE 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 ,"' ;") AS col_name
FROM
    (
        SELECT
            TABLE_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            ORDINAL_POSITION,
            DATA_TYPE,
            REGEXP_REPLACE(COLUMN_COMMENT, '[\'\\r\\n]', '') COLUMN_COMMENT
        FROM
            information_schema.COLUMNS
        WHERE
            TABLE_SCHEMA='ods'
            -- 测试表
              and TABLE_NAME ='order'
           
        ) 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.dim_ddl_convert
	    where source='mysql' and target='odps'
    ) AS c
ON
    a.DATA_TYPE=c.data_type1
where b.TABLE_TYPE='BASE TABLE'
-- 过滤指定表
  and a.TABLE_NAME not like 'dim_%'
GROUP BY
    a.TABLE_NAME,
    b.TABLE_COMMENT
;