この記事は何
TableauのデータソースでSnowflakeをライブ接続で利用し、従来では考えられなかった規模感のデータをTableauで扱うことが増えてきました。しかし、そのような利用が進むと、コスト管理やガバナンスの観点から、各クエリの発生元のワークブックなどをなるべく詳細に把握しておきたくなるケースがあると思います。
そこで、今回は、Snowflakeのクエリ履歴テーブルSNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYにあるQuery Tagの情報を使って「各クエリがTableau Cloudのどのダッシュボードやワークブックから発行されているのか」をQuery IDレベルで分かるようにする方法を紹介します。
TableauのQuery TaggingとSnowflakeのQuery Tag
今回は、Tableauのネイティブ機能であるQuery Tagging機能を活用します。
TableauのQuery Taggingは、Tableau Cloudからデータベース側に発行される各クエリに対して、発行元のワークブック・ダッシュボード・ワークシート※などのメタデータを付与してくれる機能です。
Tableauのワークブック・ダッシュボード・ワークシートとは
- ワークブック：Tableauの作業ファイル。複数のダッシュボードやワークシートを含む。.twbまたは.twbx形式。
- ダッシュボード：複数のワークシートのコレクション。
-
ワークシート：ダッシュボードを構成する単一のビジュアライゼーション。単体でも利用できる。
参考：Tableauヘルプ:ワークブックとシート
SnowflakeのQuery TagはTableauの初期SQLを使ってデータソース単位で付与することも可能ですが、TableauのQuery Taggingを活用すると、ワークシート単位のQuery Tagが自動的に付与されるため、手間なく詳細なメタデータを取得できます。
Tableau Cloud × Snowflakeを利用している場合、Query Taggingはデフォルトで有効化されており、実行要件が満たされていれば、SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYのクエリの一部に以下のようなTableau CloudのLUIDを含むクエリタグを見つけられます。
※ Ask Data, Accelerated Workbooks, 抽出の更新などのクエリは対象外です。
-- ワークシートから発行されるクエリタグ
ALTER SESSION SET QUERY_TAG = $${
"tableau-query-origins": {
"site-luid": "315b7bd0-c6a2-42f8-a5fb-72366c88115c",
"user-luid": "73fe2467-66fd-47bc-907e-fe7033438751",
"workbook-luid": "5c327ef1-da96-4c74-be98-9aae3dce0b29",
"worksheet-luid": "a3359961-3c6e-4ed8-afa4-5aeef73da16f"
}
}$$
-- ダッシュボード上のワークシートから発行されるクエリタグ
ALTER SESSION SET QUERY_TAG = $${
"tableau-query-origins": {
"site-luid": "315b7bd0-c6a2-42f8-a5fb-72366c88115c",
"workbook-luid": "5c327ef1-da96-4c74-be98-9aae3dce0b29",
"dashboard-luid": "a3db65a8-4586-4cad-b492-51bb2e995e39",
"worksheet-luid": "a3359961-3c6e-4ed8-afa4-5aeef73da16f",
"user-luid": "73fe2467-66fd-47bc-907e-fe7033438751"
}
}$$
しかし、このQuery Tagには各リソースのLUIDしか含まれていないため、各リソース名を紐付けられるマスタを作る必要があります。
LUIDからリソース名を紐づける
そこで、LUIDから各種リソースを紐付けられる以下のようなマスタをSnowflake上に作成します。
本記事では、マスタの作成方法として以下の２つを紹介します。環境や制約に合わせて好みの方法を選んでください。
方法１. Tableau Prepを使ってAdmin InsightのSite ContentをSnowflake上に書き込む
Tableau Cloudの管理者が利用可能なAdmin InsightプロジェクトあるSite ContentというデータソースをTableau Prepの外部データベース書き込み機能を用いてSnowflakeに書き込むという方法です。
詳細はこちら
Site Contentは、Tableau Cloud上のコンテンツに関する情報を持つデータソースであり、各ワークシートやダッシュボードのLUIDと名称も保持しており、上記のようなマスタも作成可能です。
Site ContentのデータをSnowflakeに書き込む手順は以下の通りです。
1. Tableau Prepフローの作成
以下の画像のような、Site Contentデータソースに含まれるViewの情報を、SnowflakeのSITE_CONTENT_BASEというテーブルに書き込むPrepフローを作成します。
上記フローは、以下の２つの処理を行っています。
Step1： 不要なフィールドの削除＆列名変更
Admin InsightにあるSite Contentデータソースを読み込みんだあと、今回必要なフィールドのみに絞りつつ、Snowflake上で扱いやすいようにフィールド名を変更します。
| # | Before | After |
|---|---|---|
| 1 | Item LUID | ITEM_LUID |
| 2 | Item ID | ITEM_ID |
| 3 | Item Name | ITEM_NAME |
| 4 | View Workbook ID | WORKBOOK_ID |
| 5 | View Workbook Name | WORKBOOK_NAME |
| 6 | Item Parent Project ID | PROJECT_ID |
| 7 | Item Parent Project Name | PROJECT_NAME |
| 8 | Item Type | ITEM_TYPE |
Step2： ITEM_TYPE="View"と"Workbook" でフィルター
今回欲しい情報はViewとWorkbookのメタデータのため、それ以外の情報は除外します。
2. Tableau Prepフローの実行
Tableau Prepのフローを実行して、成功するとSnowflake上にテーブルSITE_CONTENT_BASEが作成されます。
3. ビューの作成
各Item_LUIDにワークシート・ダッシュボード・ワークブックの情報がひもづく状態になったビューSITE_CONTENT_DATAを作成します。
CREATE OR REPLACE VIEW site_content_data AS
SELECT -- ワークシート、ダッシュボードの情報
item_luid,
item_name,
workbook_id,
workbook_name,
project_id,
project_name
FROM
site_content_base
WHERE
item_type = 'View'
UNION ALL
SELECT -- ワークブックの情報
item_luid,
null AS item_name,
item_id AS workbook_id,
item_name AS workbook_name,
project_id,
project_name
FROM
site_content_base
WHERE
item_type = 'Workbook'
方法２. Tableau Metadata APIにSnowflakeからアクセスする方法
Tableau Cloud上のメタデータをGraphQLを使って取得出来るTableau Metadata APIから必要なデータを取得して、Snowflake上にマスタを作成する方法です。本記事ではSnowflakeのExternal Network Access（記事執筆時点でAWS, AzureはGA）を利用してMetadata APIに接続します。
詳細はこちら
1. Tableau Cloudのパーソナルアクセストークンを取得する
Tableau Metadata APIの認証に必要なパーソナルアクセストークンを取得します。
2. Tableau Cloud用のExternal Accessを作成をする
SnowflakeからTableau Metadata APIにアクセスするためのTableau Cloud東京リージョンへのExternal Network Accessを有効にします。
-- ネットワークルールの作成
CREATE OR REPLACE NETWORK RULE tableau_cloud_nw_rule
TYPE = HOST_PORT,
MODE = EGRESS,
VALUE_LIST = ('prod-apnortheast-a.online.tableau.com'); -- Tableau Cloud東京リージョン
-- パーソナルアクセストークンをシークレットに登録
CREATE OR REPLACE SECRET tableau_cloud_token
TYPE = GENERIC_STRING,
SECRET_STRING = '{
"personalAccessTokenName": "**********",
"personalAccessTokenSecret": "**********",
"site": {"contentUrl": "**********"}
}';
-- External Access Integrationの作成
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION tableau_cloud_api_access_integration
ALLOWED_NETWORK_RULES = (tableau_cloud_nw_rule),
ALLOWED_AUTHENTICATION_SECRETS = (tableau_cloud_token),
ENABLED = true;
3. TableauのViewのメタデータを取得するPython UDFの作成
手順2で作成した外部ネットワークアクセスポリシーを利用し、Metadata APIからGraphQLでデータを取得するPython UDFを作成します。
CREATE OR REPLACE FUNCTION fetch_view_metadata()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'main'
EXTERNAL_ACCESS_INTEGRATIONS = (tableau_cloud_api_access_integration)
PACKAGES = ('requests')
SECRETS = ('cred' = tableau_cloud_token)
AS
$$
import requests
import _snowflake
import json
TABLEAU_CLOUD_URL = "https://prod-apnortheast-a.online.tableau.com/"
API_VERSION = "3.21"
def main():
# Tableau Cloud APIの認証
auth_url = f"{TABLEAU_CLOUD_URL}/api/{API_VERSION}/auth/signin"
auth_creds = _snowflake.get_generic_secret_string('cred')
auth_payload = {"credentials": json.loads(auth_creds)}
auth_headers = {"Content-Type": "application/json", "Accept": "application/json"}
auth_response = requests.post(auth_url, headers=auth_headers, json=auth_payload).json()
auth_token = auth_response['credentials']['token']
# Metadata API Queryの実行
graphql_query = """
query getWorksheetsOnMySite {
views {
luid
name
workbook {
vizportalUrlId
luid
name
projectVizportalUrlId
projectName
}
}
}
"""
query_url = f"{TABLEAU_CLOUD_URL}/api/metadata/graphql"
query_headers = {
"Content-Type": "application/json",
"X-Tableau-Auth": auth_token,
"Accept": "application/json",
}
return requests.post(query_url, headers=query_headers, json={'query': graphql_query}).json()
$$;
4. Python UDFの実行 → 結果の書き込み
Python UDFを実行し、受け取ったJSONをフラット化してテーブルmetadataapi_dataに書き込みます。
CREATE OR REPLACE TABLE metadataapi_data AS
WITH base AS (
-- Metadata APIよりJSONのレスポンスの取得
SELECT fetch_view_metadata() AS response
), json_flatten AS (
SELECT -- JSONのフラット化
f2.value:luid::VARCHAR AS item_luid,
f2.value:name::VARCHAR AS item_name,
f2.value:workbook:luid::VARCHAR AS workbook_luid,
f2.value:workbook:vizportalUrlId::VARCHAR AS workbook_id,
f2.value:workbook:name::VARCHAR AS workbook_name,
f2.value:workbook:projectVizportalUrlId::VARCHAR AS project_id,
f2.value:workbook:projectName::VARCHAR AS project_name
FROM
base,
LATERAL FLATTEN(input => base.response:data) AS f1,
LATERAL FLATTEN(input => f1.value) AS f2
WHERE -- ワークシートのLUIDが取得出来ていないレコードを除外
f2.value:luid::VARCHAR != ''
)
SELECT -- ワークシート、ダッシュボードの情報
item_luid,
item_name,
workbook_id,
workbook_name,
project_id,
project_name
FROM
json_flatten
UNION ALL
SELECT DISTINCT -- ワークブックの情報
workbook_luid AS item_luid,
null AS item_name,
workbook_id,
workbook_name,
project_id,
project_name
FROM
json_flatten;
これでLUIDと名称のマスタの準備は完了です。
ACCOUNT_USAGE.QUERY_HISTORYに紐付ける
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYに上記で作成したマスタを紐づけます。Tableau Cloudのメタデータが紐付くものだけをクエリできる以下のビューを作成します。
CREATE OR REPLACE VIEW query_history_with_tableau_metadata AS
WITH base AS (
-- 結合キー用にworksheet_luid, dashboard_luidをQUERY_TAGより取得
SELECT
TRY_PARSE_JSON(query_tag)['tableau-query-origins']['workbook-luid']::VARCHAR AS workbook_luid,
TRY_PARSE_JSON(query_tag)['tableau-query-origins']['worksheet-luid']::VARCHAR AS worksheet_luid,
TRY_PARSE_JSON(query_tag)['tableau-query-origins']['dashboard-luid']::VARCHAR AS dashboard_luid,
*
FROM
snowflake.account_usage.query_history
WHERE
query_type = 'SELECT'
AND query_tag LIKE '%tableau-query-origins%'
AND start_time >= DATEADD('day', -28, CURRENT_DATE()) -- 任意の期間
)
SELECT
-- QUERY_HISTORYにTableauのメタデータを紐付ける
base.* EXCLUDE (worksheet_luid, dashboard_luid, workbook_luid),
meta_ws.item_name AS worksheet_name,
meta_db.item_name AS dashboard_name,
meta_wb.workbook_id,
meta_wb.workbook_name,
meta_wb.project_id,
meta_wb.project_name
FROM
base
INNER JOIN
metadataapi_data /* または site_content_data */ AS meta_wb
ON base.workbook_luid = meta_wb.item_luid
LEFT JOIN
metadataapi_data /* または site_content_data */ AS meta_ws
ON base.worksheet_luid = meta_ws.item_luid
LEFT JOIN
metadataapi_data /* または site_content_data */ AS meta_db
ON base.dashboard_luid = meta_db.item_luid;
これでとうとう、クエリIDレベルでTableau Cloudのメタデータを紐付けることができました。
クエリする
あとは自由にクエリします。上記のビューはSNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYと同じ構成をしているため、同じ感覚でクエリできます。
例えば、以下のようにワークブック×ウェアハウスごとのクエリ本数と実行時間が確認できます。
SELECT
workbook_id,
workbook_name,
project_id,
project_name,
warehouse_name,
COUNT(1) AS num_of_queries,
SUM(total_elapsed_time) AS total_elapsed_time
FROM
query_history_with_tableau_metadata
GROUP BY ALL
ORDER BY 1
結果はこんな感じです。
Discussion