hive创建与mysql表结构一致的表

hive创建与mysql表结构一致的表

代码出自导师之手，特此记录，方便下次使用。

SET SESSION group_concat_max_len = 10240;

select
    concat_ws
    ('n'
     ,concat('create table ods.ods_{库名}_','{表名}','_dt')
     ,'('
     ,group_concat(
        concat_ws
        (' '
        ,case when t.ORDINAL_POSITION = 1 then ' ' else ',' end
        ,t.column_name
        ,t.hive_data_type
        ,'COMMENT'
        ,concat('''',t.COLUMN_COMMENT,'''')
        )
        order by t.ORDINAL_POSITION asc
        separator 'n'
      )
     ,concat(') comment ''',m2.table_comment,'''')
     ,'partitioned by (statdate string COMMENT ''数据日期（yyyyMMdd）'')'
     ,'stored as parquet'
     ,'TBLPROPERTIES (''parquet.compression''=''SNAPPY'')'
     )
     as hive_sql
    ,group_concat(t.column_name order by t.ORDINAL_POSITION asc separator ',') as sqoop_column_list
from
(
select
     t.table_schema
    ,t.table_name
    ,t.column_name
    ,CASE WHEN t.DATA_TYPE IN ('int','integer','smallint','tinyint','bit')
          THEN case when t.ORDINAL_POSITION = 1 and t.COLUMN_NAME like '%id' then 'bigint' else 'int' end
          WHEN t.DATA_TYPE IN ('bigint')
          THEN 'bigint'
          WHEN t.DATA_TYPE IN ('double','decimal')
          THEN concat('decimal(',IFNULL(t.NUMERIC_PRECISION,10),',',IFNULL(t.NUMERIC_SCALE,0),')')
          WHEN t.DATA_TYPE IN ('varchar','char')
          THEN 'string'
          WHEN t.DATA_TYPE IN ('timestamp','date','datetime','year')
          THEN 'string'
          ELSE '??????'
      END as hive_data_type
    ,t.DATA_TYPE as mysql_data_type
    ,t.COLUMN_COMMENT
    ,t.ORDINAL_POSITION
from information_schema.COLUMNS t
where t.table_schema='{库名}'
  and t.table_name='{表名}'
) t
join (
    select table_schema,table_name,table_comment from information_schema.TABLES t2
    where table_schema='{库名}'
      and table_name='{表名}'
) m2
on m2.table_schema = t.table_schema
and m2.table_name = t.table_name