Exécuter des instructions SQL à l'aide de l'API Cloud SQL Data

MySQL | PostgreSQL | SQL Server

Cette page explique comment exécuter des instructions SQL sur des bases de données d'instances Cloud SQL à l'aide de l'API Data. Avec l'API Data, vous utilisez l'API Cloud SQL Admin et gcloud CLI pour exécuter des instructions SQL sur n'importe quelle instance sur laquelle vous avez activé l'accès à l'API Data.

Vous pouvez utiliser l'API Data avec des instances qui utilisent des adresses IP publiques, l'accès aux services privés ou Private Service Connect. L'API Data est compatible avec tous les types d'instructions SQL, y compris le langage de manipulation de données (LMD), le langage de définition de données (LDD) et le langage de requête de données (DQL). L'API Data est idéale pour exécuter des instructions administratives rapides et de petite taille, comme la création de rôles ou d'utilisateurs de base de données, et pour effectuer de petites mises à jour de schéma.

Avant de commencer

Avant de pouvoir exécuter des instructions SQL sur une instance, procédez comme suit.

Configurer l'utilisateur de la base de données

L'API Data doit s'authentifier en tant qu'utilisateur de base de données pour exécuter des instructions SQL.

Pour vous authentifier en tant qu'utilisateur intégré à l'aide d'un mot de passe, procédez comme suit :

1. Créez un compte utilisateur avec un mot de passe non vide. Vous pouvez également utiliser l'utilisateur par défaut sqlserver.
2. Accordez au compte les rôles ou droits requis pour exécuter des instructions SQL. Si l'utilisateur n'est pas sqlserver, accordez-lui le rôle db_owner.
3. Utilisez Secret Manager pour créer un secret régional afin de stocker le mot de passe. Pour des raisons de sécurité, l'API Data demande le nom de ressource du secret au lieu du mot de passe dans la requête API. Le secret régional doit être stocké dans la même région que votre instance Cloud SQL. Les secrets créés à l'aide du point de terminaison mondial de Secret Manager ne sont pas compatibles, même s'ils sont stockés dans la même région.
4. Accorde l'roles/secretmanager.secretAccessor à l'appelant de l'API Data. Il est recommandé de définir des conditions IAM pour permettre à un utilisateur d'accéder à un secret spécifique, mais pas aux autres secrets du projet.

Rôles ou autorisations requis

Les comptes de service ou d'utilisateur utilisés pour appeler l'API Data doivent disposer de l'autorisation d'exécuter des instructions SQL, cloudsql.instances.executesql. Cette autorisation est incluse dans l'un des rôles prédéfinis suivants :

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

Vous pouvez également définir un rôle personnalisé IAM pour le compte d'utilisateur ou le compte de service, qui inclut l'autorisation cloudsql.instances.executesql. Cette autorisation est compatible avec les rôles personnalisés IAM.

Lorsque vous utilisez un secret Secret Manager pour l'authentification, l'utilisateur ou le compte de service doit également disposer de l'autorisation d'accéder au secret, secretmanager.versions.access. Cette autorisation est incluse dans l'un des rôles prédéfinis suivants :

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

Activer ou désactiver l'API Data

Pour utiliser l'API Data, vous devez l'activer pour chaque instance. Vous pouvez désactiver l'API Data à tout moment.

Console

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom.
3. Dans le menu de navigation SQL, sélectionnez Connexions.
4. Cliquez sur l'onglet Réseau.
5. Cochez la case Autoriser l'API Data.
6. Cliquez sur Enregistrer.

gcloud

Pour activer l'accès à l'API Data sur une instance, utilisez la commande gcloud sql instances patch avec l'indicateur --data-api-access=ALLOW_DATA_API :

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

Pour désactiver l'accès à l'API Data, utilisez l'option --data-api-access=DISALLOW_DATA_API :

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

Remplacez INSTANCE_NAME par le nom de l'instance sur laquelle activer ou désactiver l'API Data.

REST

Pour activer l'accès à l'API Data sur une instance, envoyez une requête PATCH au point de terminaison instances.patch :

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

Le corps de la requête doit contenir le champ dataApiAccess défini sur ALLOW_DATA_API :

{
  "dataApiAccess": "ALLOW_DATA_API"
}

Pour désactiver l'accès à l'API Data, définissez dataApiAccess sur DISALLOW_DATA_API.

Exécuter une instruction SQL

Vous pouvez exécuter des instructions SQL sur les bases de données de votre instance Cloud SQL à l'aide de la gcloud CLI ou de l'API REST.

S'authentifier à l'aide d'un mot de passe

Vous pouvez exécuter des instructions SQL à l'aide de l'authentification par mot de passe intégrée, lorsque le mot de passe est stocké en tant que secret régional avec Secret Manager dans la même région que l'instance Cloud SQL.

gcloud

Pour exécuter une instruction SQL sur une base de données d'une instance à l'aide de la gcloud CLI, utilisez la commande 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

Effectuez les remplacements suivants :

• INSTANCE_NAME : nom de l'instance.
• DATABASE_NAME : nom de la base de données dans l'instance.
• SQL_STATEMENT : instruction SQL à exécuter. Si l'instruction contient des espaces ou des caractères spéciaux du shell, elle doit être placée entre guillemets.
• USER : utilisateur de la base de données pour l'authentification.
• PASSWORD_SECRET_VERSION : nom de ressource du secret Secret Manager contenant le mot de passe de l'utilisateur de la base de données. Le secret doit être régional et stocké dans la même région que l'instance Cloud SQL. Le format attendu pour le nom de ressource est projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE (facultatif) : Contrôle la manière de répondre lorsque le résultat est incomplet. Il peut s'agir de ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT ou PARTIAL_RESULT_MODE_UNSPECIFIED. Consultez Modifier le comportement de troncature.

Terraform

Vous pouvez utiliser l'API Data sur Terraform pour provisionner des ressources dans la base de données, telles que des bases de données, des tables, des extensions, des utilisateurs et des droits d'accès, sans vous connecter manuellement à l'instance. Pour exécuter un script SQL sur Terraform, utilisez la ressource 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
  ]
}

Appliquer les modifications

Pour appliquer votre configuration Terraform dans un projet Google Cloud , suivez les procédures des sections suivantes.

Préparer Cloud Shell

1. Lancez Cloud Shell.

2. Définissez le projet Google Cloud par défaut dans lequel vous souhaitez appliquer vos configurations Terraform.

   Vous n'avez besoin d'exécuter cette commande qu'une seule fois par projet et vous pouvez l'exécuter dans n'importe quel répertoire.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Les variables d'environnement sont remplacées si vous définissez des valeurs explicites dans le fichier de configuration Terraform.

Préparer le répertoire

Chaque fichier de configuration Terraform doit avoir son propre répertoire (également appelé module racine).

1. Dans Cloud Shell, créez un répertoire et un nouveau fichier dans ce répertoire. Le nom du fichier doit comporter l'extension .tf, par exemple main.tf. Dans ce tutoriel, le fichier est appelé main.tf.

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

2. Si vous suivez un tutoriel, vous pouvez copier l'exemple de code dans chaque section ou étape.

   Copiez l'exemple de code dans le fichier main.tf que vous venez de créer.

   Vous pouvez également copier le code depuis GitHub. Cela est recommandé lorsque l'extrait Terraform fait partie d'une solution de bout en bout.

3. Examinez et modifiez les exemples de paramètres à appliquer à votre environnement.
4. Enregistrez les modifications.
5. Initialisez Terraform. Cette opération n'est à effectuer qu'une seule fois par répertoire.

   terraform init

   Vous pouvez également utiliser la dernière version du fournisseur Google en incluant l'option -upgrade :

   terraform init -upgrade

Appliquer les modifications

1. Examinez la configuration et vérifiez que les ressources que Terraform va créer ou mettre à jour correspondent à vos attentes :

   terraform plan

   Corrigez les modifications de la configuration si nécessaire.

2. Appliquez la configuration Terraform en exécutant la commande suivante et en saisissant yes lorsque vous y êtes invité :

   terraform apply

   Attendez que Terraform affiche le message "Apply completed!" (Application terminée).

3. Ouvrez votre projet Google Cloud pour afficher les résultats. Dans la console Google Cloud , accédez à vos ressources dans l'interface utilisateur pour vous assurer que Terraform les a créées ou mises à jour.

Remarque : Les exemples Terraform partent généralement du principe que les API requises sont activées dans votre projet Google Cloud .

Supprimer les modifications

La suppression d'une ressource google_sql_provision_script n'entraîne pas la suppression des ressources dans la base de données qu'elle a créées. Pour les supprimer, vous pouvez ajouter explicitement des instructions dans le script, telles que drop ... if exists, puis appliquer les modifications.

REST

Pour exécuter une instruction SQL sur une base de données d'une instance à l'aide de l'API REST, envoyez une requête POST au point de terminaison executeSql :

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

Le corps de la requête doit contenir le nom de la base de données et l'instruction SQL :

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

Effectuez les remplacements suivants :

• PROJECT_ID : ID de votre projet.
• INSTANCE_NAME : nom de l'instance.
• DATABASE_NAME : nom de la base de données dans l'instance.
• SQL_STATEMENT : instruction SQL à exécuter.
• USER : utilisateur de la base de données pour l'authentification.
• PASSWORD_SECRET_VERSION : nom de ressource du secret Secret Manager contenant le mot de passe de l'utilisateur de la base de données. Le secret doit être régional et stocké dans la même région que l'instance Cloud SQL. Le format attendu pour le nom de ressource est projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE (facultatif) : Contrôle la façon dont l'API répond lorsque le résultat dépasse 10 Mo. Il peut s'agir de FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT ou PARTIAL_RESULT_MODE_UNSPECIFIED. Consultez Modifier le comportement de troncature.

Modifier le comportement de troncature

Vous pouvez contrôler la façon dont les résultats volumineux sont traités lors de l'exécution de SQL en incluant le champ "partialResultMode" dans la requête. Ce champ accepte les valeurs suivantes :

• FAIL_PARTIAL_RESULT : valeur par défaut. Génère une erreur si le résultat dépasse 10 Mo ou si seul un résultat partiel peut être récupéré. Ne renvoie pas le résultat.
• ALLOW_PARTIAL_RESULT : renvoie un résultat tronqué et définit partial_result sur "true" si le résultat dépasse 10 Mo ou si seul un résultat partiel peut être récupéré en raison d'une erreur. Ne générez pas d'erreur.
• PARTIAL_RESULT_MODE_UNSPECIFIED : mode non spécifié, effectivement identique à FAIL_PARTIAL_RESULT.

Requêtes d'audit

Vous pouvez suivre le nom de l'application en définissant le champ applicationName dans la requête. La base de données suivra le nom de l'application dans les statistiques de session, par exemple dans le tableau sys.dm_exec_sessions.

Vous pouvez utiliser Insights sur les requêtes pour suivre plus d'informations sur vos requêtes et analyser les problèmes de performances.

Vous pouvez également utiliser l'audit de base de données SQL Server pour consigner les requêtes à des fins de sécurité ou de conformité.

Limites

• La taille maximale d'une réponse est de 10 Mo. Les résultats dépassant cette taille sont tronqués si partialResultMode est défini sur ALLOW_PARTIAL_RESULT. Dans le cas contraire, une erreur est générée.
• Les requêtes sont limitées à 0,5 Mo.
• Vous ne pouvez exécuter des instructions SQL que pour des instances Cloud SQL pour SQL Server en cours d'exécution.
• Cloud SQL n'est pas compatible avec l'utilisation de l'API Data avec les instances configurées pour la réplication de serveur externe.
• Les requêtes qui prennent plus de 30 secondes sont annulées. Il n'est pas possible de définir un délai d'expiration d'instruction plus long à l'aide de SET LOCK_TIMEOUT.

• Cloud SQL limite le nombre de requêtes executeSql simultanées par instance pour éviter la surcharge. Si la limite est atteinte, les requêtes suivantes échouent et renvoient l'une des erreurs suivantes :

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

  La limite (x) est de cinq requêtes pour les instances dont la mémoire totale est inférieure à 10 Go, et de 10 requêtes pour les instances dont la mémoire totale est d'au moins 10 Go.

• Chaque réponse peut contenir jusqu'à 10 messages ou avertissements de base de données.

• En cas d'erreur de syntaxe ou d'exécution d'une instruction, aucun résultat n'est renvoyé.

• L'API Data ne peut pas s'authentifier en tant qu'utilisateurs intégrés avec des mots de passe vides.

• L'API Data peut être temporairement bloquée pour préserver l'intégrité des données lorsque certaines opérations de maintenance sont en cours sur l'instance. Si cela se produit, réessayez plus tard.

• La commande GO n'est pas acceptée. Cette commande est utilisée dans les utilitaires Microsoft SQL Server pour indiquer qu'un lot d'instructions est terminé et peut être envoyé à SQL Server.

• Si une requête inclut une colonne binaire, l'API Data ne peut pas l'afficher. Convertissez plutôt les valeurs binaires en chaîne.

  Par exemple, remplacez :

  SELECT my_binary_column from my_table2;
  

  avec :

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

• Lorsque vous exécutez plusieurs requêtes et que l'une d'elles échoue, la première erreur rencontrée est renvoyée. Il est possible que certaines instructions du lot avant l'erreur se soient exécutées correctement. Vous pouvez encapsuler plusieurs requêtes dans une instruction transaction pour éviter ce problème :

  BEGIN TRANSACTION
      YOUR_SQL_STATEMENTS
  COMMIT;
  

  Remplacez les éléments suivants :

  • YOUR_SQL_STATEMENTS : instructions que vous souhaitez exécuter dans le cadre de cette requête

• Le script SQL et sa réponse d'exécution peuvent transiter par des emplacements intermédiaires entre votre client et l'emplacement de l'instance cible. Pour cette raison, les requêtes échoueront avec l'erreur "not supported for instances in certain Assured Workloads control packages folders" (non compatible avec les instances dans certains dossiers de packages de contrôles Assured Workloads) pour certains projets Assured Workloads et pour les projets avec constraints/sql.restrictNoncompliantResourceCreation appliqué manuellement.

Résoudre les problèmes

Cette section contient des informations sur les problèmes associés à l'utilisation de l'API Data, ainsi que la procédure à suivre pour les résoudre.

Problème	Dépannage
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 est désactivée par défaut. Activez l'API Data sur l'instance pour résoudre le problème.
Secret cannot be provided when auto_iam_authn is true.	Lorsque vous définissez auto_iam_authn sur true, vous vous authentifiez auprès de la base de données à l'aide d'IAM. Cette méthode d'authentification ne nécessite pas de mot de passe ni de clé secrète. Consultez S'authentifier à l'aide d'IAM.
ExecuteSql API is not supported for instances in certain Assured Workloads control packages folders yet.	Le script SQL et sa réponse d'exécution peuvent transiter par des emplacements intermédiaires entre votre client et l'emplacement de l'instance cible. Pour cette raison, les requêtes échoueront pour les instances de certains projets Assured Workloads. Si votre projet n'est pas inscrit à Assured Workloads, mais que constraints/sql.restrictNoncompliantResourceCreation est appliqué manuellement, demandez à l'administrateur de votre organisation de supprimer la contrainte. Le problème sera résolu sur les instances nouvellement créées.
The server principal USERNAME is not able to access the database DATABASE_NAME under the current security context.	L'utilisateur n'est pas membre de la base de données. Connectez-vous à la base de données en tant qu'utilisateur sqlserver et ajoutez le nouvel utilisateur, puis attribuez-lui le rôle db_owner pour la base de données. Exemple : EXEC sp_adduser 'user'; EXEC sp_addrolemember 'db_owner', 'user'
The database is currently unavailable.	L'instance est peut-être en cours de redémarrage ou de maintenance, ou elle est peut-être dans un état non opérationnel. Vérifiez l'état de l'instance et réessayez plus tard.