Posted inデータベース

TrinoでPostgreSQL・MySQL・MinIOを横断クエリ：マルチソースデータ分析のための統合SQLインターフェース

Database tutorial - IT technology blog
Database tutorial - IT technology blog

データサイロ問題：データがあちこちに散在するとき

半年前、私は混乱した状況を引き継いだ。会社のデータは3つの完全に独立したシステムに分散しており、それらをつなぐ橋は何もなかった。ユーザープロフィールはWebチームが管理するMySQLデータベースに保存されていた。金融取引データはACIDコンプライアンスを重視して選ばれたPostgreSQLクラスターに格納されていた。そして約200GBの生のクリックストリームログが、ParquetファイルとしてMinIOバケットに放置されたまま毎日増え続けていた。

そこにマーケティングチームが一つの質問を持ち込んできた。「サマーキャンペーンのバナーをクリックしたユーザーの総支出額はいくらですか？」

シンプルな質問だ。しかし3つの別々のデータソース。共有インフラは一切なし。

選択肢は3つあったが、どれも良くなかった：

  1. 3つのソースからデータを取得するPythonスクリプトを書き、Pandasでメモリ内で結合し、16GB RAMのサーバーが落ちないことを祈る。
  2. すべてを単一のウェアハウスに統合するAirflow ETLパイプラインを構築する。長期的には堅実なソリューションだが、単発のアドホックな質問のために2〜3日のセットアップ作業が必要だ。
  3. CSVをエクスポートしてExcelで結合する。このアプローチでキャリアが終わった人を何人か見てきた。

データベース横断クエリが本当に難しい理由

従来のデータベースは自己完結型として設計されている。MySQLはPostgreSQLの存在を知らない。どちらもオブジェクトストレージ上のParquetファイルをネイティブに読み込むことはできない。

本当のボトルネックはコンピューティングレイヤーだ。各エンジンは独自の方言を持ち、独自のストレージ形式を使い、独自の実行計画を構築する。これらの境界をまたいでデータを結合するには、ほぼ必ずコンピューティングが存在する場所にデータを移動する必要がある。そこで遅延とコストが積み重なる。

ETL vs. データフェデレーション：適切なツールの選択

決定する前に、3つの一般的なアプローチを評価した：

  • ETL（抽出・変換・ロード）：定期レポートには有効だ。しかしレイテンシが生じる。ライブデータではなく常に昨日のスナップショットをクエリしていることになる。単発の分析のためにパイプラインを構築するのは明らかにやりすぎだ。
  • PostgreSQL外部データラッパー（FDW）：Postgresが既にハブになっている場合は有用だが、複数のリモートソースを同時に結合するとパフォーマンスが著しく低下する。
  • Trino（旧PrestoSQL）：データを一切保存しない分散SQLクエリエンジン。各データソースに直接接続し、必要な行だけを取得し、独自のワーカーメモリ内でJOINを実行する。

Trinoが勝った。ライブデータへのアドホックなマルチソースクエリでは、他のどのアプローチも近づけなかった。MySQL、Postgres、MinIOのすべてのソースを「カタログ」として扱うため、単一のSQL文でまたいでクエリできる。

DockerでのTrino環境セットアップ

Dockerを使えば最速で環境を構築できる。Trino、MySQL、Postgres、MinIO、Hive Metastoreのスタック全体を5分以内に起動できる。

Trinoの設定は/etc/trino/catalog/ディレクトリ以下の.propertiesファイルに格納される。データソースごとに1ファイルだ。

1. カタログ設定ファイル

PostgreSQLコネクター（postgres.properties）：

connector.name=postgresql
connection-url=jdbc:postgresql://postgres-server:5432/finance_db
connection-user=admin
connection-password=secret_pass

MySQLコネクター（mysql.properties）：

connector.name=mysql
connection-url=jdbc:mysql://mysql-server:3306/user_db
connection-user=analyst
connection-password=another_secret

MinIO/S3コネクター（minio.properties）：

TrinoはHiveコネクターを通じてオブジェクトストレージを読み込む。テーブルスキーマとファイルの場所を追跡するために、Hive Metastore（HMS）またはAWS Glueのメタストアが必要だ。ローカル環境ではスタンドアロンのHMSコンテナで十分に機能する。

connector.name=hive
hive.s3.endpoint=http://minio-server:9000
hive.s3.aws-access-key=minio_admin
hive.s3.aws-secret-key=minio_password
hive.s3.path-style-access=true
hive.metastore.uri=thrift://metastore:9083

2. 生データの準備

すべてのデータが既にデータベースに格納されているわけではない。MinIOにファイルをアップロードする前にCSVをJSONに素早く変換するために、toolcraft.app/ja/tools/data/csv-to-jsonを活用している。ブラウザ上で完全に動作し、サーバーには何も送信されないため、機密データを扱う際にも安心だ。TrinoでクエリするためにMinIOに格納する前の小さなデータセットの整理に重宝する。

3つのデータベースに同時に答えるクエリ

カタログを設定してTrinoを起動すれば、クエリはただのSQLだ。新しい構文を覚える必要はない。唯一の違いはcatalog.schema.tableという3段階の命名規則だけだ。

マーケティングチームの依頼に対して実行した実際のクエリはこれだ：

SELECT 
    u.username, 
    u.email, 
    SUM(t.amount) as total_spent,
    l.campaign_id
FROM mysql.user_db.users u
JOIN postgres.finance_db.transactions t ON u.id = t.user_id
JOIN minio.logs.campaign_clicks l ON u.id = l.user_id
WHERE l.campaign_name = 'Summer_2024'
GROUP BY u.username, u.email, l.campaign_id
HAVING SUM(t.amount) > 100
ORDER BY total_spent DESC;

この単一クエリが3つの異なるネットワークプロトコルにアクセスし、3つの異なるストレージ形式を処理し、Trinoの分散メモリ上ですべてを統合する。私たちの環境では約8秒で約4,200行が返ってきた。ETLなし。中間テーブルなし。

パフォーマンス：苦労して学んだこと

Trinoは強力だが、フェデレーテッドクエリには単一ソース環境では現れない障害モードがある。初期にハマったことを紹介しよう。

述語プッシュダウン：実際に機能しているか確認する

TrinoはWHEREフィルターをソースデータベースにプッシュダウンしようとする。うまく機能すれば、MySQLがローカルでフィルタリングを処理し、一致する行のみを返す。機能しない場合、Trinoはまずテーブル全体をネットワーク越しに取得する。5,000万行のテーブルでは、3秒のクエリと45分のクエリの差になる。

遅いクエリには必ず`EXPLAIN ANALYZE`を実行しよう。コネクタースキャン段階の`Input rows`と`Output rows`を確認する。入力行数がテーブル全体のサイズと一致している場合、プッシュダウンが失敗しており、フィルター条件を見直す必要がある。

大規模なクロスカタログJOINはパフォーマンスを急激に低下させる

1億行のPostgresテーブルと1億行のMySQLテーブルを結合するということは、Trinoがネットワーク越しに膨大な量のデータをワーカーノードにシャッフルしなければならないことを意味する。機能はするが、遅い。

実践的な解決策：最大のテーブルは高速な列指向ストレージ（MinIO上のParquet）に保持し、小さいJDBC接続のテーブルには厳格なフィルターを適用する。重い処理はネットワークではなく列指向スキャンに任せよう。

ネットワークレイテンシは見えない乗数だ

Trinoはリアルタイムでデータを取得する。Trinoとデータソースのレイテンシはミリセコンドごとに数百万行にわたって累積する。クエリ対象のデータベースと同じVPCにTrinoをデプロイしよう。AWS us-east-1のTrinoクラスターがシンガポールのコロケーション施設にあるMySQLサーバーと通信するのは、ワーカーノードをいくら追加しても耐えられないほど遅くなる。

結果：データ配管ではなく分析に集中できる

Trino導入前は、時間のほとんどをデータの移動に費やしていた。スクリプトを書き、パイプラインをデバッグし、ETLジョブの完了を待つ。実際の分析はほとんどおまけだった。

その比率が逆転した。PostgreSQL、MySQL、MinIOは今や単一のSQLインターフェース内で対等なカタログとして扱われる。かつてセットアップに丸一日かかっていた質問が、数分で答えられるようになった。データはある場所に留まる。Trinoがコンピューティングをそこに持ち込む。

Share:
Tags: