🔧

Spannerの全文検索の試行錯誤の記録

2024/12/22に公開3件

SQL

全文検索

Cloud Spanner

tech

はじめに

本記事は Spanner Advent Calender の21日目の記事です。

2024年8月にSpannerの全文検索機能のプレビューが発表され、同年10月7日に一般提供されました。
私は普段データベースにFirestoreのDatastoreモード、検索エンジンにAlgoliaなどの外部検索エンジンを組み合わせた構成で使用しており、この件について勤務先のチャットで雑談していた時に以下の話が出ました。

FirestoreのバックエンドはSpannerだからいつかFirestoreにも導入されるのか？
Firestore(Datastoreモード)＋AlgoliaをやめてSpanner1本に移行できるか？

本記事では検索エンジン部分をSpannerに移行する場合の試行錯誤をまとめてみました。

データモデル

データモデルは以下ER図のようにマルチテナントSaaSを想定したプロジェクト、ファイル、カスタム項目の3テーブルからなるモデルとします。
以下の項目が全文検索の対象です。

Project
- Name
- Description
Files
- Name
- Description
CustomItems
- Value

DDLにすると以下のようになります。

-- Projects テーブル
CREATE TABLE Projects (
  tenantId STRING(36) NOT NULL,   -- テナントID
  projectId STRING(36) NOT NULL DEFAULT (GENERATE_UUID()), -- プロジェクトID
  name STRING(200),              -- プロジェクト名
  description STRING(1000),       -- プロジェクト概要
  createdAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 作成日時
  updatedAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 更新日時
  nameFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(name, language_tag=>"ja")) HIDDEN, -- プロジェクト名の全文テキスト検索トークン
  nameNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(name)) HIDDEN, -- プロジェクト名のN-gramトークン
  descriptionFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(description, language_tag=>"ja")) HIDDEN, -- プロジェクト概要の全文テキストトークン
  descriptionNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(description)) HIDDEN, -- プロジェクト概要のN-gramトークン
) PRIMARY KEY (tenantId, projectId);

-- Files テーブル
CREATE TABLE Files (
  tenantId STRING(36) NOT NULL,  -- テナントID
  projectId STRING(36) NOT NULL, -- プロジェクトID
  fileId STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),    -- ファイルID
  name STRING(200),              -- ファイル名
  description STRING(1000),      -- ファイル概要
  imageUrl STRING(500),          -- 画像URL
  createdAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 作成日時
  updatedAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 更新日時
  nameFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(name, language_tag=>"ja")) HIDDEN, -- ファイル名の全文テキスト検索トークン
  nameNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(name)) HIDDEN, -- ファイル名のN-gramトークン
  descriptionFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(description, language_tag=>"ja")) HIDDEN, -- ファイル概要の全文テキスト検索トークン
  descriptionNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(description)) HIDDEN, -- ファイル概要のN-gramトークン
) PRIMARY KEY (tenantId, projectId, fileId),
  INTERLEAVE IN PARENT Projects ON DELETE CASCADE;

-- CustomItems テーブル
CREATE TABLE CustomItems (
  tenantId STRING(36) NOT NULL,  -- テナントID
  projectId STRING(36) NOT NULL, -- プロジェクトID
  fileId STRING(36) NOT NULL,    -- ファイルID
  customItemId STRING(36) NOT NULL, -- カスタム項目ID
  value STRING(1000),             -- カスタム項目の値
  createdAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 作成日時
  updatedAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 更新日時  
  valueFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(value, language_tag=>"ja")) HIDDEN, -- カスタム項目の値の全文テキスト検索トークン
  valueNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(value)) HIDDEN, -- カスタム項目の値のN-gramトークン
) PRIMARY KEY (tenantId, projectId, fileId, customItemId),
  INTERLEAVE IN PARENT Files ON DELETE CASCADE;

Spannerでのテーブル間のリレーションシップには他のRDBMSと同じく外部キーを使う方式とインターリーブを使う方式がありますが、本記事中ではインターリーブを使います。
全文検索に使用する項目のトークン化はテーブル作成の時に行っておき、それぞれ全文検索トークンリストとN-gramトークンリストを用意します。

また、DDLを見ていて各テーブルの作成日時、更新日時のcreatedAt, updatedAtやProjectsのdueDateの型を TIMESTAMP や DATE ではなく、 INT64 になっていることに「おや？」と思った方もいるかもしれません。
Spannerの検索インデックスでは TIMESTAMP や DATE が使用できないためです。日付項目の型はエポックミリ秒にしています。
ここは地味にハマるポイントなので注意が必要です。

全文検索インデックス

続いて全文検索用のインデックスを作ります。
マルチテナントSaaS用のデータベースなので、検索インデックスはテナントごとにパーティション化します。

-- Projects の検索インデックス
CREATE SEARCH INDEX ProjectsSearchIndex ON Projects (
  nameFulltextTokens,
  nameNGramTokens,
  descriptionFulltextTokens,
  descriptionNGramTokens
) PARTITION BY tenantId ORDER BY updatedAt DESC;

-- Files の検索インデックス
CREATE SEARCH INDEX FilesSearchIndex ON Files (
  nameFulltextTokens,
  nameNGramTokens,
  descriptionFulltextTokens,
  descriptionNGramTokens
) PARTITION BY tenantId ORDER BY updatedAt DESC;

-- CustomItems の検索インデックス
CREATE SEARCH INDEX CustomItemsSearchIndex ON CustomItems (
  valueFulltextTokens,
  valueNGramTokens
) PARTITION BY tenantId ORDER BY updatedAt DESC;

これで準備完了です。

検索クエリを投げる

入力したキーワードでプロジェクト名、概要、ファイル名、カスタム項目の値を検索したい場合どうすれば良いでしょう？
（とても効率悪いし問題のある方法ですが）他のRDBMSみたいにlikeを使うならこんな感じでしょうか？

SELECT
  P.tenantId, P.projectId, P.name AS projectName, P.description, P.createdAt AS projectCreatedAt, P.updatedAt AS projectUpdatedAt,
  F.fileId, F.name AS fileName, F.description AS fileDescription, F.imageUrl, F.createdAt AS fileCreatedAt, F.updatedAt AS fileUpdatedAt,
  C.customItemId, C.value, C.createdAt AS customItemCreatedAt, C.updatedAt AS customItemUpdatedAt
FROM Projects P
  INNER JOIN Files F ON P.tenantId = F.tenantID AND P.projectId = F.projectId
  INNER JOIN CustomItems C ON F.tenantId = C.tenantId AND F.projectId = C.projectId AND F.fileId = C.fileId
WHERE
  P.tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' AND F.tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' AND C.tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
  AND (P.name LIKE '%難しい%' OR P.description LIKE '%難しい%' OR F.name LIKE '%難しい%' OR C.value LIKE '%難しい%')
  AND C.customItemId IN ('464edd5a-173c-4ccf-b68d-03b340dee876', '37417f44-651b-4a2b-b311-bc4a39ea953f', '925360c2-63ab-4beb-8aac-e2ff4fbc4dc9') -- テナントで定義して且つ有効化している項目のID
ORDER BY
  projectUpdatedAt DESC, projectCreatedAt DESC, fileUpdatedAt DESC, fileCreatedAt DESC;

これをもとにLIKEで検索している箇所を全文検索の関数のSEARCHとSEARCH_NGRAMSに置き換えたらどうなるでしょうか？

SELECT
  P.tenantId, P.projectId, P.name AS projectName, P.description, P.createdAt AS projectCreatedAt, P.updatedAt AS projectUpdatedAt,
  F.fileId, F.name AS fileName, F.imageUrl, F.createdAt AS fileCreatedAt, F.updatedAt AS fileUpdatedAt,
  C.customItemId, C.value, C.createdAt AS customItemCreatedAt, C.updatedAt AS customItemUpdatedAt
FROM Projects P
  INNER JOIN Files F ON P.tenantId = F.tenantID AND P.projectId = F.projectId
  INNER JOIN CustomItems C ON F.tenantId = C.tenantId AND F.projectId = C.projectId AND F.fileId = C.fileId
WHERE
  P.tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
  AND F.tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
  AND C.tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' AND (
    SEARCH(P.nameFulltextTokens, '難しい')
    OR SEARCH_NGRAMS(P.nameNGramTokens, '難しい')
    OR SEARCH(P.descriptionFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(P.descriptionNGramTokens, '難しい')
    OR SEARCH(F.nameFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(F.nameNGramTokens, '難しい')
    OR SEARCH(F.descriptionFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(F.descriptionNGramTokens, '難しい')
    OR SEARCH(C.valueFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(C.valueNGramTokens, '難しい')
  )
  AND C.customItemId IN (
    '464edd5a-173c-4ccf-b68d-03b340dee876',
    '37417f44-651b-4a2b-b311-bc4a39ea953f',
    '925360c2-63ab-4beb-8aac-e2ff4fbc4dc9'
  ) -- テナントで定義して且つ有効化している項目のID
ORDER BY
  projectUpdatedAt DESC, projectCreatedAt DESC, fileUpdatedAt DESC, fileCreatedAt DESC;

Spanner StudioでこのSQLを書いてみると、 SEARCH is not supported in this query. Possible reasons are: There is no appropriate search index to use, or the function is used in unsupported query shapes. You can get more details on why a specific search index cannot be used by adding @{force_index} hint to the query. というエラーが出ました。
検索インデックスを使っていないのでヒントでインデックスを指定しろということでしょうか。

それでは各テーブルの後ろに @{force_index} で使用するインデックスを指定するとどうなるでしょうか？

-- テーブルの後ろにインデックスを指定するヒントを追加
FROM Projects @{force_index = ProjectsSearchIndex} P
  LEFT JOIN Files @{force_index = FilesSearchIndex} F ON P.tenantId = F.tenantId AND P.projectId = F.projectId
  LEFT JOIN CustomItems @{force_index = CustomItemsSearchIndex} C ON
    F.tenantId = C.tenantId AND F.projectId = C.projectId AND F.fileId = C.fileId

これも駄目なようです。

ではどうすれば良いでしょうか？
すぐに思いつく方法としては以下の2つがあると思います。

Project, Files, CustomItems を1つの検索テーブルにまとめる方法
先にProject, Files, CustomItems それぞれのテーブルで条件に合致するものを取ってきて、後からUNIONでまとめる方法

1つの検索専用テーブルにまとめて検索する方法

以下のようにProject, Files, CustomItemsを1つにまとめた検索専用のテーブルを作ります。
RDBMSのテーブル設計においてアンチパターン中のアンチパターンとしてよく知られている方法ですね。

CREATE TABLE FilesForSearch (
  tenantId STRING(36) NOT NULL,   -- テナントID
  projectId STRING(36) NOT NULL, -- プロジェクトID
  fileId STRING(36) NOT NULL,    -- ファイルID
  projectName STRING(200),              -- プロジェクト名
  projectDescription STRING(1000),       -- プロジェクト概要
  projectCreatedAt INT64 NOT NULL, -- 作成日時
  projectUpdatedAt INT64 NOT NULL, -- 更新日時
  fileName STRING(200),              -- ファイル名
  fileDescription STRING(1000),      -- ファイル概要
  fileImageUrl STRING(500),          -- 画像URL
  fileCreatedAt INT64 NOT NULL, -- 作成日時
  fileUpdatedAt INT64 NOT NULL, -- 更新日時
  customItemId1 STRING(36) NOT NULL, -- カスタム項目1のID
  value1 STRING(1000),          -- カスタム項目1の値
  customItemId2 STRING(36) NOT NULL, -- カスタム項目2のID
  value2 STRING(1000),          -- カスタム項目2の値
  customItemId3 STRING(36) NOT NULL, -- カスタム項目3のID
  value3 STRING(1000),          -- カスタム項目3の値

  projectNameFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(projectName, language_tag=>"ja")) HIDDEN, -- プロジェクト名の全文テキスト検索トークン
  projectNameNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(projectName)) HIDDEN, -- プロジェクト名のN-gramトークン
  projectDescriptionFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(projectDescription, language_tag=>"ja")) HIDDEN, -- プロジェクト概要の全文テキストトークン
  projectDescriptionNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(projectDescription)) HIDDEN, -- プロジェクト概要のN-gramトークン

  fileNameFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(fileName, language_tag=>"ja")) HIDDEN, -- ファイル名の全文テキスト検索トークン
  fileNameNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(fileName)) HIDDEN, -- ファイル名のN-gramトークン
  fileDescriptionFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(fileDescription, language_tag=>"ja")) HIDDEN, -- ファイル概要の全文テキストトークン
  fileDescriptionNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(fileDescription)) HIDDEN,  -- ファイル概要のN-gramトークン

  value1FulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(value1, language_tag=>"ja")) HIDDEN, -- カスタム項目1の値の全文テキスト検索トークン
  value1NGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(value1)) HIDDEN, -- カスタム項目1の値のN-gramトークン
  value2FulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(value2, language_tag=>"ja")) HIDDEN, -- カスタム項目2の値の全文テキスト検索トークン
  value2NGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(value2)) HIDDEN, -- カスタム項目2の値のN-gramトークン
  value3FulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(value3, language_tag=>"ja")) HIDDEN, -- カスタム項目2の値の全文テキスト検索トークン
  value3NGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(value3)) HIDDEN, -- カスタム項目1の値のN-gramトークン
) PRIMARY KEY (tenantId, projectId, fileId);

CREATE SEARCH INDEX FilesForSearchIndex ON FilesForSearch (
  projectNameFulltextTokens,
  projectNameNGramTokens,
  projectDescriptionFulltextTokens,
  projectDescriptionNGramTokens,
  fileNameFulltextTokens,
  fileNameNGramTokens,
  fileDescriptionFulltextTokens,
  fileDescriptionNGramTokens,
  value1FulltextTokens,
  value1NGramTokens,
  value2FulltextTokens,
  value2NGramTokens,
  value3FulltextTokens,
  value3NGramTokens
) PARTITION BY tenantId ORDER BY fileUpdatedAt DESC;

検索クエリは以下のように非常にシンプルなものになります。

SELECT
  tenantId, projectId, fileId, projectName, projectDescription, projectCreatedAt, projectUpdatedAt,
  fileName, fileDescription, fileImageUrl, fileCreatedAt, fileUpdatedAt,
  customItemId1, value1, customItemId2, value2, customItemId3, value3
FROM FilesForSearch
WHERE
  tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
  AND (
    SEARCH(projectNameFulltextTokens, '難しい')
    OR SEARCH_NGRAMS(projectNameNGramTokens, '難しい')
    OR SEARCH(projectDescriptionFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(projectDescriptionNGramTokens, '難しい')
    OR SEARCH(fileNameFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(fileNameNGramTokens, '難しい')
    OR SEARCH(fileDescriptionFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(fileDescriptionNGramTokens, '難しい')
    OR SEARCH(value1FullTextTokens, '難しい') -- 有効なカスタム項目だけクエリに含める
    OR SEARCH_NGRAMS(value1NGramTokens, '難しい')
    OR SEARCH(value2FullTextTokens, '難しい')
    OR SEARCH_NGRAMS(value2NGramTokens, '難しい')
    OR SEARCH(value3FullTextTokens, '難しい')
    OR SEARCH_NGRAMS(value3NGramTokens, '難しい')
  )
ORDER BY
  projectUpdatedAt DESC, projectCreatedAt DESC, fileUpdatedAt DESC, fileCreatedAt DESC;

しかしこの方法はCustomItemsの数が固定で少ないうちはまだ良いですが、CustomItemsを増やす必要が出た時にカラムの追加とインデックスの作り直しが発生します。
Spannerの全文検索でも極力避けたい方法なのは言うまでもありません。

先にそれぞれのテーブルで条件に合致するものを取ってきて後からまとめる方法

各テーブル毎に合うものを個別に取ってくる方法を単純に考えるなら以下のSQLのようになるでしょうか？

SELECT
  *,
  ARRAY(
    SELECT AS STRUCT * 
    FROM Files
    WHERE tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' AND (
      SEARCH(nameFulltextTokens, '難しい')
      OR SEARCH_NGRAMS(nameNGramTokens, '難しい')
      OR SEARCH(descriptionFulltextTokens, '難しい')
      OR SEARCH_NGRAMS(descriptionNGramTokens, '難しい')
    )
  ) AS files,
  ARRAY(
    SELECT AS STRUCT * 
    FROM CustomItems 
    WHERE
      tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' 
      AND (
        SEARCH(valueFulltextTokens, '難しい')
        OR SEARCH_NGRAMS(valueNGramTokens, '難しい')
      )
      AND customItemId IN (
        '464edd5a-173c-4ccf-b68d-03b340dee876',
        '37417f44-651b-4a2b-b311-bc4a39ea953f',
        '925360c2-63ab-4beb-8aac-e2ff4fbc4dc9'
      )
) AS customItems
FROM
  Projects
WHERE
  tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' AND (
    SEARCH(nameFulltextTokens, '難しい')
    OR SEARCH_NGRAMS(nameNGramTokens, '難しい')
    OR SEARCH(descriptionFullTextTokens, '難しい')
    OR SEARCH_NGRAMS(descriptionNGramTokens, '難しい')
  )
ORDER BY
  updatedAt DESC, createdAt DESC

お気づきだと思いますがこのSQLだと先にキーワードに合致する項目を持っているプロジェクトで絞り込んでしまっているので、プロジェクトに一致するデータがないがファイルまたはカスタム項目側が一致するパターンのデータを取ってくることができません。
では、プロジェクト、ファイル、カスタム項目それぞれで条件に合うものを検索し、最後に UNION ALL で連結したらどうでしょうか？

WITH
  P AS (
    SELECT
      *,
      ARRAY(
        SELECT AS STRUCT * 
        FROM Files
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
      ) AS files,
      ARRAY(
        SELECT AS STRUCT * 
        FROM CustomItems 
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' 
          AND customItemId IN (
            '464edd5a-173c-4ccf-b68d-03b340dee876',
            '37417f44-651b-4a2b-b311-bc4a39ea953f',
            '925360c2-63ab-4beb-8aac-e2ff4fbc4dc9'
          )
      ) AS customItems
    FROM
      Projects
    WHERE
      tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' AND (
        SEARCH(nameFulltextTokens, '難しい')
        OR SEARCH_NGRAMS(nameNGramTokens, '難しい')
        OR SEARCH(descriptionFullTextTokens, '難しい')
        OR SEARCH_NGRAMS(descriptionNGramTokens, '難しい')
      )
  ),
  F AS (
    SELECT
      *,
      ARRAY(
        SELECT AS STRUCT * 
        FROM Files
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
      ) AS files,
      ARRAY(
        SELECT AS STRUCT * 
        FROM CustomItems 
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' 
          AND customItemId IN (
            '464edd5a-173c-4ccf-b68d-03b340dee876',
            '37417f44-651b-4a2b-b311-bc4a39ea953f',
            '925360c2-63ab-4beb-8aac-e2ff4fbc4dc9'
          )
      ) AS customItems
    FROM
      Projects
    WHERE
      tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
      AND projectId IN UNNEST(ARRAY(
        SELECT projectId
        FROM Files
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
          AND (
            SEARCH(nameFulltextTokens, '難しい')
            OR SEARCH_NGRAMS(nameNGramTokens, '難しい')
            OR SEARCH(descriptionFulltextTokens, '難しい')
            OR SEARCH_NGRAMS(descriptionNGramTokens, '難しい')
          )
      ))
  ),
  C AS (
    SELECT
      *,
      ARRAY(
        SELECT AS STRUCT * 
        FROM Files
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
      ) AS files,
      ARRAY(
        SELECT AS STRUCT * 
        FROM CustomItems 
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede' 
          AND customItemId IN (
            '464edd5a-173c-4ccf-b68d-03b340dee876',
            '37417f44-651b-4a2b-b311-bc4a39ea953f',
            '925360c2-63ab-4beb-8aac-e2ff4fbc4dc9'
          )
      ) AS customItems
    FROM
      Projects
    WHERE
      tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
      AND projectId IN UNNEST(ARRAY(
        SELECT projectId
        FROM CustomItems
        WHERE
          tenantId = '7fc9f8bf-5f5e-4cac-a5df-0037cb075ede'
          AND (
            SEARCH(valueFulltextTokens, '難しい')
            OR SEARCH_NGRAMS(valueNGramTokens, '難しい')
          )
      ))
  )
SELECT * FROM P
UNION ALL
SELECT * FROM F
UNION ALL
SELECT * FROM C
ORDER BY updatedAt DESC, createdAt DESC;

SEARCH_NGRAMS is only supported in queries with query optimizer version 6 or above というエラーが出てしまいました。
どうやらこのクエリで使用するクエリオプティマイザーのバージョンが古いとのことのようです。

では、クエリの先頭に @{OPTIMIZER_VERSION=latest} というように最新バージョンのクエリオプティマイザーを指定するヒントを追加したらどうでしょうか？
今度は上手く行ったようです。^[1]

終わりに

以上のように少し癖が強いところがあるものの、Spannerでも通常のSQLを投げる要領で検索が行えることがわかりました。
ElasticsearchやSolr、Algoliaなどの競合と比較してパフォーマンスはどうか？費用はどうか？^[2] 運用・監視の手間はどうか？など気になる箇所がありますが、軽く触ってみた限りではデータベース、検索エンジンをSpannerで一本化する選択肢はあるのではないかと感じました。

他にも数値の範囲による絞り込みや区分値の絞り込みを組み合わせたケースやベクトル検索など書きたかったことがありますが、長くなってきた^[3]ので一旦ここで終わりにします。

脚注

ここはかなりハマりやすいポイントで、私はこれで工数を丸1日浪費しました ↩︎
2024年12月現在、東京リージョン上で最小単位である100処理ユニット、ストレージ1GiBで試算した結果では月額12995円とかなり安くなっていると感じます ↩︎
特にSQLが ↩︎

Discussion

apstndb

また、DDLを見ていて各テーブルの作成日時、更新日時の createdAt, updatedAt やProjects の dueDate の型を TIMESTAMP や DATE ではなく、 INT64 になっていることに「おや？」と思った方もいるかもしれません。

Spannerの検索インデックスでは TIMESTAMP や DATE が使用できないためです。日付項目の型はエポックミリ秒にしています。
全文検索の都合でテーブルカラムの型を変えるのは不本意なのではないかと思います~し、 TIMESTAMP 型ではなくなると Spanner の強みの commit timestamp も使えなくなる不都合があると考えられます~ (仕様上 allow_commit_timestamp は generated column では参照できないため削除)。 そこで、 createdAt や updatedAt そのものは TIMESTAMP 型のまま、 generated column で INT64 に変換してみるというのも検討の余地があるかもしれません。

例として最初の Projects だけを書き換えるとこのようになるのではと思います。
 CREATE TABLE Projects (
   tenantId STRING(36) NOT NULL,   -- テナントID
   projectId STRING(36) NOT NULL DEFAULT (GENERATE_UUID()), -- プロジェクトID
   name STRING(200),              -- プロジェクト名
   description STRING(1000),       -- プロジェクト概要
-  createdAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 作成日時
+  createdAt TIMESTAMP NOT NULL, -- 作成日時
-  updatedAt INT64 NOT NULL DEFAULT (UNIX_MILLIS(CURRENT_TIMESTAMP())), -- 更新日時
+  updatedAt TIMESTAMP NOT NULL, -- 更新日時
   createdAtMillis INT64 AS (UNIX_MILLIS(createdAt)),
   updatedAtMillis INT64 AS (UNIX_MILLIS(updatedAt)),
   nameFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(name, language_tag=>"ja")) HIDDEN, -- プロジェクト名の全文テキスト検索トークン
   nameNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(name)) HIDDEN, -- プロジェクト名のN-gramトークン
   descriptionFulltextTokens TOKENLIST AS (TOKENIZE_FULLTEXT(description, language_tag=>"ja")) HIDDEN, -- プロジェクト概要の全文テキストトークン
   descriptionNGramTokens TOKENLIST AS (TOKENIZE_NGRAMS(description)) HIDDEN, -- プロジェクト概要のN-gramトークン
 ) PRIMARY KEY (tenantId, projectId);

 CREATE SEARCH INDEX ProjectsSearchIndex ON Projects (
   nameFulltextTokens,
   nameNGramTokens,
   descriptionFulltextTokens,
   descriptionNGramTokens
 )
 PARTITION BY tenantId
-ORDER BY updatedAt DESC
+ORDER BY updatedAtMillis DESC
+WHERE updatedAtMillis IS NOT NULL -- STORED でないと NOT NULL をつけられないため NULL FILTERED に
;
SEARCH_NGRAMS is only supported in queries with query optimizer version 6 or above というエラーが出てしまいました。
Query Optimizer version 6 がデフォルトになったのは2023年10月なので、これはおそらくどこかで Optimizer Version が固定されているような気がしますね。主に疑われるのは ALTER DATABASE によるデータベースレベルでしょうか。
Spanner default ← database option ← client app ← environment variable ← client query ← statement hint

Hiro Kinko

コメントありがとうございます！
日付による絞り込みなどを考えると挿入時や取得時に毎回変換かけるより、generated columnとしてSpannerに任せて生成した方が良さそうですね。

クエリオプティマイザーの方は、こちらからはバージョンを指定したことはないのに何故か5以前のオプティマイザーになっていたのは少し気持ち悪いですね…。
クエリによってエラーが出たり出なかったり…今のところエラーが出ているのは全文検索の結果をUNIONで結合した時だけですが、データベースレベルでオプティマイザーを指定しておいた方が無難そうですね。

apstndb

クエリオプティマイザーの方は、こちらからはバージョンを指定したことはないのに何故か5以前のオプティマイザーになっていたのは少し気持ち悪いですね…。
どのクエリでも optimizer version は5以前になっていて、 UNION を使わないクエリでは何らかの最適化の結果 5 以前で動くようになったみたいな感じですかね。実際のところどのバージョンで動いているのかとかは確認しても良いかもしれませんね。
spanner-cli ならこのあたり
$ spanner-cli --project ${SPANNER_PROJECT_ID} --instance ${SPANNER_INSTANCE_ID} --database ${SPANNER_DATABASE_ID} -t -v -e "SELECT 1"
+-------+
|       |
| INT64 |
+-------+
| 1     |
+-------+
1 rows in set (1.39 msecs)
timestamp:            2024-12-26T17:08:33.679168+09:00
cpu time:             1.35 msecs
rows scanned:         0 rows
deleted rows scanned: 0 rows
optimizer version:    7
optimizer statistics: auto_20241225_03_17_40UTC
Spanner Studio ならこのあたり

FYI: 上に書いた方法で TIMESTAMP にして allow_commit_timestamp=true にできるのは良いような気はしましたが、どうもドキュメンテーションされていない挙動にあたったので一応 Google に報告しておきました。 https://issuetracker.google.com/issues/386050389