8. Queries, Modeling, And Transformation
クエリとその基礎となる重要なパターン、データモデリングのパターン、変換について。
8.1 Queries
8.1.1 What Is a Query?
クエリは、データを取得したりデータを操作したりすることを可能にする。
DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), TCL (Transaction Control Language)
8.1.2 The Life of a Query
SQLクエリのライフサイクル
- SQLクエリの発行
- パースしてバイトコードに変換
- クエリ計画と最適化
- クエリ実行
- 結果を返す
8.1.3 The Query Optimizer
クエリオプティマイザは、クエリを適切なステップに分割して効率的に実行できる順序に並べ替える。
8.1.4 Improving Query Performance
データエンジニアは、低性能のクエリを特定し修正する方法を知っておくことは重要。
Optimize your join strategy and schema
Pre-joinは、繰り返しジョインが実行される場合にあらかじめデータをジョインして保存しておく。
ネストしたサブクエリや一時テーブルではなく、WITHを使った 共通テーブル式 (CTE) を使おう。
Use the explain plan and understand your query’s performance
どのようにしてコストが最小となるクエリを決定したのか、使用されたデータベースオブジェクト、各ステージでのリソース消費量と性能統計などを知ることができる。
どのように実行されるかだけでなく、クエリの性能を見ることもできる。データのロード時間と処理時間、クエリ実行時間やレコード数、リソース競合を引き起こす可能性のある競合クエリなど。
Avoid full table scans
フルスキャンは性能的に非効率でありコストも高い。
列指向データベースの場合は必要な列だけを選択すべき。
Know how your database handles commits
データエンジニアは、データベースがどのようにコミットやトランザクションを処理するかを熟知し、クエリ結果に期待される一貫性を決定しなければならない。
使用するデータベースはACIDに準拠した方法で書き出しと更新を処理するか。
Vacuum dead records
データベースのデータレコードの削除は、テーブルの肥大化を抑えてクエリを高速化できる。クエリプランが正確になる。劣悪なインデックスが一層されてインデックスの性能が向上する。
MySQLのバキューム操作
OPTIMIZE TABLE table_name;
Leverage cached query results
クエリのキャッシュ結果を活用しよう。データベースの負荷を軽減し、頻繁に実行されるクエリに対して高速に結果を返すことができる。
8.1.5 Queries on Streaming Data
ストリームに対する基本的なクエリパターンの1つは、実運用データベースからCDCによってフォローされたアナリティクスデータベースに対してクエリを実行する (高速フォロワー法)。この方法は現在のテーブルの状態に対してSELECTクエリを実行しているだけなので、ストリームの変化に対して動的に何かをトリガーすることはできない。
Kappaアーキテクチャでは、ストリーミングストレージをリアルタイムのデータ転送レイヤとして扱うと同時に、過去のデータを検索・クエリするためのデータベースとしても扱うことができる。
ウィンドウは、動的なトリガーに基づいて処理される小さなバッチとなり、セッションウィンドウ、タンブリングウィンドウ、スライディングウィンドウがある。
セッションウィンドウは、新しいイベントが発生されるたびにウィンドウが開始され、一定の非アクティブ期間が発生するとウィンドウを閉じる。
タンブリングウィンドウは、固定長で連続する時間区間のウィンドウ。ウィンドウの重なりはない。
スライディングウィンドウは、固定長で一定間隔でスライドするウィンドウ。ウィンドウは互いに重なりがある。
ウォーターマークは、遅延データを許容する時間のしきい値。
複数のストリームをジョインしたり、ストリームとバッチ処理された履歴データを組み合わせる(リッチ化: enrichment)こともできる。
8.2 Data Modeling
8.2.1 What Is a Data Model?
データモデルは、データと現実世界の関係を表す。組織のプロセス、定義、ワークフロー、ロジックを最適に反映するように、データを構造化し、標準化する。
8.2.2 Conceptual, Logical, and Physical Data Models
3つの主要なデータモデル、概念データモデル、論理データモデル、物理データモデル。
概念データモデル: ビジネスロジックとルールを表す。スキーマ、テーブル、フィールドなど、システムのデータを定義する。
論理データモデル: 概念モデルの実装をより詳細に定義する。顧客ID、顧客名、顧客の住所のデータ型に関する情報を追加し、主キーと外部キーのマッピングを行う。
物理データモデル: 論理モデルをデータベースシステムに実装する方法を定義する。具体的なデータベース、スキーマ、テーブルを、設定の詳細も含めて論理モデルに追加する。
データモデリングの検討事項として、データの粒度も重要。データが格納されクエリされる分解能。顧客ID、注文ID、製品IDなど、テーブルの主キーレベルで表される。
一般に、可能な限り詐称の粒度でデータをモデリングすべきだ。
8.2.3 Normalization
正規化 (normalization) とは、データベース内のテーブルと列の関係を厳密に管理することを矯正するデータモデリング手法である。
正規化の目的は、データベース内のデータの冗長性を取り除き、参照一貫性を確保することだ。
非正規化: 正規化なし。ネストや冗長なデータ。
第一正規化 (1NF): 各カラムは一意であり、単一の値を持つ。テーブルは一意な主キーを持つ。
第二正規化 (2NF): 1NFの要件に加え、部分従属性を持たない。
第三正規化 (3NF): 2NFの要件に加え、各テーブルはその主キーに関連するフィールドのみを含み、数位的従属性を持たない。
8.2.4 Techniques for Modeling Batch Analytical Data
知っておくべきデータモデリングの方法。Kimball, Inmon, データボルト。
Inmonモデル: 企業全体のデータウェアハウスを構築し、高いデータの一貫性と整合性を保つトップダウンアプローチ
Kimballモデル: ビジネスプロセスごとにデータマートを構築し、ユーザにとって使いやすいボトムアップアプローチ。ファクトテーブルとディメンションテーブルで構成されるスタースキーマ。
Data Vaultモデル: データのトレーサビリティと監査可能性を重視し、大規模なデータ統合に適した高度なノーマライズ手法。ハブでビジネスキーを格納し、リンクはビジネスキー間の関係を維持し、サテライトはビジネスキーの属性とコンテキストを表す。
8.2.5 Modeling Streaming Data
多くのデータモデリング手法はパッチデータに対しては確立されているが、ストリーミングデータに対しては確立していない。
ソースデータの変化を前提に、スキーマを柔軟にしておく。
8.3 Transformations
クエリはフィルタリングやジョインに基づいて様々なソースからデータを取得する。それに対して、変換は、さらなる変換やクエリで使用するために結果を永続化する。
8.3.1 Batch Transformations
様々なバッチ変換のパターンとテクノロジーについて。
Distributed joins
分散ジョインの基本的な考えは、論理ジョイン(クエリのロジックで定義されたジョイン)をクラスタ内の個々のサーバで実行できる小さなノードジョインに分割するということ。
ブロードキャストジョインは、クエリエンジンが小さなテーブルをすべてのノードに送り、各ノードで大きいテーブルの各部分とジョインする。
シャッフルハッシュジョインは、どちらのテーブルも1つノードに収まらないくらい大きく、ハッシュ関数を使ってジョインキーでノードにシャッフルしたあとに互いにジョインする。
ETL, ELT, and data pipelines
伝統的なETLパターンは、ソースシステムからデータを引き出すレートで制限され、また、ターゲットシステムはストレージ資源とCPUで制限されていた。
ETLの進化系とされるELTは、データウェアハウスの性能とストレージ容量が増大したことによるもので、ソースデータから抽出したデータを最低限の変換でデータウェアハウスにインポートし、データウェアハウスシステムで直接変換を行う。
組織全体でETLやELTを標準化するのではなく、データパイプラインを構築する際にその場合ごとに適切な手法を適用すればいい。
SQL and code-based transformation tools
SQLは宣言的ではあるが、それでも複雑なデータフローを構成できる。SQLの方が簡単で効率的にできることを、わざわざPythonやSparkでやっているエンジニアも多い。
SQLではなくネイティブのSparkやPySparkを使うかどうかを判断するための質問
- SQLでその変換を書くのは難しいか
- できあがったSQLコードの可読性と保守性はどうか
- 変換コードの一部をカスタムライブラリにプッシュして組織全体で再利用すべきか
SQLを再利用する方法は、テーブルへのコミットやビューの作成、dbtの利用。
Sparkを採用する場合、データエンジニアリングチームは長時間かかるジョブに対して、Sparkの最適化問題に積極的に取り組む必要がある。
Sparkで直接コードを書く際に留意すべきこと:
- フィルタリングは早めに頻繁に行おう。
- コアSpark APIを多用し、Sparkらしい書き方を理解しよう。ネイティブのSpark APIで不十分な場合には、よくメンテナンスされたライブラリに頼ろう。
- UDFには気をつけよう。コアAPIやよく整理されたライブラリを使えないか。Pythonに戻さないようにScalaやJavaで書き直して性能を向上できないか。
- SQLとの併用を検討しよう。SQLを使用するとSpark Catalystオプティマイザが使えるので、ネイティブのSparkコードよりも性能を引き出せる可能性がある。
Update patterns
基本的なテーブルの更新パターン
- truncateしてリロード。テーブルの古いデータを削除して再度同じテーブルにロード。
- インサートオンリー。古いレコードを変更も削除もせずに新しいレコードを挿入する。クエリやビューを用いて現在のデータを表示する。
- 削除。ハード削除かソフト削除か。
- アップサートとマージ。キーが一致するレコードがあればそのレコードを更新、なければ新しいレコードを挿入する。マージパターンはこれにレコードを削除する機能を追加する。
Schema updates
スキーマの更新によって下流の変換が壊れてしまうのを避けるためのスキーマ更新プロセスを検討する。
生のJSONをフィールドに格納し、その中の頻繁にアクセスされるデータは別のフィールドにも格納するという手法によって、データの利便性と半構造化データの柔軟性を両立できる。
Data wrangling
データラングリングは、不正なデータを有用できれいなデータに変換する。
グラフィカルなデータラングリングツールは通常、データのサンプルをGUIで表示し、推定されたデータ型、分布、異常データ、ハズレ値、NULL値などの統計情報を表示する。ユーザは不正なデータによる問題を後処理で解決することができる。
MapReduce, After MapReduce
ポストMapReduceは依然としてマップ、シャッフル、リデュースを用いるが、ローカルディスクに書き出す代わりにインメモリキャッシュを使う。
データが利用可能なメモリからあふれると、ディスクへのスピルが発生する。
8.3.2 Materialized Views, Federation, and Query Virtualization
クエリ結果を仮想化するいくつかのテクニック。
Materialized views
ビューは、他のテーブルのデータを参照する仮想テーブル。新しいテーブルを作るのではない。ユーザのデータアクセス権限、インサートオンリーパターンの最新バージョンテーブルなどに使われる。
マテリアライズドビューは、。元のクエリの結果を物理的に保存したテーブル。ビューと違って事前に処理がされている状態になるので高速。元のテーブルからの変更を更新するためにはリフレッシュが必要。
Federated queries
フェデレーテッドクエリ (federated query) とは、OLAPデータベースがオブジェクトストレージやRDBMSなどの外部ソースから先テクできるようにするデータベース機能。
OLAPデータベースがオブジェクトストレージやMySQLなどに対してクエリを発行し、そのデータを組み合わせてOLAPデータベースがクエリの結果を返す。
Data virtualization
データ仮想化は、異なるデータソースを仮想的に統合し、統合されたビューを提供する。TrinoやPrestoなど。
データは内部には保存せず、クエリを実行すると毎回ソースからデータを取り出す。
8.3.3 Streaming Transformations and Processing
ストリーミング変換とストリーミングクエリ。
ストリーミングクエリは、動的に実行され、データの現在の様子を提示する。
ストリーミング変換は、データを下流で消費するための準備をする。
Pulsarは、バッチで使われるDAGをストリーミング抽象化として扱うことで、エンジニアは複数のシステムのフローを管理するのではなく、ストリーミングDAGを探知つシステム内のコードとして定義することができる。
マイクロバッチか真のストリーミングか。DDoS攻撃の検出のためなら真のストリーミングが必要かもしれない。
マイクロバッチは、1秒や2分などの実行間隔で、バッチのフレームワークをストリーミングに適用する。
真のストリーミングは、一度に1つのイベントだけを処理する。これには大きなオーバーヘッドが伴う。
8.4 Whom You’ll Work With
上流の利害関係者は、ビジネス定義をコントロールする人と、データを生成するシステムをコントロールする人。
下流の利害関係者は、データアナリスト、データサイエンティスト、MLエンジニア、ビジネス関係者。
8.5 Undercurrents
セキュリティ: 変換後のデータセットへのアクセス管理をする。認証情報を隠す。
Data Management:変換後の新しいデータセットが利害関係者の期待に合っているか。データがどこから来たのかを理解するためにデータリネージツールは重要。
DataOps: データとシステムの2つの領域が関連してくる。データについては、入力データセットと変換後のデータセットに対してデータ品質テストを実行し、データが上流ユーザと下流ユーザの期待に応えていることを確認する。システムについては、クエリのパフォーマンスを監視しよう。
データアーキテクチャ: 適切にデータを処理・変換できる堅牢なシステムを構築しよう。
オーケストレーション: オーケストレーションツールを使って、依存関係に基づく方法で複雑なパイプラインを管理しよう。
ソフトウェアエンジニアリング: 変換コードを書くときに使われる、SQL、Python、JVMベースの言語やプラッtフォームのベストプラクティスを知っておこう。