Esegui istruzioni SQL utilizzando l'API Cloud SQL Data

MySQL   |  PostgreSQL   |  SQL Server

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 attivato l'accesso all'API Data.

Puoi utilizzare l'API di dati 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, inclusi DML (Data Manipulation Language), DDL (Data Definition Language) e DQL (Data Query Language). L'API Data è adatta per eseguire istruzioni amministrative piccole e rapide, ad esempio 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 Data 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 le istruzioni SQL. Se l'utente non è sqlserver, concedi all'utente il ruolo db_owner.
3. Utilizza Secret Manager per creare un secret regionale per archiviare la password. Per motivi di sicurezza, l'API Data 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 l'accesso al chiamante dell'API Data roles/secretmanager.secretAccessor. 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 Data 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 personalizzato IAM per l'utente o il account di servizio che include l'autorizzazione cloudsql.instances.executesql. Questa autorizzazione è supportata nei ruoli personalizzati IAM.

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)

Attivare o disattivare l'API Data

Per utilizzare l'API di dati, devi abilitarla per ogni istanza. Puoi disattivare l'API Data 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 Data.
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 Data.

REST

Per attivare 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 Data, imposta dataApiAccess su DISALLOW_DATA_API.

Esegui un'istruzione SQL

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

Esegui l'autenticazione utilizzando la password

Puoi eseguire istruzioni SQL utilizzando l'autenticazione con password integrata quando 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 di 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 da autenticare.
• 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. Vedi Modificare il comportamento di troncamento.

Terraform

Puoi utilizzare l'API di dati su Terraform per eseguire il provisioning di risorse nel database, ad esempio 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 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.

Preparare la directory

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

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 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 main.tf appena creato.

   (Facoltativo) Copia il codice da GitHub. Questa opzione è consigliata quando lo snippet Terraform fa parte di una soluzione end-to-end.

3. Rivedi 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

   (Facoltativo) Per utilizzare l'ultima versione del provider Google, includi l'opzione -upgrade:

   terraform init -upgrade

Applica le modifiche

1. Rivedi 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 questo comando e inserendo yes al prompt:

   terraform apply

   Attendi che Terraform visualizzi il messaggio "Apply complete!".

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

Nota: gli esempi di Terraform in genere presuppongono che le API richieste siano attive nel tuo progetto Google Cloud .

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 istruzioni esplicite 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"
}

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 da autenticare.
• 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 il modo in cui l'API risponde quando il risultato supera i 10 MB. Può essere FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT o PARTIAL_RESULT_MODE_UNSPECIFIED. Vedi 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 campo "partialResultMode" nella richiesta. Questo campo accetta i seguenti valori:

• FAIL_PARTIAL_RESULT: predefinito. Genera un errore se il risultato supera 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 di sessione, ad esempio nella tabella sys.dm_exec_sessions.

Puoi utilizzare Query Insights per monitorare più informazioni sulle tue query e analizzare i problemi di rendimento.

Puoi anche utilizzare l'audit del database SQL Server per registrare le query a fini di sicurezza o conformità.

Limitazioni

• La dimensione massima di una risposta è 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 Data 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 il limite viene raggiunto, le richieste successive non vanno a buon fine e viene restituito 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ò eseguire l'autenticazione come utenti integrati con password vuote.

• L'API Data può essere bloccata temporaneamente per motivi di integrità dei dati quando sono in corso determinate operazioni di manutenzione sull'istanza. Se si verifica, 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 Data non può mostrarla. Converti invece 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 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 nell'ambito di questa query

• Lo script SQL e la relativa risposta di esecuzione potrebbero transitare attraverso località intermedie tra il client e la posizione 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 Assured Workloads) per determinati 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 Data, 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 Data è disabilitata per impostazione predefinita. Abilita l'API di dati sull'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 segreto. Consulta la pagina Autenticarsi 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 posizione 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 è applicato manualmente, chiedi all'amministratore della tua organizzazione di rimuovere il vincolo e il problema verrà risolto sulle istanze appena create.
The server principal USERNAME is not able to access the database DATABASE_NAME under the current security context.	L'utente non è membro del database. Connettiti al database come utente sqlserver e aggiungi il nuovo utente, quindi assegna a quest'ultimo 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.