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. Vous pouvez vous authentifier en tant qu'utilisateur intégré, utilisateur IAM, compte de service IAM ou groupe IAM.

Pour vous authentifier à l'aide d'IAM, procédez comme suit :

1. Configurez l'instance pour l'authentification IAM pour les bases de données.
2. Ajoutez un utilisateur, un compte de service ou un groupe IAM à l'instance.
3. Accordez au compte les rôles ou privilèges requis pour exécuter des instructions SQL. Vous pouvez attribuer des rôles de base de données lorsque vous créez le compte ou le modifiez. Si vous avez créé des rôles de base de données personnalisés avec le minimum de privilèges, attribuez-les au compte. Sinon, attribuez le rôle prédéfini cloudsqlsuperuser au compte, utilisez l'API Data pour créer des rôles de base de données personnalisés avec moins de droits, puis accordez les nouveaux rôles au compte à la place de cloudsqlsuperuser.

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

1. Créez un utilisateur. Notez que l'API Data ne peut pas s'authentifier en tant qu'utilisateur root par défaut.
2. 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.
Pour un contrôle des accès plus efficace,
3. Définissez des conditions IAM facultatives pour autoriser un utilisateur à accéder à un secret spécifique, mais pas aux autres secrets du projet.

Rôles ou autorisations requis

Par défaut, les comptes utilisateur ou de service disposant de l'un des rôles suivants sont autorisés à exécuter des instructions SQL sur une instance 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)

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.

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.

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 gcloud CLI ou de l'API REST.

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.

Pour vous connecter à l'aide d'IAM :

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--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.
• 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.

Vous pouvez également inclure l'indicateur --project=PROJECT_ID si nécessaire.

Pour vous connecter en tant qu'utilisateur intégré à l'aide d'un mot de passe :

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 :

• USER : utilisateur de la base de données pour l'authentification. Omettez @ et le nom d'hôte.
• 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}.

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_database_instance" "instance" {
  name             = "my-instance"
  database_version = "MYSQL_8_4"

  settings {
    tier            = "db-perf-optimized-N-2"
    data_api_access = "ALLOW_DATA_API"  # This allows the use of Data API.
    database_flags {
      name  = "cloudsql_iam_authentication"
      value = "on"
    }
  }
}

/*
 * Create a database user for your account and grant roles so it has privilege
 * to access the database. Set the type to CLOUD_IAM_USER for huamn
 * account or CLOUD_IAM_SERVICE_ACCOUNT for service account.
 */
resource "google_sql_user" "iam_user" {
  name     = "[e-mail]"
  instance = google_sql_database_instance.instance.name
  type     = "CLOUD_IAM_USER"

  # Roles granted to the user. To follow the principle of least privilege, you
  can first use `google_sql_provision_script` to create custom database role(s)
  with lesser privileges and then assign them to this user in place of
  `cloudsqlsuperuser`.
  # This field doesn't support MySQL 5.6 and 5.7.
  database_roles = ["cloudsqlsuperuser"]
}

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 DATABASE pets;"
  instance = google_sql_database_instance.instance.name

  # Some of your queries may require a database. You can create and use a
  # database in the script or explicitly create and reference a database
  # like database = google_sql_database.database.name.

  description = "sql script to create DBs"

  # The identity account used to apply your Terraform config must exist as an
  # IAM user or IAM service account in the instance. Terraform connects to the
  # instance via IAM database authentication to execute the script.
  depends_on = [google_sql_user.iam_user]
}

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",
  "partialResultMode": "PARTIAL_RESULT_MODE"
  "autoIamAuthn": true
}

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.
• 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.

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 MySQL 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 SESSION MAX_EXECUTION_TIME. Pour Cloud SQL pour MySQL 5.6 et 5.7, l'expiration du délai des instructions LDD de longue durée peut entraîner un rollback en toute sécurité de fichiers ou de tables orphelins. Soyez prudent avec les instructions telles que ALTER TABLE sur les grandes tables.
• Cloud SQL limite le nombre de requêtes executeSql simultanées à 10 par instance et par utilisateur. Si cette limite est atteinte, les requêtes suivantes échouent avec le message "Au maximum 10 requêtes simultanées peuvent être exécutées sur cette instance. Réessayez plus tard" ou "Nombre maximal de lectures simultanées (10) atteint".
• 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é.
• Pour Cloud SQL pour MySQL, les notifications et les avertissements ne sont disponibles que pour la dernière instruction d'une exécution multi-instructions.
• Les instructions qui consomment une grande quantité de mémoire peuvent entraîner des erreurs de mémoire insuffisante. Pour savoir comment éviter ces erreurs, consultez Bonnes pratiques pour gérer l'utilisation de la mémoire. Une instance de base de données exécutée avec une utilisation élevée de la mémoire entraîne souvent des problèmes de performances, des blocages ou même des temps d'arrêt de la base de données.
• 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.
• 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ôle Assured Workloads) pour certains projets Assured Workloads et pour les projets avec constraints/sql.restrictNoncompliantResourceCreation appliqué manuellement.