なぜ SQL Server から ClickHouse にデータをストリーミングするのか？
- 本番アプリに負荷をかけない社内レポート
- 高速で、常に最新の状態を保つ必要がある顧客向けダッシュボード
- 分析のためにユーザーアクティビティログを常に最新に保つようなイベントストリーミング
始める前に必要なもの
前提条件
- 稼働中の SQL Server インスタンス
- このチュートリアルでは AWS RDS for SQL Server を使用しますが、最新の SQL Server インスタンスであれば問題なく使用できます。AWS SQL Server をゼロからセットアップする
- ClickHouse インスタンス
- セルフホストまたはクラウドに対応しています。ClickHouse をゼロからセットアップする
- Streamkap
- このツールは、データストリーミングパイプラインの中核を担います。
接続情報
- SQL Server のサーバーアドレス、ポート、ユーザー名、パスワード。Streamkap が SQL Server データベースにアクセスするための専用のユーザーとロールを作成することを推奨します。設定については、こちらのドキュメントをご覧ください。
- ClickHouse server のアドレス、ポート、ユーザー名、パスワード。ClickHouse の IP Access List では、どのサービスが ClickHouse データベースに接続できるかを制御します。こちらの手順に従ってください。
- ストリーミングしたいテーブル。まずは 1 つから始めてください
1
Streamkap で SQL Server のソースを作成する
それでは始めましょう。まずはソース接続を設定します。これにより、Streamkap はどこから変更を取得すればよいかを把握できます。手順は次のとおりです。
- Streamkap を開き、SOURCES セクションに移動します。
- 新しいソースを作成します。
- 識別しやすい名前を付けます (例: sqlserver-demo-source) 。
- SQL Server の接続情報を入力します。
- ホスト (例: your-db-instance.rds.amazonaws.com)
- ポート (SQL Server のデフォルトは 3306)
- ユーザー名とパスワード
- データベース名
バックグラウンドで行われること
この設定を行うと、Streamkap は SQL Server に接続してテーブルを検出します。このデモでは、events や transactions のように、すでにデータがストリーミングされているテーブルを選びます。2
StreamkapでClickHouseの宛先を追加する
それでは、このデータをすべて送信する宛先を設定しましょう。ソース側と同様に、ClickHouse の接続情報を使って宛先を作成します。
手順:
- Streamkap の destinations セクションに移動します。
- 新しい宛先を追加し、宛先タイプとして ClickHouse を選択します。
- ClickHouse の情報を入力します:
- ホスト
- ポート (デフォルトは 9000)
- ユーザー名とパスワード
- データベース名
Upsert モード: これは何ですか?
これは重要なステップです。ここでは ClickHouse の「upsert」モードを使用します。これは内部的に ClickHouse の ReplacingMergeTree engine を利用するものです。これにより、受信レコードを効率よくマージし、ClickHouse でいう「パーツマージ」によって、取り込み後の更新にも対応できます。- これにより、SQL Server 側で変更があった場合でも、宛先テーブルが重複データで埋まるのを防げます。
スキーマ進化への対応
ClickHouse と SQL Server では、カラムが完全には一致しないことがあります。特に、アプリが本番稼働中で、開発者がその場でカラムを追加していくような場合はなおさらです。- 朗報です。Streamkap は基本的なスキーマ進化に対応しています。つまり、SQL Server で新しいカラムを追加すると、ClickHouse 側にもそのカラムが反映されます。
3
Streamkapでパイプラインを設定する
ソースと宛先の設定が完了したら、いよいよ楽しい部分、データをストリーミングする段階です。
パイプラインの設定
- Streamkap の Pipelines タブに移動します。
- 新しいパイプラインを作成します。
- SQL Server のソース (sqlserver-demo-source) を選択します。
- ClickHouse の宛先 (clickhouse-tutorial-destination) を選択します。
-
ストリーミングしたいテーブルを選択します。ここでは
eventsとします。 - CDC (変更データキャプチャ) 用に設定します。
- 今回は新しいデータをストリーミングします (最初はバックフィルを省略し、CDC イベントに集中するとよいでしょう) 。
バックフィルは必要ですか？
「古いデータもバックフィルしたほうがよいのだろうか？」と思うかもしれません。多くの分析用途では、まずはこれ以降の変更をストリーミングするだけで十分ですが、必要になれば後から古いデータを読み込むこともできます。明確な必要がない限り、ひとまず「バックフィルしない」を選んでください。4
データストリームを確認する
これでパイプラインの設定が完了し、稼働を開始しました。動作は次のとおりです。高負荷時には多少の遅延が発生する場合がありますが、ほとんどのユースケースではほぼリアルタイムでストリーミングされます。
- SQL Server のsource tableに新しいデータが入ると、Streamkap パイプラインがその変更をキャプチャして ClickHouse に送信します。
- ClickHouse は、ReplacingMergeTree とパーツマージによってこれらの行を取り込み、更新をマージします。
- スキーマも自動で追随します。SQL Server でカラムを追加すると、ClickHouse にも反映されます。
内部では何が起きているのか: Streamkap は実際に何をしているのか？
- Streamkap は SQL Server のバイナリログ (レプリケーションにも使われるログ) を監視します。
- テーブルで行が挿入、更新、または削除されると、Streamkap はそのイベントを即座に検知します。
- そのイベントを ClickHouse が理解できる形式に変換して送り込み、分析 DB に変更を即時反映します。
詳細オプション
Upsert モードと Insert モード
- Insert モード: 新しい行はすべて追加されるため、更新であっても重複が発生します。
- Upsert モード: 既存の行への更新はその内容を上書きするため、分析データを常に最新かつ整った状態に保つのに適しています。
スキーマ変更への対応
- 運用テーブルに新しいカラムを追加した場合は？ Streamkap がそれを検出し、ClickHouse 側にも追加します。
- カラムを削除した場合は？ 設定によっては移行が必要になることもありますが、追加のほとんどはスムーズに反映されます。
実運用での監視: パイプラインの状態を把握する
パイプラインの状態を確認する
- パイプラインの遅延を確認する (データはどの程度新しいか)
- 行数とスループットを監視する
- 何か異常があればアラートを受け取る
確認すべき主要なメトリクス
- 遅延: ClickHouse が SQL Server に対してどれくらい遅れているか
- スループット: 1 秒あたりの行数
- エラー率: ほぼゼロであるべき
本番運用: ClickHouse のクエリ
次のステップと詳細ガイド
- フィルタリングしたストリームの設定 (一部のテーブル/カラムのみを同期)
- 複数のソースを1つの分析用DBにストリーミング
- これをS3/データレイクと組み合わせてコールドストレージに活用
- テーブル変更時のスキーマ移行の自動化
- SSLとファイアウォールルールによるパイプラインの保護
よくある質問とトラブルシューティング
まとめ
- upsert と insert の違いと、それぞれの細かなポイント
- エンドツーエンドの latency: 最終的な分析ビューをどれだけ速く得られるか
- パフォーマンスチューニングと スループット
- このスタック上での実運用ダッシュボード