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 pour 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 (LQD). L'API Data est utile pour exécuter des instructions d'administration rapides et de petite taille, telles que la création de rôles ou d'utilisateurs de base de données et l'apport de petites modifications 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 privilèges 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. Nous vous recommandons de définir IAM conditions pour autoriser un utilisateur à accéder à un secret spécifique, mais pas à d'autres secrets du projet.

Rôles ou autorisations requis

Par défaut, les comptes utilisateur ou comptes de service dotés 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'cloudsql.instances.executesql autorisation. 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 Google Cloud console, 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 gcloud sql instances patch commande avec l'option --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 des bases de données de votre instance Cloud SQL à l'aide de 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 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 de l'interface système, elle doit être entre guillemets.
• USER: utilisateur de base de données à authentifier.
• 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 un secret régional et être stocké dans la même région que l'instance Cloud SQL. Le format de nom de ressource attendu 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. Peut être ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT ou PARTIAL_RESULT_MODE_UNSPECIFIED. Consultez la section 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 octrois de privilèges, sans vous connecter manuellement à l'instance. Pour exécuter un script SQL sur Terraform, utilisez la google_sql_provision_script ressource 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
  ]
}

Appliquer les modifications

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

Préparer Cloud Shell

1. Lancez Cloud Shell.

2. Définissez le Google Cloud projet 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 Google Cloud projet pour afficher les résultats. Dans la Google Cloud console, 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 Google Cloud projet.

Supprimer les modifications

La suppression d'une ressource google_sql_provision_script ne supprime pas les 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 du 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 base de données à authentifier.
• 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 un secret régional et être stocké dans la même région que l'instance Cloud SQL. Le format de nom de ressource attendu est projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE : facultatif. Contrôle la manière dont l'API répond lorsque le résultat dépasse 10 Mo. Peut être FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT, ou PARTIAL_RESULT_MODE_UNSPECIFIED. Consultez la section Modifier le comportement de troncature.

Modifier le comportement de troncature

Vous pouvez contrôler la taille des résultats lors de l'exécution de SQL en incluant le "partialResultMode" champ 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ère pas d'erreur.
• PARTIAL_RESULT_MODE_UNSPECIFIED: mode non spécifié, qui est en fait le même que 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. Sinon, 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 des instances configurées pour la réplication de serveur externe.
• Les requêtes qui prennent plus de 30 secondes sont annulées. La définition d'un délai d'attente d'instruction plus élevé à l'aide de SET LOCK_TIMEOUT n'est pas acceptée.

• 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 un maximum de 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'utilisateur intégré avec des mots de passe vides.

• L'API Data peut être temporairement bloquée à des fins d'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. Certaines des instructions du lot précédant l'erreur peuvent avoir été 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 échouent avec l'erreur "not supported for instances in certain Assured Workloads control packages folders" (non compatible avec les instances de certains dossiers de packages de contrôle Assured Workloads) pour certains projets Assured Workloads et pour les projets pour lesquels constraints/sql.restrictNoncompliantResourceCreation est appliqué manuellement.