大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
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;
/
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/191058.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...