Snowflake×Tableau：クエリ履歴にダッシュボードのメタデータを紐付ける

この記事は何

TableauのデータソースでSnowflakeをライブ接続で利用し、従来では考えられなかった規模感のデータをTableauで扱うことが増えてきました。しかし、そのような利用が進むと、コスト管理やガバナンスの観点から、各クエリの発生元のワークブックなどをなるべく詳細に把握しておきたくなるケースがあると思います。

そこで、今回は、Snowfalkeのクエリ履歴テーブル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と名称も保持しており、上記のようなマスタも作成可能です。

この方法は、以下の考慮事項もあります。これらが課題になる場合は、後述のMetadataAPIを使った方法を検討しましょう。

1. Tableau Data Managementが利用出来ない場合、上記で作成したPrepフローのスケジューリング実行が出来ない点。
2. Admin Insightのデータ更新は毎週又は毎日であり、任意のタイミングでメタデータの更新ができないこと。

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

結果はこんな感じです。