[GCP] Spanner で 一括更新(20000 ミューテーション以上)のDMLを実行する

[GCP] Spanner で 一括更新(20000 ミューテーション以上)のDMLを実行する

こんばんは。七色メガネです。

今日は、Spanner で 20000ミューテーション以上の一括更新などを実行する方法を紹介します。

 

Spanner ってなに?

Spanner は、GCP で提供される分散型のRDBです。次の記事で Spanner についての紹介を行なっていますので、よろしければご参照ください。

[ GCP ] メガネと学ぶ GCP (8) ストレージ・サービスの特徴を知る

Spanner における一括更新の制限について

Spanner における制限事項

Spanner にはその使用に当たっていくつかの制限事項があります。例えば次のような事項です。

  • 1ノードあたりのサイズは 2T まで
  • JOIN による結合は 15回 まで
  • Commit サイズは 100M まで
  • Commit あたりのミューテーションは 20000 まで

今回のテーマである一括更新を行おうとするときに考慮しなければいけないのが、[1 Commit では 20000 ミューテーション以下の操作しか行えない]、という制限です。端的に言えば、一度の更新で10万行や100万行の更新は基本的に行えない、ということです。

[参考]

https://cloud.google.com/spanner/quotas?hl=ja

ミューテーションとは

ミューテーションとは、オペレーションの影響を受ける列数を表現する単位のことです。1つの update 文で 1 レコードの全列 が更新されるとして、そのレコードが 10 列から構成されるものであれば、この時のミューテーションは 10 になります。

したがって 20000 ミューテーションの制限とは、「1度のオペレーションで影響を受ける列数は常に 20000 以下でなくてはいけない」ということを意味します。

10列を持つテーブルの全列更新であれば一度に 2000 レコードまで、10000列を持つテーブルの全列更新であれば一度の 2 レコードまでの操作しか許容されません。

なお、列数がそのままミューテーションとして数えらえれるのは、挿入と更新のオペレーションについてのみです。削除については列数はミューテーションとして数えられず、削除したい行数がそのままミューテーションとなります。

パーティションDMLについて

Spanner ではミューテーション上限に引っかかるような DML の発行について、パーティションDML という機能がサポートされています。これは大量データの一括更新などのために設計されており、通常の 20000 ミューテーションの上限に制約されることなくオペレーションを実行することができます。

パーティションDML の特徴は、主に次の通りです。

  • GCPコンソールからは実行できない
  • 1つのオペレーションには1つのDMLステートメントのみ含めることができる。(サブクエリなどは使用できない)
  • Insert はサポートされていない
  • クエリプラン(SQL実行計画) についての機能はサポートされていない

つまり、Spanner を外部プログラムから操作することができる場合における一括更新について、このパーティションDML を用いることで問題を解決することができます。

[参考]

https://cloud.google.com/spanner/docs/dml-partitioned?hl=ja

データ準備とミューテーション上限に引っかかる挙動の確認

では実際に、パーティションDMLを用いて一括更新を実行してみましょう。
まずはテストデータの作成から始めます。

テストデータの準備

あらかじめ、テスト用のテーブルを Spanner 上に作成しておきます。
今回はミューテションの計測をしやすくするため、特に意味のない列を 10 個用意したテーブルを作成します。

次にこのテストテーブルにデータを用意します。

今回は update を実行するときにミューテーション上限に引っかかる挙動も確認したいので、テストデータはそれなりの数を用意したいと思います。
テストテーブルの列数が 10 ですから、全ての列を更新すると仮定したときに必要なレコード数は、 10 * 2000 = 20000 で、最低 2000 レコードです。

とりあえず、10000 レコードほど用意したいと思います。

はい、tranz テーブルに 10000 件のデータが用意できました。これで一括更新のテストを行う準備ができました。

ミューテーション上限に引っかけてみる

ではパーティションDMLを実行して見る前に、ミューテーションの上限に引っ掛けてみましょう。

現在、10 列のテーブルに 10000 レコードが存在します。ミューテーションの計算は オペレーションが行われる列数 * レコード 数ですから、6000レコードを更新すると仮定したら、3列までは通常のDMLで行えますが、4列では上限に引っかかります。

ただ、今回はサブクエリで対象のレコードを limit して update をかけています。ミューテーションはサブクエリで取得されるレコードについても計算対象となりますから、今回のミューテーション計算は、「サブクエリで取得されるレコード数 + アップデートされる列数 * レコード数」で行われます。

結論として、次のSQLで更新できるのは、5000レコードまでです(select 5000 + update 3列 * 5000 = 20000)。5001レコードからはミューテーション上限にかかります(select 5001 + update 3列 * 5001 = 20004)。

文章では分かりにくいですね。実際にやってみましょう。まずは 5000 レコードの更新です。操作はGCPコンソールから行います。

成功しました。では 5001 レコードではどうでしょうか。

 

想定通り、エラーになりました。

パーティションDMLの実行(Python)

では、ミューテーション上限を回避するパーティションDMLを実行してみましょう。
前述の通り、パーティションDMLはコンソール上からは実行できません。いくつか実行の方法はありますが、今回は外部プログラム(Python)から専用のライブラリを使用して実行してみたいと思います。

Spanner にアクセスするためのサービスアカウントの用意

まずはSpannerに外部アクセスするためのサービスアカウントを用意します。

  1. GCPコンソールの [IAMと管理] > [サービスアカウント] を選択します。
  2. [サービスアカウントの作成] を選択し、任意の名称を付与します。
  3. [役割の選択] を選択し、[Project – オーナー] 権限を付与します。
  4. [キーの作成] を選択し、発行される JSONキー を任意の場所に保存します。

テスト環境の画像が公開できなかったので文章だけでごめんなさい。
上記の操作で、Spanner にアクセスするためのサービスアカウントとキーが発行されます。ここで作成されたキーを用いて、Spanner へのアクセスを実現します。

ライブラリの準備

Spanner 関連の操作を行うためのライブラリとして、google-cloud-spanner を使用します。pip でインストールしてください。

https://pypi.org/project/google-cloud-spanner/

実装

では実装していきます。今回は4ファイルを作成します。

  • main.py
    処理ロジックの呼び出しと結果の表示を行う。
  • ctlSpa.py
    処理ロジックを実行する。
  • conf.py
    環境情報などを保持する。
  • doMyTest.sh
    環境変数を設定し、main.py を実行する。

 

main.py

特筆事項はないです。ctlSpa の処理を呼び出し、その結果を出力するだけのsrcです。

main.py

conf.py

接続する Spanner の インスタンスIDとデータベースIDを保持させます。

conf.py

ctlSpa.py

ここでやることは2つです。

  1. データベース情報とインスタンス情報を使用して、Spanner 接続のためのデータベースクラスを生成する。
  2. 生成されたデータベースクラスの execute_partition_dml メソッドを使用し、DMLを実行する。

ライブラリの使用方法は、下記の src を参照してください。

今回はDMLを直接プログラムの中に書き込んでいます。

ここで、Spanner での update には必ず where 句が必要であることに注意してください。Spanner では where 指定なしでの全件更新処理を許容していないので、全件更新をしたい場合には全件が引っかかるような where 文を指定する必要があります。

また、パーティションDMLとして実行できるのは単一のステートメントであることも忘れないでください。サブクエリを使用した update などは、パーティションDML では実行することができません。

ctlSpa.py

doMyTest.sh

最後に、main.py を実行するためのシェルです。
別にこれは必須ではないのですが、Spanner へのアクセスを行うためには環境変数

GOOGLE_APPLICATION_CREDENTIALS にJSONキーの位置を設定する必要があります。これを毎回 export するのが面倒なので、今回はシェルを使用しています。
doMyTest.sh

実行

準備が整いました。では、パーティションDMLを実行してみましょう。今回は 9列 * 10000 レコードの更新を行なっているのでミューテーションは 90000 です。

 

はい、ミューテーション20000 を大きく超えるDMLでしたが、問題なく実行できました。一応レコードの内容を確認します。

問題なく処理が行われていることが確認できました。

余談ですが、パーティションDMLを実行した時の実行件数の戻り値は必ずしも正確ではないようです。10000件程度であれば件数はブレませんが、50万件以上の update などになってくると、どうも実際にupdateした件数と戻り値の値が乖離することが発生するようです。

まとめ

・Spanner には、「実行するオペレーションが 20000ミューテーション 以下でなければならない」という制約がある。

・ミューテーションとは、「オペレーションの影響を受ける列数 * レコード数」を意味する。

・20000 ミューテーション以上のDMLを実行したい場合には、パーティションDML を使用する。

・パーティションDML はGCPコンソールからの実行ができない。

・パーティションDML では単一のステートメントしか実行ができない。

 

以上です。ここまでご覧いただき、ありがとうございました!

 

余談:20000 ミューテーションを超える Insert の実行

今回はテストデータとして 10000 のレコードを用意しました。では、この 10000 レコードはどうやって用意したのでしょうか。

残念ながら今回紹介した パーティションDML は、Insert に対応していません。したがって、Insert 処理はミューテーション上限に引っかからないように実行するしか、現在は術がありません。

今回は、次のようなロジックで Insert を実行しています。仕組みは単純で、20000ミューテーション以下になるように一度のInsert数を制限しながら処理ループを回しているだけです。

ctlSpa.py

 

 

スライス処理が微妙なのは、ご愛嬌…。
実行すると、こんな感じです。

ゴリ押しですね!

参考

https://cloud.google.com/spanner/quotas?hl=ja

https://cloud.google.com/spanner/docs/dml-partitioned?hl=ja

 

書籍紹介

Google Cloud Platform エンタープライズ設計ガイド

Google Cloud Platform 実践Webアプリ開発 ストーリーで学ぶGoogle App Engine

プログラマのためのGoogle Cloud Platform入門 サービスの全体像からクラウドネイティブアプリケーション構築まで

 

 

GoogleCloudPlatformカテゴリの最新記事