抽出、変換、ロード

抽出、変換、ロード( ETL ) は、入力ソースからデータを抽出し、変換(クリーニングを含む) して、出力データ コンテナーにロードする3 段階のコンピューティングプロセスです。データは 1 つ以上のソースから収集でき、1 つ以上の出力先に出力することもできます。ETL 処理は通常、ソフトウェア アプリケーションを使用して実行されますが、システム オペレーターが手動で行うこともできます。ETL ソフトウェアは通常、プロセス全体を自動化し、手動で実行することも、単一のジョブとして、またはジョブのバッチに集約して定期的なスケジュールで実行することもできます。

適切に設計されたETLシステムは、ソースシステムからデータを抽出し、データタイプとデータ有効性の標準を適用し、出力の要件に構造的に準拠していることを保証します。一部のETLシステムは、アプリケーション開発者がアプリケーションを構築し、エンドユーザーが意思決定を行えるように、プレゼンテーション可能な形式でデータを配信することもできます。^[1]

ETLプロセスは、データウェアハウスでよく使用されます。^[2] ETLシステムは、通常、異なるベンダーによって開発およびサポートされているか、別のコンピュータハードウェアでホストされている複数のアプリケーション（システム）からのデータを統合します。元のデータを含む個別のシステムは、多くの場合、異なる利害関係者によって管理および運用されます。たとえば、原価計算システムは、給与、売上、購買からのデータを結合する場合があります。

データ抽出は、同種または異種のソースからデータを抽出することを含みます。データ変換は、データをクリーニングし、クエリと分析の目的で適切なストレージ形式/構造に変換することによってデータを処理します。最後に、データロードは、運用データストア、データマート、データレイク、データウェアハウスなどの最終ターゲットデータベースにデータを挿入することを指します。^{[3] [4]}

ETL 処理には、ソース システムからのデータの抽出が含まれます。多くの場合、これは ETL の最も重要な側面を表します。データを正しく抽出することで、後続のプロセスが成功するための準備が整うためです。ほとんどのデータ ウェアハウス プロジェクトでは、異なるソース システムのデータが結合されます。各個別のシステムでは、異なるデータ編成や形式が使用される場合もあります。一般的なデータ ソース形式には、リレーショナル データベース、フラット ファイル データベース、XML、JSONなどがありますが、IBM Information Management Systemなどの非リレーショナル データベース構造や、Virtual Storage Access Method (VSAM)やIndexed Sequential Access Method (ISAM)などのその他のデータ構造、さらにはWeb クローラーやデータ スクレイピングなどの手段で外部ソースから取得された形式も含まれる場合があります。抽出されたデータ ソースをストリーミングし、宛先データベースにオンザフライでロードすることは、中間データ ストレージが不要な場合に ETL を実行するもう 1 つの方法です。

抽出の本質的な部分には、ソースから取得したデータが特定のドメイン (パターン/デフォルトまたは値のリストなど) で正しい/期待される値を持っているかどうかを確認するためのデータ検証が含まれます。データが検証ルールを満たさない場合、そのデータは全体または一部が拒否されます。拒否されたデータは理想的にはソース システムに報告され、さらに分析されて不正なレコードを識別して修正したり、データ ラングリングを実行したりします。

データ変換段階では、抽出されたデータを最終ターゲットにロードできるように準備するために、一連のルールまたは関数が抽出されたデータに適用されます。

変換の重要な機能は、データ クレンジングです。これは、「適切な」データのみをターゲットに渡すことを目的としています。異なるシステムが相互作用する場合の課題は、関連するシステムのインターフェイスと通信にあります。あるシステムで使用できる文字セットが、他のシステムでは使用できない場合があります。

その他の場合では、サーバーまたはデータ ウェアハウスのビジネスおよび技術ニーズを満たすために、次の変換タイプの 1 つ以上が必要になることがあります。

• ロードする特定の列のみを選択します (または、ロードしないnull列を選択します)。たとえば、ソース データに 3 つの列 (別名「属性」)、roll_no、age、salary がある場合、選択では roll_no と salary のみが使用されます。または、選択メカニズムでは、salary が存在しない (salary = null) すべてのレコードが無視されることがあります。
• コード化された値の変換: (例: ソース システムでは男性を「1」、女性を「2」とコード化しているが、ウェアハウスでは男性を「M」、女性を「F」とコード化している場合)
• 自由形式の値のエンコード: (例: 「男性」を「M」にマッピング)
• 新しい計算値の導出: (例: sale_amount = qty * unit_price)
• 検索パフォーマンスを向上させるために、列のリストに基づいてデータを並べ替えたり順序付けたりする
• 複数のソースからのデータの結合（例：ルックアップ、マージ）とデータの重複排除
• 集計 (例: ロールアップ - 複数行のデータの要約 - 各店舗および各地域の合計売上など)
• 代理キー値の生成
• 転置またはピボット（複数の列を複数の行に変換する、またはその逆）
• 列を複数の列に分割する（例：1つの列で文字列として指定されたコンマ区切りのリストを、異なる列の個別の値に変換する）
• 繰り返し列の分解
• テーブルまたは参照ファイルから関連データを検索して検証する
• あらゆる形式のデータ検証を適用します。検証に失敗すると、データの完全な拒否、部分的な拒否、またはまったく拒否されない可能性があり、ルールの設計と例外処理に応じて、データの一部またはすべてが次のステップに渡されます。上記の変換の多くは、コード変換で抽出されたデータ内の不明なコードを解析する場合など、例外を引き起こす可能性があります。

ロード フェーズでは、データを最終ターゲットにロードします。最終ターゲットは、単純な区切りフラット ファイルやデータ ウェアハウスなどの任意のデータ ストアにすることができます。組織の要件によって、このプロセスは大きく異なります。一部のデータ ウェアハウスでは、既存の情報が累積情報で上書きされます。抽出されたデータの更新は、毎日、毎週、または毎月頻繁に行われます。他のデータ ウェアハウス (または同じデータ ウェアハウスの他の部分) では、たとえば 1 時間ごとなど、定期的に履歴形式で新しいデータを追加する場合があります。これを理解するために、昨年の売上記録を維持する必要があるデータ ウェアハウスについて考えてみましょう。このデータ ウェアハウスでは、1 年以上前のデータは新しいデータで上書きされます。ただし、1 年間の期間のデータの入力は、履歴形式で行われます。置き換えまたは追加のタイミングと範囲は、使用可能な時間とビジネスニーズに応じた戦略的な設計上の選択です。より複雑なシステムでは、データ ウェアハウスにロードされたデータに対するすべての変更の履歴と監査証跡を維持できます。ロード フェーズではデータベースと対話するため、データベース スキーマで定義された制約、およびデータのロード時にアクティブになるトリガー (一意性、参照整合性、必須フィールドなど) が適用されます。これらも ETL プロセスの全体的なデータ品質パフォーマンスに貢献します。

• たとえば、金融機関では、顧客に関する情報が複数の部門に存在し、各部門で顧客情報がそれぞれ異なる方法でリストされている場合があります。会員部門では顧客を名前でリストし、経理部門では顧客を番号でリストする場合があります。ETL は、これらすべてのデータ要素をバンドルし、データベースやデータ ウェアハウスに保存できるように、統一された形式で統合できます。
• 企業が ETL を使用するもう 1 つの方法は、情報を別のアプリケーションに永続的に移動することです。たとえば、新しいアプリケーションでは別のデータベース ベンダーが使用され、データベース スキーマも大きく異なる可能性があります。ETL を使用すると、データを新しいアプリケーションに適した形式に変換できます。
• 例としては、会計士、コンサルタント、法律事務所などで使用されている経費および費用回収システムがあります。データは通常、時間と請求のシステムに送られますが、一部の企業では、生のデータを人事部への従業員の生産性レポートや施設管理への機器使用レポートに利用することもあります。

実際の ETL サイクルは、次のような追加の実行ステップで構成される場合があります。

1. サイクルの開始
2. 参照データの構築
3. 抜粋（ソースから）
4. 検証
5. 変換（クリーンアップ、ビジネス ルールの適用、データの整合性の確認、集計または分解の作成）
6. ステージング（使用されている場合はステージングテーブルにロード）
7. 監査レポート（たとえば、ビジネスルールの遵守状況など。また、障害が発生した場合の診断/修復にも役立ちます）
8. 公開（ターゲットテーブルへ）
9. アーカイブ

ETL プロセスはかなり複雑になる可能性があり、不適切に設計された ETL システムでは重大な運用上の問題が発生する可能性があります。

運用システムのデータ値の範囲やデータ品質は、検証および変換ルールの指定時に設計者の予想を超える場合があります。データ分析中にソースのデータ プロファイリングを行うと、変換ルールの仕様で管理する必要があるデータ条件を特定できるため、ETL プロセスで明示的または暗黙的に実装された検証ルールを修正できます。

データ ウェアハウスは通常、さまざまな形式と目的を持つさまざまなデータ ソースから構成されます。そのため、ETL はすべてのデータを標準の同質環境にまとめる重要なプロセスです。

設計分析^[5]では、 ETLシステムの利用期間全体にわたるスケーラビリティを確立する必要があります。これには、サービスレベル契約の範囲内で処理する必要があるデータの量を理解することも含まれます。ソースシステムから抽出できる時間は変化する可能性があり、同じ量のデータをより短時間で処理する必要がある場合があります。ETLシステムによっては、数十テラバイトのデータを含むデータウェアハウスを更新するために、テラバイト単位のデータ処理に拡張する必要があります。データ量の増加に応じて、毎日のバッチから数日間のマイクロバッチ、メッセージキューとの統合、または継続的な変換と更新のためのリアルタイムの変更データキャプチャまで拡張できる設計が必要になる場合があります。

ユニーク キーは、すべてを結び付けるため、すべてのリレーショナル データベースで重要な役割を果たします。ユニーク キーは特定のエンティティを識別する列ですが、外部キーは主キーを参照する別のテーブルの列です。キーは複数の列で構成できます。その場合、キーは複合キーになります。多くの場合、主キーは自動生成された整数で、表されるビジネス エンティティには意味がなく、リレーショナル データベースの目的のためだけに存在します。一般に、代理キーと呼ばれます。

通常、データ ウェアハウスにロードされるデータ ソースは複数あるため、キーは対処すべき重要な問題です。たとえば、顧客は複数のデータ ソースで表され、 1 つのソースでは社会保障番号が主キー、別のソースでは電話番号が主キー、3 つ目のソースではサロゲートが主キーとして使用されます。ただし、データ ウェアハウスでは、すべての顧客情報を 1 つのディメンションに統合する必要がある場合があります。

この問題に対処するための推奨される方法は、ファクトテーブルからの外部キーとして使用されるウェアハウス代理キーを追加することです。^[6]

通常、ディメンションのソース データに対して更新が行われ、これは当然データ ウェアハウスに反映される必要があります。

ソース データの主キーがレポート作成に必要な場合、ディメンションには各行の情報がすでに含まれています。ソース データが代理キーを使用する場合、ウェアハウスは、クエリやレポートで使用されていない場合でも、そのキーを追跡する必要があります。これは、ウェアハウスの代理キーと元のキーを含むルックアップ テーブルを作成することによって行われます。 ^[7]この方法により、ディメンションはさまざまなソース システムからの代理キーで汚染されず、更新機能が保持されます。

ルックアップテーブルは、ソースデータの性質に応じてさまざまな方法で使用されます。考慮すべき5つのタイプがあります。^[7]ここでは3つを紹介します。

タイプ1

ディメンション行は、ソースシステムの現在の状態に合わせて単純に更新されます。ウェアハウスは履歴をキャプチャしません。ルックアップテーブルは、更新または上書きするディメンション行を識別するために使用されます。

タイプ2

ソースシステムの新しい状態で新しいディメンション行が追加され、新しい代理キーが割り当てられ、ソースキーはルックアップテーブル内で一意ではなくなります。

完全にログイン済み

ソース システムの新しい状態を示す新しいディメンション行が追加され、以前のディメンション行は、アクティブではなくなったことと非アクティブ化された時刻を反映するように更新されます。

ETL ベンダーは、複数の CPU、複数のハード ドライブ、複数のギガビット ネットワーク接続、大量のメモリを備えた強力なサーバーを使用して、1 時間あたり数 TB (テラバイト) (または 1 秒あたり約 1 GB) でレコード システムをベンチマークします。

実際には、ETL プロセスで最も遅い部分は、通常、データベースのロード フェーズで発生します。データベースは、同時実行、整合性の維持、およびインデックスを処理する必要があるため、パフォーマンスが遅くなる可能性があります。したがって、パフォーマンスを向上させるには、次の方法を採用すると効果的です。

• 高速抽出を実現しながらソースシステムの負荷を軽減するために、可能な限り（データベースにクエリを実行する代わりに）ダイレクトパス抽出方法または一括アンロードを使用します。
• 変換処理のほとんどはデータベース外で行われる
• 可能な限り一括ロード操作

それでも、一括操作を使用した場合でも、データベース アクセスが ETL プロセスのボトルネックになることがよくあります。パフォーマンスを向上させるために使用される一般的な方法は次のとおりです。

• パーティションテーブル (およびインデックス): パーティションのサイズを同じに保つようにします (nullパーティション分割を歪める可能性のある値に注意してください)
• ロード前にETLレイヤーですべての検証を実行します。ロード中はターゲットデータベーステーブルで整合性チェック（ ...）を無効にします。disable constraint
• ロード中にターゲットデータベーステーブル内のトリガー（ ...）を無効にします。その効果を別のステップとしてシミュレートします。disable trigger
• ETL レイヤーで ID を生成する (データベースではなく)
• ロード前にインデックス（テーブルまたはパーティション上）を削除し、ロード後に再作成します（SQL: drop index... ; create index...）
• 可能な場合は並列バルク ロードを使用します。テーブルがパーティション化されている場合やインデックスがない場合に適しています (注: 同じテーブル (パーティション) に並列ロードを実行しようとすると、通常はロックが発生します。データ行でない場合はインデックスでロックが発生します)
• 挿入、更新、または削除を行う必要がある場合は、ETL レイヤーでどの行をどのような方法で処理する必要があるかを調べ、データベースでこれらの 3 つの操作を個別に処理します。挿入の場合は一括ロードを実行できることが多いですが、更新と削除は通常、API ( SQL を使用)を介して行われます。

特定の操作をデータベース内で実行するか、データベース外で実行するかによって、トレードオフが生じる場合があります。たとえば、 を使用して重複を削除するとdistinct、データベース内では時間がかかる可能性があるため、データベース外で実行するのが合理的です。一方、 を使用するとdistinct抽出する行数が大幅に (x100) 減少する場合は、データをアンロードする前に、データベース内でできるだけ早く重複を削除するのが合理的です。

ETL で問題が発生する一般的な原因は、ETL ジョブ間の依存関係の数が多いことです。たとえば、ジョブ「A」が終了していない間は、ジョブ「B」を開始できません。通常、すべてのプロセスをグラフ上で視覚化し、並列処理を最大限に活用してグラフを縮小し、連続処理の「チェーン」をできるだけ短くすることで、パフォーマンスを向上させることができます。この場合も、大きなテーブルとそのインデックスをパーティション分割すると非常に役立ちます。

データが複数のデータベースに分散され、それらのデータベースで処理が順番に行われる場合にも、よくある問題が発生します。データベース間でデータをコピーする方法として、データベース レプリケーションが使用される場合もありますが、これによりプロセス全体が大幅に遅くなる可能性があります。一般的な解決策は、処理グラフを 3 つのレイヤーに減らすことです。

• 出典
• 中央ETL層
• ターゲット

このアプローチにより、処理で並列処理を最大限に活用できます。たとえば、2 つのデータベースにデータをロードする必要がある場合、ロードを並列で実行できます (最初のデータベースにロードしてから 2 番目のデータベースに複製するのではなく)。

場合によっては、処理を順番に実行する必要があります。たとえば、メインの「ファクト」テーブルの行を取得して検証する前に、ディメンション (参照) データが必要になります。

一部の ETL ソフトウェア実装には並列処理が含まれています。これにより、大量のデータを処理する際に、さまざまな方法で ETL の全体的なパフォーマンスを向上させることができます。

ETL アプリケーションは、主に 3 種類の並列処理を実装します。

• データ: 単一のシーケンシャルファイルを小さなデータファイルに分割して並列アクセスを可能にする
• パイプライン: 同じデータ ストリーム上で複数のコンポーネントを同時に実行できるようにします。たとえば、レコード 1 の値を検索すると同時に、レコード 2 に 2 つのフィールドを追加します。
• コンポーネント:同じジョブ内の異なるデータ ストリームで複数のプロセスを同時に実行します。たとえば、1 つの入力ファイルを並べ替えながら、別のファイルの重複を削除します。

通常、これら 3 種類の並列処理はすべて、単一のジョブまたはタスク内で組み合わせて動作します。

アップロードされるデータが比較的一貫していることを確認するのも困難です。複数のソース データベースは更新サイクルが異なる場合があるため (数分ごとに更新されるものもあれば、数日または数週間かかるものもある)、すべてのソースが同期されるまで ETL システムで特定のデータを保留する必要がある場合があります。同様に、ウェアハウスをソース システムの内容または総勘定元帳と照合する必要がある場合は、同期および照合ポイントを確立する必要があります。

データ ウェアハウス プロシージャでは通常、大きな ETL プロセスを、順次または並行して実行される小さな部分に分割します。データ フローを追跡するには、各データ行に「row_id」のタグを付け、プロセスの各部分に「run_id」のタグを付けると便利です。障害が発生した場合、これらの ID があると、障害が発生した部分をロールバックして再実行するのに役立ちます。

ベスト プラクティスでは、プロセスの特定のフェーズが完了したときの状態であるチェックポイントも求められます。チェックポイントに達したら、すべてをディスクに書き込み、一時ファイルを消去し、状態をログに記録するなどすることをお勧めします。

確立された ETL フレームワークにより、接続性とスケーラビリティが向上する場合があります。^{[要出典]優れた ETL ツールは、多くの異なる}リレーショナル データベースと通信し、組織全体で使用されるさまざまなファイル形式を読み取ることができなければなりません。 ETL ツールは、エンタープライズ アプリケーション統合、さらにはエンタープライズ サービス バスシステムに移行し始めており、これらのシステムはデータの抽出、変換、読み込みだけにとどまらず、はるかに多くのことをカバーするようになりました。 多くの ETL ベンダーは現在、データ プロファイリング、データ品質、メタデータ機能を備えています。 ETL ツールの一般的な使用例としては、CSV ファイルをリレーショナル データベースで読み取り可能な形式に変換することが挙げられます。 数百万件のレコードの一般的な変換は、ユーザーが csv のようなデータ フィード/ファイルを入力し、可能な限り少ないコードでデータベースにインポートできるようにする ETL ツールによって容易になります。

ETL ツールは、通常、大規模なデータセットを迅速にインポートしたいコンピュータサイエンスの学生から、会社のアカウント管理を担当するデータベース アーキテクトまで、幅広い専門家によって使用されています。ETL ツールは、最大のパフォーマンスを得るために頼りになる便利なツールとなっています。ほとんどの場合、ETL ツールには GUI が含まれており、ユーザーは、ファイルを解析してデータ タイプを変更するために大規模なプログラムを作成するのではなく、ビジュアル データ マッパーを使用して、データを簡単に変換できます。

ETLツールは従来、開発者やITスタッフ向けのものでしたが、調査会社ガートナーは、新しいトレンドはビジネスユーザーにこれらの機能を提供することで、ITスタッフに頼るのではなく、必要に応じてビジネスユーザーが自分で接続やデータ統合を作成できるようにすることだと述べています。^[8]ガートナーは、これらの非技術系ユーザーを「シチズン・インテグレーター」と呼んでいます。^[9]

オンライントランザクション処理（OLTP）アプリケーションでは、個々のOLTPインスタンスからの変更が検出され、更新のスナップショットまたはバッチに記録されます。ETLインスタンスを使用すると、これらのバッチをすべて定期的に収集し、共通の形式に変換して、データレイクまたはウェアハウスにロードできます。^[1]

データ仮想化は、 ETL 処理の高度化に使用できます。データ仮想化を ETL に適用することで、複数の分散データ ソースのデータ移行とアプリケーション統合という最も一般的な ETL タスクを解決できるようになりました。仮想 ETL は、さまざまなリレーショナル、半構造化、非構造化データソースから収集されたオブジェクトまたはエンティティの抽象化された表現を使用して動作します。ETL ツールは、オブジェクト指向モデリングを活用し、中央にあるハブ アンド スポークアーキテクチャに永続的に保存されたエンティティの表現を操作できます。ETL 処理のためにデータ ソースから収集されたエンティティまたはオブジェクトの表現を含むこのようなコレクションは、メタデータ リポジトリと呼ばれ、メモリ内に常駐することも、永続化することもできます。永続的なメタデータ リポジトリを使用することで、ETL ツールは 1 回限りのプロジェクトから永続的なミドルウェアに移行し、データの調和とデータ プロファイリングを一貫してほぼリアルタイムで実行できます。

抽出、ロード、変換（ELT）はETLの一種で、抽出されたデータがまずターゲットシステムにロードされます。^[10]分析パイプラインのアーキテクチャでは、データのクレンジングとエンリッチを行う場所^[10]とディメンションの適合方法も考慮する必要があります。^[1] ELTプロセスの利点には、速度と、非構造化データと構造化データの両方をより簡単に処理できることなどがあります。^[11]

データウェアハウスにおけるETLプロセスを教えるコースの教科書として使用されているラルフ・キンボールとジョー・カゼルタの著書『データウェアハウスETLツールキット』（Wiley、2004年）では、この問題が取り上げられています。^[12]

Amazon Redshift、Google BigQuery、Microsoft Azure Synapse Analytics、Snowflake Inc.などのクラウドベースのデータ ウェアハウスは、高度にスケーラブルなコンピューティング能力を提供できるようになりました。これにより、企業は事前ロード変換を回避し、生データをデータ ウェアハウスに複製して、必要に応じてSQL を使用してデータを変換できます。

ELTを使用した後、データはさらに処理され、データマートに保存される。^[13]

ほとんどのデータ統合ツールはETLに偏っていますが、ELTはデータベースやデータウェアハウスアプライアンスで人気があります。同様に、データをブロックチェーン上で最初に変換し（トークンのバーンなど、データの変更を記録する方法として）、その後抽出して別のデータストアにロードするTEL（変換、抽出、ロード）を実行することも可能である。^[14]

• アーキテクチャパターン（EAリファレンスアーキテクチャ）
• CMS パイプライン
• 作成、読み取り、更新、削除(CRUD)
• データクレンジング
• データ統合
• データマート
• データメッシュ、ドメイン指向データアーキテクチャ
• データ移行
• データ変換（コンピューティング）
• 電子データ交換(EDI)
• エンタープライズアーキテクチャ
• 法的電子データ交換標準(LEDES)
• メタデータの検出
• オンライン分析処理(OLAP)
• オンライントランザクション処理(OLTP)
• 空間ETL