京都研究所の田中です。 マイクロアドでは、Redash上に作成されたクエリを利用してデータ抽出が行われています。 この記事では、Redashの利用者がクエリの混雑状況などを簡単に確認できるように作成した監視ダッシュボードを紹介します。
背景と目的
Redashから接続しているデータソースのうち、マイクロアドで最もよく利用されているのはImpala(Hadoopクラスタ上で動作する分散SQLクエリエンジン)1です。 RedashからImpalaを利用する体制で運用していく中で、
- クエリの混雑状況:現在実行中のImpalaクエリの一覧や総数
- クエリの進行状況:該当するクエリがいま何%くらい進んでいるか
- ゾンビクエリ:Redashの画面上でキャンセルしたクエリのプロセスが生き残っていないか
などをビジネス側のRedash利用者が自分で確認する方法がないという課題がありました。
エンジニアであればCloudera Manager(Hadoop管理ツール)2や Impala Web User Interface for Debugging 3を使ってこれらを確認できます。 しかし、このようなエンジニア向けのツールをそのままビジネス側のスタッフに開放することは現実的ではありません。 これらのツールの権限管理・運用・セキュリティ上の問題や、画面に表示されている内容のわかりにくさが主な理由です。
そこで、クエリの混雑状況などを簡単に確認できる画面を作成して、ビジネス側へ提供することにしました。
構成要素
クエリの実行状況を取得して可視化するまでを以下のような流れで設計しました。
データソース
Impalaのクエリはデータセンター内に構築されたHadoopクラスタ上で実行されます。このHadoopクラスタから、後述するImpaladサーバーのAPIエンドポイントが提供されています。 実行中のクエリの情報はこのAPIを利用して取得します。
データ収集アプリ
実行中のImpalaクエリの情報を取得し、必要な情報を付与するなどの加工をした上で、ダッシュボード側に書き込みます。
ダッシュボード
用途はクエリの実行状況を確認するだけなので、できるだけ手間を掛けずに開発・運用できるツールが望ましいと考え、スプレッドシートを採用しました。4
データ収集アプリがスプレッドシート内の書き込み用のシートにデータを書き込み、そのデータを別の表示用シートから参照して見た目を整えます。
この表示用シートをRedash利用者にクエリ監視ダッシュボードとして利用者に提供します。
可視化までの流れ
1. Impala Web UIのAPIを利用して、Impalaで実行中のクエリの一覧を取得
Impalaで実行中のクエリの情報は以下のようなAPIエンドポイントから取得できます。5
http://impala‑server‑hostname:25000/queries?json
このAPIを使って、クエリがサブミットされる可能性があるすべてのimpaladサーバーにアクセスし、クエリ情報を取得します。こうすることでImpalaで実行されるすべてのクエリの情報を集約できます。
2. 取得したデータを加工して書き込み用の形式に変換
Impala Web UIのAPIから取得できるデータはjson形式になっています。実行中のクエリに関するデータは in_flight_queries
というキーの中に入っているので、これを抽出します。
実行中のクエリ1つに対応するデータは以下のようになっています。
{ "effective_user": "user_x", "default_db": "xxxxxx_db", "stmt": "select col1, col2 from table_name where col3 = 'xxx' ", "stmt_type": "QUERY", "start_time": "2024-08-06 15:00:00.000000000", "end_time": "1970-01-01 09:00:00.000000000", "duration": "1m1s", "progress": "86 / 803 (10.7098%)", "state": "RUNNING", "rows_fetched": 0, "query_id": "xxxxxxxxxxxx:xxxxxxxxxxx", "last_event": "All 12 execution backends (37 fragment instances) started", "waiting": false, "executing": true, "waiting_time": "1m1s", "resource_pool": "resource_pool_name" }
Impalaのユーザー(effective_user
)、クエリ(stmt
)、経過時間(duration
)、クエリの進行状況(progress
)など、必要な情報は一通り取得できます。
また、Redash(v10.1.0)でImpalaのクエリを実行すると、クエリに自動的に以下のようなコメントが挿入された後でサブミットされます。
/* Username: user_name1@example.com, query_id: 1000, Queue: queries, Job ID: XXXXXXXXXX, Query Hash: YYYYYYYYYYY, Scheduled: False */ select col1, col2 from table_name
このコメントを利用して、クエリの実行者(メールアドレス)、redashクエリのURLを抽出します。クエリのURLは以下のようにクエリIDとRedashサーバーのホスト名を組み合わせて作成します。
(例:query_id: 1000 → URL: https://<redash-host-name>/queries/1000
)
3.閲覧用シートの作成
ダッシュボードとして利用するスプレッドシートを用意し、その中に「書き込み専用のシート」と「閲覧専用のシート」を作成します。 「書き込み専用のシート」は上記のデータ収集アプリが書き込み先として利用します。 「閲覧専用のシート」はquery関数などを利用して「書き込み専用のシート」上のデータを抽出し、罫線やフォントなどの見た目を調整してダッシュボードとして使います。
4.スプレッドシートへデータを書き込む
書き込み先のスプレッドシートの中に作成した「書き込み専用のシート」に加工したデータを書き込みます。
データの書き込みは毎回「書き込み専用のシート」のデータをすべて削除したあとに実行します。 そのためダッシュボードで確認できるクエリの混雑状況は最新のものだけで、過去の状況までは表示されません。 ただし、スプレッドシートは変更履歴が確認できるため、過去分のデータをデータベースなどに蓄積しなくても簡単な確認が可能です。
完成図
こうして完成したクエリ監視用ダッシュボードは下図のようになります。
- 実行中のクエリの総数
- キューに入っているクエリの総数
- 実行中/キューに入っている各クエリの詳細
を表示しています。 テーブル形式の簡素なものですが、実行中のImpalaクエリすべての状況を確認するには十分ではないかと考えています。
実際に使用しているスプレッドシートでは、Redash以外のインターフェース6から実行されたクエリもすべて確認できるようにしています。 そのため表示用のシートを下図のように複数用意し、利用者の属性ごとに使い分けています。
今後に向けて
今回紹介したダッシュボードを提供することで、 クエリの混雑状況やゾンビクエリの発生状況をビジネス側の利用者が自分で確認できるようになりました。これにより、
- 混雑していないタイミングを選んでクエリを実行する
- ゾンビクエリ(redashのUI上でキャンセルしたのに残ってしまったプロセス)を発生させたらすぐに管理者に連絡して停止してもらう
- クエリの実行時間が普段より長いときに、単に実行中のクエリが多く混雑しているためなのか、データの不備やクエリのミスなど他に原因がありそうかを推測しやすくする
といった効果が期待されます。
とはいえ、この手のツールは作って社内に公開するだけでは十分な効果が見込めません。 社内のRedash利用者にこのダッシュボードの存在を認知してもらい、見方を知ってもらい、役に立つことを実感してもらう必要があります。
その際に役立ちそうなのが、Googleスプレッドシートに付属する「アクティビティダッシュボード」という簡易なアクセス解析ツールです。 ここから利用者の傾向を探るなどして、今後の運用改善につなげようと考えています。
- Introducing Apache Impala↩
- Cloudera Manager↩
- Impala Web User Interface for Debugging↩
- 情報を表示する画面アプリの開発や、そのアプリを動かすサーバーの準備・運用も不要になるので、開発工数や運用コストを下げられます。↩
- Impala Web User Interface for Debugging > Queries Page↩
- Hue、impala-shell、バッチ処理システムなどがあります↩