Oracle 移轉的資料移轉、ETL 和載入

發行項
06/15/2023

本文是七部分系列文章的第二部分，並提供從 Oracle 移轉到 Azure Synapse Analytics 的指引。本文重點為 ETL 和載入移轉的最佳做法。

資料移轉考量

將資料、ETL 和載入從舊版 Oracle 資料倉儲和資料超市移轉至 Azure Synapse 時，需要考慮許多因素。

從 Oracle 移轉資料的初始決策

當您在規劃移轉出現有的 Oracle 環境時，請考慮下列資料相關的問題：

是否應該移轉未使用的資料表結構？
可將使用者的風險和影響降到最低的最佳移轉方法為何？
移轉資料超市時：維持實體或轉成虛擬？

下一節將在移轉出 Oracle 的情境下討論這些要點。

要移轉未使用的資料表嗎？

只移轉使用中的資料表是合理的做法。沒有使用的資料表可以進行封存，而不是移轉，如此一來未來需要時才能使用資料。最好使用系統中繼資料和記錄檔，而不是文件來判斷使用中的資料表為何，因為文件可能已過期。

Oracle 系統目錄資料表和記錄會包含可用於判斷指定資料表上次存取時間的資訊，此資訊可進而用來決定資料表是否為移轉的候選項目。

如果您已授權 Oracle 診斷套件，則您可以存取使用中的工作階段歷程記錄，利用其判斷上次存取資料表的時間。

提示

在舊版系統中，資料表在一段時間後變得多餘並不少見，大部分情況下這些並不需要進行移轉。

以下是在指定時間範圍內尋找特定資料表使用量的範例查詢：

SELECT du.username,
    s.sql_text,
    MAX(ash.sample_time) AS last_access ,
    sp.object_owner ,
    sp.object_name ,
    sp.object_alias as aliased_as ,
    sp.object_type ,
    COUNT(*) AS access_count 
FROM v$active_session_history ash         
    JOIN v$sql s ON ash.force_matching_signature = s.force_matching_signature
    LEFT JOIN v$sql_plan sp ON s.sql_id = sp.sql_id
    JOIN DBA_USERS du ON ash.user_id = du.USER_ID
WHERE ash.session_type = 'FOREGROUND'
    AND ash.SQL_ID IS NOT NULL
    AND sp.object_name IS NOT NULL
    AND ash.user_id <> 0
GROUP BY du.username,
    s.sql_text,
    sp.object_owner,
    sp.object_name,
    sp.object_alias,
    sp.object_type 
ORDER BY 3 DESC;

若您已開始執行數個查詢，此查詢可能需要一段時間才能執行。

可將使用者的風險和影響降到最低的最佳移轉方法為何？

因為公司可能會想要降低變更對資料倉儲資料模型的影響來改善靈活度，所以經常出現這樣的疑問。公司通常會在 ETL 移轉期間看到將其資料進一步現代化或轉型的機會。這種方法的風險較高，因為此方法會同時變更多個因素，因此難以比較舊系統與新系統的成果。在此進行資料模型變更也可能會影響其他系統的上游或下游 ETL 作業。基於該風險，最好在資料倉儲移轉之後重新設計此級別。

即使整體移轉過程會刻意變更資料模型，良好的做法仍是將現有的模型依原樣移轉至 Azure Synapse，而不是在新平台上重新設計。這種方法可將對現有生產系統的影響降到最低，同時可在進行一次性的重新設計工作時，得益於 Azure 平台的效能和彈性延展性。

提示

即使您未來計畫變更資料模型，一開始也請先依原樣移轉現有的模型。

資料超市移轉：維持實體或轉成虛擬？

在舊版 Oracle 資料倉儲環境中，通常會建立許多資料超市，其建構方式可針對組織內的指定部門或商務功能，提供良好的特定自助查詢和報表效能。資料超市通常由資料倉儲的子集所組成，其中包含匯總的資料版本，其形式可讓使用者輕鬆查詢該資料，並享受快速的回應時間。使用者可使用使用者友善的查詢工具，例如 Microsoft Power BI，此工具可支援商務使用者與資料超市進行互動。資料超市中的資料形式通常是維度資料模型。資料超市的其中一種用法是以可用形式公開資料，即使基礎倉儲資料模型有些不同 (例如資料保存庫)。

您可以針對組織內的個別商務單位使用不同的資料超市，以實作穩固的資料安全性制度。限制存取與使用者相關的特定資料超市，並將敏感性資料消除、模糊化或匿名。

若將這些資料超市實作為實體資料表，則需要額外的儲存體資源並進行額外處理，才能夠定期建立及重新整理這些資料。此外，超市中的資料只會維持在上次重新整理作業時的最新狀態，因此可能不適合高度變動的資料儀表板。

提示

虛擬化資料超市可以節省儲存體和處理資源。

隨著成本較低的可調整 MPP 架構問世，例如 Azure Synapse 及其固有的效能特性，您可以提供資料超市功能，而不需要將超市具現化為一組實體資料表。其中一種方法是透過主要資料倉儲的 SQL 檢視將資料超市有效虛擬化。另一種方式是使用 Azure 或協力廠商虛擬化產品中的檢視等功能，透過虛擬化層來虛擬化資料超市。此方法可簡化或消除對額外儲存體和彙總處理的需求，並減少要移轉的資料庫物件總數。

此方法還有另一個潛在優點。藉由在虛擬化層內實作彙總和聯結邏輯，以及透過虛擬化檢視呈現外部報告工具，便可將建立這些檢視所需的處理作業向下推送至資料倉儲。資料倉儲通常是在大型資料磁碟區上執行聯結、匯總和其他相關作業的最佳位置。

選擇實作虛擬資料超市而非實體資料超市的主要因素包括：

更敏捷：虛擬資料超市比實體資料表和相關聯的 ETL 流程更容易變更。
擁有權總成本較低：虛擬化實作所需的資料存放區和資料複本較少。
消除用於移轉的 ETL 作業，並簡化虛擬環境中的資料倉儲架構。
效能：雖然實體資料超市過去的效能較好，但現在的虛擬化產品可實作智慧型快取技術來降低效能差異。

提示

Azure Synapse 的效能和可擴縮性可啟用虛擬化，而不需要犧牲效能。

從 Oracle 移轉資料

了解您的資料

您應詳細了解要移轉的資料量，此為移轉規劃的一部分，因為資料量可能會影響移轉方法的決策。使用系統中繼資料來判斷要移轉資料表內原始資料所佔用的實體空間。這裡的原始資料表示資料表內資料列所使用的空間量，不包括索引和壓縮等額外負荷。最大的事實資料表通常會包含超過 95% 的資料。

此查詢會提供 Oracle 的總計資料庫大小：

SELECT
  ( SELECT SUM(bytes)/1024/1024/1024 data_size 
    FROM sys.dba_data_files ) +
  ( SELECT NVL(sum(bytes),0)/1024/1024/1024 temp_size 
    FROM sys.dba_temp_files ) +
  ( SELECT SUM(bytes)/1024/1024/1024 redo_size 
    FROM sys.v_$log ) +
  ( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size 
    FROM v$controlfile ) "Size in GB"
FROM dual

資料庫大小等於 (data files + temp files + online/offline redo log files + control files) 的大小。整體資料庫大小包括已使用的空間和可用空間。

下列範例查詢會提供資料表資料和索引所使用的磁碟空間明細：

SELECT
   owner, "Type", table_name "Name", TRUNC(sum(bytes)/1024/1024) Meg
FROM
  ( SELECT segment_name table_name, owner, bytes, 'Table' as "Type" 
    FROM dba_segments 
    WHERE segment_type in  ('TABLE','TABLE PARTITION','TABLE SUBPARTITION' )
UNION ALL
    SELECT i.table_name, i.owner, s.bytes, 'Index' as "Type"
    FROM dba_indexes i, dba_segments s
    WHERE s.segment_name = i.index_name
    AND   s.owner = i.owner
    AND   s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
UNION ALL
    SELECT l.table_name, l.owner, s.bytes, 'LOB' as "Type"
    FROM dba_lobs l, dba_segments s
    WHERE s.segment_name = l.segment_name
    AND   s.owner = l.owner
    AND   s.segment_type IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
UNION ALL
    SELECT l.table_name, l.owner, s.bytes, 'LOB Index' as "Type"
    FROM dba_lobs l, dba_segments s
    WHERE s.segment_name = l.index_name
    AND   s.owner = l.owner
    AND   s.segment_type = 'LOBINDEX')
    WHERE owner in UPPER('&owner')
GROUP BY table_name, owner, "Type"
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc;

此外，Microsoft 資料庫移轉小組提供許多資源，包括 Oracle 詳細目錄指令碼成品。 Oracle 詳細目錄指令碼成品工具包含可存取 Oracle 系統資料表的 PL/SQL 查詢，並依結構描述類型、物件類型和狀態來提供物件計數。此工具也提供每個結構描述中原始資料的粗略估計，以及每個結構描述中的資料表大小，並以 CSV 格式儲存這些結果。工具中包含的計算機試算表會採用 CSV 來輸入，並提供調整大小的資料。

針對所有資料表，您都可以將代表性的資料樣本 (例如一百萬個資料列) 擷取至未壓縮的分隔一般 ASCII 資料檔案，以精確估計需要移轉的資料量。然後，您可以使用該檔案的大小來取得各資料列的平均原始資料大小。最後，您可以將該平均大小乘以完整資料表中資料列的總數，以提供資料表的原始資料大小。您可在規劃中使用該原始資料大小。

使用 SQL 查詢來尋找資料類型

藉由查詢 Oracle 靜態資料字典 DBA_TAB_COLUMNS 檢視，您可以判斷結構描述中使用的資料類型，以及是否需要變更任何資料類型。您可以使用 SQL 查詢來尋找 Oracle 結構描述中的資料行，且此結構描述的資料類型未直接對應至 Azure Synapse。同樣地，您可以使用查詢來計算每個未直接對應至 Azure Synapse 的 Oracle 資料類型發生次數。將這些查詢結果和資料類型比較資料表一起使用，您便可以判斷哪些資料類型需要在 Azure Synapse 環境中進行變更。

若要尋找資料類型未對應至 Azure Synapse 中資料類型的資料行，請在以結構描述的相關擁有者取代 <owner_name> 之後執行下列查詢：

SELECT owner, table_name, column_name, data_type
FROM dba_tab_columns
WHERE owner in ('<owner_name>')
AND data_type NOT IN 
    ('BINARY_DOUBLE', 'BINARY_FLOAT', 'CHAR', 'DATE', 'DECIMAL', 'FLOAT', 'LONG', 'LONG RAW', 'NCHAR', 'NUMERIC', 'NUMBER', 'NVARCHAR2', 'SMALLINT', 'RAW', 'REAL', 'VARCHAR2', 'XML_TYPE') 
ORDER BY 1,2,3;

若要計算不可對應的資料類型數量，請使用下列查詢：

SELECT data_type, count(*) 
FROM dba_tab_columns 
WHERE data_type NOT IN 
    ('BINARY_DOUBLE', 'BINARY_FLOAT', 'CHAR', 'DATE', 'DECIMAL', 'FLOAT', 'LONG', 'LONG RAW', 'NCHAR', 'NUMERIC', 'NUMBER', 'NVARCHAR2', 'SMALLINT', 'RAW', 'REAL', 'VARCHAR2', 'XML_TYPE') 
GROUP BY data_type 
ORDER BY data_type;

Microsoft 提供適用於 Oracle 的 SQL Server 移轉小幫手 (SSMA)，以自動移轉舊版 Oracle 環境的資料倉儲，包括資料類型的對應。您也可以使用 Azure 資料庫移轉服務來協助規劃和執行 Oracle 等環境的移轉。協力廠商也會提供工具和服務來將移轉自動化。若您已在 Oracle 環境中使用協力廠商 ETL 工具，便可使用此工具來實作所有必要的資料轉換。下一節將探索現有 ETL 流程的移轉。

ETL 移轉考量

Oracle ETL 移轉的初始決策

針對 ETL/ELT 處理，舊版 Oracle 資料倉儲通常會使用自訂建置指令碼、協力廠商 ETL 工具，或隨著時間演進的方法組合。規劃移轉至 Azure Synapse 時，您必須判斷在新環境中實作所需 ETL/ELT 處理的最佳方式，同時將成本和風險降至最低。

提示

事先規劃 ETL 移轉的方法，並適時運用 Azure 設施。

下列流程圖會摘要說明一種方法：

Flowchart of migration options and recommendations.

如流程圖所示，初始步驟一律是建置需要移轉的 ETL/ELT 流程詳細目錄。使用標準內建 Azure 功能時，某些現有的流程可能不需要移動。基於規劃目的，了解移轉規模十分重要。接下來，請考量流程圖決策樹中的問題：

是否要移至原生 Azure？ 您的答案取決於您是否要移轉至完全的 Azure 原生環境。若是，我們建議您使用 Azure Data Factory 或 Azure Synapse 管線中的管線和活動來重新設計 ETL 處理。
是否正在使用協力廠商 ETL 工具？ 若您並沒有要移至完全的 Azure 原生環境，請檢查現有的協力廠商 ETL 工具是否已在使用中。在 Oracle 環境中，您可能會發現部分或所有 ETL 處理是由使用 Oracle 特定公用程式的自訂指令碼來執行，例如 Oracle SQL Developer、Oracle SQL*Loader 或 Oracle Data Pump。此情況的應對方法是使用 Azure Data Factory 重新設計。
協力廠商是否支援 Azure Synapse 內的專用 SQL 集區？ 請考量您是否大量投資協力廠商 ETL 工具中的技能，或者現有的工作流程和排程是否使用此工具。若是，請判斷工具是否可以有效率地支援 Azure Synapse 作為目標環境。在理想情況下，此工具會包含原生連接器，這些連接器可以利用 PolyBase 或 COPY INTO 等 Azure 設施以最有效率的方式載入資料。但即使沒有原生連接器，您通常還是可以呼叫外部流程，例如 PolyBase 或 COPY INTO，並傳入適用的參數。在此情況下，請使用現有的技能和工作流程，並將 Azure Synapse 作為新的目標環境。

若您正在使用 Oracle 資料整合器 (ODI) 進行 ELT 處理，則需要適用於 Azure Synapse 的 ODI 知識模組。若您無法在組織中使用這些模組，但您有 ODI，則可以使用 ODI 來產生一般檔案。接著您可將這些一般檔案移至 Azure，並內嵌至 Azure Data Lake Storage 以載入 Azure Synapse 中。
是否要在 Azure 中執行 ETL 工具？ 如果您決定保留現有的協力廠商 ETL 工具，您可以在 Azure 環境 (而不是在現有的內部部署 ETL 伺服器) 中執行該工具，並讓 Data Factory 處理現有工作流程的整體協調流程。因此，請決定要讓現有的工具維持原狀執行，或將其移至 Azure 環境，以達到成本、效能和可擴縮性優勢。

提示

考慮在 Azure 中執行 ETL 工具來利用效能、可擴縮性和成本效益。

重新設計現有的 Oracle 特定指令碼

若部分或所有現有的 Oracle 倉儲 ETL/ELT 處理是由使用 Oracle 特定公用程式的自訂指令碼處理，例如 Oracle SQL*Plus、Oracle SQL Developer、Oracle SQL*Loader 或 Oracle Data Pump，則您必須針對 Azure Synapse 環境將這些指令碼重新編碼。同樣地，若已在 Oracle 中使用預存程序實作 ETL 流程，則您必須將這些流程重新編碼。

ETL 流程的一些元素很容易移轉，例如，從外部檔案將簡單的大量資料載入暫存表格，便可進行移轉。您甚至可能可將該部分的流程自動化，例如，您可藉由使用 Azure Synapse COPY INTO 或 PolyBase 來進行自動化，而非 SQL*Loader。包含任意複雜 SQL 和/或預存程序的其他部分流程，將需要更多時間來重新設計。

提示

要移轉的 ETL 工作詳細目錄應該包含指令碼和預存程序。

測試 Oracle SQL 與 Azure Synapse 相容性的其中一種方式，是從 Oracle v$active_session_history 和 v$sql 聯結中擷取一些具代表性的 SQL 陳述式並取得 sql_text，然後在這些查詢前面加上 EXPLAIN。假設 Azure Synapse 中存在同等的已移轉資料模型，請在 Azure Synapse 中執行這些 EXPLAIN 陳述式。所有不相容的 SQL 都會產生錯誤。您可以使用此資訊來判斷重新編碼工作的規模。

提示

使用 EXPLAIN 來尋找 SQL 不相容。

在最糟的情況下，您可能需要手動重新編碼。不過 Microsoft 合作夥伴會提供產品與服務來協助您重新設定 Oracle 特定的程式碼。

提示

合作夥伴會提供產品和技能來協助您重新設計 Oracle 特定的程式碼。

使用現有的協力廠商 ETL 工具

在許多情況下，現有的舊版資料倉儲系統將會由協力廠商 ETL 產品進行填入和維護。如需 Azure Synapse 的目前 Microsoft 資料整合合作夥伴清單，請參閱 Azure Synapse Analytics 資料整合合作夥伴。

Oracle 社群經常使用數個熱門的 ETL 產品。下列段落將討論 Oracle 倉儲最熱門的 ETL 工具。您可以在 Azure 中的 VM 內執行所有熱門產品，並使用這些產品來讀取及寫入 Azure 資料庫和檔案。

提示

利用現有第三方工具的投資來降低成本和風險。

從 Oracle 載入資料

當您準備從 Oracle 資料倉儲移轉資料時，請決定如何將資料從現有的內部部署環境實際移至雲端中的 Azure Synapse，以及將用來執行傳輸和載入的工具。請考慮下列問題，下列各節將討論這些問題。

您是否會將資料擷取至檔案，或透過網路連線直接移動資料？
您是否會從來源系統或 Azure 目標環境協調流程？
您會使用哪些工具來自動化和管理移轉流程？

要透過檔案還是網路連線傳輸資料？

在 Azure Synapse 中建立要移轉的資料庫資料表之後，您可以將填入這些資料表的資料移出舊版 Oracle 系統，並移至新環境中。有兩個基本方法：

檔案擷取：將資料從 Oracle 資料表擷取到一般分隔檔案，通常是 CSV 格式。您可以透過數種方式擷取資料表資料：
- 使用標準 Oracle 工具，例如 SQL*Plus、SQL Developer 和 SQLcl。
- 使用 Oracle 資料整合器 (ODI) 來產生一般檔案。
- 使用 Data Factory 中的 Oracle 連接器平行卸載 Oracle 資料表，以按照分割區載入資料。
- 使用協力廠商 ETL 工具。
如需擷取 Oracle 資料表資料的範例，請參閱附錄文章。

這種方法需要空間才能放置擷取的資料檔案。若有足夠的儲存體，該空間可能是 Oracle 來源資料庫的本機位置，或是遠端存放在 Azure Blob 儲存體。在本機寫入檔案時可達到最佳效能，因為這能避免網路負荷。

若要將儲存體和網路傳輸需求降到最低，請使用 gzip 等公用程式來壓縮擷取的資料檔案。

擷取之後，請將一般檔案移至 Azure Blob 儲存體。 Microsoft 提供多種選項來移動大量資料，包括：
- AzCopy 可將檔案跨網路移至 Azure 儲存體。
- Azure ExpressRoute 可透過私人網路連線移動大量資料。
- Azure 資料箱可將檔案移至您寄送至 Azure 資料中心供載入用的實體儲存體裝置。
如需詳細資訊，請參閱從 Azure 來回傳輸資料。
透過網路直接擷取和載入：目標 Azure 環境通常會透過 SQL 命令將資料擷取要求傳送至舊版 Oracle 系統以擷取資料。結果會透過網路傳送，並直接載入 Azure Synapse，而不需要將資料放入中繼檔案。此案例中的限制因素通常是 Oracle 資料庫與 Azure 環境之間的網路連線頻寬。針對非常大型的資料磁碟區，這種方法可能不實用。

提示

務必了解要移轉的資料磁碟區和可用的網路頻寬，因為這些因素會影響移轉方法決策。

還有一種使用上述兩種方法的混合式方法。例如，您可以針對較小的維度資料表和較大的事實資料表樣本使用直接網路擷取方法，在 Azure Synapse 中快速提供測試環境。針對大量歷程記錄資料表，您可以使用 Azure 資料箱擷取和傳輸檔案。

從 Oracle 或 Azure 進行協調？

移至 Azure Synapse 時的建議方法是使用 SSMA 或 Data Factory 來協調從 Azure 環境擷取和載入資料的作業。若要以最有效率的方式載入資料，請使用相關聯的公用程式，例如 PolyBase 或 COPY INTO。此方法受益於內建 Azure 功能，並減少建置可重複使用的資料載入管線時需要投入的心力。您可以使用中繼資料驅動資料載入管線，將移轉流程自動化。

建議方法也會在資料載入流程期間將現有 Oracle 環境的效能叫用降到最低，因為管理和載入流程會在 Azure 中執行。

現有的資料移轉工具

資料轉換和移動是所有 ETL 產品的基本功能。若資料移轉工具已在現有的 Oracle 環境中使用，且支援 Azure Synapse 作為目標環境，請考慮使用該工具來簡化資料移轉。

即使現有的 ETL 工具尚未就緒，Azure Synapse Analytics 資料整合合作夥伴仍會提供 ETL 工具來簡化資料移轉的工作。

最後，若您打算使用 ETL 工具，請考慮在 Azure 環境中執行該工具，以善用 Azure 雲端效能、可擴縮性和成本。此方法也會釋放 Oracle 資料中心的資源。

摘要

總之，我們針對將資料和相關的 ETL 流程從 Oracle 移轉至 Azure Synapse 的建議如下：

事先規劃以確保移轉作業成功。
針對要儘快移轉的資料和程序建立詳細詳細目錄。
使用系統中繼資料和記錄檔，以準確了解資料和程序使用方式。請勿依賴文件，因為文件可能會過時。
了解要移轉的資料磁碟區，以及內部部署資料中心與 Azure 雲端環境間的網路頻寬。
考慮使用 Azure VM 中的 Oracle 執行個體，協助從舊版 Oracle 環境卸載移轉。
使用標準內建 Azure 功能，將移轉工作負載降至最低。
識別並了解在 Oracle 和 Azure 環境中擷取和載入資料最有效率的工具。在流程各階段使用適當的工具。
使用 Data Factory 等 Azure 設備來協調移轉流程並將其自動化，同時將對 Oracle 系統的影響降至最低。

附錄：擷取 Oracle 資料的技巧範例

當您從 Oracle 移轉至 Azure Synapse 時，可以使用數種技巧來擷取 Oracle 資料。下列各節將示範如何使用 Oracle SQL Developer 和 Data Factory 中的 Oracle 連接器來擷取 Oracle 資料。

使用 Oracle SQL Developer 來擷取資料

您可以使用 Oracle SQL Developer UI 將資料表資料匯出成許多格式，包括 CSV，如下列螢幕擷取畫面所示：

其他匯出選項包括 JSON 和 XML。您可以使用 UI 將一組資料表名稱新增至「購物車」，然後將匯出套用至購物車中的整個集合：

您也可以使用 Oracle SQL Developer 命令列 (SQLcl) 匯出 Oracle 資料。此選項可支援使用殼層指令碼進行自動化。

針對相對小型的資料表，若您在透過直接連線擷取資料時發生問題，可能會發現這項技巧十分有用。

在 Azure Data Factory 中使用 Oracle 連接器進行平行複製

您可以使用 Data Factory 中的 Oracle 連接器來平行卸載大型 Oracle 資料表。 Oracle 連接器提供內建的資料分割，以從 Oracle 平行複製資料。您可以在複製活動的 [來源] 索引標籤上找到資料分割選項。

Screenshot of Azure Data Factory Oracle partition options in the source tab.

如需設定 Oracle 連接器來進行平行複製的資訊，請參閱從 Oracle 平行複製。

如需 Data Factory 複製活動效能和可擴縮性的詳細資訊，請參閱複製活動效能和可擴縮性指南。

下一步

如需了解安全性存取作業，請參閱本系列文章中的下一篇文章：Oracle 移轉的安全性、存取和作業。

Share via