041.mysql-查询mysql元数据来格式化datax同步脚本，查询语句、拼接的json语句datax-mysql到hive

 

 

--自动生成json数据
select
     TABLE_NAME ,
     concat (',',COLUMN_NAME) COLUMN_NAME  ,
     case when
     t.data_TYPE in('varchar','text') then 'string'
     when
     t.data_TYPE = 'decimal' then  t.COLUMN_TYPE 
     when
     t.data_TYPE = 'datetime' then 'timestamp'
     when
     t.data_TYPE = 'float' then 'double'
     else t.data_TYPE end data_TYPE
     ,
     concat('comment ''',COLUMN_COMMENT,'''') COLUMN_COMMENT ,
     concat ('{"name":"',COLUMN_NAME,'","type":"' ,
     case when
     t.data_TYPE in('varchar','text')  then 'string'
     when
     t.data_TYPE in('decimal', 'float') then 'double'
     when
     t.data_TYPE in('datetime') then 'timestamp'
     when
     t.data_TYPE in('tinyint','smallint') then 'int'
     else t.data_TYPE end ,'"},') data_TYPE  
from
    information_schema.columns  t
where
    TABLE_NAME = 'mk_event_popup_record'
    and TABLE_SCHEMA = 'cloud_cube_marketing'
;


-- 自动生成select语句
    select
         concat ('select ',group_concat(
         case when  DATA_TYPE ='datetime' 
              then  concat('date_format(',COLUMN_NAME,',''%Y-%m-%d %H:%i:%s'') ',COLUMN_NAME )
         else COLUMN_NAME end        
         order by ORDINAL_POSITION),' from ',table_name)  
    from
        information_schema.columns  t
    where
    TABLE_NAME = 'mk_event_popup_record'
    and TABLE_SCHEMA = 'cloud_cube_marketing'