查询oracle数据字典，并对应出hive的数据类型

SQL开始

select
t2.owner||'.'||t2.TABLE_NAME 源表名,
'dl_{0}_seq.'||'tt_{1}_'||lower(t2.table_name) hive表名,
nvl(t3.comments,'{2}') hive表注释,
lower(t2.COLUMN_NAME) 字段名,
t2.DATA_TYPE 源类型,
case
 when instr(t2.DATA_TYPE,'CHAR')>0 then 'string'
 when instr(t2.DATA_TYPE,'NUMBER')>0 and t2.DATA_PRECISION is null and t2.DATA_SCALE is null then 'decimal(38,5)'
 when instr(t2.DATA_TYPE,'INT')>0 then 'decimal(38,0)'
 when instr(t2.DATA_TYPE,'NUMBER')>0 then 'decimal('||nvl(t2.DATA_PRECISION,38)||','||t2.DATA_SCALE||')'
 when instr(t2.DATA_TYPE,'TIMESTAMP')>0 then 'timestamp'
 when instr(t2.DATA_TYPE,'DATE')>0 then 'timestamp'
 when instr(t2.DATA_TYPE,'FLOAT')>0 then ''
 when instr(t2.DATA_TYPE,'DOUBLE')>0 then ''
 when instr(t2.DATA_TYPE,'CLOB')>0 then 'string'
 when instr(t2.DATA_TYPE,'LONG')>0 then 'string'
 else ''
   end as hive类型,
t4.comments 字段注释,
t2.DATA_LENGTH,
t2.DATA_PRECISION,
t2.DATA_SCALE,
t2.COLUMN_ID
from all_users t1,
all_tab_columns t2,
all_tab_comments t3,
all_col_comments t4
where t1.username not in('SYS','SYSTEM','ACCESS_LOG')
and  t2.owner='{3}' and t2.table_name='{4}'
and t2.OWNER=t1.username
and t3.table_name=t2.TABLE_NAME and t3.owner=t2.OWNER
and t4.table_name=t2.TABLE_NAME and t4.owner=t2.owner and t4.column_name=t2.COLUMN_NAME
order by t1.username,t2.TABLE_NAME,t2.COLUMN_ID

 

SQL结束