Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说
oracle转hive脚本_string字符串转为数组,希望能够帮助你!!!。

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; /

今天的分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。