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

2022-11-08 14:39:41 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

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

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

代码语言：javascript复制
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;
代码语言：javascript复制
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;
/

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/191058.html原文链接：https://javaforall.cn

hive oracle https 网络安全

0 人点赞