oracle表转换hive,oracle脚本一键转hive脚本

注意的地方：拼接的sql可能超出了要截取的长度，字符串varchar2最多存储4000

select to_char(substr(table_prefix || col_strs || table_subfix, 1, 4000)) con

from (select n.table_prefix,

wm_concat(m.col_str) as col_strs,

n.table_subfix

from (select a.table_name,

'drop table if exists ' || lower(a.table_name) ||

';create table ' || lower(a.table_name) || '(' as table_prefix,

') comment ''' || b.comments ||

''' ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\t'' ESCAPED BY ''\\'' STORED AS TEXTFILE;' as table_subfix

from user_tables a, user_tab_comments b

where a.table_name = b.table_name

--and a.table_name = 'MID'

order by a.table_name) n,

(select c.TABLE_NAME,

c.column_name || ' ' ||

decode(c.data_type,

'VARCHAR2',

'varchar' || '(' || c.DATA_LENGTH || ')',

'NUMBER',

'int',

'DATE',

'timestamp',

'CHAR',

'CHAR' || '(' || c.DATA_LENGTH || ')',

'CLOB',

'string',

c.data_type) as col_str

from user_tab_cols c) m

where n.table_name = m.table_name

group by n.table_prefix, n.table_subfix)

union all

select 'alter table ' || c.TABLE_NAME || ' change ' || c.column_name || ' ' ||

c.column_name || ' ' || decode(c.data_type,

'VARCHAR2',

'varchar' || '(' || c.DATA_LENGTH || ')',

'NUMBER',

'int',

'DATE',

'timestamp',

'CHAR',

'CHAR' || '(' || c.DATA_LENGTH || ')',

'CLOB',

'string',

c.data_type) || ' comment ''' ||

d.comments || ''';' as col_str

from user_tab_cols c

left join user_col_comments d

on c.TABLE_NAME = d.table_name

and c.COLUMN_NAME = d.column_name

下面的过程要注意plsql dev的缓冲问题

declare

v_table_str        varchar2(4000);

v_col_str          varchar2(4000);

v_col_comments_str varchar2(4000);

v_type_str         varchar2(50) := '';

begin

for v_temp in (select a.table_name, b.comments

from user_tables a, user_tab_comments b

where a.table_name = b.table_name

--and a.table_name = 'MID'

order by a.table_name) loop

v_table_str := 'create table ' || lower(v_temp.table_name) || '(';

for v_col in (select c.COLUMN_NAME,

c.DATA_TYPE,

c.DATA_LENGTH,

d.comments

from user_tab_cols c

WHERE c.TABLE_NAME = v_temp.table_name) loop

if v_col.data_type = 'VARCHAR2' then

v_type_str := 'VARCHAR(' || v_col.data_length || ')';

elsif v_col.data_type = 'NUMBER' then

v_type_str := 'int';

elsif v_col.data_type = 'DATE' then

v_type_str := 'timestamp';

elsif v_col.data_type = 'CHAR' then

v_type_str := 'CHAR(' || v_col.data_length || ')';

elsif v_col.data_type = 'CLOB' then

v_type_str := 'STRING';

else

v_type_str := v_col.data_type;

end if;

v_col_str := v_col_str || v_col.column_name || ' ' || v_col.data_type || ',';

end loop;

v_col_str   := substr(v_col_str, 0, length(v_col_str) - 1);

v_table_str := v_table_str || v_col_str || ') comment ''' ||

v_temp.comments ||

''' ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\t'' ESCAPED BY ''\\'' STORED AS TEXTFILE;';

dbms_output.put_line(v_table_str); --注意字符串长度过长

for v_col_comments in (select 'alter table ' || c.TABLE_NAME ||

' change ' || c.column_name || ' ' ||

c.column_name || ' ' ||

decode(c.data_type,

'VARCHAR2',

'varchar' || '(' || c.DATA_LENGTH || ')',

'NUMBER',

'int',

'DATE',

'timestamp',

'CHAR',

'CHAR' || '(' || c.DATA_LENGTH || ')',

'CLOB',

'string',

c.data_type) || ' comment ''' ||

d.comments || ''';' as col_comments

from user_tab_cols c

left join user_col_comments d

on c.TABLE_NAME = d.table_name

and c.COLUMN_NAME = d.column_name

WHERE c.TABLE_NAME = v_temp.table_name) loop

dbms_output.put_line(v_col_comments.col_comments);

end loop;

end loop;

end;

begin

for v_tmp in (select a.table_name,

'drop table if exists ' || lower(a.table_name) ||

';create table ' || lower(a.table_name) || '(' as table_prefix,

') comment ''' || b.comments ||

''' ROW FORMAT DELIMITED FIELDS TERMINATED BY ''~'' ESCAPED BY ''\\'' STORED AS TEXTFILE;' as table_subfix

from user_tables a, user_tab_comments b

where a.table_name = b.table_name) loop

insert into test_ww

(name)

select v_tmp.table_prefix || wm_concat(col_str) || v_tmp.table_subfix as col_strs

from (select c.TABLE_NAME,

c.column_name || ' ' ||

decode(c.data_type,

'VARCHAR2',

'varchar' || '(' || c.DATA_LENGTH || ')',

'NUMBER',

'int',

'DATE',

'timestamp',

'CHAR',

'CHAR' || '(' || c.DATA_LENGTH || ')',

'CLOB',

'string',

c.data_type) as col_str

from user_tab_cols c

where c.TABLE_NAME = v_tmp.table_name

order by c.COLUMN_ID);

commit;

end loop;

end; 总结：第三种能原封不动的将表结构转换(最重要的是字段的先后顺序)