oracle到hive数据类型转换「建议收藏」

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

oracle和hive中的数据类型存在差异，在oracle集成数据到hive中这样的场景下，我们希望在hive中的数据是贴源的，所以在hive中希望创建和oracle结构一致的表。

oracle到hive数据类型映射参考如下：

select case when t1.column_id=1 then 'CREATE TABLE IF NOT EXISTS '||'project'||'.'||t1.owner||'_'||t1.table_name||chr(10) end
||RPad(case when t1.column_id=1 then '(' else ',' end||t1.column_name,33,' ')
||RPad(case when t1.data_type in ('CHAR','VARCHAR2')     then 'VARCHAR('||t1.data_length||')' 
            when t1.data_type in ('TIMESTAMP(6)','DATE') then 'TIMESTAMP' 
			when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision =1 then 'TINYINT'
			when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision <4 then 'SMALLINT'
            when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision <9 then 'INT'
			when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision <19 then 'BIGINT'
            when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision >=19 then 'STRING'
            when t1.data_type ='NUMBER' and t1.data_scale>0 and t1.data_scale <=38 and t1.data_precision <=38 then 'DECIMAL('||t1.data_precision||','||t1.data_scale||')'
            when t1.data_type ='FLOAT'  then 'DOUBLE'
            else 'STRING' end,50,' ')
 ||' COMMENT '''||case when t4.column_name is not null then 'primary_key:' else '' end||replace(t2.comments,'''','')||''''
 ||case when max(t1.column_id) over() = t1.column_id then chr(10)||
 ',ETL_SJ STRING COMMENT ''ETL时间'')
 COMMENT '''||t3.comments||
 ''' 
PARTITIONED BY 
 (RFQ STRING COMMENT ''同步日期'');' end as col
  from sys.dba_tab_cols t1
 inner join sys.dba_col_comments t2 
    on t1.owner =t2.owner
   and t1.table_name = t2.table_name
   and t1.column_name = t2.column_name
 inner join sys.dba_tab_comments t3
    on t1.owner =t3.owner
   and t1.table_name = t3.table_name
  left join(
select t2.table_owner,t2.table_name,t2.column_name
  from (
select table_name,table_owner,index_name
,row_number() over(order by case when instr(index_name,'PK')>0 then instr(index_name,'PK') else 100 end asc) as cc
  from sys.dba_indexes
 where uniqueness  =upper('UNIQUE')
    and index_type ='NORMAL'
    and table_owner||'_'||table_name=upper('owner_table'))t1
  inner join sys.dba_ind_columns t2
    on t1.index_name =t2.index_name
   and t1.table_name =t2.table_name
   and t1.table_owner=t2.table_owner
 where t1.cc=1)t4
    on t1.owner      =t4.table_owner
   and t1.table_name =t4.table_name
   and t1.column_name=t4.column_name
 where t1.data_type not in ('LONG','NCLOB', 'CLOB','BLOB','BFILE','CFILE')
   and t1.owner||'_'||t1.table_name =upper('owner_table')
order by t1.column_id;

begin
    DBMS_OUTPUT.enable(BUFFER_SIZE => NULL);
  for i in (select owner||'_'||table_name tab from dba_tables where owner in （
'HX_ZS'
）) LOOP
   FOR J IN 
   (select case when t1.column_id=1 then 'CREATE TABLE IF NOT EXISTS '||'project'||'.'||t1.owner||'_'||t1.table_name||chr(10) end
||RPad(case when t1.column_id=1 then '(' else ',' end||case when t1.column_name='SJLYBZ' then 'SJLYBZ_1' else t1.column_name end,33,' ')
||RPad(case when t1.data_type in ('VARCHAR2')     then 'VARCHAR('||t1.data_length||')' 
      when t1.data_type in ('CHAR')     then 'CHAR('||t1.data_length||')'  
            when t1.data_type in ('TIMESTAMP(6)','DATE') then 'TIMESTAMP' 
      when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision =1 then 'TINYINT'
      when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision <4 then 'SMALLINT'
            when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision <9 then 'INT'
      when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision <19 then 'BIGINT'
            when t1.data_type ='NUMBER' and t1.data_scale=0 and t1.data_precision >=19 then 'DECIMAL('||t1.data_precision||','||t1.data_scale||')'
            when t1.data_type ='NUMBER' and t1.data_scale>0 and t1.data_scale <=38 and t1.data_precision <=38 then 'DECIMAL('||t1.data_precision||','||t1.data_scale||')'
            when t1.data_type ='FLOAT'  then 'DOUBLE'
            else 'STRING' end,50,' ')
 ||' COMMENT '''||case when t4.column_name is not null then 'primary_key:' else '' end||replace(t2.comments,'''','')||''''
 ||case when max(t1.column_id) over() = t1.column_id then chr(10)||
 ',YPTETL_SJ TIMESTAMP COMMENT  '||CHR(39)|| '云平台ETL时间'||CHR(39)||')
 COMMENT '''||t3.comments||
 ''' 
PARTITIONED BY 
  (rfq CHAR(8) COMMENT '||CHR(39)|| '日分区'||CHR(39)||',sjlybz VARCHAR(20)  COMMENT '||CHR(39)|| '数据来源标志'||CHR(39)||');' end as col
  from sys.dba_tab_cols t1
 inner join sys.dba_col_comments t2 
    on t1.owner =t2.owner
   and t1.table_name = t2.table_name
   and t1.column_name = t2.column_name
 inner join sys.dba_tab_comments t3
    on t1.owner =t3.owner
   and t1.table_name = t3.table_name
  left join(
select t2.table_owner,t2.table_name,t2.column_name
  from (
 select t6.owner table_owner, t6.table_name, t6.column_name
  from dba_constraints t5, dba_cons_columns t6
 where t5.table_name = t6.table_name
   and t5.owner = t6.owner
   and t5.constraint_name = t6.constraint_name
   and CONSTRAINT_TYPE IN ('P')
   and t5.OWNER ||'_'||t5.table_name =i.tab)t4
    on t1.owner      =t4.table_owner
   and t1.table_name =t4.table_name
   and t1.column_name=t4.column_name
 where t1.data_type not in ('LONG','NCLOB', 'CLOB','BLOB','BFILE','CFILE')
   and t1.owner||'_'||t1.table_name =i.tab
order by t1.column_id) loop
dbms_output.put_line(j.col);
end loop ;
end loop;
end;
/