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

MySQL   |  PostgreSQL   |  SQL Server

Auf dieser Seite wird beschrieben, wie Sie SQL-Anweisungen für Datenbanken in Cloud SQL-Instanzen mithilfe der Data API ausführen. Mit der Data API können Sie die Cloud SQL Admin API und die gcloud CLI verwenden, um SQL-Anweisungen auf jeder Instanz auszuführen, auf der Sie den Zugriff auf die Data API 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 Datenbearbeitungssprache (DML), Datendefinitionssprache (DDL) und Datenabfragesprache (DQL). 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 zum Vornehmen kleiner 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 Passwort, das nicht leer ist. Sie können auch den Standardnutzer sqlserver verwenden.
2. Weisen Sie dem Konto die erforderlichen Rollen oder Berechtigungen zu, um SQL-Anweisungen auszuführen. Wenn der Nutzer nicht sqlserver ist, weisen Sie ihm die Rolle db_owner zu.
3. Verwenden Sie Secret Manager, um ein regionales Secret zu erstellen, in dem das Passwort gespeichert wird. Aus Sicherheitsgründen wird in der API-Anfrage der Ressourcenname des Secrets anstelle des Passworts abgefragt. 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. Gewähren Sie dem Aufrufer der Data API roles/secretmanager.secretAccessor. Als Best Practice sollten Sie IAM-Bedingungen definieren, damit ein Nutzer auf ein bestimmtes Secret, aber nicht auf andere Secrets im Projekt zugreifen kann.

Erforderliche Rollen oder Berechtigungen

Die Nutzer- oder Dienstkonten, die zum Aufrufen der Data API verwendet werden, müssen die Berechtigung zum Ausführen von SQL-Anweisungen (cloudsql.instances.executesql) haben. Die Berechtigung ist in einer der folgenden vordefinierten Rollen enthalten:

• 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 Berechtigung cloudsql.instances.executesql enthält. Diese Berechtigung wird in benutzerdefinierten IAM-Rollen unterstützt.

Wenn Sie ein Secret Manager-Secret zur Authentifizierung verwenden, muss das Nutzer- oder Dienstkonto auch die Berechtigung secretmanager.versions.access für den Zugriff auf das Secret haben. Die Berechtigung ist in einer der folgenden vordefinierten Rollen enthalten:

• Secret Manager Secret Accessor (roles/secretmanager.secretAccessor)
• Secret Manager Admin (roles/secretmanager.admin)

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. Rufen Sie in der Google Cloud Console die Seite Cloud SQL-Instanzen auf.

   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 Befehl gcloud sql instances patch mit dem Flag --data-api-access=ALLOW_DATA_API, um den Data API-Zugriff auf einer 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, auf der Sie die Data API aktivieren oder deaktivieren möchten.

REST

Wenn Sie den Data API-Zugriff auf einer Instanz aktivieren möchten, senden Sie eine PATCH-Anfrage an den Endpunkt instances.patch:

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

Der Anfragetext muss das Feld dataApiAccess enthalten, das auf ALLOW_DATA_API gesetzt ist:

{
  "dataApiAccess": "ALLOW_DATA_API"
}

Wenn Sie den Zugriff auf die Data API deaktivieren möchten, setzen Sie dataApiAccess auf DISALLOW_DATA_API.

SQL-Anweisung ausführen

Sie können SQL-Anweisungen für Datenbanken in 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

Wenn Sie eine SQL-Anweisung für eine Datenbank auf einer Instanz mit der gcloud CLI ausführen möchten, verwenden Sie den Befehl 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

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 Sie sich authentifizieren möchten.
• 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 Format für den Ressourcennamen 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. Kann ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT oder PARTIAL_RESULT_MODE_UNSPECIFIED sein. Weitere Informationen finden Sie unter Kürzungsverhalten ändern.

Terraform

Sie können die Data API in Terraform verwenden, um datenbankinterne Ressourcen wie Datenbanken, Tabellen, Erweiterungen, Nutzer und Berechtigungen bereitzustellen, ohne manuell eine Verbindung zur Instanz herstellen zu müssen. Verwenden Sie zum Ausführen eines SQL-Skripts in Terraform die Terraform-Ressource google_sql_provision_script.

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 Google Cloud -Standardprojekt 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 in Google Cloud aktiviert sind.

Änderungen löschen

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

REST

Wenn Sie eine SQL-Anweisung für eine Datenbank auf einer Instanz mithilfe der REST API ausführen möchten, senden Sie eine POST-Anfrage an den executeSql-Endpunkt:

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 Sie sich authentifizieren möchten.
• 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 Format für den Ressourcennamen ist projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE: optional. Steuert, wie die API-Antwort reagiert, wenn das Ergebnis 10 MB überschreitet. Kann FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT oder PARTIAL_RESULT_MODE_UNSPECIFIED sein. Weitere Informationen finden Sie unter Kürzungsverhalten ändern.

Verhalten beim Abschneiden ändern

Sie können steuern, wie große Ergebnisse bei der Ausführung von SQL verarbeitet werden, indem Sie das Feld "partialResultMode" in die Anfrage einfügen. In dieses Feld können die folgenden Werte eingegeben werden:

• FAIL_PARTIAL_RESULT: Standard. Es wird ein Fehler ausgegeben, wenn das Ergebnis 10 MB überschreitet oder nur ein Teilergebnis abgerufen werden kann. Geben Sie 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 wenn aufgrund eines Fehlers nur ein Teilergebnis abgerufen werden kann. Keinen Fehler ausgeben.
• PARTIAL_RESULT_MODE_UNSPECIFIED: Nicht angegebener Modus, entspricht im Wesentlichen FAIL_PARTIAL_RESULT.

Audit-Abfragen

Sie können den Anwendungsnamen verfolgen, indem Sie das Feld applicationName in der Anfrage festlegen. Die Datenbank verfolgt den Anwendungsnamen in den Sitzungsstatistiken, z. B. in der Tabelle sys.dm_exec_sessions.

Mit Query Insights können Sie weitere Informationen zu Ihren Abfragen erfassen und Leistungsprobleme analysieren.

Sie können auch die SQL Server-Datenbankprüfung verwenden, um Abfragen zu Sicherheits- oder Compliance-Zwecken zu protokollieren.

Beschränkungen

• Die Größenbeschränkung für eine Antwort beträgt 10 MB. Ergebnisse, die diese Größe überschreiten, werden abgeschnitten, wenn partialResultMode auf ALLOW_PARTIAL_RESULT gesetzt ist. Andernfalls wird ein Fehler ausgegeben.
• Anfragen sind auf 0,5 MB beschränkt.
• Sie können SQL-Anweisungen nur für ausgeführte Cloud SQL for SQL Server-Instanzen ausführen.
• Cloud SQL unterstützt die Verwendung der Data API nicht mit Instanzen, die für die Replikation von externen Servern 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 der gleichzeitigen 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 Datenbanknachrichten oder ‑warnungen enthalten.

• Wenn ein Syntax- oder Ausführungsfehler in der Anweisung vorliegt, 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 auf der Instanz ausgeführt werden. Versuchen Sie es später noch einmal, falls das Problem auftritt.

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

• Wenn eine Abfrage eine binäre Spalte enthält, kann die Data API sie nicht anzeigen. Konvertieren Sie stattdessen Binärwerte 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 Sie mehrere Abfragen ausführen und eine davon fehlschlägt, wird der erste festgestellte 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 im Rahmen dieser Abfrage ausführen möchten

• Das SQL-Script und die Ausführungsantwort werden möglicherweise über Zwischenspeicherorte zwischen Ihrem Client und dem Standort der Zielinstanz übertragen. Aus diesem Grund schlagen Anfragen für bestimmte Assured Workloads-Projekte und für Projekte, bei denen constraints/sql.restrictNoncompliantResourceCreation manuell erzwungen wird, mit dem Fehler „Nicht unterstützt für Instanzen in bestimmten Assured Workloads-Kontrollpaketordnern“ fehl.

Fehlerbehebung

Dieser Abschnitt enthält Informationen zu Problemen im Zusammenhang mit der Verwendung der Data API sowie Schritte zur Fehlerbehebung.

Problem	Fehlerbehebung
The instance doesn't allow using ExecuteSql to access this instance. You can allow it by patching the instance with {settings: { dataApiAccess: "ALLOW_DATA_API" }}	Die Data API ist standardmäßig deaktiviert. Aktivieren Sie die Data API auf der Instanz, um das Problem zu beheben.
Secret cannot be provided when auto_iam_authn is true.	Wenn Sie auto_iam_authn auf true setzen, authentifizieren Sie sich mit IAM bei der Datenbank. Für diese Authentifizierungsmethode ist kein Passwort oder Secret erforderlich. Weitere Informationen finden Sie unter Mit IAM authentifizieren.
ExecuteSql API is not supported for instances in certain Assured Workloads control packages folders yet.	Das SQL-Script und die Ausführungsantwort werden möglicherweise über Zwischenspeicherorte zwischen Ihrem Client und dem Standort der Zielinstanz übertragen. Aus diesem Grund schlagen Anfragen für Instanzen in bestimmten Assured Workloads-Projekten fehl. Wenn Ihr Projekt nicht für Assured Workloads registriert ist, aber constraints/sql.restrictNoncompliantResourceCreation manuell erzwungen wird, bitten Sie den Administrator Ihrer Organisation, die Einschränkung zu entfernen. Das Problem wird dann bei neu erstellten Instanzen behoben.
The server principal USERNAME is not able to access the database DATABASE_NAME under the current security context.	Der Nutzer ist kein Mitglied der Datenbank. Stellen Sie eine Verbindung zur Datenbank als sqlserver-Nutzer her und fügen Sie den neuen Nutzer hinzu. Weisen Sie dann dem neuen Nutzer die Rolle db_owner für die Datenbank zu. Beispiel: EXEC sp_adduser 'user'; EXEC sp_addrolemember 'db_owner', 'user'
The database is currently unavailable.	Die Instanz wird möglicherweise neu gestartet, gewartet oder befindet sich in einem fehlerhaften Zustand. Prüfen Sie den Status der Instanz und versuchen Sie es später noch einmal.