Esegui istruzioni SQL utilizzando l'API Cloud SQL Data

Questa pagina descrive come eseguire istruzioni SQL sui database nelle istanze Cloud SQL utilizzando l'API Data. Con l'API Data, utilizzi l'API Cloud SQL Admin e gcloud CLI per eseguire istruzioni SQL su qualsiasi istanza in cui hai abilitato l'accesso all'API Data.

Puoi utilizzare l'API Data con istanze che utilizzano indirizzi IP pubblici, accesso privato ai servizi o Private Service Connect. L'API Data supporta tutti i tipi di istruzioni SQL, tra cui Data Manipulation Language (DML), Data Definition Language (DDL) e Data Query Language (DQL). L'API Data è utile per eseguire istruzioni amministrative piccole e rapide, come la creazione di ruoli o utenti del database e l'esecuzione di piccoli aggiornamenti dello schema.

Prima di iniziare

Prima di poter eseguire istruzioni SQL su un'istanza, segui questi passaggi.

Configura l'utente del database

L'API di dati deve autenticarsi come utente del database per eseguire istruzioni SQL.

Per eseguire l'autenticazione come utente integrato utilizzando la password, segui questi passaggi:

  1. Crea un account utente con una password non vuota. Puoi anche utilizzare l'utente predefinito sqlserver.
  2. Concedi all'account i ruoli o i privilegi richiesti per eseguire istruzioni SQL. Se l'utente non è sqlserver, concedigli il ruolo db_owner.
  3. Utilizza Secret Manager per creare un secret regionale in cui archiviare la password. Per motivi di sicurezza, l'API di dati richiede il nome della risorsa del secret anziché la password nella richiesta API. Il secret regionale deve essere archiviato nella stessa regione dell'istanza Cloud SQL. Un secret creato utilizzando l'endpoint globale di Secret Manager non è supportato anche se è archiviato nella stessa regione.
  4. Concedi roles/secretmanager.secretAccessor al chiamante dell'API di dati. Come best practice, definisci le condizioni IAM per consentire a un utente di accedere a un secret specifico, ma non ad altri secret nel progetto.

Ruoli o autorizzazioni richiesti

Gli account utente o di servizio utilizzati per chiamare l'API di dati devono disporre dell'autorizzazione per eseguire istruzioni SQL, cloudsql.instances.executesql. L'autorizzazione è inclusa in uno dei seguenti ruoli predefiniti:

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

Puoi anche definire un ruolo IAM personalizzato per l'utente o il account di servizio che include l'autorizzazione cloudsql.instances.executesql. Questa autorizzazione è supportata nei ruoli IAM personalizzati.

Quando utilizzi un secret di Secret Manager per l'autenticazione, l'utente o il service account deve disporre anche dell'autorizzazione per accedere al secret, secretmanager.versions.access. L'autorizzazione è inclusa in uno dei seguenti ruoli predefiniti:

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

Attiva o disattiva l'API di dati

Per utilizzare l'API di dati, devi abilitarla per ogni istanza. Puoi disattivare l'API di dati in qualsiasi momento.

Console

  1. Nella console Google Cloud , vai alla pagina Istanze Cloud SQL.

    Vai a Istanze Cloud SQL

  2. Per aprire la pagina Panoramica di un'istanza, fai clic sul nome dell'istanza.
  3. Dal menu di navigazione SQL, seleziona Connessioni.
  4. Fai clic sulla scheda Networking.
  5. Seleziona la casella di controllo Consenti API di dati.
  6. Fai clic su Salva.

gcloud

Per abilitare l'accesso all'API di dati su un'istanza, utilizza il comando gcloud sql instances patch con il flag --data-api-access=ALLOW_DATA_API:

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

Per disattivare l'accesso all'API di dati, utilizza il flag --data-api-access=DISALLOW_DATA_API:

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

Sostituisci INSTANCE_NAME con il nome dell'istanza su cui attivare o disattivare l'API di dati.

REST

Per abilitare l'accesso all'API di dati su un'istanza, invia una richiesta PATCH all'endpoint instances.patch:

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

Il corpo della richiesta deve contenere il campo dataApiAccess impostato su ALLOW_DATA_API:

{
  "dataApiAccess": "ALLOW_DATA_API"
}

Per disattivare l'accesso all'API di dati, imposta dataApiAccess su DISALLOW_DATA_API.

Esegui un'istruzione SQL

Puoi eseguire istruzioni SQL sui database della tua istanza Cloud SQL utilizzando gcloud CLI o l'API REST.

Esegui l'autenticazione usando la password

Puoi eseguire istruzioni SQL utilizzando l'autenticazione con password integrata, se la password è archiviata come secret regionale con Secret Manager nella stessa regione dell'istanza Cloud SQL.

gcloud

Per eseguire un'istruzione SQL su un database in un'istanza utilizzando gcloud CLI, utilizza il comando 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

Effettua le seguenti sostituzioni:

  • INSTANCE_NAME: il nome dell'istanza
  • DATABASE_NAME: il nome del database all'interno dell'istanza.
  • SQL_STATEMENT: l'istruzione SQL da eseguire. Se l'istruzione contiene spazi o caratteri speciali della shell, deve essere racchiusa tra virgolette.
  • USER: l'utente del database con cui eseguire l'autenticazione.
  • PASSWORD_SECRET_VERSION: il nome della risorsa del secret di Secret Manager contenente la password dell'utente del database. Il secret deve essere un secret regionale e deve essere archiviato nella stessa regione dell'istanza Cloud SQL. Il formato del nome della risorsa previsto è projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
  • PARTIAL_RESULT_MODE: facoltativo. Controlla come rispondere quando il risultato è incompleto. Può essere ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT o PARTIAL_RESULT_MODE_UNSPECIFIED. Consulta Modifica del comportamento di troncamento.

Terraform

Puoi utilizzare l'API Data su Terraform per eseguire il provisioning di risorse nel database, come database, tabelle, estensioni, utenti e concessioni di privilegi, senza connetterti manualmente all'istanza. Per eseguire uno script SQL su Terraform, utilizza la risorsa Terraform 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
  ]
}

Applica le modifiche

Per applicare la configurazione di Terraform in un progetto Google Cloud , completa i passaggi descritti nelle sezioni seguenti.

Prepara Cloud Shell

  1. Avvia Cloud Shell.

  2. Imposta il progetto Google Cloud predefinito in cui vuoi applicare le configurazioni Terraform.

    Devi eseguire questo comando una sola volta per progetto e puoi eseguirlo in qualsiasi directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Le variabili di ambiente vengono sostituite se imposti valori espliciti nel file di configurazione Terraform.

Prepara la directory

Ogni file di configurazione Terraform deve avere la propria directory (chiamata anche modulo principale).

  1. In Cloud Shell, crea una directory e un nuovo file al suo interno. Il nome file deve avere l'estensione .tf, ad esempio main.tf. In questo tutorial, il file è denominato main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Se stai seguendo un tutorial, puoi copiare il codice campione in ogni sezione o passaggio.

    Copia il codice campione nel file main.tf appena creato.

    Facoltativamente, copia il codice da GitHub. Questa opzione è consigliata quando lo snippet Terraform fa parte di una soluzione end-to-end.

  3. Esamina e modifica i parametri di esempio da applicare al tuo ambiente.
  4. Salva le modifiche.
  5. Inizializza Terraform. Devi eseguire questa operazione una sola volta per directory. 
    terraform init

    Facoltativamente, per utilizzare l'ultima versione del provider Google, includi l'opzione -upgrade: 

    terraform init -upgrade

Applica le modifiche

  1. Esamina la configurazione e verifica che le risorse che Terraform creerà o aggiornerà corrispondano alle tue aspettative: 
    terraform plan

    Apporta le correzioni necessarie alla configurazione.

  2. Applica la configurazione di Terraform eseguendo questo comando e inserendo yes al prompt: 
    terraform apply

    Attendi finché Terraform non visualizza il messaggio "Apply complete!".

  3. Apri il tuo progetto Google Cloud per visualizzare i risultati. Nella console Google Cloud , vai alle risorse nell'interfaccia utente per assicurarti che Terraform le abbia create o aggiornate.

Elimina le modifiche

L'eliminazione di una risorsa google_sql_provision_script non comporta l'eliminazione delle risorse nel database che ha creato. Per eliminarli, puoi aggiungere esplicitamente istruzioni nello script, ad esempio drop ... if exists, e poi applicare le modifiche.

REST

Per eseguire un'istruzione SQL su un database in un'istanza utilizzando l'API REST, invia una richiesta POST all'endpoint executeSql:

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

Il corpo della richiesta deve contenere il nome del database e l'istruzione SQL:

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

Effettua le seguenti sostituzioni:

  • PROJECT_ID: il tuo ID progetto.
  • INSTANCE_NAME: il nome dell'istanza
  • DATABASE_NAME: il nome del database all'interno dell'istanza.
  • SQL_STATEMENT: l'istruzione SQL da eseguire.
  • USER: l'utente del database con cui eseguire l'autenticazione.
  • PASSWORD_SECRET_VERSION: il nome della risorsa del secret di Secret Manager contenente la password dell'utente del database. Il secret deve essere un secret regionale e deve essere archiviato nella stessa regione dell'istanza Cloud SQL. Il formato del nome della risorsa previsto è projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
  • PARTIAL_RESULT_MODE: facoltativo. Controlla il modo in cui la risposta dell'API supera i 10 MB. Può essere FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT o PARTIAL_RESULT_MODE_UNSPECIFIED. Consulta Modifica del comportamento di troncamento.

Modifica il comportamento di troncamento

Puoi controllare il modo in cui vengono gestiti i risultati di grandi dimensioni durante l'esecuzione di SQL includendo il campo "partialResultMode" nella richiesta. Questo campo accetta i seguenti valori:

  • FAIL_PARTIAL_RESULT: predefinito. Genera un errore se il risultato supera i 10 MB o se è possibile recuperare solo un risultato parziale. Non restituire il risultato.
  • ALLOW_PARTIAL_RESULT: restituisce un risultato troncato e imposta partial_result su true se il risultato supera i 10 MB o se è possibile recuperare solo un risultato parziale a causa di un errore. Non generare un errore.
  • PARTIAL_RESULT_MODE_UNSPECIFIED: modalità non specificata, di fatto uguale a FAIL_PARTIAL_RESULT.

Query di controllo

Puoi monitorare il nome dell'applicazione impostando il campo applicationName nella richiesta. Il database monitorerà il nome dell'applicazione nelle statistiche della sessione, ad esempio nella tabella sys.dm_exec_sessions.

Puoi utilizzare Query Insights per monitorare ulteriori informazioni sulle query e analizzare i problemi di prestazioni.

Puoi anche utilizzare il controllo dei database SQL Server per registrare le query per motivi di sicurezza o conformità.

Limitazioni

  • Il limite di dimensioni per una risposta è 10 MB. I risultati che superano questa dimensione vengono troncati se partialResultMode è impostato su ALLOW_PARTIAL_RESULT, altrimenti viene generato un errore.
  • Le richieste sono limitate a 0,5 MB.
  • Puoi eseguire istruzioni SQL solo per le istanze Cloud SQL per SQL Server in esecuzione.
  • Cloud SQL non supporta l'utilizzo dell'API di dati con istanze configurate per la replica di server esterni.
  • Le richieste che richiedono più di 30 secondi vengono annullate. L'impostazione di un timeout dell'istruzione più elevato utilizzando SET LOCK_TIMEOUT non è supportata.

  • Cloud SQL limita il numero di richieste executeSql simultanee per istanza per evitare il sovraccarico. Se il limite viene raggiunto, le richieste successive non vanno a buon fine e restituiscono uno dei seguenti errori:

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

    Il limite (x) è di 5 query per le istanze con meno di 10 GB di memoria totale e di 10 query per le istanze con almeno 10 GB di memoria totale.

  • Ogni risposta può contenere un massimo di 10 messaggi o avvisi del database.

  • Se si verifica un errore di sintassi o di esecuzione dell'istruzione, non viene restituito alcun risultato.

  • L'API Data non può autenticarsi come utente integrato con password vuote.

  • L'API di dati può essere temporaneamente bloccata per motivi di integrità dei dati quando sono in corso determinate operazioni di manutenzione sull'istanza. Se si verifica questo problema, riprova più tardi.

  • Il comando GO non è supportato. Questo comando viene utilizzato nelle utilità di Microsoft SQL Server per indicare che un gruppo di istruzioni è terminato e può essere inviato a SQL Server.

  • Se una query include una colonna binaria, l'API di dati non può mostrarla. Converti i valori binari in una stringa.

    Ad esempio, sostituisci:

    SELECT my_binary_column from my_table2;

    with:

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

  • Quando esegui più query e una di queste non va a buon fine, viene restituito il primo errore riscontrato. Alcune delle istruzioni nel batch precedenti all'errore potrebbero essere state eseguite correttamente. Per evitare questo problema, puoi aggregare più query in un'istruzione transaction:

    BEGIN TRANSACTION
    YOUR_SQL_STATEMENTS
COMMIT;

    Sostituisci quanto segue:

    • YOUR_SQL_STATEMENTS: le istruzioni che vuoi eseguire nell'ambito di questa query
  • Lo script SQL e la relativa risposta di esecuzione potrebbero transitare attraverso località intermedie tra il client e la località dell'istanza di destinazione. Per questo motivo, le richieste non andranno a buon fine con l'errore "non supportato per le istanze in determinate cartelle dei pacchetti di controllo di Assured Workloads" per alcuni progetti Assured Workloads e per i progetti con constraints/sql.restrictNoncompliantResourceCreation applicato manualmente.

Risoluzione dei problemi

Questa sezione contiene informazioni sui problemi associati all'utilizzo dell'API di dati, nonché i passaggi per la risoluzione dei problemi.

Problema Risoluzione dei problemi
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" }} L'API di dati è disattivata per impostazione predefinita. Abilita l'API di dati nell'istanza per risolvere il problema.
Secret cannot be provided when auto_iam_authn is true. Quando imposti auto_iam_authn su true, esegui l'autenticazione nel database utilizzando IAM. Questo metodo di autenticazione non richiede una password o un secret. Consulta Autenticati utilizzando IAM.
ExecuteSql API is not supported for instances in certain Assured Workloads control packages folders yet. Lo script SQL e la relativa risposta di esecuzione potrebbero transitare attraverso località intermedie tra il client e la località dell'istanza di destinazione. Per questo motivo, le richieste non andranno a buon fine per le istanze in determinati progetti Assured Workloads. Se il tuo progetto non è registrato in Assured Workloads, ma constraints/sql.restrictNoncompliantResourceCreation viene applicato manualmente, chiedi all'amministratore della tua organizzazione di rimuovere il vincolo e il problema verrà risolto nelle istanze appena create.
The server principal USERNAME is not able to access the database DATABASE_NAME under the current security context. L'utente non è un membro del database. Connettiti al database come utente sqlserver e aggiungi il nuovo utente, quindi assegna al nuovo utente il ruolo db_owner per il database. Ad esempio: 
  EXEC sp_adduser 'user';
  EXEC sp_addrolemember 'db_owner', 'user'
The database is currently unavailable. L'istanza potrebbe essere in fase di riavvio, in manutenzione o in uno stato non integro. Controlla lo stato dell'istanza e riprova più tardi.