SQL-Anweisungen mit der Cloud SQL Data API ausführen

MySQL   |  PostgreSQL   |  SQL Server

Auf dieser Seite wird beschrieben, wie Sie mit der Data API SQL-Anweisungen für Datenbanken auf Cloud SQL-Instanzen ausführen. Mit der Data API verwenden Sie die Cloud SQL Admin API und die gcloud CLI, um SQL-Anweisungen für jede Instanz auszuführen, für die Sie den Data API-Zugriff aktiviert haben.

Sie können die Data API mit Instanzen verwenden, die öffentliche IP-Adressen, den Zugriff auf private Dienste oder Private Service Connect nutzen. Die Data API unterstützt alle Arten von SQL-Anweisungen, einschließlich DML (Datenbearbeitungssprache), DDL (Datendefinitionssprache) und DQL (Datenabfragesprache). Die Data API eignet sich gut für die Ausführung kleiner und schneller administrativer Anweisungen, z. B. zum Erstellen von Datenbankrollen oder ‑Nutzern und für kleine Schemaaktualisierungen.

Hinweis

Bevor Sie SQL-Anweisungen für eine Instanz ausführen können, müssen Sie die folgenden Schritte ausführen.

Datenbanknutzer konfigurieren

Die Data API muss sich als Datenbanknutzer authentifizieren, um SQL-Anweisungen auszuführen.

So authentifizieren Sie sich als integrierter Nutzer mit einem Passwort:

1. Erstellen Sie ein Nutzerkonto mit einem nicht leeren Passwort. Sie können auch den Standardnutzer sqlserver verwenden.
2. Gewähren Sie dem Konto die erforderlichen Rollen oder Berechtigungen zum Ausführen von SQL-Anweisungen. Wenn der Nutzer nicht sqlserver, gewähren Sie ihm die db_owner Rolle.
3. Erstellen Sie mit Secret Manager ein regionales Secret, um das Passwort zu speichern. Aus Sicherheitsgründen fordert die Data API in der API-Anfrage den Ressourcennamen des Secrets anstelle des Passworts an. Das regionale Secret sollte in derselben Region wie Ihre Cloud SQL-Instanz gespeichert werden. Secrets, die mit dem globalen Endpunkt von Secret Manager erstellt wurden, werden nicht unterstützt, auch wenn sie in derselben Region gespeichert sind.
4. Als Best Practice sollten Sie IAM Bedingungen definieren, um einem Nutzer Zugriff auf ein bestimmtes Secret zu gewähren, aber nicht auf andere Secrets im Projekt.

Erforderliche Rollen oder Berechtigungen

Standardmäßig haben Nutzer- oder Dienstkonten mit einer der folgenden Rollen die Berechtigung, SQL-Anweisungen für eine Cloud SQL-Instanz auszuführen (cloudsql.instances.executesql):

• Cloud SQL Admin (roles/cloudsql.admin)
• Cloud SQL Instance User (roles/cloudsql.instanceUser)
• Cloud SQL Studio User (roles/cloudsql.studioUser)

Sie können auch eine benutzerdefinierte IAM-Rolle für das Nutzer- oder Dienstkonto definieren, die die cloudsql.instances.executesql Berechtigung enthält. Diese Berechtigung wird unterstützt in benutzerdefinierten IAM-Rollen.

Data API aktivieren oder deaktivieren

Wenn Sie die Data API verwenden möchten, müssen Sie sie für jede Instanz aktivieren. Sie können die Data API jederzeit deaktivieren.

Console

1. Öffnen Sie in der Google Cloud Console die Cloud SQL-Instanzen Seite.

   Cloud SQL-Instanzen aufrufen

2. Klicken Sie auf den Instanznamen, um die Seite Übersicht einer Instanz zu öffnen.
3. Wählen Sie im SQL-Navigationsmenü die Option Verbindungen aus.
4. Klicken Sie auf den Tab Netzwerk.
5. Klicken Sie auf das Kästchen Data API zulassen.
6. Klicken Sie auf Speichern.

gcloud

Verwenden Sie den gcloud sql instances patch Befehl mit dem --data-api-access=ALLOW_DATA_API Flag, um den Data API-Zugriff für eine Instanz zu aktivieren:

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

Verwenden Sie das Flag --data-api-access=DISALLOW_DATA_API, um den Data API-Zugriff zu deaktivieren:

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

Ersetzen Sie INSTANCE_NAME durch den Namen der Instanz, für die Sie die Data API aktivieren oder deaktivieren möchten.

SQL-Anweisung ausführen

Sie können SQL-Anweisungen für Datenbanken auf Ihrer Cloud SQL Instanz entweder mit der gcloud CLI oder der REST API ausführen.

Mit Passwort authentifizieren

Sie können SQL-Anweisungen mit der integrierten Passwortauthentifizierung ausführen, wenn das Passwort als regionales Secret mit Secret Manager in derselben Region wie die Cloud SQL-Instanz gespeichert ist.

gcloud

Verwenden Sie den Befehl gcloud sql instances execute-sql, um eine SQL-Anweisung für eine Datenbank auf einer Instanz mit der gcloud CLI auszuführen.

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

Ersetzen Sie die folgenden Werte:

• INSTANCE_NAME: der Name der Instanz.
• DATABASE_NAME: der Name der Datenbank in der Instanz.
• SQL_STATEMENT: die auszuführende SQL-Anweisung. Wenn die Anweisung Leerzeichen oder Shell-Sonderzeichen enthält, muss sie in Anführungszeichen gesetzt werden.
• USER: der Datenbanknutzer, als der die Authentifizierung erfolgen soll.
• PASSWORD_SECRET_VERSION: der Ressourcenname des Secret Manager-Secrets, das das Passwort für den Datenbanknutzer enthält. Das Secret sollte ein regionales Secret sein und in derselben Region wie die Cloud SQL -Instanz gespeichert werden. Das erwartete Ressourcennameformat ist projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE: optional. Steuert, wie reagiert werden soll, wenn das Ergebnis unvollständig ist. Mögliche Werte sind ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT oder PARTIAL_RESULT_MODE_UNSPECIFIED. Weitere Informationen finden Sie unter Verhalten bei der Kürzung ändern.

Terraform

Sie können die Data API in Terraform verwenden, um Ressourcen in der Datenbank wie Datenbanken, Tabellen, Erweiterungen, Nutzer und Berechtigungen bereitzustellen, ohne manuell eine Verbindung zur Instanz herzustellen. Verwenden Sie die google_sql_provision_script Terraform-Ressource, um ein SQL-Skript in Terraform auszuführen.

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
  ]
}

Änderungen anwenden

Führen Sie die Schritte in den folgenden Abschnitten aus, um Ihre Terraform-Konfiguration auf ein Google Cloud Projekt anzuwenden.

Cloud Shell vorbereiten

1. Rufen Sie Cloud Shell auf.

2. Legen Sie das Standard Google Cloud projekt fest, auf das Sie Ihre Terraform-Konfigurationen anwenden möchten.

   Sie müssen diesen Befehl nur einmal pro Projekt und in jedem beliebigen Verzeichnis ausführen.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Umgebungsvariablen werden überschrieben, wenn Sie in der Terraform-Konfigurationsdatei explizite Werte festlegen.

Verzeichnis vorbereiten

Jede Terraform-Konfigurationsdatei muss ein eigenes Verzeichnis haben (auch als Stammmodul bezeichnet).

1. Erstellen Sie in Cloud Shell ein Verzeichnis und eine neue Datei in diesem Verzeichnis. Der Dateiname muss die Erweiterung .tf haben, z. B. main.tf. In dieser Anleitung wird die Datei als main.tf bezeichnet.

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

2. Wenn Sie einer Anleitung folgen, können Sie den Beispielcode in jedem Abschnitt oder Schritt kopieren.

   Kopieren Sie den Beispielcode in das neu erstellte main.tf.

   Kopieren Sie optional den Code aus GitHub. Dies wird empfohlen, wenn das Terraform-Snippet Teil einer End-to-End-Lösung ist.

3. Prüfen und ändern Sie die Beispielparameter, die auf Ihre Umgebung angewendet werden sollen.
4. Speichern Sie die Änderungen.
5. Initialisieren Sie Terraform. Dies ist nur einmal für jedes Verzeichnis erforderlich.

   terraform init

   Fügen Sie optional die Option -upgrade ein, um die neueste Google-Anbieterversion zu verwenden:

   terraform init -upgrade

Änderungen anwenden

1. Prüfen Sie die Konfiguration und prüfen Sie, ob die Ressourcen, die Terraform erstellen oder aktualisieren wird, Ihren Erwartungen entsprechen:

   terraform plan

   Korrigieren Sie die Konfiguration nach Bedarf.

2. Wenden Sie die Terraform-Konfiguration an. Führen Sie dazu den folgenden Befehl aus und geben Sie yes an der Eingabeaufforderung ein:

   terraform apply

   Warten Sie, bis Terraform die Meldung „Apply complete“ anzeigt.

3. Öffnen Sie Ihr Google Cloud Projekt, um die Ergebnisse aufzurufen. Rufen Sie in der Google Cloud Console Ihre Ressourcen in der Benutzeroberfläche auf, um sicherzustellen dass Terraform sie erstellt oder aktualisiert hat.

Hinweis: Bei Terraform-Beispielen wird in der Regel davon ausgegangen, dass die erforderlichen APIs in Ihrem Projekt aktiviert sind. Google Cloud

Änderungen löschen

Durch das Löschen einer google_sql_provision_script-Ressource werden die von ihr erstellten Ressourcen in der Datenbank nicht gelöscht. Wenn Sie sie löschen möchten, können Sie dem Skript explizit Anweisungen wie drop ... if exists hinzufügen und dann die Änderungen anwenden.

REST

Senden Sie eine POST-Anfrage an den Endpunkt executeSql, um eine SQL-Anweisung für eine Datenbank auf einer Instanz mit der REST API auszuführen:

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

Der Anfragetext sollte den Datenbanknamen und die SQL-Anweisung enthalten:

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

Ersetzen Sie die folgenden Werte:

• PROJECT_ID: Ihre Projekt-ID.
• INSTANCE_NAME: der Name der Instanz.
• DATABASE_NAME: der Name der Datenbank in der Instanz.
• SQL_STATEMENT: die auszuführende SQL-Anweisung.
• USER: der Datenbanknutzer, als der die Authentifizierung erfolgen soll.
• PASSWORD_SECRET_VERSION: der Ressourcenname des Secret Manager-Secrets, das das Passwort für den Datenbanknutzer enthält. Das Secret sollte ein regionales Secret sein und in derselben Region wie die Cloud SQL -Instanz gespeichert werden. Das erwartete Ressourcennameformat ist projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE: optional. Steuert, wie die API reagiert wenn das Ergebnis 10 MB überschreitet. Mögliche Werte sind FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT oder PARTIAL_RESULT_MODE_UNSPECIFIED. Weitere Informationen finden Sie unter Verhalten bei der Kürzung ändern.

Verhalten bei der Kürzung ändern

Sie können festlegen, wie große Ergebnisse bei der Ausführung von SQL-Anweisungen verarbeitet werden, indem Sie das "partialResultMode" Feld in die Anfrage einfügen. Für dieses Feld sind die folgenden Werte zulässig:

• FAIL_PARTIAL_RESULT: Standard. Gibt einen Fehler aus, wenn das Ergebnis 10 MB überschreitet oder nur ein Teilergebnis abgerufen werden kann. Gibt das Ergebnis nicht zurück.
• ALLOW_PARTIAL_RESULT: Gibt ein gekürztes Ergebnis zurück und setzt partial_result auf „true“, wenn das Ergebnis 10 MB überschreitet oder nur ein Teilergebnis aufgrund eines Fehlers abgerufen werden kann. Gibt keinen Fehler aus.
• PARTIAL_RESULT_MODE_UNSPECIFIED: Nicht angegebener Modus, entspricht im Wesentlichen FAIL_PARTIAL_RESULT.

Beschränkungen

• Die maximale Größe einer Antwort beträgt 10 MB. Ergebnisse, die diese Größe überschreiten, werden gekürzt, wenn partialResultMode auf ALLOW_PARTIAL_RESULT gesetzt ist. Andernfalls wird ein Fehler ausgegeben.
• Anfragen sind auf 0,5 MB begrenzt.
• Sie können SQL-Anweisungen nur für Cloud SQL for SQL Server-Instanzen ausführen, die ausgeführt werden.
• Cloud SQL unterstützt die Verwendung der Data API nicht für Instanzen, die für die Replikation externer Server eingerichtet sind.
• Anfragen, die länger als 30 Sekunden dauern, werden abgebrochen. Das Festlegen eines höheren Zeitlimits für Anweisungen mit SET LOCK_TIMEOUT wird nicht unterstützt.

• Cloud SQL begrenzt die Anzahl gleichzeitiger executeSql-Anfragen pro Instanz, um eine Überlastung zu vermeiden. Wenn das Limit erreicht ist, schlagen nachfolgende Anfragen fehl und geben einen der folgenden Fehler zurück:

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

  Das Limit (x) beträgt 5 Abfragen für Instanzen mit weniger als 10 GB Gesamtspeicher und 10 Abfragen für Instanzen mit mindestens 10 GB Gesamtspeicher.

• Jede Antwort kann maximal 10 Datenbankmeldungen oder ‑Warnungen enthalten.

• Wenn ein Syntax- oder Ausführungsfehler in der Anweisung auftritt, wird kein Ergebnis zurückgegeben.

• Die Data API kann sich nicht als integrierte Nutzer mit leeren Passwörtern authentifizieren.

• Die Data API kann aus Gründen der Datenintegrität vorübergehend blockiert werden, wenn bestimmte Wartungsvorgänge für die Instanz ausgeführt werden. Versuchen Sie es später noch einmal.

• Der Befehl GO wird nicht unterstützt. Dieser Befehl wird in Microsoft SQL Server-Dienstprogrammen verwendet, um anzugeben, dass ein Batch von Anweisungen beendet ist und an SQL Server gesendet werden kann.

• Wenn eine Abfrage eine binäre Spalte enthält, kann sie von der Data API nicht angezeigt werden. Konvertieren Sie stattdessen binäre Werte in einen String.

  Beispiel: Ersetzen Sie

  SELECT my_binary_column from my_table2;
  

  mit

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

• Wenn mehrere Abfragen ausgeführt werden und eine davon fehlschlägt, wird der erste aufgetretene Fehler zurückgegeben. Einige der Anweisungen im Batch vor dem Fehler wurden möglicherweise erfolgreich ausgeführt. Sie können mehrere Abfragen in einer transaction-Anweisung zusammenfassen, um dieses Problem zu vermeiden:

  BEGIN TRANSACTION
      YOUR_SQL_STATEMENTS
  COMMIT;
  

  Ersetzen Sie Folgendes:

  • YOUR_SQL_STATEMENTS: die Anweisungen, die Sie als Teil dieser Abfrage ausführen möchten

• Das SQL-Skript und die Ausführungsantwort werden möglicherweise über Zwischenstandorte zwischen Ihrem Client und dem Standort der Zielinstanz übertragen. Aus diesem Grund schlagen Anfragen mit dem Fehler „not supported for instances in certain Assured Workloads control packages folders“ für bestimmte Assured Workloads-Projekte und für Projekte fehl, bei denen constraints/sql.restrictNoncompliantResourceCreation manuell erzwungen wird.