Oracle自动生成根据源表迁移到HIVE创表语句_rtrim(xmlagg(xmlparse(content(

SQL脚本

============================================
自动生成表脚本文件
============================================
select 'CREATE TABLE IF NOT EXISTS &OWNER.' || table_name || chr(13) || '(' ||
       RTRIM(XMLAGG(XMLPARSE(CONTENT(column_name || chr(9) || DATA_TYPE || chr(10) || ',') WELLFORMED) order by column_id)
             .GETCLOBVAL(),
             ',') || ')' || 'COMMENT ' || '"' || tab_comments || '"' ||
       chr(10) || 'PARTITIONED BY (col type)' || chr(10) ||
       'CLUSTERED BY (col) SORTED BY(col) INTO <num> BUCKETS' || chr(10) ||
       'STORED AS parquet' || chr(10) ||
       'tblproperties(''parquet.compression''=''snappy'');' as sq,
       table_name
  from (select col.table_name,
               col.COLUMN_NAME,
               decode(col.DATA_TYPE,
                      'NUMBER',
                      'DECIMAL',
                      'TIMESTAMP(6)',
                      'TIMESTAMP') || CASE
                 WHEN DATA_TYPE = 'NUMBER' THEN
                  '(' || nvl(to_char(DATA_PRECISION), 22) || ',' ||
                  to_char(NVL(COL.data_scale, 0)) || ')'
                 WHEN LOWER(col.COLUMN_NAME) in ('inserttime', 'updatetime') then
                  ' TIMESTAMP'
                 when data_type IN ('DATE', 'TIMESTAMP(6)') THEN
                  ' TIMESTAMP'
                 ELSE
                  ' STRING'
               END || ' COMMENT ' || '''' || cms.COMMENTS || '''' || CHR(13) AS DATA_TYPE,
               col.COLUMN_ID,
               tms.COMMENTS as tab_comments
          from all_tab_columns col
          join all_col_comments cms
            on col.TABLE_NAME = cms.table_name
           and col.COLUMN_NAME = cms.COLUMN_NAME
           AND col.OWNER = cms.OWNER
          join all_tab_comments tms
            on col.TABLE_NAME = tms.TABLE_NAME
           AND col.OWNER = tms.OWNER
         where COL.TABLE_NAME like upper('%&tab_name%')
           AND regexp_substr(tms.table_name, '[0-9]+') is null
           and col.OWNER = '&src_owner')
 group by table_name, tab_comments
 ORDER BY table_name;

测试过程

执行脚本SQL-F8

填写参数 

OWNER：表示迁移到的数据库名称
 
tab_name：要迁移的表名
 
src_owner：表示表原来所在的数据库名称

查看结果 

 测试结果

=========================================================
创建表语句
=========================================================
 
CREATE TABLE IF NOT EXISTS DEFAULT.SSA_TP_TDIVIDENDDETAIL_HIS
(D_CDATE     TIMESTAMP COMMENT ''
,C_CSERIALNO     STRING COMMENT ''
,D_REGDATE     TIMESTAMP COMMENT ''
,D_DATE     TIMESTAMP COMMENT ''
,C_FUNDACCO     STRING COMMENT ''
,C_TRADEACCO     STRING COMMENT ''
,C_FUNDCODE     STRING COMMENT ''
,C_SHARETYPE     STRING COMMENT ''
,C_AGENCYNO     STRING COMMENT ''
,C_NETNO     STRING COMMENT ''
,F_TOTALSHARE    DECIMAL(16,2) COMMENT ''
,F_UNITPROFIT    DECIMAL(7,4) COMMENT ''
,F_TOTALPROFIT    DECIMAL(16,2) COMMENT ''
,F_TAX    DECIMAL(16,2) COMMENT ''
,C_FLAG     STRING COMMENT ''
,F_REALBALANCE    DECIMAL(16,2) COMMENT ''
,F_REINVESTBALANCE    DECIMAL(16,2) COMMENT ''
,F_REALSHARES    DECIMAL(16,2) COMMENT ''
,F_FARE    DECIMAL(16,2) COMMENT ''
,D_LASTDATE     TIMESTAMP COMMENT ''
,F_NETVALUE    DECIMAL(7,4) COMMENT ''
,F_FROZENBALANCE    DECIMAL(16,2) COMMENT ''
,F_FROZENSHARES    DECIMAL(16,2) COMMENT ''
,F_INCOMETAX    DECIMAL(9,4) COMMENT ''
,C_RESERVE     STRING COMMENT ''
,D_REQUESTDATE     TIMESTAMP COMMENT ''
,C_SHARECLASS     STRING COMMENT ''
,C_SENDTYPE     STRING COMMENT ''
)COMMENT ""
PARTITIONED BY (col type)
CLUSTERED BY (col) SORTED BY(col) INTO <num> BUCKETS
STORED AS parquet
tblproperties('parquet.compression'='snappy');

根据自己的情况自行选择是分区还是分桶，按照哪些字段。
 
注意分区的话，要将分区字段注释。