カオスを利益に変える：ETLプロセスを理解する

公開: 2022-03-11

企業が重要な意思決定情報をビジネスユーザーに提供することを妨げる最も重大な間違いの1つは、1つ以上のデータソースからの信頼できるデータがなく、単一の場所に収集され、整理されて使用できるように準備されていることです。

このシナリオを想像してみてください。あなたは、米国全土に小売店を構える携帯電話ケース会社のITマネージャーです。 情報は入手可能ですが、場所や形式が異なるため、取締役会は売上高に基づいて意思決定を行うのに苦労しています。 取締役の1人が、次の取締役会で提示するために、すべてのPOSからの販売情報を含むダッシュボードを生成するように依頼します。

フォーマットや構造が異なるため、情報を統合することはほとんど不可能であることはすでにご存知でしょう。 一部の小売キオスクは、引き続きOracleデータベースで独自のシステムを使用しています。 より大きな店舗では、新しいSalesforceシステムを使用しています。 システム移行中に運用を開始した最新のキオスクは、売上の計算に使用されるスプレッドシートを共有しています。

さまざまな場所、形式、構造のすべてのデータを、ダッシュボード生成のためにアクセスできるようになっている一意のデータベースにどのように照合しますか？

ETLの基礎

ETLは、 Extract、Transform、およびLoadの略です。 ETLは、この複雑なデータストアを、組織化された信頼性の高い複製可能なプロセスに変換するように設計されたプロセスのグループであり、企業が既存のデータでより多くの売上を生み出すのに役立ちます。

この例では、Oracleデータベース（ほとんどのキオスク）、Salesforce（ストア）、およびスプレッドシート（​​新しいキオスク）からデータを受け取り、データを抽出し、必要に応じて変換して、単一のデータウェアハウスデータベースにロードします。レポートツールからアクセスし、ダッシュボードとスコアカードを生成するために使用します。

ETLの3つのステップに飛び込んで、プロセスを詳しく説明しましょう。

抽出

抽出は、単一または複数のソースからデータを受信するプロセスです。 ソースは、ドキュメント、スプレッドシート、CSVファイル、フラットファイル、Oracle、MySQL、SQL Serverなどのリレーショナルデータベース、非リレーショナルデータベースなど、さまざまな形式と構造を持つことができます。

抽出には、主に2つのタイプがあります。完全と部分です。

  • 完全抽出は、最初の抽出、またはデータの量とその結果として抽出にかかる時間が許容できる場合に使用されます。
  • すべてのデータを再度抽出する必要がない場合、またはデータ量が多すぎて完全な抽出が不可能な場合は、部分抽出をお勧めします。 部分抽出では、更新されたデータまたは新しいデータのみが抽出されます。

これらの側面に加えて、完全抽出と部分抽出のどちらかを選択する際には、他の考慮事項が必要です。そのうちの1つであるデータの可用性と整合性について説明します。

これは、完了したトランザクションのみが抽出の対象と見なされ、整合性障害を引き起こす可能性のあるデータが排除されることを意味します。 たとえば、10の質問でエンジニアの知識を特定するためのオンラインテスト。 エンジニアがテストの途中で、いくつかの質問に回答したがまだ終了していない場合、抽出プロセスは未完了のテストからの質問を読み取ることができません。 整合性障害を引き起こす可能性があります。

変身

データを抽出した後、変換プロセスを開始できます。ビジネスルールと技術的基準に従ってデータをクリーンアップ、操作、および変換して、許容可能なレベルのデータ品質を維持します。

いくつかの要因によっては、ステージング領域の使用が必要になる場合があります。 ステージング領域は、変換されるデータソースから抽出されたデータを一時的に格納するために使用される中間ストレージスペースです。

一部のプロジェクト（通常はデータ量が少ないプロジェクト）では、ステージング領域を使用する必要はありませんが、ほとんどのプロジェクトではステージング領域を使用します。

変換フェーズ中に実行されるタスクの範囲は次のとおりです。

  • 選択：抽出されたデータを選択するための基準。 選択は、抽出段階、変換段階、または両方の段階で行うことができます。
  • 統合：抽出フェーズからステージング領域までのデータの組み合わせ。 この組み合わせは、抽出されたデータに基づいてステージング領域のデータを追加、削除、および更新することを意味します。
  • 結合： SQL結合（内部結合、左結合、右結合、完全結合など）と同様に、抽出されたデータを結合するために使用されます
  • クレンジングまたはスクラブ：一貫性のないデータや無効なデータ、またはエラーのあるデータを削除して、データ品質を向上させます。 複数のデータソースを使用すると、次のようなクリーニングが必要なデータの問題が発生する可能性が高くなります。
    • 参照整合性（カテゴリが存在しない顧客）
    • 欠落している値（IDのない顧客）
    • 一意性（同じSSNを持つ複数の人）
    • スペルミス（サンディエゴ、カナダ、L.Angeles）
    • 矛盾する値（Alex dob 04.27.1974、Alex dob 04.14.2000）
    • と他の多く
  • 要約：事後使用のためにデータセットを要約します
  • 集計：グループにまとめられて要約されたデータ
  • 統合：単一のデータセットに統合された複数のソースまたは構造からのデータ

一般的な変換タイプは次のとおりです。

  • 重複データを削除する
  • 分割とマージ
  • 変換（日付、時刻、数値マスク、測定値）
  • エンコーディング（男性からM）
  • 計算（item_value = unit_Price *数量）
  • キー生成

読み込み中

最後になりましたが、ETLの最後のプロセスは、データを宛先にロードすることです。 ロードとは、変換されたデータ（ステージング領域からかどうかに関係なく）をリポジトリ（通常はデータウェアハウスデータベース）に挿入することです。

データの読み込みには、主に3つのタイプがあります。フルまたは初期、インクリメンタル、および更新です。

  • フルまたは初期とは、抽出および変換されたデータの完全なロードを意味します。 ステージング領域のすべてのデータは、ビジネスユーザーが利用できるように、最終的な宛先にロードされます。
  • インクリメンタルロードは、変換されたデータを最終的な宛先のデータと比較し、新しいデータのみをロードするプロセスです。 インクリメンタルロードは、以下で説明するリフレッシュロードと組み合わせて使用​​できます。
  • 更新ロードは、元のソースで行われた変更を反映するために、最終的な宛先のデータを更新するプロセスです。 更新は、完全または増分にすることができます。

要約すると、すべての企業は、規模に関係なく、ETLプロセスを使用して、既存の情報を統合し、意思決定のためにさらに豊富な情報を生成して、以前は使用できなかったデータを新しい収益源に変えることができます。

テスト

テストはETLの最も重要なフェーズの1つですが、それでも最も見過ごされているフェーズの1つです。

さまざまなソースや構造からのデータを変換してデータウェアハウスにロードすることは非常に複雑であり、エラーが発生する可能性があります。 最も一般的なエラーは、上記の変換フェーズで説明されています。

データの正確さは成功の鍵ですが、不正確さは災害のレシピです。 したがって、ETLの専門家には、プロセス全体でデータの整合性を保証するという使命があります。 各フェーズの後に、テストを実行する必要があります。 単一のソースからデータを抽出する場合でも、複数のソースからデータを抽出する場合でも、データをチェックしてエラーがないことを確認する必要があります。

変換後も同じことを行う必要があります。 たとえば、変換フェーズ中にデータを要約する場合、データが失われず、合計が正しいことを保証するためにデータをチェックする必要があります。

変換されたデータをデータウェアハウスにロードした後、テストプロセスを再度適用する必要があります。 ロードされたデータは、変換されたデータと比較され、その後、抽出されたデータと比較される必要があります。

携帯電話のケース会社の例では、3つの異なるソース（独自のOracleデータベース、Salesforce、およびスプレッドシート）と異なる形式を使用しています。 テストフェーズでは、元のソースからのサンプルデータを使用し、それらをステージング領域にあるデータと比較して、エラーなしで抽出が行われたことを確認できます。

サンプルデータ（この場合は、3つの異なる場所（店舗、古いキオスク、新しいキオスク）からの販売情報である可能性があります）は、元のソースと比較されます。 違いがある場合は、それが許容できるかどうか、またはエラーであるかどうかを確認するために分析する必要があります。

エラーが見つかった場合は修正する必要があり、修正する必要がある場合はいくつかの決定を行う必要があります。元のデータを変更する必要がありますか？ そうすることは可能ですか？ 元のソースでエラーを修正できない場合、何らかの変換によってエラーを修正できますか？

場合によっては、エラーのあるデータを削除し、担当者に通知するアラートをトリガーする必要があります。

いくつかのテスト例：

  • データの検証が必要
  • データ品質
  • パフォーマンス
  • データルール
  • データモデリング

ロギング

ETLプロセスのログ記録は、保守が容易で修正が容易なシステムを確実に提供するための重要な要素です。

正しいロギングプロセスを備えたETLは、ETL操作全体を常に改善された状態に保つために重要であり、チームがデータソース、データ形式、変換、宛先などのバグや問題を管理するのに役立ちます。

堅牢なログプロセスにより、チームは時間を節約して問題をより迅速かつ簡単に特定でき、リードエンジニアは問題を直接特定するために必要な時間を短縮できます。 大量のデータを抽出している最中にエラーが発生することがあり、ログがないと、問題を特定するのが難しく、ほとんど不可能な場合もあります。 ログがない場合は、プロセス全体を再実行する必要があります。 チームはログを使用して、問題の原因となったファイルと行を迅速に特定し、そのデータのみを修正できます。

ログがそれほど重要ではないことを私が想像できる唯一のケースは、プロセスが手動で実行され、手動で監視できる少量のデータがある非常に小さな自動化されていないシステムの場合です。

ログは自動化を改善します。 自動的に実行される大量のデータを使用するETLプロセスには、ログシステムが必要です。 それらが適切に計画され実行されている場合、ログシステムの構築に注がれるすべての努力は、より高速なエラー識別、より信頼性の高いデータ、およびログファイルにある改善点の形で利益をもたらします。

ログシステムの作成には、生成、アーカイブ、分析の3つの主要なステップがあります。

  • 生成は、ETLパイプラインの実行中に何が起こっているかを文書化するプロセスです。プロセスの開始時、どのファイルまたはテーブルが抽出されているか、ステージング領域に保存されているデータ、エラーメッセージなどです。 エンジニアに役立つすべての重要な情報をログに記録する必要があります。 警告：時間とスペースを消費するだけで役に立たないほど多くの情報を生成しないように注意してください。
  • ログデータのアーカイブとは、過去の実行の実績を保持して過去のシナリオを検索し、エラーを特定したり、現在のシナリオと比較して改善を求めたりすることを意味します。 保存する履歴の特定のポイントの関連性を確認することが重要です。構造が何度も変更された昔のデータは、保持する価値がありません。
  • 分析します。 ログ分析は非常に重要です。 分析されていない大量のデータを保存することは意味がありません。 データの生成と保存には時間とお金がかかるだけです。 ログ分析は、エラーの検索を支援するだけでなく、改善点を特定し、全体的なデータ品質を向上させるためにも重要です。

パフォーマンス

ETLプロセスは大量のデータを処理でき、セットアップに費やした時間とデータの処理に必要な計算リソースの両方の点で多くの費用がかかる可能性があります。 統合を計画するとき、エンジニアは使用されるすべてのデータの必要性を念頭に置く必要があります。 最善の方法は、無駄なデータを移行するだけで時間とお金を費やすのではなく、目的を達成するために最小限のデータで作業することです。 また、データ量は時間の経過とともに増加する傾向があることに注意してください。将来のニーズを考慮してください。

ロギングシステムの設計にはどのくらいの努力が必要ですか？

これは、処理されるデータの量や頻度など、さまざまな要因によって異なります。 データがほとんどない小さなシステムは、高度なロギングシステムに投資することなく、手動で管理できます。

大量のデータ、多くの異なるプロセス、異なるデータソースと形式、および複雑なパイプラインを持つ企業は、ロギングシステムの作成に投資する必要があります。 すでに述べたように、優れたログシステムは多くの時間とお金を節約できます。

より明白なアプローチでは、会社の規模、データの量、統合の頻度に関係なく、ETLプロセスは有益である必要があります。 エコノミストのミルトンフリードマンが言ったように、時間、お金、技術リソースへの投資は利益を生み出さなければなりません。「フリーランチはありません。」

要約すると、ETLプロセスは、企業がすでに持っているデータで利益を増やすのに役立ちますが、それは正しい方法で使用されていません。 ETLパイプラインは、さまざまなシステム、重要な情報を含むスプレッドシート、およびさまざまな部門や支店に分散しているデータの他の部分を統合できるため、組織はデータを最大限に活用できます。