數據治理中Oracle SQL和存儲過程的數據血緣分析

數據治理中的一個重要基礎工作是分析組織中數據的血緣關係。有了完整的數據血緣關係，我們可以用它進行數據溯源、表和欄位變更的影響分析、數據合規性的證明、數據品質的檢查等。

分析數據血緣的方法主要分為四類

• 自動解析
• 系統跟蹤
• 機器學習
• 人工收集

自動解析主要是利用工具解析 SQL 語句、存儲過程和 ETL等文件。 本文以 Oracle 為例，來說明如何分析 SQL 和存儲過程中的數據血緣。

產生數據血緣的 SQL 語句

• SELECT
• INSERT
• UPDATE
• MERGE
• CREATE VIEW
• CREATE TABLE
• 存儲過程

SELECT

可能你會感到奇怪， SELECT 語句沒有對數據進行增、改操作，如何會產生數據血緣？ 秘密就在於 SELECT 語句中的 select list 部分，在這裡，可以對數據進行轉換。 以下面這個 SELECT 語句為例：

select  sal + commission as totalSal
from emp;

我們可以看到，totalSal 欄位的數據來自 emp.sal 和 emp.commission，在這裡，數據進行了一次轉換。 這種在 SELECT 內部產生的數據血緣是臨時性的，但是這個 SELECT 語句和 CREATE VIEW 或者 CREATE TABLE 一結合，這個數據血緣就真正落地形成了。例如：

create view v_sal(mySal)
as
select  sal + commission as totalSal
from emp;

這個 CREATE VIEW 語句通過 SELECT 形成了 從 emp.sal 和 emp.commission 到 v_sal.mySal 的數據血緣。

因此我們可以知道，分析好 SELECT 語句是對 SQL 語句進行數據血緣分析的基礎。總體來說，通過分析 SQL 語句來獲得數據血緣是比較直觀和簡單的，但問題的關鍵是人工分析的效率太低，對於企業內眾多的 SQL 來說，人工分析基本是不可能的。

存儲過程

存儲過程可以包含比較複雜的邏輯處理，例如條件判斷、循環分支等。因此常用來完成數據抽取、轉換、載入、清洗等任務。 這其中，就產生了大量的數據血緣關係。為了更好的對企業內數據進行治理，整理存儲過程中的數據血緣工作是必不可少的。

分析存儲過程中的數據血緣，游標 cursor 是一個關鍵因素，數據流一般都是圍繞著游標進行處理。

在下面這個 Oracle PL/SQL 的存儲過程中，首先定義了游標 CURSOR cur_stclerk。

DECLARE
	CURSOR cur_stclerk IS
		SELECT employee_id,
		department_id,
		first_name,
		last_name
		FROM employees
		WHERE job_id = 'ST_CLERK';

然後通過這個游標，用 LOOP 進行循環， 把數據從 employees 表中插入到表 emp_temp 和 emp_detls_temp。

INSERT INTO emp_temp
(employee_id,
department_id,
job_id)
VALUES (z_empid,
z_depid,
'ST_CLERK');

INSERT INTO emp_detls_temp
(employee_id,
empname)
VALUES (z_empid,
z_firstname
|| ' '
|| z_lastname);
END LOOP;

CLOSE cur_stclerk;
COMMIT;
END;

因此我們可以建立從 employees 表到表 emp_temp 和 emp_detls_temp 的數據血緣。 

完整的 PLSQL 存儲過程。

DECLARE
	z_empid employees.employee_id%TYPE;
	z_depid employees.department_id%TYPE;
	z_firstname employees.first_name%TYPE;
	z_lastname employees.last_name%TYPE;

	CURSOR cur_stclerk IS
		SELECT employee_id,
		department_id,
		first_name,
		last_name
		FROM employees
		WHERE job_id = 'ST_CLERK';
BEGIN
OPEN cur_stclerk;
LOOP
	FETCH cur_stclerk INTO z_empid,z_depid,z_firstname,
	z_lastname;
	EXIT WHEN cur_stclerk%NOTFOUND;

	INSERT INTO emp_temp
	(employee_id,
	department_id,
	job_id)
	VALUES (z_empid,
	z_depid,
	'ST_CLERK');

	INSERT INTO emp_detls_temp
	(employee_id,
	empname)
	VALUES (z_empid,
	z_firstname
	|| ' '
	|| z_lastname);
END LOOP;

CLOSE cur_stclerk;
COMMIT;
END;

自動化數據血緣分析工具

SQLFlow 支援分析多達 20 多種主流資料庫的 SQL 語句。 支援的資料庫有 bigquery, couchbase, dax, db2, greenplum, hana, hive, impala, informix, mdx, mysql, netezza, odbc, openedge, oracle, postgresql, redshift, snowflake, sparksql, sqlserver, sybase, teradata, vertica。

支援分析存儲過程和動態 SQL 語句。

通過 UI 分析 SQL 語句

通過 SQLFlow 的 UI 可以快速的獲取一個 SQL 的數據血緣情況， 並可以得到可視化的結果，幫助用戶迅速了解一個 SQL 中的數據血緣。

通過 API 方式分析 SQL 的數據血緣

有時，我們需要把分析所得的數據血緣作為元數據存儲到我們自己的數據治理平台中，和其它元數據進行整合， 這時，我們可以利用 SQLFlow 提供的 Restful API， 利用 shell, python 等腳本對數據血緣分析工作進行自動化。

這裡用 curl 展示如何用 API 訪問 SQLFlow 進行數據血緣分析。

• 準備好用 API 連接 SQLFlow 需要的 userid 和 secret code
• 利用 userid 和 secret code 來獲取 token。注意，用你自己的 userid 和 secret code 來替換掉命令中的對應部分。

  curl -X POST "//api.gudusoft.com/gspLive_backend/user/generateToken" -H  "Request-Origion:testClientDemo" -H  "accept:application/json;charset=utf-8" -H  "Content-Type:application/x-www-form-urlencoded;charset=UTF-8" -d "secretKey=YOUR SECRET KEY" -d "userId=YOUR USER ID HERE"
  

• 把需要分析的 SQL 語句遞交給 SQLFlow 進行分析，並返回處理結果。注意，用你自己的 userid 和上面命令中返回的 token 來替換掉命令中的對應部分。

  curl -X POST "//api.gudusoft.com/gspLive_backend/sqlflow/generation/sqlflow?showRelationType=fdd" -H  "Request-Origion:testClientDemo" -H  "accept:application/json;charset=utf-8" -H  "Content-Type:multipart/form-data" -F "sqlfile=" -F "dbvendor=dbvoracle" -F "ignoreRecordSet=true" -F "simpleOutput=false" -F "sqltext=create view v_sal(mySal) as select  sal + commission as totalSal from emp;" -F "userId=YOUR USER ID HERE"  -F "token=YOUR TOKEN HERE"
  

• 返回的 json 包含了如下數據血緣關係。

  "relations": [
    {
        "id": "3",
        "type": "fdd",
        "effectType": "create_view",
        "target": {
            "id": "11",
            "column": "mySal",
            "parentId": "9",
            "parentName": "v_sal",
        },
        "sources": [
            {
                "id": "3",
                "column": "sal",
                "parentId": "2",
                "parentName": "emp",
            },
            {
                "id": "4",
                "column": "commission",
                "parentId": "2",
                "parentName": "emp",
            }
        ],
        "processId": "10"
    }
  ]

更詳細的資訊見官網 SQLFlow Restful API。

小結

本文介紹了如何通過分析 Oracle 的 SQL 語句和存儲過程來獲取組織中的數據血緣，從而更好的進行數據治理。 並介紹了如何利用 SQLFlow 工具把分析 SQL 語句中的數據血緣工作自動化， 提高數據自理效率和水平。