mysql字段映射set_mysql字段与hive字段映射关系

select

REPLACE(t.table_schema, '_', '') AS table_schema

,'rsc'

, t.table_name

, t2.table_comment

,t.column_name, case when t.column_comment is null or t.column_comment='' then t.column_name else t.column_comment end as column_comment

,case when t.DATA_TYPE='varchar' then CONCAT(t.DATA_TYPE,'(',t.CHARACTER_MAXIMUM_LENGTH,')')

when t.DATA_TYPE in ('varchar','char') then CONCAT(t.DATA_TYPE,'(',t.CHARACTER_MAXIMUM_LENGTH,')')

when t.DATA_TYPE in ('text','set','mediumtext','enum' )then 'string'

when t.DATA_TYPE in ('datetime' )then 'timestamp'

when t.DATA_TYPE in ('varbinary' )then 'binary'

when t.DATA_TYPE in ('float' )then 'decimal(20,4)'

when t.DATA_TYPE in ('double','decimal' )then concat('decimal','(',t.NUMERIC_PRECISION,',',case when t.NUMERIC_SCALE is null then 0 else t.NUMERIC_SCALE end,')')

when t.DATA_TYPE in ('mediumint' )then 'int'

when t.DATA_TYPE in ('tinyint' ) and t.COLUMN_TYPE like '% unsigned%' then 'smallint'

when t.DATA_TYPE in ('smallint' ) and t.COLUMN_TYPE like '% unsigned%' then 'int'

when LOWER(t.column_name) like '%id' AND t.DATA_TYPE in ('int') THEN 'bigint'

else t.DATA_TYPE end DATA_TYPE

from information_schema.`COLUMNS` t

LEFT JOIN information_schema.`TABLES` t2 on t.table_schema=t2.table_schema and t.table_name=t2.table_name

where

t.table_schema='ferry_cuishou_india'

order by t.table_schema,t.table_name,t.ordinal_position ;