mysql数据表映射 mysql到hive数据映射规则

按照以下脚本执行，导出txt文件即可得到相应的hive字段映射。

select concat('`', column_name, '`') colname,
       case
         when NUMERIC_PRECISION is not null and
              (data_type = 'decimal' or data_type = 'numeric') then
          concat('decimal(', NUMERIC_PRECISION, ',', NUMERIC_SCALE, ')')
         when (CHARACTER_MAXIMUM_LENGTH is not null or
              data_type = 'uniqueidentifier') and
              data_type not like '%text%' then
          'string'
         when data_type = 'datetime' then
          'timestamp'
         when data_type = 'money' then
          'decimal(9,2)'
         when data_type = 'tinyint' then
          'int'
         else
          data_type
       end as data_type,
       concat('comment', ' \'',COLUMN_COMMENT,' '') as comment,
       ' ,' as splitCharacter
  from information_schema.columns
 where table_name = 'xxxxxxxxxx'