oracle表转换hive,oracle12c中 表结构转换成hive表结构的脚本

原文出自：

https://www.cnblogs.com/lq147760524/p/9168223.html

oracle11g后 推荐使用listagg函数

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,

LISTAGG(m.col_str,','  )  within group(order by n.table_prefix) as col_strs,

n.table_subfix

FROM

(

SELECT

a.table_name,

'create table if not exists ' || LOWER(a.table_name) || '(' AS table_prefix,

') comment ''' || b.comments || ''';' AS table_subfix

FROM

user_tables a,

user_tab_comments b

WHERE

a.table_name = b.table_name

ORDER BY

a.table_name) n,

(

SELECT

c.TABLE_NAME,

c.column_name ||

CASE

WHEN c.DATA_TYPE IN ('CHAR',

'NCHAR',

'VARCHAR',

'VARCHAR2',

'NVARCHAR2',

'DATE',

'TIMESTAMP',

'TIMESTAMP WITH TIME ZONE',

'TIMESTAMP WITH LOCAL TIME ZONE',

'INTERVAL YEAR TO MOTH',

'INTERVAL DAY TO SECOND',

'BLOB',

'CLOB',

'NCLOB',

'BFILE',

'RAW',

'LONG RAW') THEN ' STRING '

WHEN C.DATA_TYPE = 'INTEGER' THEN ' BIGINT '

WHEN C.DATA_TYPE = 'NUMBER' THEN (

CASE WHEN C.DATA_SCALE IS NOT NULL

AND c.DATA_SCALE <> 0 THEN ' DECIMAL(' || C.DATA_PRECISION || ',' || C.DATA_SCALE || ') '

WHEN C.DATA_PRECISION < 3 THEN ' TINYINT '

WHEN C.DATA_PRECISION < 5 THEN ' SMALLINT '

WHEN C.DATA_PRECISION < 10 THEN ' INT '

ELSE ' BIGINT '

END)

WHEN C.DATA_TYPE IN ('BINARY_FLOAT',

'BINARY_DOUBLE',

'FLOAT') THEN ' DOUBLE '

ELSE ' STRING '

END || 'comment ''' || REGEXP_REPLACE(T.comments, '[' || CHR(10) || CHR(13) || CHR(9) || CHR(32) || ']', '') || '''' AS col_str

FROM

user_tab_cols c,

user_col_comments t

WHERE

c.TABLE_NAME = t.table_name

AND c.COLUMN_NAME = t.column_name) m

WHERE

n.table_name = m.table_name

GROUP BY

n.table_prefix,

n.table_subfix);

注意：但当数据量比较大时，一般clob字段超过4000，却报ORA-01489:字符串连接的结果过长。有兴趣的小伙伴可以研究下解决办法。