基于Oracle系统表将Oracle表生成出相应的HIVE内部表&外部表

一、前提

1.能够访问oracle系统表的用户操作
  user_tab_columns
  user_col_comments
  user_tab_comments
2.并在脚本下面添加四个字段，可以根据情况自行调整
inserttime、updatetime、batchno、tano、srcsys
3.内部表默认是parquet格式，snappy压缩模式，可以根据情况自行调整
4.外部表默认是textfile格式，前缀路径/tmp/hive/ ，可以根据情况自行调整

二、SQL实现代码

2.1 内部表

2.1.1 入参说明

OWNER ：创建的Hive表的schema
table_name：Oracle库中要转换的表名

2.1.2 脚本

--内部表
WITH TT AS
 (SELECT NULL AS TABLE_NAME,
         'INSERTTIME' AS COLUMN_NAME,
         ' TIMESTAMP' AS DATA_TYPE,
         1001 AS COLUMN_ID,
         '插入时间' AS COL_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'UPDATETIME' AS COLUMN_NAME,
         ' TIMESTAMP' AS DATA_TYPE,
         1002 AS COLUMN_ID,
         '更新时间' AS COL_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'BATCHNO' AS COLUMN_NAME,
         ' DECIMAL(15,0)' AS DATA_TYPE,
         1003 AS COLUMN_ID,
         '批次号' AS COL_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'TANO' AS COLUMN_NAME,
         ' STRING' AS DATA_TYPE,
         1003 AS COLUMN_ID,
         'TANO' AS COL_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'SRCSYS' AS COLUMN_NAME,
         ' STRING' AS DATA_TYPE,
         1003 AS COLUMN_ID,
         '系统号' AS COL_COMMENTS
    FROM DUAL)
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(),
              ',') || ')' || 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 USER_TAB_COLUMNS COL
          JOIN USER_COL_COMMENTS CMS
            ON COL.TABLE_NAME = CMS.TABLE_NAME
           AND COL.COLUMN_NAME = CMS.COLUMN_NAME
        --AND col.OWNER = cms.OWNER
          JOIN USER_TAB_COMMENTS TMS
            ON COL.TABLE_NAME = TMS.TABLE_NAME --AND  col.OWNER = tms.OWNER 
         WHERE COL.TABLE_NAME = UPPER('&tab_name')
           AND REGEXP_SUBSTR(TMS.TABLE_NAME, '[0-9]+') IS NULL /*and col.OWNER ='NEW_AZ_DCDW'*/
        UNION ALL
        SELECT TB.TABLE_NAME,
               COLUMN_NAME,
               DATA_TYPE || ' COMMENT ''' || '' || TT.COL_COMMENTS || '''',
               COLUMN_ID,
               TMS.COMMENTS
          FROM TT
          JOIN USER_TABLES TB
            ON TB.TABLE_NAME = UPPER('&tab_name')
           AND REGEXP_SUBSTR(TB.TABLE_NAME, '[0-9]+') IS NULL
          JOIN USER_TAB_COMMENTS TMS
            ON TB.TABLE_NAME = TMS.TABLE_NAME
         ORDER BY COLUMN_ID)
 GROUP BY TABLE_NAME
 ORDER BY TABLE_NAME;

2.1.3 输出结果

2.2 外部表

2.2.1 入参说明

OWNER ：创建的Hive表的schema
table_name：Oracle库中要转换的表名

2.2.2 脚本

--外部表
WITH TT AS
 (SELECT NULL AS TABLE_NAME,
         'inserttime' AS COLUMN_NAME,
         ' timestamp' AS DATA_TYPE,
         1001 AS COLUMN_ID,
         NULL AS TAB_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'updatetime' AS COLUMN_NAME,
         ' timestamp' AS DATA_TYPE,
         1002 AS COLUMN_ID,
         NULL AS TAB_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'batchno' AS COLUMN_NAME,
         ' decimal(15,0)' AS DATA_TYPE,
         1003 AS COLUMN_ID,
         NULL AS TAB_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'tano' AS COLUMN_NAME,
         ' string' AS DATA_TYPE,
         1003 AS COLUMN_ID,
         NULL AS TAB_COMMENTS
    FROM DUAL
  UNION
  SELECT NULL AS TABLE_NAME,
         'srcsys' AS COLUMN_NAME,
         ' string' AS DATA_TYPE,
         1003 AS COLUMN_ID,
         NULL AS TAB_COMMENTS
    FROM DUAL)
SELECT 'CREATE EXTERNAL TABLE IF NOT EXISTS &OWNER.' || TABLE_NAME ||
        '_EXT' || CHR(13) || '(' || RTRIM(XMLAGG(XMLPARSE(CONTENT(COLUMN_NAME || CHR(9) || DATA_TYPE || CHR(10) || ',') WELLFORMED) ORDER BY COLUMN_ID)
                                          .GETCLOBVAL(),
                                          ',') || ')' || CHR(10) ||
       --   'ROW FORMAT DELIMITED FIELDS TERMINATED BY ''|''' || chr(10) ||
        'STORED AS TEXTFILE' || CHR(10) ||
        'location ''/tmp/hive/&owner/' || LOWER(TABLE_NAME) ||
        '_ext'';' AS SQ,
       TABLE_NAME
  FROM (SELECT COL.TABLE_NAME,
               COL.COLUMN_NAME,
               'STRING' || ' COMMENT ' || '''' || CMS.COMMENTS || '''' ||
               CHR(13) AS DATA_TYPE,
               COL.COLUMN_ID,
               TMS.COMMENTS AS TAB_COMMENTS
          FROM USER_TAB_COLUMNS COL
          JOIN USER_COL_COMMENTS CMS
            ON COL.TABLE_NAME = CMS.TABLE_NAME
           AND COL.COLUMN_NAME = CMS.COLUMN_NAME
          JOIN USER_TAB_COMMENTS TMS
            ON COL.TABLE_NAME = TMS.TABLE_NAME
         WHERE COL.TABLE_NAME = UPPER('&tab_name')
        UNION
        SELECT TB.TABLE_NAME,
               COLUMN_NAME,
               DATA_TYPE,
               COLUMN_ID,
               TAB_COMMENTS
          FROM TT
          JOIN USER_TABLES TB
            ON TB.TABLE_NAME = UPPER('&tab_name')
         ORDER BY COLUMN_ID
        )
 GROUP BY TABLE_NAME, TAB_COMMENTS
 ORDER BY TABLE_NAME;

2.2.3 输出结果