一、前提
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) || '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
JOIN USER_TAB_COMMENTS TMS
ON COL.TABLE_NAME = TMS.TABLE_NAME
WHERE COL.TABLE_NAME = UPPER('&tab_name')
AND REGEXP_SUBSTR(TMS.TABLE_NAME, '[0-9]+') IS NULL
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) ||
'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 输出结果