Esegui istruzioni SQL utilizzando l'API Cloud SQL Data

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

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

Prima di iniziare

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

Configurare l'utente del database

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

Per autenticarti come utente integrato utilizzando la password:

  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 necessari per eseguire istruzioni SQL. Se l'utente non è sqlserver, concedi all'utente 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 della tua istanza Cloud SQL. I secret creati utilizzando l'endpoint globale di Secret Manager non sono supportati, anche se sono archiviati nella stessa regione.
  4. Come best practice, definisci IAM condizioni per consentire a un utente di accedere a un secret specifico, ma non ad altri secret nel progetto.

Ruoli o autorizzazioni richiesti

Per impostazione predefinita, gli account utente o di servizio con uno dei seguenti ruoli hanno l'autorizzazione per eseguire istruzioni SQL su un'istanza Cloud SQL (cloudsql.instances.executesql):

  • 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 personalizzato IAM per l'utente o l'account di servizio che includa l'cloudsql.instances.executesql autorizzazione. Questa autorizzazione è supportata nei ruoli personalizzati IAM.

Abilitare o disabilitare l'API di dati

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

Console

  1. Nella Google Cloud console, 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 gcloud sql instances patch comando con il --data-api-access=ALLOW_DATA_API flag:

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

Per disabilitare 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 abilitare o disabilitare l'API di dati.

Eseguire un'istruzione SQL

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

Autenticarsi utilizzando la password

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

gcloud

Per eseguire un'istruzione SQL su un database di un'istanza utilizzando il gcloud CLI, utilizza il gcloud sql instances execute-sql comando.

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

Esegui 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 che contiene la password per l'utente del database. Il secret deve essere un secret regionale e archiviato nella stessa regione dell'istanza Cloud SQL. Il formato previsto per il nome della risorsa è 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 Modificare il comportamento di troncamento.

Terraform

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

Applica le modifiche

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

Preparare Cloud Shell

  1. Avvia Cloud Shell.

  2. Imposta il Google Cloud progetto 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

Preparare la directory

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

  1. In Cloud Shell, crea una directory e un nuovo file all'interno della directory. Il nome del file deve avere l' .tf estensione, ad esempio main.tf. In questo tutorial, il file viene chiamato 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 operazione è 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 la versione più recente 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 Terraform eseguendo il seguente comando e inserendo yes al prompt: 
    terraform apply

    Attendi finché Terraform non visualizza il messaggio "Apply complete!" (Applicazione completata).

  3. Apri il Google Cloud progetto per visualizzare i risultati. Nella Google Cloud console, 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 elimina le risorse in-database che ha creato. Per eliminarle, 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 di 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"
}

Esegui 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 che contiene la password per l'utente del database. Il secret deve essere un secret regionale e archiviato nella stessa regione dell'istanza Cloud SQL. Il formato previsto per il nome della risorsa è projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
  • PARTIAL_RESULT_MODE: facoltativo. Controlla come risponde l'API quando il risultato supera i 10 MB. Può essere FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT o PARTIAL_RESULT_MODE_UNSPECIFIED. Consulta Modificare il comportamento di troncamento.

Modificare il comportamento di troncamento

Puoi controllare la gestione dei risultati di grandi dimensioni durante l'esecuzione di SQL includendo il "partialResultMode" campo 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, effettivamente uguale a FAIL_PARTIAL_RESULT.

Limitazioni

  • Il limite di dimensioni per una risposta è di 10 MB. I risultati che superano queste dimensioni 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 le istanze configurate per la replica del server esterno.
  • 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 viene raggiunto il limite, 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 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 di dati non può autenticarsi come utenti integrati con password vuote.

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

  • Il comando GO non è supportato. Questo comando viene utilizzato nelle utilità di Microsoft SQL Server per indicare che un batch 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 invece i valori binari in una stringa.

    Ad esempio, sostituisci:

    SELECT my_binary_column from my_table2;

    con:

    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 rilevato. Alcune delle istruzioni nel batch prima dell'errore potrebbero essere state eseguite correttamente. Puoi racchiudere più query in un'istruzione transaction per evitare questo problema:

    BEGIN TRANSACTION
    YOUR_SQL_STATEMENTS
COMMIT;

    Sostituisci quanto segue:

    • YOUR_SQL_STATEMENTS: le istruzioni che vuoi eseguire come parte 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 e verrà visualizzato l'errore "not supported for instances in certain Assured Workloads control packages folders" (non supportato per le istanze in determinate cartelle dei pacchetti di controllo di Assured Workloads) per determinati progetti Assured Workloads e per i progetti con constraints/sql.restrictNoncompliantResourceCreation applicato manualmente.