Load Data Infile / Bulk Insert sur Google Cloud SQL avec Python

Google_Cloud_Platform

Le LOAD DATA INFILE est une solution très pratique mais qui n’est pas disponible en natif sur Google Cloud SQL, le serveur MySQL de Google Cloud.
On peut utiliser LOAD DATA LOCAL INFILE depuis App Engine mais si vous n’utilisez pas déjà cette technologie, monter une application uniquement pour cette tache est fastidieux, surtout que ce n’est pas exactement la bonne façon de procéder.

La philosophie des services du Cloud de Google étant de s’interconnecter avec Google Cloud Storage (GCS) utilisé comme système de fichier commun, le but de ce post est d’expliquer comment utiliser les APIs pour charger un fichier depuis GCS dans une table sur Cloud SQL ou par extension dans tout autre service SQL/NoSQL.
 

Pré-Requis

Il n’y a pas forcement tous les détails de création des différents éléments nécessaires mais l’aide de Google Cloud est suffisamment explicite.
Les spécificités et opérations plus ardues à trouver sont néanmoins expliquées.

Installation du Package google-api-python-client

Pour utiliser les APIs d’administration du Cloud de Google il faut installer le package google-api-python-client avec votre gestionnaire favori.
N’utilisez pas google-cloud-python car cette bibliothèque ne sert qu’à l’utilisation des services du Cloud et non pas à leur administration.

La documentation complète des classes et méthodes est disponible sur https://developers.google.com/api-client-library/python/apis/.

Création d’un Compte de Service

Il faut ensuite créer un Compte de Service et récupérer son identifiant et sa clé JSON associée.
Ce compte doit avoir les droits d’éditeur sur Google Cloud SQL et sur Google Cloud Storage car votre script va l’utiliser pour communiquer avec ces différents services.

N’oubliez pas de bien spécifier à la création les droits d’utilisation corrects et non pas à posteriori dans IAM.
A défaut de le faire vous aurez une erreur “The client is not authorized to make this request” assez complexe à comprendre.

Création d’un Ensemble dans Google Cloud Storage

A présent il va falloir créer un Ensemble (Ou Bucket en anglais) sur Google Cloud Storage dans lequel on va déposer par la suite le fichier à insérer dans Cloud SQL.

Le Compte de Service précédemment créé doit recevoir les droits d’accéder et de créer des objets dans le Bucket.
Pour cela il faut cliquer sur le menu au bout de la ligne de l’Ensemble.
Google Cloud Storage Bucket Permissions

Et dans les deux options Autorisation relatives à l’ensemble & Autorisations relatives aux valeurs par défauts des objets, insérer une nouvelle ligne, de type Utilisateur, avec l’identifiant du Compte de Service en iam.gserviceaccount.com avec l’accréditation Propriétaire.
Google Cloud Storage Bucket Permissions

Google Cloud SQL va aussi ajouter par la suite et de manière automatique son Compte de Service avec des droits de lecture sur le Bucket.

Activer les API

Si ce n’est pas déjà fait, vérifier que les APIs Google Cloud SQL et Google Cloud Storage sont bien actives sur https://console.developers.google.com/apis/library
Ces étapes réalisées, il ne reste plus qu’à coder.

Le Code Python

Le code est en Python 2 et très facile à adapter en Python 3.
Avant de se lancer, il faudra réunir les paramètres suivants :

  • L’ID de Projet Google
  • Le Nom de l’Instance Google Cloud SQL
  • Le nom du Bucket
  • Le nom de la Base de Données et de la Table
  • L’emplacement de la clé JSON du Compte de Service

Explication Étape par Étape

I. Écriture et Compression du Fichier CSV

Le fichier CSV doit impérativement être encodé en UTF-8 et pour diminuer la bande passante utilisée on peut aussi le compresser en GZIP.
Le format du fichier et des données doit suivre la spécification disponible sur https://cloud.google.com/sql/docs/mysql/import-export/

Dans l’exemple c’est un dataframe Pandas simple et to_csv() suffit à satisfaire les exigences de Google Cloud SQL.

II. Authentification avec l’API Google Cloud Storage

Création des credentials, on fournit à la méthode from_json_keyfile_name le chemin du fichier JSON du Compte de Service et les scopes qui vont être utilisés.

III. API Google Cloud Storage & Upload

Authentification avec Google Cloud Storage et découverte des méthodes de l’API Cloud Storage
Ouverture du fichier en local et upload via un stream dans GCS.

IV. Chargement du fichier depuis Google Cloud Storage dans Google Cloud SQL

De la même façon que précédemment, on découvre les méthodes de l’API puis on crée un job d’importation avec les diverses informations requises.
Le protocole gs:// est la méthode d’accès direct aux fichiers sur GCS depuis les autres machines du Cloud Google, on retrouve cette notation sur App Engine ou Big Query notamment.

Intégralité du Script Python

On retrouve à la suite les différentes étapes, avec les imports de packages et les variables.
La fin du script sert à vérifier le statut actuel de l’opération d’importation en cours et à réagir selon son état.

Avec un peu d’adaptation dans les APIs utilisées, vous pouvez désormais effectuer les transmissions de données entre les différents services du Cloud de Google en toute simplicité.

Leave a Reply

Your email address will not be published. Required fields are marked *