Zenn
💭

Spanner REST API で SQL を実行する方法: 基礎編

2024/12/03に公開
Google Cloud
curl
rest
Cloud Spanner
tech

この記事は Spanner Advent Calendar 2日目の記事 & apstndb Advent Calendar 4日目の記事です。
この記事では Spanner REST API を使い SQL を実行する方法を説明します。

TL;DR

  • Spanner は REST API を持つため curl のような HTTP クライアントで操作できる
  • DDL, DML, クエリの基本的な実行例を紹介
  • API レベルでの挙動が分かれば Spanner がもっと理解できるかも

導入

Spanner の使用方法はユーザにわかりやすいものでは

がありますが、どれも REST API もしくは gRPC API を使って実装された抽象です。
これらを使っても API がどう動くのかは隠蔽されていて理解が進まない場合があります。分散トレースなどをする上でも API がどう働くかの理解は必須です。

公式ドキュメントには Getting started with Spanner using REST として REST API を API リファレンスの Try this method 機能を使って叩く例はあります。
しかし、コマンドラインでの curl 等での実行例がないとイメージが付かない人も居るのではないでしょうか。この記事では Spanner の API の基礎をコマンドラインを使って直接 Spanner REST API を叩いて説明します。

なおこの記事の例では 俺の考えた最強の curl ラッパーを考えてみる(jqurl) で解説した curl ラッパー jqurl を使用します。
jq(gojq) と curl の組み合わせのラッパーでしかないので適宜読み替えてください。

REST API を叩く

この記事では Spanner の SQL 実行に絞って説明していきます。

Spanner の SQL は主に下記の3つに分けて良いでしょう。

  • スキーマ変更を行う DDL(Data Definition Language)
  • データの更新を行う DML(Data Manipulation Language)
  • データを取得するためのクエリ

なおこの記事では GCPUG Public Spanner 下のデータベースを使うため、特にプロジェクト名、インスタンス名、データベース名は隠していません。

DDL の実行

既に作成されたデータベースのスキーマ変更を行うために DDL を実行するには、projects.instances.databases.updateDdl メソッドを使います。

この API メソッドは REST では次の HTTP メソッドと URL にマッピングされています。

PATCH https://spanner.googleapis.com/v1/{database=projects/*/instances/*/databases/*}/ddl

まだ、リクエストボディは下記のように説明されており、実行する DDL 文を入れる statements のみが必須であることがわかります。

{
  "statements": [
    string
  ],
  "operationId": string,
  "protoDescriptors": string
}

よって、次のように実行できます。

$ jqurl -s -X PATCH --auth=google --yaml-output --data-jq '.statements = ["CREATE TABLE ExampleTable(PK INT64, Col INT64) PRIMARY KEY(PK)"]' \
    "https://spanner.googleapis.com/v1/projects/${SPANNER_PROJECT}/instances/${SPANNER_INSTANCE}/databases/${SPANNER_DATABASE}/ddl"
metadata:
  '@type': type.googleapis.com/google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata
  actions:
    - action: CREATE
      entityNames:
        - ExampleTable
      entityType: TABLE
  database: projects/gcpug-public-spanner/instances/merpay-sponsored-instance/databases/apstndb-sampledb3
  progress:
    - startTime: "2024-12-02T16:53:52.869590Z"
  statements:
    - |-
      CREATE TABLE ExampleTable (
        PK INT64,
        Col INT64,
      ) PRIMARY KEY(PK)
name: projects/gcpug-public-spanner/instances/merpay-sponsored-instance/databases/apstndb-sampledb3/operations/_auto_op_7d0282940259b65c

これで DDL の実行がはじまります。拍子抜けするほど簡単ではないでしょうか。
実際には REST API レスポンスが返ってきても Long-Running Operation として DDL は実行途中であるため、実行結果は projects.instances.databases.operations.get などで確認する必要があります。

GET https://spanner.googleapis.com/v1/{name=projects/*/instances/*/databases/*/operations/*}

先ほどの updateDdl メソッドの実行結果に含まれる name フィールドを見れば指定すべき name がわかります。

$ jqurl -s --auth=google --yaml-output "https://spanner.googleapis.com/v1/projects/gcpug-public-spanner/instances/merpay-sponsored-instance/databases/apstndb-sampledb3/operations/_auto_op_7d0282940259b65c"
done: true
metadata:
  '@type': type.googleapis.com/google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata
  actions:
    - action: CREATE
      entityNames:
        - ExampleTable
      entityType: TABLE
  commitTimestamps:
    - "2024-12-02T16:53:57.578042Z"
  database: projects/gcpug-public-spanner/instances/merpay-sponsored-instance/databases/apstndb-sampledb3
  progress:
    - endTime: "2024-12-02T16:53:57.578042Z"
      progressPercent: 100
      startTime: "2024-12-02T16:53:52.869590Z"
  statements:
    - |-
      CREATE TABLE ExampleTable (
        PK INT64,
        Col INT64,
      ) PRIMARY KEY(PK)
name: projects/gcpug-public-spanner/instances/merpay-sponsored-instance/databases/apstndb-sampledb3/operations/_auto_op_7d0282940259b65c
response:
  '@type': type.googleapis.com/google.protobuf.Empty

エラーなく成功していることが分かりますね。
gcloud spanner databases ddl update コマンドなどの裏ではこの2つの API が実行されています。

DML の実行

DML や後述するクエリのように、データベース内のデータを扱う操作は同じコンテキストを持った一連の API 呼び出しが必要なため、それらを識別するためにセッションの概念が登場します。
この記事では最も基本的な流れを説明するためセッションの概念については詳しく触れません。詳しくは公式ドキュメントを参照することをおすすめします。

https://cloud.google.com/spanner/docs/sessions?hl=en

セッションの生成は projects.instances.databases.sessions.create メソッドで行うことができます。

POST https://spanner.googleapis.com/v1/{database=projects/*/instances/*/databases/*}/sessions

この API のリクエストボディは次のように説明されています。

{
  "session": {
    object (Session) # Required. The session to create.
  }
}

Session オブジェクトは次のような定義となっています

{
  "name": string,
  "labels": {
    string: string,
    ...
  },
  "createTime": string,
  "approximateLastUseTime": string,
  "creatorRole": string,
  "multiplexed": boolean
}

たくさんのフィールドがあるように見えますが、よく読むと全て出力のみか、必須ではないフィールドです。よって session フィールドは必須ですが、その値は空オブジェクトで構いません。
なお、レスポンスも Session オブジェクトとなります。 name フィールドに入った値が今後使うセッションリソースを識別するものとなります。

jqurl は2番目の位置パラメータを jq フィルタとして扱うため、 --raw-output と組み合わせることで目当ての名前をシェル変数に入れることができます。

$ session=$(jqurl -s --auth=google --data-jq '.session = {}' --raw-output \
                 "https://spanner.googleapis.com/v1/projects/${SPANNER_PROJECT}/instances/${SPANNER_INSTANCE}/databases/${SPANNER_DATABASE}/sessions" .name)
$ echo ${session}
projects/gcpug-public-spanner/instances/merpay-sponsored-instance/databases/apstndb-sampledb3/sessions/AL40lrGSvkoR2NLlp5__kZRx23C9XNkarGpgSwlL35USVZ3E4UT3bqoVzhLcJw

このセッションを使うことで DML の実行が可能です。
DML を実行する手段は次のようなものがあります。

executeStreamingSql サーバ側からストリームで分割された結果が返ってくるため curl では扱いづらいためこの記事では扱いません。
また、今回は THEN RETURN も使って結果を取得してみたいため、結果を取得できない executeBatchDml ではなく executeSql を使います。

executeSql のリクエストは複雑なので要点だけ説明します。

多くの場合、 DML の実行にはあらかじめ用意した Read/Write トランザクションを使用しますが、 Spanner では API 実行と同時に Read/Write トランザクションを開始することができます。 この機能を使うには transaction.begin.readWrite に空オブジェクトを指定します。
また、 SQL 本文は sql フィールドに指定します。

結果として、 DML の実行は先ほど作成したセッションを使用した下記のようなコマンドになります。

$ jqurl -s --auth=google --yaml-output \
    --data-jq '.transaction.begin.readWrite = {} | .sql = "INSERT ExampleTable(PK, Col) VALUES(3, 42) THEN RETURN *"' \
    "https://spanner.googleapis.com/v1/${session}:executeSql"
metadata:
  rowType:
    fields:
      - name: PK
        type:
          code: INT64
      - name: Col
        type:
          code: INT64
  transaction:
    id: QUw0MGxyR3VHeWtZcjgwMFVOQ3lYLWpSOXkweklYZ2F3THN0eFFqRncxcjhVZFFaVmc=
  undeclaredParameters: {}
rows:
  - - "3"
    - "42"
stats:
  rowCountExact: "1"
  • metadata.transaction.id の Read/Write transaction の ID が入っている
  • THEN RETURN の結果返ってくる行の方が metadata.rowType に入っている
  • rows に今回 INSERT した行の値が入っている

などに注目してください。

なおこのままでは Read/Write トランザクションのコミットが行われないため、下記の通りトランザクションは10秒でアボートされロールバックされます。

https://cloud.google.com/spanner/docs/reference/rest/v1/TransactionOptions

A transaction is considered idle if it has no outstanding reads or SQL queries and has not started a read or SQL query within the last 10 seconds.

更新を確定するには10秒以内にコミットを行う必要があります。
コミットを行うには projects.instances.databases.sessions.commit メソッドを呼びます。
DML に使った Read/Write トランザクションをコミットする場合 transactionId が必須です。先ほどの executeSql で返ってきた metadata.transaction.id の値を使いましょう。(変数にとっていないのでコピペします。)

$ jqurl -s --auth=google --yaml-output --data-jq '.transactionId = "QUw0MGxyR3ZhM2FYdXBFUjNMaC13TDYyOWdjMzllNDNKbWRIMW1CTkdyTzBlUTZIYWc="' "https://spanner.googleapis.com/v1/${session}:commit"
commitTimestamp: "2024-12-02T17:55:20.756594Z"

コミットタイムスタンプが返ってきたので、このタイムスタンプで更新が確定したことになります。

クエリの実行

クエリの実行は先ほどの DML の実行とほぼ同じです。最大の違いは Read/Write トランザクションが不要で Read-Only トランザクションを使うことができることです。
Read-Only トランザクションは明示的に扱うこともできますが、 Single-Use トランザクションとして暗黙に扱うことができます。

$ jqurl -s --auth=google --yaml-output \
    --data-jq '.transaction.singleUse.readOnly.strong = true | .sql = "SELECT * FROM ExampleTable"' \
    "https://spanner.googleapis.com/v1/${session}:executeSql"
metadata:
  rowType:
    fields:
      - name: PK
        type:
          code: INT64
      - name: Col
        type:
          code: INT64
  transaction: {}
  undeclaredParameters: {}
rows:
  - - "3"
    - "42"

Single-Use トランザクションはその読み取りにのみ使い、他の操作には使わないためトランザクション ID は存在しません。これでクエリの実行は完了です。

まとめ

この記事では Spanner における SQL の実行方法の3種類について、もっとも基本的な形を説明しました。

  • DDL
  • DML
  • クエリ

より複雑な形はこの基本的な形に対する差分として理解できます。

「まあ curl あれば Spanner の SQL は実行できるしな…」という気持ちを持てば Spanner を API レベルで理解するのは遠くはないでしょう。

Discussion