Cloud SQL Data API を使用して SQL ステートメントを実行する

MySQL   |  PostgreSQL   |  SQL Server

このページでは、Data API を使用して Cloud SQL インスタンス上のデータベースに対して SQL ステートメントを実行する方法について説明します。Data API を使用すると、Cloud SQL Admin API と gcloud CLIを使用して、Data API アクセスを有効にした任意のインスタンスで SQL ステートメントを実行できます。

Data API は、パブリック IP アドレス、プライベート サービス アクセス、または Private Service Connect を使用するインスタンスで使用できます。Data API は、データ操作言語（DML）、データ定義言語（DDL）、データクエリ言語（DQL）など、あらゆる種類の SQL ステートメントをサポートしています。Data API は、データベース ロールやユーザーの作成、スキーマの小規模な更新など、小規模で迅速な管理ステートメントの実行に適しています。

始める前に

インスタンスで SQL ステートメントを実行する前に、次の手順を行います。

データベース ユーザーを構成する

Data API は、SQL ステートメントを実行するためにデータベース ユーザーとして認証する必要があります。

パスワードを使用して組み込みユーザーとして認証するには、次の操作を行います。

1. 空でないパスワードを使用してユーザー アカウントを作成します。デフォルト ユーザー sqlserver を使用することもできます。
2. SQL ステートメントを実行するために必要なロールまたは権限を アカウントに付与します。ユーザーが sqlserverでない場合は、ユーザーに db_ownerロールを付与します。
3. Secret Manager を使用して、作成 するリージョン シークレットにパスワードを保存します。セキュリティ上の理由から、Data API は API リクエストでパスワードではなくシークレットのリソース名を要求します。リージョン シークレットは、 Cloud SQL インスタンスと同じリージョンに保存する必要があります。Secret Manager のグローバル エンドポイントを使用して作成されたシークレットは、同じリージョンに保存されていてもサポートされません。
4. ベスト プラクティスとして、IAM Conditions を定義して、ユーザーが特定のシークレットにアクセスできるようにしますが、プロジェクト内の他の シークレットにはアクセスできないようにします。

必要なロールまたは権限

デフォルトでは、次のいずれかのロールを持つユーザーまたはサービス アカウントには、Cloud SQL インスタンス（cloudsql.instances.executesql）で SQL ステートメントを実行する権限があります。

• Cloud SQL Admin（roles/cloudsql.admin）
• Cloud SQL Instance User（roles/cloudsql.instanceUser）
• Cloud SQL Studio User（roles/cloudsql.studioUser）

また、IAM カスタムロール を、cloudsql.instances.executesql 権限を持つユーザーまたはサービス アカウントに対して定義することもできます。この権限は サポートされています IAM カスタムロールで。

Data API を有効または無効にする

Data API を使用するには、インスタンスごとに有効にする必要があります。 Data API はいつでも無効にできます。

コンソール

1. Google Cloud コンソールで、Cloud SQL の [インスタンス] ページに移動します。

   Cloud SQL の [インスタンス] に移動

2. インスタンスの [概要] ページを開くには、インスタンス名をクリックします。
3. SQL ナビゲーション メニューから [接続] を選択します。
4. [ネットワーキング] タブをクリックします。
5. [Data API を許可する] チェックボックスをオンにします。
6. [保存] をクリックします。

gcloud

インスタンスで Data API アクセスを有効にするには、gcloud sql instances patch コマンドを --data-api-access=ALLOW_DATA_API フラグとともに使用します。

gcloud sql instances patch INSTANCE_NAME --data-api-access=ALLOW_DATA_API

Data API アクセスを無効にするには、--data-api-access=DISALLOW_DATA_API フラグを使用します。

gcloud sql instances patch INSTANCE_NAME --data-api-access=DISALLOW_DATA_API

INSTANCE_NAME は、Data API を有効または無効にするインスタンスの名前に置き換えます。

SQL ステートメントを実行する

gcloud CLI または REST API を使用して、Cloud SQL インスタンス上のデータベースに対して SQL ステートメントを実行できます。

パスワードによる認証

パスワードが Cloud SQL インスタンスと同じリージョンの Secret Manager にリージョン シークレットとして保存されている場合は、組み込みのパスワード認証を使用して SQL ステートメントを実行できます。

gcloud

gcloud CLI を使用してインスタンス上のデータベースに対して SQL ステートメントを実行するには、gcloud CLI gcloud sql instances execute-sqlコマンドを使用します。

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--user=USER \
--password-secret-version=PASSWORD_SECRET_VERSION \
--partial-result-mode=PARTIAL_RESULT_MODE

次のように置き換えます。

• INSTANCE_NAME: インスタンスの名前。
• DATABASE_NAME: インスタンス内のデータベースの名前。
• SQL_STATEMENT: 実行する SQL ステートメント。ステートメントにスペースまたはシェル特殊文字が含まれている場合は、引用符で囲む必要があります。
• USER: 認証するデータベース ユーザー。
• PASSWORD_SECRET_VERSION: データベース ユーザーのパスワードを保持する Secret Manager シークレットのリソース名。シークレットはa リージョン シークレットであり、Cloud SQL インスタンスと同じリージョンに保存する必要があります。リソース名の形式は projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}. です。
• PARTIAL_RESULT_MODE: 省略可。結果が不完全な場合のレスポンス方法を制御します。ALLOW_PARTIAL_RESULT、FAIL_PARTIAL_RESULT、PARTIAL_RESULT_MODE_UNSPECIFIED を指定できます。切り捨て動作を変更するをご覧ください。

Terraform

Terraform で Data API を使用すると、インスタンスに手動で 接続せずに、データベース、テーブル、拡張機能、ユーザー、権限付与などのデータベース内リソースをプロビジョニングできます。Terraform で SQL スクリプトを実行するには、 google_sql_provision_script Terraform リソースを使用します。

resource "google_sql_user" "built_in_user" {
  name     = "tf-user"
  host     = "%"  # Don't set this field for PostgreSQL and SQL Server.
  instance = google_sql_database_instance.instance.name
  password = "changeme"
  type     = "BUILT_IN"
}

# Create a regional secret. Global secrets are not supported even if
# located in one region only.
resource "google_secret_manager_regional_secret" "secret" {
  secret_id = "db-password"

  # Use the same region as the Cloud SQL instance.
  location = "us-central1"
}

resource "google_secret_manager_regional_secret_version" "secret_version" {
  secret = google_secret_manager_regional_secret.secret.id
  secret_data = "changeme"
}

resource "google_sql_provision_script" "script" {
  # You can inline the script or import from a file like script  = file("${path.module}/script.sql")
  # When modified, the whole script will be executed again. It's recommended to
  # make the script idempotent with patterns like create if not exists ... or
  # if not exists (select ...) then ... end if.
  script  = "CREATE TABLE IF NOT EXISTS table1 ( col VARCHAR(16) NOT NULL );"

  instance = google_sql_database_instance.instance.name
  database = google_sql_database.database.name
  description = "sql script to create tables"
  user = google_sql_user.built_in_user.name

  # The location should be the same as the Cloud SQL instance's location.
  password_secret_version = "projects/my-project/locations/us-central1/secrets/db-password/versions/latest"

  # The built-in database user and password secret version must be created
  # first. Cloud SQL will retrieve password from Secret Manager
  # and connect to this user account to execute your script.
  depends_on = [
    google_sql_user.built_in_user,
    google_secret_manager_regional_secret_version.secret_version
  ]
}

変更を適用する

Google Cloud プロジェクトで Terraform 構成を適用するには、次のセクションの手順を完了します。

Cloud Shell を準備する

1. Cloud Shell を起動します。

2. Terraform 構成を適用するデフォルトの Google Cloud プロジェクトを設定します。

   このコマンドは、プロジェクトごとに 1 回だけ実行する必要があります。これは任意のディレクトリで実行できます。

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Terraform 構成ファイルに明示的な値を設定すると、環境変数がオーバーライドされます。

ディレクトリを準備する

Terraform 構成ファイルには独自のディレクトリ（ルート モジュールとも呼ばれます）が必要です。

1. Cloud Shell で、ディレクトリを作成し、そのディレクトリ内に新しいファイルを作成します。ファイル名の拡張子は .tf にする必要があります（例: main.tf）。このチュートリアルでは、このファイルを main.tf とします。

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. チュートリアルを使用している場合は、各セクションまたはステップのサンプルコードをコピーできます。

   新しく作成した main.tf にサンプルコードをコピーします。

   必要に応じて、GitHub からコードをコピーします。Terraform スニペットがエンドツーエンドのソリューションの一部である場合は、この方法をおすすめします。

3. 環境に適用するサンプル パラメータを確認し、変更します。
4. 変更を保存します。
5. Terraform を初期化します。これは、ディレクトリごとに 1 回だけ行います。

   terraform init

   最新バージョンの Google プロバイダを使用する場合は、-upgrade オプションを使用します。

   terraform init -upgrade

変更を適用する

1. 構成を確認して、Terraform が作成または更新するリソースが想定どおりであることを確認します。

   terraform plan

   必要に応じて構成を修正します。

2. 次のコマンドを実行します。プロンプトで「yes」と入力して、Terraform 構成を適用します。

   terraform apply

   Terraform に「Apply complete!」というメッセージが表示されるまで待ちます。

3. Google Cloud プロジェクトを開いて結果を表示します。 Google Cloud コンソールの UI でリソースに移動して、Terraform によって作成または更新されたことを確認します。

注: Terraform のサンプルでは通常、必要な API が Google Cloud プロジェクトで有効になっていることを前提としています。

変更を削除する

google_sql_provision_script リソースを削除しても、作成されたデータベース内リソースは削除されません。削除するには、スクリプトに drop ... if exists などのステートメントを明示的に追加して、変更を適用します。

REST

REST API を使用してインスタンス上のデータベースに対して SQL ステートメントを実行するには、executeSql エンドポイントに POST リクエストを送信します。

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/executeSql

リクエストの本文には、データベース名と SQL ステートメントを含める必要があります。

{
  "database": "DATABASE_NAME",
  "sqlStatement": "SQL_STATEMENT",
  "user": "USER",
  "passwordSecretVersion": "PASSWORD_SECRET_VERSION",
  "partialResultMode": "PARTIAL_RESULT_MODE"
}

次のように置き換えます。

• PROJECT_ID: プロジェクト ID。
• INSTANCE_NAME: インスタンスの名前。
• DATABASE_NAME: インスタンス内のデータベースの名前。
• SQL_STATEMENT: 実行する SQL ステートメント。
• USER: 認証するデータベース ユーザー。
• PASSWORD_SECRET_VERSION: データベース ユーザーのパスワードを保持する Secret Manager シークレットのリソース名。シークレットはa リージョン シークレットであり、Cloud SQL インスタンスと同じリージョンに保存する必要があります。リソース名の形式は projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}. です。
• PARTIAL_RESULT_MODE: 省略可。結果が 10 MB を超えた場合の API のレスポンス方法を制御します。 FAIL_PARTIAL_RESULT、 ALLOW_PARTIAL_RESULT、または PARTIAL_RESULT_MODE_UNSPECIFIED を指定できます。 切り捨て動作を変更するをご覧ください。

切り捨て動作を変更する

リクエストに "partialResultMode" フィールドを含めることで、SQL の実行時に大きな結果を処理する方法を制御できます。このフィールドには次の値を指定できます。

• FAIL_PARTIAL_RESULT: デフォルト。結果が 10 MB を超える場合、または部分的な結果しか取得できない場合は、エラーをスローします。結果は返されません。
• ALLOW_PARTIAL_RESULT: 結果が 10 MB を超える場合、またはエラーにより部分的な結果しか取得できない場合は、切り捨てられた結果を返し、partial_result を true に設定します。エラーはスローされません。
• PARTIAL_RESULT_MODE_UNSPECIFIED: モードが指定されていません。事実上 FAIL_PARTIAL_RESULT と同じです。

制限事項

• レスポンスのサイズ上限は 10 MB です。このサイズを超える結果は、partialResultMode が ALLOW_PARTIAL_RESULT に設定されている場合は切り捨てられます。それ以外の場合はエラーがスローされます。
• リクエストは 0.5 MB に制限されています。
• SQL ステートメントは、実行中の Cloud SQL for SQL Server インスタンスに対してのみ実行できます。
• Cloud SQL では、外部サーバー レプリケーション用に設定されたインスタンスで Data API を使用することはできません。
• 30 秒を超えるリクエストはキャンセルされます。SET LOCK_TIMEOUT を使用してステートメント タイムアウトを長く設定することはできません。

• Cloud SQL では、過負荷を防ぐため、インスタンスあたりの同時 executeSql リクエストの数が制限されています。上限に達すると、後続のリクエストは失敗し、次のいずれかのエラーが返されます。

  • At most 'x' concurrent queries may be run on this instance. Try again later.
  • Maximum concurrent reads 'x' reached.

  上限（x）は、 合計メモリが 10 GB 未満のインスタンスの場合は 5 クエリ、 合計メモリが 10 GB 以上のインスタンスの場合は 10 クエリです。

• 各レスポンスには、最大 10 個のデータベース メッセージまたは警告を含めることができます。

• ステートメントの構文エラーまたは実行エラーがある場合、結果は返されません。

• Data API は、パスワードが空の組み込みユーザーとして認証できません。

• インスタンスで特定のメンテナンス オペレーションが進行中の場合、データの完全性を確保するために Data API が一時的にブロックされることがあります。その場合は、後で再試行してください。

• GO コマンドはサポートされていません。このコマンドは、ステートメントのバッチが終了し、SQL Server に送信できるようになったことを示すために Microsoft SQL Server ユーティリティで使用されます。

• クエリにバイナリ列が含まれている場合、Data API はその列を表示できません。 その場合、バイナリ値を文字列に変換します。

  たとえば、次のように置き換えます。

  SELECT my_binary_column from my_table2;
  

  次のように置き換えます。

  SELECT CONVERT(NVARCHAR(4000), my_binary_column, 1) from my_table2;
  

• 複数のクエリを実行し、そのうちの 1 つが失敗した場合は、最初に発生したエラーが返されます。エラーが発生する前にバッチ内の一部のステートメントが正常に処理されている可能性があります。この問題を回避するには、複数のクエリを transaction ステートメントでラップします。

  BEGIN TRANSACTION
      YOUR_SQL_STATEMENTS
  COMMIT;
  

  次のように置き換えます。

  • YOUR_SQL_STATEMENTS: 実行するステートメント このクエリの一部として

• SQL スクリプトとその実行レスポンスは、クライアントとターゲット インスタンスの場所の間の中間ロケーションを経由する可能性があります。このため、特定の Assured Workloads プロジェクトと、constraints/sql.restrictNoncompliantResourceCreation が手動で適用されているプロジェクトでは、リクエストが失敗し、「not supported for instances in certain Assured Workloads control packages folders」というエラーが返されます。