Bonnes pratiques pour l'importation et l'exportation de données

Cette page présente les bonnes pratiques à suivre pour importer et exporter des données avec Cloud SQL. Pour obtenir des instructions détaillées sur l'importation de données dans Cloud SQL, consultez la page Importer des données. Pour obtenir des instructions détaillées sur l'exportation de vos données, que ce soit dans Cloud SQL ou dans une instance que vous gérez, consultez la page Exporter des données.

Bonnes pratiques pour l'importation et l'exportation

Voici les bonnes pratiques à adopter lors de l'importation et de l'exportation de données :

Ne pas utiliser les buckets "Paiements" du demandeur Cloud Storage

Vous ne pouvez pas utiliser de buckets Cloud Storage pour lesquels les paiements du demandeur sont activés pour réaliser des importations et des exportations depuis Cloud SQL.

Compresser les données afin de réduire les coûts

Cloud SQL permet d'importer et d'exporter des fichiers compressés ou non. Grâce à la compression, vous pouvez économiser un espace de stockage considérable sur Cloud Storage et réduire les coûts de stockage, en particulier lorsque vous exportez des instances volumineuses.

Lorsque vous exportez un fichier BAK, utilisez une extension de fichier .gz pour compresser les données. Lorsque vous importez un fichier avec une extension .gz, il est automatiquement décompressé.

Réduire les processus d'importation et d'exportation de longue durée

L'exécution d'opérations d'importation dans Cloud SQL et d'exportations à partir de Cloud SQL peut prendre beaucoup de temps selon la taille des données traitées. Cela peut avoir les conséquences suivantes :

  • Vous ne pouvez pas arrêter une opération d'instance Cloud SQL de longue durée.
  • Vous ne pouvez effectuer qu'une seule opération d'importation ou d'exportation à la fois pour chaque instance, tandis qu'une importation ou une exportation de longue durée bloque d'autres opérations, telles que les sauvegardes automatiques quotidiennes.

Vous pouvez réduire le temps nécessaire à l'exécution de chaque opération en utilisant la fonctionnalité d'importation ou d'exportation Cloud SQL avec de plus petits lots de données.

Pour les migrations de bases de données entières, vous devez généralement utiliser des fichiers BAK plutôt que des fichiers SQL pour les importations. En règle générale, l'importation à partir d'un fichier SQL prend beaucoup plus de temps qu'avec un fichier BAK.

Utiliser SqlPackage pour l'importation et l'exportation de données

Vous pouvez importer et exporter des données dans Cloud SQL à l'aide de SqlPackage. Il vous permet d'exporter une base de données SQL, y compris le schéma de base de données et les données utilisateur, vers un fichier BACPAC (.bacpac), et d'importer les données de schéma et de table depuis un fichier BACPAC vers une nouvelle base de données utilisateur.

SqlPackage utilise vos identifiants pour se connecter à SQL Server afin d'effectuer des importations et des exportations de bases de données. Il rend les migrations disponibles pour tous les utilisateurs de Cloud SQL. Pour effectuer des opérations d'importation et d'exportation, vous devez disposer des éléments suivants :

  • Un poste de travail connecté à votre instance, sur lequel vous pouvez exécuter SqlPackage. Pour en savoir plus sur les options de connectivité, consultez la section À propos des options de connexion.

  • SqlPackage installé sur votre système. Pour en savoir plus sur le téléchargement et l'installation de SqlPackage, consultez la documentation Microsoft.

  • Identifiants configurés pour accéder à votre instance. Pour en savoir plus sur la configuration des identifiants, consultez la section Comment s'authentifier auprès de Cloud SQL.

Exemples

Importer

Pour importer des données dans une base de données AdventureWorks2017, exécutez la commande suivante :

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Import /tsn:myTargetServer /tdn:AdventureWorks2017
/tu:myUsername /sf:mySourceFile
/TargetTrustServerCertificate:True /tp:myPassword

Ici,

  • mySourceFile est un fichier source que vous souhaitez utiliser comme source d'action depuis l'espace de stockage local. Si vous utilisez ce paramètre, aucun autre paramètre source n'est valide.
  • myTargetServer est le nom du serveur qui héberge la base de données cible.
  • myUsername est le nom d'utilisateur SQL Server que vous souhaitez utiliser pour accéder à la base de données cible.
  • myPassword est votre mot de passe dans les identifiants.

Pour en savoir plus, consultez la documentation Microsoft.

Exporter

Pour exporter des données depuis une base de données AdventureWorks2017, exécutez la commande suivante :

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Export /TargetFile:"myTargetFile"
/ssn:mySourceServer /su:myUsername /sdn:AdventureWorks2017
/SourceTrustServerCertificate:True /sp:myPassword

Ici,

  • myTargetFile est le fichier cible (un fichier .dacpac) que vous souhaitez utiliser comme cible de l'action plutôt que comme base de données. Si vous utilisez ce paramètre, aucun autre paramètre cible n'est valide. Ce paramètre n'est pas valide pour les actions qui ne sont compatibles qu'avec les cibles de base de données.
  • myUsername est le nom d'utilisateur SQL Server que vous souhaitez utiliser pour accéder à la base de données source.
  • mySourceServer est le nom du serveur qui héberge la base de données source.
  • myPassword est votre mot de passe dans les identifiants.

Pour en savoir plus, consultez la documentation Microsoft.

Utiliser l'utilitaire bcp pour l'importation et l'exportation de données

Une autre option pour importer et exporter des données dans Cloud SQL consiste à utiliser l'utilitaire de copie en bloc bulk copy program (bcp). À l'aide de l'utilitaire bcp, vous pouvez exporter des données depuis une base de données SQL Server vers un fichier de données et importer les données dans une base de données SQL Server. L'utilitaire bcp utilise vos identifiants pour se connecter à SQL Server afin d'effectuer des importations et des exportations de bases de données. Il rend les transferts disponibles pour tous les utilisateurs de Cloud SQL. Pour effectuer des opérations d'importation et d'exportation, vous devez disposer des éléments suivants :

  • Un poste de travail où vous pouvez exécuter l'utilitaire bcp, et qui dispose d'une connectivité à votre instance Cloud SQL. Pour en savoir plus sur les options de connectivité, consultez la section À propos des options de connexion.

  • L'utilitaire bcp installé sur votre système. Pour en savoir plus sur le téléchargement et l'installation de bcp, consultez la documentation Microsoft.

  • Identifiants configurés pour accéder à votre instance. Pour en savoir plus sur la configuration des identifiants, consultez la section Comment s'authentifier auprès de Cloud SQL.

Exemples

Importer

Pour importer des données depuis le fichier person.csv vers la table Person de la base de données AdventureWorks2017, exécutez la commande suivante :

bcp Person.Person in "person.csv" -d AdventureWorks2017 -U myLoginID -S myServer

Ici,

  • myLoginID est l'ID de connexion utilisé pour se connecter à SQL Server.
  • myServer est l'instance de SQL Server à laquelle vous souhaitez vous connecter. Si vous ne spécifiez pas de serveur, l'utilitaire bcp se connecte à l'instance par défaut de SQL Server sur l'ordinateur local.

Pour en savoir plus, consultez la documentation Microsoft.

Exporter

Pour exporter des données depuis la table Person de la base de données AdventureWorks2017 vers le fichier person.dat, exécutez la commande suivante :

bcp Person.Person out "person.dat" -U myLoginID -S myServer -d AdventureWorks2017

Ici,

  • myLoginID est l'ID de connexion utilisé pour se connecter à SQL Server.
  • myServer est l'instance de SQL Server à laquelle vous souhaitez vous connecter. Si vous ne spécifiez pas de serveur, l'utilitaire bcp se connecte à l'instance par défaut de SQL Server sur l'ordinateur local.

Pour en savoir plus, consultez la documentation Microsoft.

Importer des données à l'aide de l'insertion groupée

L'insertion groupée vous permet d'importer des données dans votre base de données Cloud SQL pour SQL Server à partir d'un fichier stocké dans Cloud Storage.

Cette section décrit les opérations suivantes :

Rôles et autorisations requis

Pour configurer l'insertion groupée, vous avez besoin des éléments suivants :

  • L'autorisation CONTROL sur la base de données vers laquelle vous souhaitez importer les données

  • Une clé d'accès HMAC et un secret mappés à un compte IAM avec les autorisations suivantes :

    • storage.buckets.get
    • storage.objects.create et storage.multipartUploads.create pour écrire des journaux d'erreurs et des exemples de données incorrectes.

    Vous pouvez également utiliser les rôles suivants :

    • Storage Object Viewer
    • Storage Object Creator pour écrire des journaux d'erreurs et des exemples de données incorrectes.

Pour utiliser l'insertion groupée, vous avez besoin des éléments suivants :

  • Autorisation EXECUTE sur la procédure stockée msdb.dbo.gcloudsql_bulk_insert. Cloud SQL crée la procédure stockée une fois l'insertion groupée activée sur l'instance. Par défaut, Cloud SQL accorde l'autorisation EXECUTE au compte administrateur sqlserver.
  • L'autorisation INSERT sur l'objet vers lequel vous souhaitez importer les données

Pour savoir comment créer des utilisateurs pour l'insertion groupée, consultez la section Créer et gérer des utilisateurs.

Éléments à prendre en compte lors de l'insertion groupée

Cette section contient des recommandations pour la gestion de la sécurité, des performances et de la fiabilité sur les instances lors de l'insertion groupée.

Sécurité

Cloud SQL chiffre et stocke la clé d'accès et le secret HMAC dans une instance en tant qu'identifiant à l'échelle de la base de données. Une fois enregistrées, leurs valeurs ne sont plus accessibles. Vous pouvez supprimer la clé et le secret d'une instance en supprimant les identifiants à l'échelle de la base de données à l'aide d'une commande T-SQL. Si vous effectuez une sauvegarde pendant que la clé et le secret sont stockés sur l'instance, cette sauvegarde contient cette clé et ce secret. Vous pouvez également rendre la clé non valide en désactivant et en supprimant la clé HMAC.

Les opérations suivantes peuvent transférer par inadvertance la clé d'accès et le secret, et les rendre disponibles :

  • Cloner l'instance : la clé et le secret sont disponibles sur l'instance clonée.
  • Créer une instance dupliquée avec accès en lecture : la clé et le secret sont disponibles sur l'instance dupliquée avec accès en lecture créée.
  • Effectuer une restauration à partir d'une sauvegarde : la clé et le secret sont disponibles sur l'instance restaurée à partir d'une sauvegarde.

Nous vous recommandons de supprimer la clé et le secret de l'instance cible après avoir effectué ces opérations.

L'insertion groupée peut écrire des données qu'elle ne peut pas analyser dans un fichier stocké dans un bucket Cloud Storage. Si vous souhaitez protéger les données auxquelles l'insertion groupée a accès, configurez VPC Service Controls.

Performances

Nous vous recommandons de procéder comme suit pour limiter les impacts sur les performances lors de l'insertion groupée :

  • Testez et définissez une valeur appropriée pour @batchsize, car par défaut, toutes les données sont importées dans un seul lot.
  • Pour les insertions volumineuses, désactivez temporairement les index pour accélérer l'insertion des données.
  • Dans la mesure du possible, utilisez l'option @tablock, car elle peut réduire les conflits et améliorer les performances de chargement des données.
  • Utilisez le paramètre @ordercolumnsjson pour spécifier des données triées dans l'ordre de l'index clusterisé. Cela permet d'améliorer les performances des instances.
Fiabilité

Nous vous recommandons de procéder comme suit pour limiter l'impact sur la fiabilité des instances lors de l'insertion groupée :

  • Si une défaillance se produit et que @batchsize est utilisé, des données partiellement chargées peuvent être générées. Vous devrez peut-être nettoyer manuellement ces données sur votre instance.
  • Utilisez l'option @errorfile pour conserver un journal des erreurs et des exemples de données incorrectes détectées lors du processus de chargement. Cela permet d'identifier plus facilement les lignes qui n'ont pas été chargées.

Effectuer une insertion groupée

Vous pouvez effectuer l'opération d'insertion groupée en procédant comme suit :

msdb.dbo.gcloudsql_bulk_insert

Pour en savoir plus, consultez la section Procédure stockée pour utiliser l'insertion groupée.

Exemple : Importer des données à partir d'un fichier dans Cloud Storage et spécifier un fichier d'erreur
1. Activer l'insertion groupée

Pour activer l'insertion groupée sur votre instance, activez l'indicateur cloud sql enable bulk insert.

gcloud sql instances patch INSTANCE_NAME --database-flags="cloud sql enable bulk insert"=on

Remplacez INSTANCE_NAME par le nom de l'instance que vous souhaitez utiliser pour l'insertion groupée.

Pour en savoir plus, consultez la page Configurer des options de base de données.

Une fois que vous avez activé cet indicateur sur votre instance, Cloud SQL installe la procédure stockée d'insertion groupée sur votre instance et accorde au compte administrateur sqlserver les autorisations d'exécution.

2. Créer une clé HMAC

Vous avez besoin d'une clé HMAC pour accéder à votre bucket Cloud Storage. Nous vous recommandons de créer une clé HMAC pour un compte de service et d'accorder à ce compte des autorisations sur les buckets que vous souhaitez utiliser pour l'insertion groupée. Pour en savoir plus et obtenir des informations sur la sécurité, consultez la section Éléments à prendre en compte lors de l'insertion groupée.

3. Créer des exemples de données à importer

  1. À l'aide d'un éditeur de texte, créez un fichier avec un encodage ANSI ou UTF-16 contenant l'exemple de données suivant. Enregistrez le fichier dans votre bucket Cloud Storage et nommez-le bulkinsert.bcp, par exemple.

    1,Elijah,Johnson,1962-03-21
2,Anya,Smith,1982-01-15
3,Daniel,Jones,1990-05-21

  2. Créez un fichier de format à l'aide de l'exemple de données suivant. Enregistrez le fichier dans votre bucket Cloud Storage et nommez-le bulkinsert.fmt, par exemple. Pour en savoir plus sur les fichiers de format XML et non XML dans SQL Server, consultez la section Créer un fichier de format.

    13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName            SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName            SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate             ""
4. Exécuter la procédure stockée

  1. Connectez-vous à votre instance à l'aide de l'utilisateur sqlserver et créez un exemple de base de données et de table pour l'insertion groupée.

    USE MASTER
GO
-- create test database
DROP DATABASE IF EXISTS bulktest
CREATE DATABASE bulktest
GO

-- create table to insert
USE bulktest;
GO
CREATE TABLE dbo.myfirstimport(
PersonID smallint,
FirstName varchar(25),
LastName varchar(30),
BirthDate Date
);

  2. Créez une clé principale de base de données, un identifiant à l'échelle de la base de données et une source de données externe. Définissez l'identité sur S3 Access Key.

      -- create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';

-- create database scoped credential
CREATE DATABASE SCOPED CREDENTIAL GCSCredential
WITH IDENTITY = 'S3 Access Key',
SECRET = '<Access key>:<Secret>';

--create external data source
CREATE EXTERNAL DATA SOURCE GCSStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
, CREDENTIAL = GCSCredential
);

CREATE EXTERNAL DATA SOURCE GCSStorageError
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
, CREDENTIAL = GCSCredential
);

  3. Exécutez la procédure stockée d'insertion groupée pour importer les exemples de données.

    EXEC msdb.dbo.gcloudsql_bulk_insert
@database = 'bulktest',
@schema = 'dbo',
@object = 'myfirstimport',
@file = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.bcp',
@formatfile = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.fmt',
@fieldquote = '"',
@formatfiledatasource = 'GCSStorage',
@ROWTERMINATOR = '0x0A',
@fieldterminator = ',',
@datasource ='GCSStorage',
@errorfiledatasource = 'GCSStorageError',
@errorfile = 's3://storage.googleapis.com/oom-data/bulkinsert/bulkinsert_sampleimport.log',
@ordercolumnsjson =
'[{"name": "PersonID","order": " asc "},{"name": "BirthDate","order": "asc"}]'

Afficher les données importées

Vous pouvez afficher les données importées de l'une des manières suivantes :

  • Exécutez la requête suivante :

    SELECT * FROM dbo.myfirstimport

  • Cloud SQL ajoute un enregistrement de cette procédure au journal des erreurs SQL. Vous pouvez le voir dans Cloud Logging. Vous pouvez également le voir dans les données du journal des erreurs SQL dans SQL Server Management Studio (SSMS).

Désactiver l'insertion groupée

Pour désactiver l'insertion groupée, supprimez l'option cloud sql enable bulk insert :

  gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable bulk insert"=off

Remplacez INSTANCE_NAME par le nom de l'instance sur laquelle vous souhaitez supprimer l'insertion groupée.

Vous pouvez également exécuter la commande suivante pour effacer toutes les options de base de données :

  gcloud sql instances patch INSTANCE_NAME --clear-database-flags

Remplacez INSTANCE_NAME par le nom de l'instance sur laquelle vous souhaitez supprimer l'insertion groupée.

Utiliser l'importation et l'exportation agrégées par bandes

Lorsque vous effectuez une importation ou une exportation agrégées par bandes, vous réduisez le temps nécessaire à l'exécution de l'opération et permettez l'importation et l'exportation des bases de données de plus de 5 To. Pour en savoir plus, consultez la page Exporter et importer à l'aide de fichiers BAK.

Vérifier la base de données importée

Une fois l'importation terminée, connectez-vous à votre base de données et exécutez les commandes de base de données appropriées pour vous assurer que le contenu est correct. Par exemple, connectez et répertoriez les bases de données, les tables et les entrées spécifiques.

Limites connues

Pour obtenir la liste des limites connues, consultez la section Problèmes d'importation et d'exportation des données.

Automatiser les opérations d'exportation

Bien que Cloud SQL ne dispose pas d'une méthode intégrée pour automatiser les exportations de base de données, vous pouvez créer votre propre outil d'automatisation à l'aide de plusieurs composants Google Cloud. Pour en savoir plus, consultez ce tutoriel.

Dépannage

Résoudre les problèmes liés aux opérations d'importation

Problème Dépannage
HTTP Error 409: Operation failed because another operation was already in progress. Une opération est déjà en attente pour votre instance. Il n'est possible d'exécuter qu'une seule opération à la fois. Envoyez votre requête lorsque l'opération en cours est terminée.
L'opération d'importation prend trop de temps. Un trop grand nombre de connexions actives peut interférer avec les opérations d'importation.

Fermez les opérations inutilisées. Vérifiez l'utilisation du processeur et de la mémoire de votre instance Cloud SQL pour vous assurer que de nombreuses ressources sont disponibles. Le meilleur moyen de s'assurer de la présence d'un nombre maximal de ressources pour l'opération d'importation consiste à redémarrer l'instance avant de lancer l'importation.

Un redémarrage :

  • ferme toutes les connexions ;
  • met fin à toutes les tâches susceptibles de consommer des ressources.
Une opération d'importation peut échouer lorsqu'un ou plusieurs utilisateurs référencés dans le fichier de dump n'existent pas. Avant d'importer un fichier de dump, tous les utilisateurs de la base de données qui possèdent des objets ou disposent d'autorisations sur les objets qu'elle contient doivent exister dans la base de données cible. Si ce n'est pas le cas, l'opération d'importation ne parvient pas à recréer les objets en rétablissant les propriétaires ou les autorisations d'origine.

Créez les utilisateurs de la base de données avant de l'importer.
Incohérence du LSN L'ordre d'importation des sauvegardes du journal des transactions est incorrect ou la chaîne du journal des transactions est interrompue.
Importez les sauvegardes du journal des transactions dans l'ordre indiqué dans la table des ensembles de sauvegardes.
StopAt trop tôt Cette erreur indique que le premier journal du fichier de journal des transactions se trouve après le code temporel StopAt. Par exemple, si le premier journal du fichier de journal des transactions est au format 2023-09-01T12:00:00 et que le champ StopAt a la valeur 2023-09-01T11:00:00, Cloud SQL renvoie cette erreur.
Assurez-vous d'utiliser le bon code temporel StopAt et le bon fichier de journal des transactions.

Résoudre les problèmes liés aux opérations d'exportation

Problème Dépannage
HTTP Error 409: Operation failed because another operation was already in progress. Une opération est déjà en attente pour votre instance. Il n'est possible d'exécuter qu'une seule opération à la fois. Envoyez votre requête lorsque l'opération en cours est terminée.
HTTP Error 403: The service account does not have the required permissions for the bucket. Assurez-vous que le bucket existe et que le compte de service de l'instance Cloud SQL (qui effectue l'exportation) dispose du rôle Storage Object Creator (roles/storage.objectCreator) pour autoriser l'exportation vers le bucket. Consultez la page Rôles IAM pour Cloud Storage.
Vous souhaitez automatiser les exportations. Cloud SQL ne permet pas d'automatiser les exportations.

Vous pouvez créer votre propre système d'exportation automatisé à l'aide de produits Google Cloud tels que Cloud Scheduler, Pub/Sub et les fonctions Cloud Run, de manière semblable à cet article sur l'automatisation des sauvegardes.

Étape suivante