はじめに
株式会社NTTデータ デジタルサクセスソリューション事業部 の nttd-saitouyun です。
Databricks のリリースノートを振り返っていたところ、2024年7月に、何やらシステムテーブルがたくさんリリースされていました。今回は Query history システムテーブル を触ってみます。
他のテーブルについてはこちらです。
アップデート内容
アップデートは以下でした。
Query history system table is now available (Public Preview)
[July 16, 2024]
Databricks system tables now include a query history table. This table logs detailed records of each query run on a SQL warehouse in your account. To access the table, admins must enable the new query system schema. See Query history system table reference.
SQLウェアハウスで実行されたクエリの詳細が記録されるシステムテーブルがリリースされています。サーバレスSQLウェアハウスの利用が増加しているのでありがたい機能です！
Query history システムテーブル（system.query.history）
テーブルの詳細は以下のページにまとまっています。
実テーブルのイメージは以下の通りです。34カラムもあり情報盛りだくさんです。
以下のような情報を得られます。誰が、いつ、どこで、なんのツールからどのようなクエリを実行したかがわかるだけではなく、カラムの後半にはチューニングに役立つ情報（クエリ履歴から見られる情報）も載っています。
| 情報 | カラム |
|---|---|
| 誰が | executed_by / executed_by_user_id |
| どこで（どのワークスペースで） | workspace_id |
| どこで（どのクラスタで） | compute |
| どこで（どのツールから） | client_application / client_driver |
| いつ | start_time / end_time |
| 何を | statement_text |
クエリ履歴と違いログインしているワークスペース以外の情報も確認できるため、Databricks アカウント全体でクエリの情報を確認できます。
様々なデータパイプラインを管理するデータスチュワードやコスト最適化をしたいシステム管理者にうれしい機能ですね。
利用上の注意点
-
タイムゾーン
日付カラムのタイムゾーンは UTC なので、JST として表示するためには、＋9 時間を忘れないようにしましょう。 -
データの保持期間
データの保持期間は90日です。有用な情報なので保存しておきたいのであれば、パイプラインを作ってアーカイブしておきましょう。
クエリプロファイルを表示する
クエリをチューニングするときに、クエリプロファイルを見たくなると思います。Query history テーブルでチューニングしたいクエリを特定した後に、クエリプロファイルを見る方法を解説します。
クエリ履歴は同じワークスペースのものしか見られないため、該当のワークスペースにログインし直す必要があります。Databricks ワークスペースのURLに含まれる「o=」以下の文字列がワークスペース IDです。
同じワークスペースであることを確認したら、statement_id の値をコピーします。
左のメニューにある「クエリー履歴」を開きます。
ステートメントIDにコピーした値を貼り付けます。
デフォルトで直近7日間のフィルターがかかっているため、クエリが表示されない場合はフィルタの期間を伸ばします。
Query History システムテーブルにも載っていた情報が表示されていますね。（こちらはタイムゾーンが JST で表示されています。）
「クエリプロファイルを表示」をクリックすればプロファイルを開くことができます。
Query history システムテーブル を触ってみる
それでは、Query history システムテーブルを使って色々な情報を見てみます。
まずはワークスペースごとにクエリの数を見てみます。
SELECT
workspace_id,
count(*) AS number_of_queries
FROM system.query.history
GROUP BY workspace_id;
次にワークスペースごとのクエリ数の遷移を見てみます。
SELECT
workspace_id,
MONTH(start_time) AS month,
COUNT(*)
FROM system.query.history
WHERE workspace_id = "<ワークスペースID>"
GROUP BY workspace_id, MONTH(start_time)
ORDER BY workspace_id, month;
続いて、ユーザ別にクエリの実行数を見てみます。
SELECT
executed_by,
count(*)
FROM system.query.history
GROUP BY executed_by ORDER BY count(*) DESC;
各ワークスペースで実行されているクエリの種類（CREATE、INSERT、・・・）の割合を表示してみます。
SELECT
workspace_id,
statement_type,
count(*)
FROM system.query.history
GROUP BY statement_type, workspace_id;
SELECT（緑）、DESCRIBE（水色）が多いです。ワークスペースCはEXPLAINが多いのでクエリのチューニングをしていそうですね。データの利活用が進んでいるワークスペースとそうでないワークスペースでどのような特徴があるか見比べてみるのも面白そうです。
SQLウェアハウスがどのツールで利用されているのか見てみます。
SELECT
workspace_id,
client_driver,
client_application,
count(*)
FROM system.query.history
GROUP BY client_driver, client_application, workspace_id;
Tableau などの外部ツールからの接続もわかります。Databricks 内でもSQL、ノートブック、Genieなど分類されています。利用状況の把握に役立ちそうです。
実行時間の長いクエリを特定してみます。
SELECT
statement_type,
statement_text,
executed_by,
total_duration_ms
FROM system.query.history
ORDER BY total_duration_ms DESC;
パフォーマンスチューニングのヒントになりそうですね。
Query history システムテーブル の情報を提供する
かなり有用なテーブルですが、デフォルトでは管理者のみがアクセスできます。そのため、一般ユーザへデータを共有する場合は、ユーザごと・グループごとに動的ビューを作成することが推奨されています。
自分のクエリのみを表示するビューの作成します。
CREATE VIEW main.default.filtered_query_history AS
SELECT
*
FROM system.query.history
WHERE
CASE
WHEN current_user() == executed_by THEN TRUE
ELSE FALSE
END;
以下の通り、自分が実行したクエリーのみを表示することができました。
おわりに
Query history テーブル とその使い方についてみてきました。
ワークスペース横断でクエリに対する様々な情報を確認することができ、活用のしがいがあるテーブルだなと思いました。ぜひダッシュボードを作ってユーザの利用状況を可視化しましょう！
データの利活用は継続的な活用促進が必要となります。本書がその一助になれば幸いです。
仲間募集
NTTデータ デザイン＆テクノロジーコンサルティング事業本部 では、以下の職種を募集しています。
1. クラウド技術を活用したデータ分析プラットフォームの開発・構築(ITアーキテクト/クラウドエンジニア)
クラウド／プラットフォーム技術の知見に基づき、DWH、BI、ETL領域におけるソリューション開発を推進します。https://enterprise-aiiot.nttdata.com/recruitment/career_sp/cloud_engineer
2. データサイエンス領域（データサイエンティスト／データアナリスト）
データ活用／情報処理／AI／BI／統計学などの情報科学を活用し、よりデータサイエンスの観点から、データ分析プロジェクトのリーダーとしてお客様のDX／デジタルサクセスを推進します。https://enterprise-aiiot.nttdata.com/recruitment/career_sp/datascientist
3.お客様のAI活用の成功を推進するAIサクセスマネージャー
DataRobotをはじめとしたAIソリューションやサービスを使って、 お客様のAIプロジェクトを成功させ、ビジネス価値を創出するための活動を実施し、 お客様内でのAI活用を拡大、NTTデータが提供するAIソリューションの利用継続を推進していただく人材を募集しています。4.DX／デジタルサクセスを推進するデータサイエンティスト《管理職/管理職候補》
データ分析プロジェクトのリーダとして、正確な課題の把握、適切な評価指標の設定、分析計画策定や適切な分析手法や技術の評価・選定といったデータ活用の具現化、高度化を行い分析結果の見える化・お客様の納得感醸成を行うことで、ビジネス成果・価値を出すアクションへとつなげることができるデータサイエンティスト人材を募集しています。ソリューション紹介
Trusted Data Foundationについて
～データ資産を分析活用するための環境をオールインワンで提供するソリューション～https://enterprise-aiiot.nttdata.com/tdf/
最新のクラウド技術を採用して弊社が独自に設計したリファレンスアーキテクチャ（Datalake+DWH+AI/BI）を顧客要件に合わせてカスタマイズして提供します。
可視化、機械学習、DeepLearningなどデータ資産を分析活用するための環境がオールインワンで用意されており、これまでとは別次元の量と質のデータを用いてアジリティ高くDX推進を実現できます。
NTTデータとDatabricksについて
NTTデータは、お客様企業のデジタル変革・DXの成功に向けて、「databricks」のソリューションの提供に加え、情報活用戦略の立案から、AI技術の活用も含めたアナリティクス、分析基盤構築・運用、分析業務のアウトソースまで、ワンストップの支援を提供いたします。TDF-AM（Trusted Data Foundation - Analytics Managed Service）について
～データ活用基盤の段階的な拡張支援（Quick Start) と保守運用のマネジメント（Analytics Managed）をご提供することでお客様のDXを成功に導く、データ活用プラットフォームサービス～https://enterprise-aiiot.nttdata.com/service/tdf/tdf_am
TDF-AMは、データ活用をQuickに始めることができ、データ活用の成熟度に応じて段階的に環境を拡張します。プラットフォームの保守運用はNTTデータが一括で実施し、お客様は成果創出に専念することが可能です。また、日々最新のテクノロジーをキャッチアップし、常に活用しやすい環境を提供します。なお、ご要望に応じて上流のコンサルティングフェーズからAI/BIなどのデータ活用支援に至るまで、End to Endで課題解決に向けて伴走することも可能です。
NTTデータとSnowflakeについて
NTTデータでは、Snowflake Inc.とソリューションパートナー契約を締結し、クラウド・データプラットフォーム「Snowflake」の導入・構築、および活用支援を開始しています。 NTTデータではこれまでも、独自ノウハウに基づき、ビッグデータ・AIなど領域に係る市場競争力のあるさまざまなソリューションパートナーとともにエコシステムを形成し、お客さまのビジネス変革を導いてきました。 Snowflakeは、これら先端テクノロジーとのエコシステムの形成に強みがあり、NTTデータはこれらを組み合わせることでお客さまに最適なインテグレーションをご提供いたします。NTTデータとInformaticaについて
データ連携や処理方式を専門領域として10年以上取り組んできたプロ集団であるNTTデータは、データマネジメント領域でグローバルでの高い評価を得ているInformatica社とパートナーシップを結び、サービス強化を推進しています。NTTデータとTableauについて
ビジュアル分析プラットフォームのTableauと2014年にパートナー契約を締結し、自社の経営ダッシュボード基盤への採用や独自のコンピテンシーセンターの設置などの取り組みを進めてきました。さらに2019年度にはSalesforceとワンストップでのサービスを提供開始するなど、積極的にビジネスを展開しています。これまでPartner of the Year, Japanを4年連続で受賞しており、2021年にはアジア太平洋地域で最もビジネスに貢献したパートナーとして表彰されました。
また、2020年度からは、Tableauを活用したデータ活用促進のコンサルティングや導入サービスの他、AI活用やデータマネジメント整備など、お客さまの企業全体のデータ活用民主化を成功させるためのノウハウ・方法論を体系化した「デジタルサクセス」プログラムを提供開始しています。
NTTデータとAlteryxについて
Alteryxは、業務ユーザーからIT部門まで誰でも使えるセルフサービス分析プラットフォームです。 Alteryx導入の豊富な実績を持つNTTデータは、最高位にあたるAlteryx Premiumパートナーとしてお客さまをご支援します。導入時のプロフェッショナル支援など独自メニューを整備し、特定の業種によらない多くのお客さまに、Alteryxを活用したサービスの強化・拡充を提供します。