Google CloudがBigQueryの横断SQL例を紹介、例の環境で3クラウドのデータ結合が可能

BigQueryのクロスクラウド分析の例

BigQueryでは、federated Iceberg tablesがネイティブのテーブルのように振る舞います。そのため、後段の分析機能も、federated Iceberg tablesへのアクセスをそのまま引き継ぐ仕組みです。^[1]

図1　例の環境で示されたクロスクラウド分析

例の環境では、BigQueryにある販売の取引データと、SnowflakeやDatabricks、AWS Glueのデータを1つのSQLで結合しています。BigQueryは、これらの結合をその場で処理します。

conversational analytics in BigQueryは、自然言語の指示を連携クエリへ変換する機能です。連携クエリはSnowflake、Databricks、AWS Glueにまたがり、連携先のスキーマに向ければクロスクラウドのデータを自然言語で探索できます。

BigQueryで横断分析するSQLの例

例のSQLは、場所にus-central1を指定し、存在しない場合にlocal_retail_datasetスキーマを作成します。続いて、my-lakehouse-project.local_retail_dataset.fact_salesテーブルを作成または置換する定義です。^[1]

図2　例の環境における横断分析の要素

続くCREATE OR REPLACE TABLEの対象は、my-lakehouse-project.local_retail_dataset.fact_salesです。fact_salesの11列とデータ型を、以下に示します。

fact_salesの列とデータ型

fact_salesは、sale_dateでパーティション分割する定義でした。クラスタ化には、store_keyとproduct_keyの2列をこの順で指定しています。

横断SQLで結合する例

federated Iceberg tablesは、project.catalog.namespace.tableの4つの部分からなるパスで指定する形式です。例のSQLは、fact_salesにSnowflakeの顧客やDatabricksの製品、AWS Glueの店舗の各テーブルを結合します。

図3　例のSQLが結合するテーブル

SnowflakeやDatabricks、AWS Glueのテーブルを結合し、売上の多い組み合わせを集計するSQLは、以下の通りです。

SELECT 
    c.first_name || ' ' || c.last_name AS customer_name, -- From Snowflake Iceberg
    p.product_name,                                      -- From Databricks Iceberg
    s.store_name,                                        -- From AWS S3 Iceberg
    SUM(f.quantity) AS total_units_sold,                 -- From BigQuery native fact
    SUM(f.total_revenue) AS total_sales_revenue          -- From BigQuery native fact
FROM `my-lakehouse-project.local_retail_dataset.fact_sales` f
INNER JOIN `my-lakehouse-project.sf_retail_db.CORE.DIM_CUSTOMERS` c 
  ON f.customer_key = c.customer_key
INNER JOIN `my-lakehouse-project.dbx_retail_db.dbx_core.dim_products` p 
  ON f.product_key = p.product_key
INNER JOIN `my-lakehouse-project.glue_retail_db.glue_retail_db.dim_stores` s 
  ON f.store_key = s.store_key
GROUP BY 1, 2, 3
ORDER BY total_sales_revenue DESC
LIMIT 10;

公式資料からの抜粋：Cross-cloud conversational analytics made simple with open standards（Run one query across three clouds）。対象版・範囲：固定資料の記述（版・提供状態の記載なし）。

上記のコードでは、顧客名や製品名、店舗名の組み合わせごとに販売数量と売上を合計し、売上の多い順に10件を返します。この例では、Snowflakeの識別子がCORE.DIM_CUSTOMERSのように大文字です。

一方、DatabricksとAWS Glueのテーブルパスは小文字のまま保たれます。SnowflakeやDatabricks、AWS Glueのテーブルを1つのSQLで扱う場合は、連携先ごとにパスの表記を確かめてから書く進め方を、編集部としては勧めます。

論理ビューと自然言語の質問例

この例では、conversational analyticsに運用上の文脈を十分に渡すため、横断結合を論理ビューとして保存する流れです。質問の例は、総売上の多い顧客と、その顧客が買った製品や利用した店舗を尋ねるものです。

図4　conversational analyticsの処理の流れ

この質問を受けたconversational analyticsは、3つのリモートカタログのスキーマを評価し、SQL文を生成します。続けてクエリを実行し、回答を示します。

例の環境で、横断結合を論理ビューとして保存するSQLは、以下の通りです。

CREATE OR REPLACE VIEW `my-lakehouse-project.local_retail_dataset.v_omnichannel_sales_pulse` AS
SELECT 
    f.sale_id,
    f.sale_date,
    c.first_name || ' ' || c.last_name AS customer_name,
    c.country AS customer_country,
    p.product_name,
    p.category AS product_category,
    p.brand AS product_brand,
    s.store_name,
    s.city AS store_city,
    s.region AS store_region,
    f.quantity,
    f.total_revenue,
    f.margin
FROM `my-lakehouse-project.local_retail_dataset.fact_sales` f
INNER JOIN `my-lakehouse-project.sf_retail_db.CORE.DIM_CUSTOMERS` c 
  ON f.customer_key = c.customer_key
INNER JOIN `my-lakehouse-project.dbx_retail_db.dbx_core.dim_products` p 
  ON f.product_key = p.product_key
INNER JOIN `my-lakehouse-project.glue_retail_db.glue_retail_db.dim_stores` s 
  ON f.store_key = s.store_key;

公式資料からの抜粋：Cross-cloud conversational analytics made simple with open standards（Explore cross-cloud data with natural language）。対象版・範囲：固定資料の記述（版・提供状態の記載なし）。

上記のコードでは、結合の結果をv_omnichannel_sales_pulseという名前の論理ビューにしています。ビューには、顧客の国や製品のカテゴリ、店舗の地域などの列も含めています。

federated catalogsの運用上の動作

Google Cloudは、federated catalogsを本番環境に導入する前に、運用上の動作を考慮するよう勧めています。対象は、メタデータの同期や自動キャッシュ、暗号化、利用状況の監視の4点です。^[1]

図5　federated catalogsの4つの運用上の動作

メタデータ同期の確認点

メタデータの同期は--refresh-intervalフラグで制御し、最小の間隔は300秒です。同期が予定の周期を超えた場合、BigQueryはタスクの滞留を避けるために次の予定実行をスキップします。

図6　メタデータ同期の主な動作

数百のテーブルを含む大きなカタログでは、メタデータの取得に時間がかかる場合があります。大きなカタログを連携する場合は、同期1回の所要時間を確かめてから--refresh-intervalの値を決めるのが編集部の整理です。

クロスクラウドのキャッシュ保存先

BigQueryは、取得したクロスクラウドのデータブロックを、federated catalogがあるリージョン（us-central1など）に自動でキャッシュします。キャッシュは、繰り返し実行するクエリのネットワーク転送を減らす助けとなる仕組みです。

図7　クロスクラウドのキャッシュ保存先

キャッシュはそのリージョンのGoogle Cloud storageに置かれるため、Google Cloudは組織のデータ所在ポリシーとの整合の確認を勧めています。データの所在に制約がある組織では、federated catalogを置くリージョンを決める前に、ポリシーとの整合を確かめるのが編集部の整理です。

クロスクラウドキャッシュの暗号化

クロスクラウドのキャッシュのブロックは、Google管理の暗号鍵を使用する仕組みです。組織がconstraints/gcp.restrictNonCmekServicesを強制している場合、BigQueryはクロスクラウドのキャッシュを迂回します。

constraints/gcp.restrictNonCmekServicesを強制している組織では、BigQueryがクエリごとにリモートストレージから直接読み取ります。この場合、クロスクラウドのキャッシュは読み取りに使われない動作です。

図8　BigQueryのクロスクラウドキャッシュと組織ポリシー制約

キャッシュ利用状況の監視

キャッシュの利用状況は、クエリ実行計画で評価できる仕組みです。BigQueryのジョブ統計にあるobjectStorageStatsで、cacheBytesReadとobjectStorageBytesReadを比べられます。

図9　キャッシュの利用状況を確かめる統計

constraints/gcp.restrictNonCmekServicesを強制する組織では、キャッシュによる転送の削減を前提にせず計画する進め方が考えられます。federated catalogsの導入後は、objectStorageStatsの値でキャッシュの利用状況を定期的に確かめるのが編集部の整理です。