Top 10 Queries to use with System Tables - Databricks Community - 82331の翻訳です。
本書は著者が手動で翻訳したものであり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
DatabricksのUnity Catalogは、企業が任意のクラウドやプラットフォームで保有する構造化、非構造化データ、MLモデル、ノートブック、ダッシュボード、ファイルをシームレスに統治するためのAIを活用したツールを提供することで、データインテリジェンスプラットフォームにおけるデータガバナンスに革命をもたらしました。ここで重要になるのは、Unity CatalogによってDelta Lakeとして格納され、データインテリジェンスプラットフォームにおける観測可能性の基盤として動作するシステムテーブルであり、重要なビジネス上の問い合わせにおける大規模なオペレーショナルインテリジェンスを促進します。ここでは、お使いのデータインテリジェンスプラットフォームのステータスと健康監視に関するトップ10の重要な質問に回答するために活用できる一連のクエリーを提供します。
データインテリジェンスプラットフォームにおける観測可能性
ユーザーが必要とするものを監視できるように、Databricksにおいて透明性のあるメトリクスを提供したいと我々は常に考えてきています。観測可能性をサポートするために、様々なツールの開発を継続しており、様々な製品に対してさらなる選択肢を追加しました。これは、理想的なものではありませんでしたし、意図しない複雑性をもたらすことになりました。
例えば、クラスターの監視とプラットフォームで利用できる計算リソースを監視する必要があるデータエンジニアのことを考えてみましょう。2022年までは、彼らはPNG画像のグラフとしてしか過去のCPU利用に関するデータを利用できないGangliaを使わなくてはなりませんでした。一方で、その他のデータは通常はクラウドサービスプロバイダーのストレージアカウントにダンプされるJSONやCSVフォーマットで利用できるようになっていました。これは、円滑なユーザー体験を提供するものではありませんでした。
Unity Catalogが追加されることで、これを改善する可能性を見出し、監視/観測可能性に関するデータを一貫性がありシンプルな方法でアクセスできるようにしました - システムテーブルです。
システムテーブルによって、すべての監視/観測可能性に関するデータは、事前定義かつ適切に文書化されたスキーマ(AWS、Azure、GCP)を用いて、Unity CatalogにおけるDeltaテーブルとして定義されます。有用な洞察を得るために、誰でもノートブックやSQLエディタを用いてDatabricksで直接データを探索、集計することができます。さらに、LakeviewダッシュボードやPowerBIのようなお好きなBIツールを用いて、ビジネスユーザーの手で容易にダッシュボードを作成することができます。
例えば、こちらのブログ記事にはコスト分析のためのPowerBIテンプレートが含まれています。
Unity Catalogにおけるシステムテーブル
システムテーブルは、Unity Catalogの**<>**カタログの配下で利用できる、あなたのアカウントのオペレーショナルデータの分析用のリポジトリとして動作します。これらは、あなたのアカウントにおける履歴的な観測可能性の手段を提供し、オペレーションのアクティビティに対する振り返りの分析を可能にします。お使いのデータインテリジェンスプラットフォームの様々な側面を監視するために数多くのテーブルを利用することができます: コスト、監査とアクセス、使用量、最適化などがあり、新たなリリースとともに四半期ごとにリストに追加されて行っています。これらすべては、以下の画像のようにスキーマとして整理されています。
システムテーブルのクエリートップ10
理解しやすい方法でデータを利用できるようになったので、皆様によっては、数多くの観測の可能性の扉が開いたことになります。この記事では、システムテーブルによって回答できる10個の重要なクエリーであると我々が信じるものをハイライトします。以下では、3つのカテゴリーに分割された一連の質問を見つけることができ、解答に必要なクエリーも対応付けられているので、お使いのデータインテリジェンスプラットフォームで、すぐにシステムテーブルの活用を開始することができます。
リソース監視
システムテーブルによるリソース監視によって、計算資源の分析や最適化、性能のでていないクエリーの特定など、お使いのDatabricksワークスペースにおけるオペレーションに関する深い洞察を提供します。
以下の例では、DB SQLのウェアハウスにおけるクエリーのリソース監視にフォーカスしています。他のタイプのコンピュートにも類似のクエリーを適用することができます。(queryテーブルのように)テーブルのいくつかは、執筆時点でパブリックプレビューであることに注意してください。これらは間もなくアクティベートされるはずです。
クエリー1: リソース監視 - アクティブに稼働しているDB SQLウェアハウスと稼働時間
このクエリーは、現時点でアクティブなウェアハウスとその稼働時間を理解する助けになります。
利用パターンの理解に管理者が苦慮しており、リソース利用を効率的に保つことが困難になっているような大規模な組織では、特に有用なものとなります。このクエリーによって、現在の使用量の概要を把握し、クラスターポリシーに対して適切な意思決定を行うことをサポートします。
SELECT
we.workspace_id,
we.warehouse_id,
we.event_time,
TIMESTAMPDIFF(MINUTE, we.event_time, CURRENT_TIMESTAMP()) / 60.0 AS running_hours,
we.cluster_count
FROM
system.compute.warehouse_events we
WHERE
we.event_type = 'RUNNING'
AND NOT EXISTS (
SELECT 1
FROM system.compute.warehouse_events we2
WHERE we2.warehouse_id = we.warehouse_id
AND we2.event_time > we.event_time
)
アウトプットのサンプルは以下のようになります。
アラートの機会: DBSQL管理者として、期待しているよりも長い時間稼働しているウェアハウスがある場合にはアラートを出したいと考えるかもしれません。稼働時間が特定の閾値を超えた際に、アラートをトリガーするようなアラート条件を設定するために、このクエリー結果を活用することができます。これによって、管理者は現在ウェアハウスが使用されているのか、されていないのか、手動でシャットダウンできるのかを理解することができます。新たなユーザーがクエリーを行った際はいつでも、サーバレスウェアハウスが数秒で起動します。
クエリー2: リソース監視 - 期待したよりも長い時間助ーリウアップされたウェアハウス
このクエリーでは、スケールアップ(クラスター数を増加)して、通常よりもその状態を保っているウェアハウスを特定します。
これによって、管理者は理想的でない使用パターンを特定し、最大クラスター数を削減するといったアクションを取れるようになります。
SELECT
we.workspace_id,
we.warehouse_id,
we.event_time,
TIMESTAMPDIFF(MINUTE, we.event_time, CURRENT_TIMESTAMP()) / 60.0 AS upscaled_hours,
we.cluster_count
FROM
system.compute.warehouse_events we
WHERE
we.event_type = 'SCALED_UP'
AND we.cluster_count >= 2
AND NOT EXISTS (
SELECT 1
FROM system.compute.warehouse_events we2
WHERE we2.warehouse_id = we.warehouse_id
AND (
(we2.event_type = 'SCALED_DOWN') OR
(we2.event_type = 'SCALED_UP' AND we2.cluster_count < 2)
)
AND we2.event_time > we.event_time
)
サンプルアウトプットは以下のようになります。
アラートの機会: この条件に基づくアラートによって、リソースとコストの監視の助けとなります。アップスケールしている時間が特定のリミットを超えた際にアラートを行うように設定をすることができます。
クエリー3: パフォーマンス改善のためにウェアハウスのTシャツサイズをいつ増加させるのか
このクエリーは、ウェアハウスのTシャツサイズを増加させる必要あることを示すベストなインジケーターであるディスクの溢れを検索します。頻繁にウェアハウスでディスク溢れを確認するようになったら、ウェアハウスにおける高速なメモリーが満杯となっており、ウェアハウスがディスクに書き出さなくてはならない(遅くて非効率的です)ことを意味しています。これらのケースでマシンサイズを増加させることで、クラスターの稼働時間を削減しつつも、クエリーのパフォーマンスの改善、ユーザー体験の改善につながります。
SELECT
workspace_id,
compute.warehouse_id,
COUNT(*) as frequency,
MAX(spilled_local_bytes) AS spilled_bytes
FROM system.`query`.`history`
WHERE start_time between DATE_SUB(CURRENT_DATE, 30) AND CURRENT_DATE
AND spilled_local_bytes > 0
GROUP BY workspace_id, compute.warehouse_id
HAVING spilled_bytes > 0
ORDER BY frequency DESC;
サンプルアウトプットは以下のようになります。
クエリー4: 効率の悪いクエリーの容疑者
膨大なシャッフルを行っているクエリーは、記述されたクエリーやテーブルの構造における非効率を探し出すには良い場所といえます。ユーザーがどのクエリーが非効率的なのかをしれば、修正して改善することができます。クエリーを効率的にすることは、計算時間の削減やウェアハウスのTシャツサイズの引き下げにつながり、コストの削減や最終的なユーザーにおけるパフォーマンスの改善につながります。
以下のクエリーは、過去30日間において最も非効率的な可能性があるクエリーを表示します。
SELECT
workspace_id,
compute.warehouse_id,
statement_id,
statement_text,
SUM(shuffle_read_bytes) AS shuffle_read_bytes
FROM system.query.history
WHERE start_time between DATE_SUB(CURRENT_DATE, 30) AND CURRENT_DATE
GROUP BY workspace_id, compute.warehouse_id, statement_id, statement_text
HAVING shuffle_read_bytes > 0
ORDER BY shuffle_read_bytes DESC;
サンプルアウトプットは以下のようになります。
コスト管理
コストの監視は、データプラットフォームの重要な側面となります。お客様は、見積もりと予算の観点で、現在の消費が予定通りのものであることを確実にしたいと考えます。システムテーブルを用いることで、これを簡単に達成することができ、現在の消費に対する完全なビューをFinOpsチームに提供します。Unity Catalogでの数クリックで専用のテーブルへのアクセスをユーザーに許可することで、ユーザーが独立してオペレーションを提供することすらも可能で、彼ら自身で分析を行うこともできます。
現在の消費を追跡することができるシステムテーブルは、billingスキーマで利用することができます。執筆時点では2つのテーブルを利用できます: usageとlist_pricesです。
- usageによって、ワークスペースが存在するすべてのリージョンにおけるあなたのアカウントの全体的な使用量を参照できます。
- list_pricesは、SKUごとのDatabricksの値付けの履歴にアクセスすることができます。SKU価格に変更があるごとにレコードが追加されます。
billingシステムテーブルで問い合わせることができる最も一般的な質問とはどのようなものでしょうか？
クエリー5: 様々なDatabricks製品の日々の消費は？
このクエリーは、日次ベースでのDBU消費とSKUごとに異なるリストプライスにおける$DBUを特定します。
管理者は自身のプラットフォームでユーザーがどのように利用しているのかを理解できるようになります。先月DWHのマイグレーションがライブに移行した場合には、SQLやETLのDBUがスパイクすると予想するでしょうし、機械学習のコストでスパイクがあった場合には調査を行うべきかもしれません。
SELECT
u.usage_date,
u.sku_name,
SUM(u.usage_quantity) AS dbus,
FIRST(lp.pricing.default) AS list_price,
SUM(u.usage_quantity) * FIRST(lp.pricing.default) AS dollar_dbus_list
FROM system.billing.usage u
INNER JOIN system.billing.list_prices lp ON u.sku_name = lp.sku_name
AND u.usage_start_time >= lp.price_start_time
AND (
u.usage_end_time <= lp.price_end_time
or lp.price_end_time is null
)
GROUP BY u.usage_date, u.sku_name
ORDER BY u.usage_date, u.sku_name DESC
クエリー6: 月次、ワークスペースごとのDatabricks製品の消費は？
このクエリーは、すべてのワークスペースごとの月次のDBU消費とリストプライスにおける$DBUを特定します。
このクエリーは、ワークスペースごとの集計を伴う、上のものよりもより優れたバージョンとなります。
SELECT
LEFT(u.usage_date, 7) AS year_month,
u.workspace_id,
u.sku_name,
SUM(u.usage_quantity) AS dbus,
FIRST(lp.pricing.default) AS list_price,
SUM(u.usage_quantity) * FIRST(lp.pricing.default) AS dollar_dbus_list
FROM
system.billing.usage u
INNER JOIN system.billing.list_prices lp ON u.sku_name = lp.sku_name
AND u.usage_start_time >= lp.price_start_time
AND ( u.usage_end_time <= lp.price_end_time OR lp.price_end_time is null)
GROUP BY u.workspace_id, LEFT(u.usage_date, 7), u.sku_name
ORDER BY year_month, u.workspace_id DESC
クエリー7: ジョブID 652895398180890 のパイプラインの月間コストは？
このクエリーは、特定のDatabricksワークフローによるDBUと$DBUの月間消費を特定します。
管理者は、最適化のためにいくつか思いパイプラインを確認するためにデータエンジニアにエンゲージするかもしれません。
SELECT
LEFT(u.usage_date, 7) AS year_month,
SUM(u.usage_quantity) AS dbus,
FIRST(lp.pricing.default) AS list_price,
SUM(u.usage_quantity) * FIRST(lp.pricing.default) AS dollar_dbus_list
FROM
system.billing.usage u
INNER JOIN system.billing.list_prices lp ON u.sku_name = lp.sku_name
AND u.usage_start_time >= lp.price_start_time
AND (
u.usage_end_time <= lp.price_end_time OR lp.price_end_time is null
)
WHERE usage_metadata.job_id = '652895398180890'
GROUP BY LEFT(u.usage_date, 7)
ORDER BY LEFT(u.usage_date, 7) DESC
監査
情報システムにおいて監査は重要なものであり、機微なビジネス情報を取り扱うデータプラットフォームにおいては間違いなく重要なものとなります。コンプライアンス、セキュリティ、説明責任、インシデント対応など、これが当てはまるのかの理由は複数存在します。Unity Catalogでは、accessカタログ配下で利用できるauditシステムテーブルを通じて、お使いのデータの移動、変換、公開のような網羅的なオペレーションの一覧を提供します。
世界中のお客様において、accessシステムテーブルで回答される最も一般的な質問はどのようなものでしょうか？
クエリー8: ユーザー email@databricks.com によってアクセスされるトップ10のテーブルは？
このクエリーでは、ユーザー email@databricks.com によってアクセスされるテーブルのトップ10を特定します。管理者は必要であれば、クエリーを最適化するのをサポートするためにこのようなユーザーにコンタクトするかもしれません。
ネイティブのUnity Catalogの権限に沿って、システムテーブルへのアクセスを制限することができます。メタストア管理者は、特定のユーザーにのみアクセスを制限することができます。あるいは、クエリーを行うユーザーのロールに基づいて、動的なアクセスを提供するビューを作成することができます。これは、メールアドレスのようなPIIデータが結果の一部として表示されるが、企業の誰でもアクセスして良いわけではないようなシチュエーションで有用となります。例えば、管理者がクエリーを実行した際にはメールアドレスはそのまま表示されますが、それ以外の場合にはハッシュを返却するか、完全にカラムが非表示となります。
SELECT
IFNULL(request_params.full_name_arg, 'Non-specific') AS `TABLE ACCESSED`
FROM system.access.audit
WHERE user_identity.email = 'email@databricks.com'
AND action_name IN ('commandSubmit','getTable')
GROUP BY IFNULL(request_params.full_name_arg, 'Non-specific')
ORDER BY COUNT(*) DESC
LIMIT 10
クエリー9: catalog.schema.tableにアクセスするユーザーのトップ10は？
このクエリーは、テーブルcatalog.schema.tableにアクセスするトップ10のユーザーを特定します。
SELECT
COUNT(*),
user_identity.email
FROM
system.access.audit
WHERE
action_name IN ('commandSubmit', 'getTable')
AND request_params.full_name_arg = 'catalog.schema.table'
GROUP BY user_identity.email
ORDER BY COUNT(*) DESC
LIMIT 10
クエリー10: 過去24時間でこのユーザーは何にアクセスしたのか？
このクエリーは、ユーザー email@databricks.com が過去24時間にどのテーブルにアクセスしたのかを特定します。
SELECT
IFNULL(request_params.full_name_arg, 'Non-specific') AS `table_accessed`
FROM system.access.audit
WHERE user_identity.email = 'email@databricks.com'
AND action_name IN ('commandSubmit','getTable')
AND event_time >= NOW() - '1 day'::INTERVAL
GROUP BY IFNULL(request_params.full_name_arg, 'Non-specific')
ORDER BY COUNT(*) DESC
まとめ
まとめると、DatabricksのUnity Catalogは、企業が任意のクラウドやプラットフォームで保有する構造化、非構造化データ、MLモデル、ノートブック、ダッシュボード、ファイルをシームレスに統治するためのAIを活用したツールを提供することで、データインテリジェンスプラットフォームにおけるデータガバナンスを劇的に強化します。この能力の中央にあるのが、Delta Lakeとして格納、統治され、データインテリジェンスプラットフォームにおける観測可能性の基盤として動作するシステムテーブルであり、プラットフォームの観測可能性の堅牢な基盤を提供し、スケーラブルなオペレーショナルインテリジェンスを可能にします。このインフラストラクチャは、企業のデータプラットフォームのステータスや健康状態に関する重要な問い合わせに対応するために、企業を支援します。包括的な一連のクエリーを活用することで、企業は自身のデータインテリジェンスプラットフォームを効果的に監視、分析することができ、最適なパフォーマンスと戦略的な意思決定を確実にすることができます。