G-gen の佐々木です。当記事では、BigQuery の外部データセットとして Spanner データベース内のテーブルを参照する方法を解説します。

前提知識

BigQuery

BigQuery は Google Cloud における代表的なサービスの1つであり、高パフォーマンスかつ自動でスケールするフルマネージドの分析用データベース（データウェアハウス）を従量課金で利用することができます。

参考：BigQuery

BigQuery の詳細については、以下の記事も参照してください。

blog.g-gen.co.jp

Spanner

Spanner は、Google Cloud のフルマネージド RDB（リレーショナルデータベース）サービスです。強整合性を保証する RDB の特徴と、グローバルに水平スケーリングできる NoSQL データベースの特徴を併せ持つ、強力な分散データベースを利用することができます。

参考：Spanner

Spanner の詳細については、以下の記事も参照してください。

blog.g-gen.co.jp

BigQuery と Spanner の連携

2つの連携方法

BigQuery から Spanner データベース内のデータにアクセスするには、以下の2通りの方法が使用できます。いずれの方法でも、Spanner から BigQuery にデータを移動することなく、Spanner 上のテーブルに対する読み取り専用のアクセスが提供されます。

連携クエリ（Federated query）
外部データセット（External dataset または Federated dataset）

連携クエリとは

BigQuery の連携クエリとは、EXTERNAL_QUERY 関数を用いて、Spanner や Cloud SQL などの外部テーブルに SQL を実行できる機能です。Spanner、Cloud SQL、AlloyDB for PostgreSQL、SAP Datasphere（プレビュー）に対応しています。

連携クエリを使うには、まず BigQuery で接続（connections）というリソースを作成し、Spanner などの外部データベースに対する接続設定を行います。クエリする際は、EXTERNAL_QUERY 関数を使用して、テーブルを指定したクエリを実行します。

連携クエリでは、BigQuery の GUI である BigQuery Studio から Spanner 等の外部データベースのテーブル一覧やスキーマ情報は閲覧できません。

参考 : 連携クエリの概要
参考 : Spanner federated queries

外部データセットとは

当記事で紹介する BigQuery の外部データセット とは、Spanner のデータベース全体を BigQuery と連携し、テーブル一覧やスキーマ情報の閲覧を可能にするほか、BigQuery から Spanner への読み取りクエリを可能にする機能です。

外部データセットを設定するには、まず CREATE EXTERNAL SCHEMA でステートメントで BigQuery に外部データセットを作成します。外部データセットは通常の BigQuery データセット同様に、BigQuery Studio から直接テーブルやスキーマを確認することができ、クエリを実行する際は FROM 句で直接指定することができます。

参考：Create Spanner external datasets

Spanner 上のデータに対するクエリは Spanner Data Boost を使用して実行されます（連携クエリの場合は任意で使用）。これにより、Spanner インスタンスのコンピュートリソースを使用することなく、つまり Spanner のパフォーマンスに影響を与えることなく BigQuery にデータを連携することができます。

参考 : Data Boost の概要

連携クエリと外部データセットの比較

連携クエリと外部データセットの違いを簡単にまとめると以下のとおりです。

	連携クエリ	外部データセット
利用方法	BigQuery で接続を作成し `EXTERNAL_QUERY` 関数で Spanner データベース内のテーブルを指定してクエリを実行	BigQueryで外部データセットを作成し `FROM` 句で外部データセット内のテーブルを指定してクエリを実行
必要な IAM ロール	・Cloud Spanner データベース読み取り（roles/spanner.databaseReader）・BigQuery 接続ユーザー（roles/bigquery.connectionUser）	・Cloud Spanner Database 読み取りと DataBoost（roles/spanner.databaseReaderWithDataBoost）
使用できる SQL	・Google 標準 SQL ・PostgreSQL 互換 ※レガシー SQL は使用不可	・Google 標準 SQL ・レガシー SQL
Spanner Data Boost	任意で有効化	常に使用される
GUI でスキーマ確認	×（information_schema ビューで確認可能）	○
料金	・BigQuery のクエリ料金（オンデマンドの場合・参考）・Spanner Data Boost の料金（有効化する場合・参考）	・BigQuery のクエリ料金（オンデマンドの場合）・Spanner Data Boost の料金

外部データセットは接続リソースを作成しないため、設定方法やクエリがシンプルです。BigQuery と Spanner の連携を検討する場合、まずは外部データセットの利用を検討し、制限事項が問題となる場合に連携クエリを利用するとよいでしょう。

制限事項

連携クエリには以下のような制限事項があります。

クエリは読み取り専用。DML、DDL ステートメントはサポートされない
BigQuery でサポートされていないデータ型の列を含むクエリは失敗する（サポートされているデータ型へのキャストは可能）
顧客管理の暗号鍵（CMEK）を使用する場合、BigQuery と Spanner のそれぞれに設定する必要がある

外部データセットでは上記に加えて、さらに以下の制限が追加されます。

PostgreSQL 互換の Spanner データベースはサポートされない
BigQuery でサポートされていないデータ型の列にはアクセスできない
名前付きスキーマを使用する Spanner のテーブルにはアクセスできない
行レベルのセキュリティ、列レベルのセキュリティ、データマスキングは使用できない
Spanner 外部データセットのテーブルに基づくマテリアライズドビューは使用できない

詳細は、以下の公式ドキュメントも参照してください。

参考 : 連携クエリの概要 - 制限事項
参考 : Spanner 外部データセットを作成する - 制限事項

BigQuery から Spanner へのリバース ETL

外部データセットを使うことで、BigQuery から Spanner のデータを取得するだけではなく、BigQuery 上のテーブルを Spanner データベースにエクスポートすることもできます。

これにより、Spanner 外部データセットからデータを抽出して BigQuery 上で大規模な変換処理を行い、変換後のデータを Spanner に書き戻してアプリケーションから利用するリバース ETL を実現することができます。

以下のように EXPORT DATA OPTIONS ステートメントを使用することで、BigQuery から Spanner へのエクスポートを行うことができます。

/* BigQuery から Spanner へのリバース ETL */
EXPORT DATA OPTIONS (
  uri="https://spanner.googleapis.com/projects/<プロジェクトID>/instances/<Spannerインスタンス名>/databases/<Spannerデータベース名>",
  format='CLOUD_SPANNER',
  spanner_options="""{ "table": "<Spanner上の宛先テーブル>" }"""
)
AS SELECT * FROM `<BigQuery上のソーステーブル>`;

この機能は BigQuery Enterprise エディションもしくは Enterprise Plus エディションでのみ利用可能です。

参考：Export data to Spanner (reverse ETL)

手順

Spanner の準備

インスタンス、データベースの作成

まず、Spanner のインスタンスを作成します。当記事では東京リージョンを使用し、最小のコンピュートリソースである100処理ユニットでインスタンスを作成します。

# Spanner インスタンスを作成する
$ gcloud spanner instances create <インスタンス名> \
    --config=regional-asia-northeast1 \
    --description="Test Instance" \
    --processing-units=100

作成したインスタンスを指定してデータベースを作成します。

データベースを BigQuery の外部データセットとして利用する場合、言語（dialect）は Google 標準 SQL にする必要があります。

# Spanner インスタンス内にデータベースを作成する
$ gcloud spanner databases create <データベース名> \
    --instance=<インスタンス名> \
    --database-dialect=GOOGLE_STANDARD_SQL

テーブルの作成

ここからは Google Cloud コンソールを使用して、作成したデータベースの Spanner Studio から操作を行います。

以下のクエリを実行し、Spanner データベースにテーブルを作成します。

/* テーブルを2つ作成する */
CREATE TABLE Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);
  
CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

データの挿入

作成した各テーブルにデータを挿入します。

/* 各テーブルにデータを挿入する */
INSERT INTO Singers (SingerId, FirstName, LastName) VALUES
  (1, 'Marc', 'Richards'),
  (2, 'Catalina', 'Smith'),
  (3, 'Alice', 'Trentor'),
  (4, 'Lea', 'Martin'),
  (5, 'David', 'Lomond');
  
INSERT INTO Albums (SingerId, AlbumId, AlbumTitle) VALUES
  (1, 1, 'Total Junk'),
  (1, 2, 'Go, Go, Go'),
  (2, 1, 'Green'),
  (2, 2, 'Forever Hold Your Peace'),
  (2, 3, 'Terrified');

外部データセットの作成

ここからは BigQuery Studio で作業を実施します。

CREATE EXTERNAL SCHEMA ステートメントを使用することで、BigQuery の外部データセットを作成することができます。OPTIONS で参照先となる Spanner データベースを指定します。

/* BigQuery で Spanner 外部データセットを作成する */
CREATE EXTERNAL SCHEMA `<プロジェクトID>.<作成する外部データセットの名前>`
OPTIONS (
  external_source = 'google-cloudspanner:/projects/<プロジェクトID>/instances/<Spannerインスタンス名>/databases/Spannerデータベース名',
  location = 'asia-northeast1');

上記のクエリを実行すると、Spanner データベースに存在する2つのテーブルを含む Spanner 外部データセットが作成されます。

外部データセットに対するクエリ実行

外部データセットに対するクエリを実行するには、通常の BigQuery データセットに対するクエリと同様に、FROM 句でデータセットとテーブルを指定します。

以下のクエリでは、Spanner データベース内の2つのテーブルを結合する SELECT クエリを実行します。

/* Spanner 外部データセット内のテーブルにクエリを実行する */
SELECT 
  s.FirstName, 
  s.LastName,
  a.AlbumTitle
FROM `<プロジェクトID>.<外部データセットの名前>.Singers` AS s
JOIN `<プロジェクトID>.<外部データセットの名前>.Albums` AS a  
ON s.SingerId = a.SingerId;