oracle 数据库表结构同步到 hive 数据库

现在业务场景是：比如HR 系统 Oracle 数据库系统有 300 张表，需要同步到 Hive 。手工调整有一定工作量。

HR 系统：

Hive 结果：

解决处理过程：

1 配置表新增HIVE 需要增加的字段 DW01.HIVE_TABLE_COLUMN_EXTEND_CONFIG.sql （初始化配置表和结果表）

2 DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE.sql （初始化函数）

CREATE OR REPLACE FUNCTION DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE(
IN_DATA_TYPE VARCHAR2
,IN_DATA_PRECISION NUMBER
,IN_DATA_SCALE NUMBER )
RETURN VARCHAR2
-------------------------------------------------------------------------------
-- (C) Copyright and <date>
-- File name: DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE.sql
-- Function name: DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE
-- Purpose: ORACLE数据类型映射HIVE数据类型
--=============================================================================
-- Creation Date: 2022.01.01
-- Origin Author:
-- Add:
-- ERROR CODE: -20090
-- Version: %1.0%
-- Date ByPerson 　　　　　　　　　　　　　　　　　　Description
-- ---------- --------------------- 　-----------------------------------------------
-- 2022-07-28 　　　Create SP File
-- 2022-08-01 Refinement logic For Number DataType
----------------------------------------------------------------------------------------
AS

V_DATA_TYPE VARCHAR2(200); -- 数据类型
V_DATA_PRECISION NUMBER; -- 有效位数
V_DATA_SCALE NUMBER; -- 小数位数

V_DATA_TYPE_RESULT VARCHAR2(200); -- 返回结果

BEGIN

V_DATA_TYPE := UPPER(IN_DATA_TYPE);
V_DATA_PRECISION := IN_DATA_PRECISION;
V_DATA_SCALE := IN_DATA_SCALE;

IF( ( V_DATA_TYPE IN ('NUMBER') AND ( V_DATA_PRECISION IS NULL AND V_DATA_SCALE IS NULL) )
OR V_DATA_TYPE IN ('FLOAT','BINARY_FLOAT','BINARY_DOUBLE') ) THEN
V_DATA_TYPE_RESULT := 'DOUBLE';

ELSIF ( V_DATA_TYPE IN ('NUMBER') AND ( V_DATA_PRECISION >0 AND V_DATA_SCALE>0) ) THEN
V_DATA_TYPE_RESULT :='DECIMAL('||V_DATA_PRECISION ||','|| V_DATA_SCALE||')';

ELSIF ( V_DATA_TYPE IN ('NUMBER') AND ( V_DATA_PRECISION IS NULL OR V_DATA_PRECISION >=19 ) AND V_DATA_SCALE=0 ) THEN
V_DATA_TYPE_RESULT := 'DECIMAL(38,0)';

ELSIF ( V_DATA_TYPE IN ('NUMBER') AND V_DATA_PRECISION =1 AND V_DATA_SCALE =0 ) THEN
V_DATA_TYPE_RESULT := 'TINYINT'; -- 1 byte有符号(即最高位为"符号位"，下同)整数(取值范围为：-128~127)

ELSIF ( V_DATA_TYPE IN ('NUMBER') AND V_DATA_PRECISION >=2 AND V_DATA_PRECISION <=4 AND V_DATA_SCALE=0 ) THEN
V_DATA_TYPE_RESULT := 'SMALLINT'; -- 2 byte有符号整数(取值范围为: -32768~32767)

ELSIF ( V_DATA_TYPE IN ('NUMBER') AND V_DATA_PRECISION >=5 AND V_DATA_PRECISION <=9 AND V_DATA_SCALE=0 ) THEN
V_DATA_TYPE_RESULT := 'INT'; -- 4 byte有符号整数(取值范围为: -2147483648~2147483647)

ELSIF ( V_DATA_TYPE IN ('NUMBER') AND V_DATA_PRECISION >=10 AND V_DATA_PRECISION <=18 AND V_DATA_SCALE=0 ) THEN
V_DATA_TYPE_RESULT := 'BIGINT'; -- 8 byte有符号整数(取值范围为: -9223372036854775808~9223372036854775807)

ELSIF ( V_DATA_TYPE IN ('DATE') OR INSTR(V_DATA_TYPE,'TIMESTAMP')>0 ) THEN
V_DATA_TYPE_RESULT := 'TIMESTAMP';

ELSIF ( V_DATA_TYPE IN ('VARCHAR2','CHAR','NCHAR','NVARCHAR2','BLOB','NCLOB','CLOB','ROWID','RAW','LONG RAW','LONG')
OR INSTR(V_DATA_TYPE,'INTERVAL')>0 ) THEN
V_DATA_TYPE_RESULT := 'STRING';
ELSE
RAISE_APPLICATION_ERROR(-20090,'调用 DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE 出错(类型不匹配，类型可能需要新增)');
END IF;

RETURN V_DATA_TYPE_RESULT;

EXCEPTION
--异常处理语句
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20091,'调用 DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE 出错(捕获函数异常)');
END;

3 DW01.PROC_TRANSFER_ORACLE_TO_HIVE_TABLE.sql （初始化SP）

CREATE OR REPLACE PROCEDURE DW01.PROC_TRANSFER_ORACLE_TO_HIVE_TABLE(
IN_TARGET_OWNER IN VARCHAR2,
OUT_RESPONSE_CODE OUT INTEGER
)
-------------------------------------------------------------------------------
-- (C) Copyright <date>
--
-- File name: DW01.PROC_TRANSFER_ORACLE_TO_HIVE_TABLE.sql
-- Procedure name: DW01.PROC_TRANSFER_ORACLE_TO_HIVE_TABLE
-- Source Table:
-- DW01.HIVE_TABLE_COLUMN_EXTEND_CONFIG HIVE字段配置表
-- Target Table:
--
-- Project: EDW
-- Note: Delete and Insert and Update
-- Purpose: 根据 DW01下面的表迁移生成HIVE结果表
-- Comment : 初始化：创建
--=============================================================================
-- Creation Date: 2022.07.27
-- Origin Author:
-- Add:
-- ERROR CODE: -20070
-- Version: %1.0%
--
-- Modification History
-- --------------------
-- Run Condition：
-- Date ByPerson 　　　　　　　　　　　　　　　　　　Description
-- ---------- --------------------- 　-----------------------------------------------
-- 2022-07-27 　　　Create SP File
----------------------------------------------------------------------------------------
AS

C_SOURCE_SYSTEM CONSTANT VARCHAR2(100) := 'DW01'; -- 源系统SCHEMAL
C_STORED CONSTANT VARCHAR2(100) := 'ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\001'' STORED AS ORC ;'; -- 存储格式
C_SQL_DELETE CONSTANT VARCHAR2(1000):= ' DELETE FROM DW01.EXP_HIVE_TABLE_COLUMN_EXTEND_CONFIG_RESULT WHERE UPPER(TAB_NAME) = UPPER(:1) ';
C_SQL_INSERT CONSTANT VARCHAR2(1000):= ' INSERT INTO DW01.EXP_HIVE_TABLE_COLUMN_EXTEND_CONFIG_RESULT (TAB_NAME,HIVE_DDL) VALUES(:1,:2) ';

V_TABLE_NAME VARCHAR2(1000); -- 表名
V_TABLE_COMMENTS VARCHAR2(1000); -- 表名注释
V_CREATE_ROWNUM VARCHAR2(5); -- CREATE 首行标志

V_TABLE_TARGET VARCHAR2(100); -- 目标表

V_TMP_CLOB CLOB; -- 瞬时态 CLOB
V_DEST_CLOB CLOB; -- 持久化 CLOB

IN_SMY_ACT_DT VARCHAR2(10);
IN_SMY_PROC_NM VARCHAR2(500) := 'DW01.PROC_TRANSFER_ORACLE_TO_HIVE_TABLE';
IN_SMY_STEP_NUM INTEGER := 0;
IN_SMY_STEP_DESC VARCHAR2(4000);
IN_SMY_SQL_CODE INTEGER := 0;
IN_SMY_RCOUNT INTEGER;

-- 所有表
CURSOR C1_TABLE IS
SELECT DISTINCT OBJECT_NAME
FROM SYS.ALL_OBJECTS
WHERE UPPER(OBJECT_TYPE) = 'TABLE'
AND UPPER(OWNER) = C_SOURCE_SYSTEM
AND UPPER(OBJECT_NAME)
NOT IN (
'HIVE_TABLE_COLUMN_EXTEND_CONFIG'
,'EXP_HIVE_TABLE_COLUMN_EXTEND_CONFIG_RESULT'
,'ETL_AUTO_SPLIT_PARTITION_CFG'
,'ETL_AUTO_SPLIT_PARTITION_HIVE_DATA'
,'EXP_HIVE_PARTITION_TABLE_LIST_RESULT'
);

BEGIN

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') INTO IN_SMY_ACT_DT FROM DUAL; -- 0 初始化

FOR C1 IN C1_TABLE LOOP

V_CREATE_ROWNUM := 0;
V_DEST_CLOB := ''; -- 段初始化
V_TMP_CLOB := ''; --临时变量初始化
V_TABLE_NAME := C1.OBJECT_NAME;
V_TABLE_TARGET := IN_TARGET_OWNER||'.'||V_TABLE_NAME; -- 目标表

--1.1 DROP TABLE
IN_SMY_STEP_NUM := IN_SMY_STEP_NUM + 1; -- 1 Log
IN_SMY_STEP_DESC := 'DROP TABLE '||V_TABLE_NAME ; -- 2 Log

V_TMP_CLOB := TO_CLOB( 'DROP TABLE '||V_TABLE_TARGET||' ; ' || CHR(10) );
V_DEST_CLOB := V_DEST_CLOB || V_TMP_CLOB;

IN_SMY_SQL_CODE := SQLCODE; -- 3 Log
IN_SMY_RCOUNT := SQL%ROWCOUNT; -- 4 Log
DW01.ORACLE_PROC_LOG(IN_SMY_ACT_DT,IN_SMY_PROC_NM,IN_SMY_STEP_NUM,IN_SMY_STEP_DESC,IN_SMY_SQL_CODE,IN_SMY_RCOUNT); -- 5 Log

IN_SMY_STEP_NUM := IN_SMY_STEP_NUM + 1; -- 1 Log
IN_SMY_STEP_DESC := 'CREATE TABLE '||V_TABLE_NAME ; -- 2 Log

--1.2 CREATE TABLE
V_TMP_CLOB := TO_CLOB( 'CREATE TABLE IF NOT EXISTS '||V_TABLE_TARGET||'(' || CHR(10) );
V_DEST_CLOB := V_DEST_CLOB || V_TMP_CLOB;

IN_SMY_SQL_CODE := SQLCODE; -- 3 Log
IN_SMY_RCOUNT := SQL%ROWCOUNT; -- 4 Log
DW01.ORACLE_PROC_LOG(IN_SMY_ACT_DT,IN_SMY_PROC_NM,IN_SMY_STEP_NUM,IN_SMY_STEP_DESC,IN_SMY_SQL_CODE,IN_SMY_RCOUNT); -- 5 Log

IN_SMY_STEP_NUM := IN_SMY_STEP_NUM + 1; -- 1 Log
IN_SMY_STEP_DESC := 'CREATE TABLE '||V_TABLE_NAME ||' COLUMN ' ; -- 2 Log

--1.3 CREATE COLUMN
FOR C2 IN (
SELECT T2.COLUMN_NAME,T2.DATA_TYPE,T2.COMMENTS
FROM (
SELECT
UPPER(T1.COLUMN_NAME) COLUMN_NAME
,DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE(T1.DATA_TYPE,T1.DATA_PRECISION,T1.DATA_SCALE) DATA_TYPE
,T2.COMMENTS COMMENTS
,T1.COLUMN_ID COLUMN_ID
FROM SYS.ALL_TAB_COLUMNS T1 LEFT JOIN SYS.ALL_COL_COMMENTS T2
ON T1.OWNER=T2.OWNER
AND T1.TABLE_NAME=T2.TABLE_NAME
AND T1.COLUMN_NAME=T2.COLUMN_NAME
WHERE T1.OWNER = C_SOURCE_SYSTEM
AND T1.TABLE_NAME = V_TABLE_NAME
UNION ALL
SELECT
COL_NAME COLUMN_NAME
,DW01.FUN_TRANSFER_ORACLE_TYPE_TO_HIVE_TYPE(COL_TYPE,COL_PRECISION,COL_SCALE) DATA_TYPE
,COL_COMMENT COMMENTS
,10000+COL_ORDER COLUMN_ID
FROM DW01.HIVE_TABLE_COLUMN_EXTEND_CONFIG
WHERE STATUS = 1 -- 1启用
AND IS_DELETE = 0 -- 0否
AND OWNER_TYPE = 0 -- 0 全量(所有表都增加)
AND OWNER = IN_TARGET_OWNER
) T2 ORDER BY T2.COLUMN_ID ASC
)
LOOP

V_CREATE_ROWNUM := V_CREATE_ROWNUM+1;
IF V_CREATE_ROWNUM = 1 THEN
V_TMP_CLOB := TO_CLOB( ' '||C2.COLUMN_NAME||' '||C2.DATA_TYPE||' COMMENT '''||C2.COMMENTS||'''' || CHR(10) );
ELSE
V_TMP_CLOB := TO_CLOB( ','||C2.COLUMN_NAME||' '||C2.DATA_TYPE||' COMMENT '''||C2.COMMENTS||'''' || CHR(10) );
END IF;
V_DEST_CLOB := V_DEST_CLOB || V_TMP_CLOB;

END LOOP;

IN_SMY_SQL_CODE := SQLCODE; -- 3 Log
IN_SMY_RCOUNT := SQL%ROWCOUNT; -- 4 Log
DW01.ORACLE_PROC_LOG(IN_SMY_ACT_DT,IN_SMY_PROC_NM,IN_SMY_STEP_NUM,IN_SMY_STEP_DESC,IN_SMY_SQL_CODE,IN_SMY_RCOUNT); -- 5 Log

IN_SMY_STEP_NUM := IN_SMY_STEP_NUM + 1; -- 1 Log
IN_SMY_STEP_DESC := 'CREATE TABLE '||V_TABLE_NAME ||' COMMENTS ' ; -- 2 Log

--1.4 表注释
SELECT COMMENTS INTO V_TABLE_COMMENTS FROM SYS.ALL_TAB_COMMENTS WHERE OWNER=C_SOURCE_SYSTEM AND TABLE_NAME=V_TABLE_NAME;

V_TMP_CLOB := TO_CLOB( ') COMMENT '''||V_TABLE_COMMENTS||'''' || CHR(10) );
V_DEST_CLOB := V_DEST_CLOB || V_TMP_CLOB;

IN_SMY_SQL_CODE := SQLCODE; -- 3 Log
IN_SMY_RCOUNT := SQL%ROWCOUNT; -- 4 Log
DW01.ORACLE_PROC_LOG(IN_SMY_ACT_DT,IN_SMY_PROC_NM,IN_SMY_STEP_NUM,IN_SMY_STEP_DESC,IN_SMY_SQL_CODE,IN_SMY_RCOUNT); -- 5 Log

--1.5 表存储格式
V_TMP_CLOB := TO_CLOB( C_STORED || CHR(10) );
V_DEST_CLOB := V_DEST_CLOB || V_TMP_CLOB;

--1.6 保存
EXECUTE IMMEDIATE C_SQL_DELETE USING V_TABLE_TARGET ;
EXECUTE IMMEDIATE C_SQL_INSERT USING V_TABLE_TARGET,V_DEST_CLOB;

END LOOP;

COMMIT;

EXCEPTION
--异常处理语句
WHEN OTHERS THEN
ROLLBACK;

IN_SMY_STEP_DESC := IN_SMY_STEP_DESC||':'||SQLERRM; -- 2 Log
IN_SMY_SQL_CODE := SQLCODE; -- 3 Log
IN_SMY_RCOUNT := SQL%ROWCOUNT; -- 4 Log
DW01.ORACLE_PROC_LOG(IN_SMY_ACT_DT,IN_SMY_PROC_NM,IN_SMY_STEP_NUM,IN_SMY_STEP_DESC,IN_SMY_SQL_CODE,IN_SMY_RCOUNT); -- 5 Log

OUT_RESPONSE_CODE := -20070;
RAISE_APPLICATION_ERROR(-20070,'调用 DW01.PROC_FROM_DB_CFG_TO_HIVE_DYNAMIC_PARTITION_TABLE 出错');

END;

4 调用SP

DECLARE OUT_RESPONSE_CODE VARCHAR2(20);
BEGIN
DW01.PROC_TRANSFER_ORACLE_TO_HIVE_TABLE('DW02', OUT_RESPONSE_CODE);
DBMS_OUTPUT.put_line(OUT_RESPONSE_CODE);
END;

注释：这里主要说一下 Oracle 18 字段类型映射到 Hive 2 字段类型

posted @ 2022-09-16 10:59 163博客阅读(667) 评论(0) 收藏举报

刷新页面返回顶部