Ce tutoriel explique comment utiliser Dataflow pour extraire, transformer et charger des données (ETL, "extract, transform and load") à partir d'une base de données relationnelle de traitement des transactions en ligne (OLTP, "online transaction processing") vers BigQuery à des fins d'analyse.
Ce tutoriel est destiné aux administrateurs de bases de données, aux responsables d'exploitation et aux architectes cloud qui souhaitent bénéficier des capacités de requêtes analytiques de BigQuery et des fonctionnalités de traitement par lots de Dataflow.
Les bases de données OLTP sont souvent des bases de données relationnelles qui stockent des informations et traitent des transactions pour des sites d'e-commerce, des applications SaaS (Software as a Service) ou encore des jeux. Elles sont généralement optimisées pour les transactions présentant des schémas hautement normalisés et exigeant des propriétés ACID : atomicité, cohérence, isolation et durabilité. En revanche, les entrepôts de données sont généralement optimisés pour l'extraction et l'analyse des données plutôt que pour les transactions, et possèdent donc des schémas dénormalisés. Généralement, la dénormalisation des données à partir d'une base de données OLTP facilite leur analyse dans BigQuery.
Objectifs
Le tutoriel présente deux approches pour l'ETL de données de SGBDR normalisés vers des données BigQuery dénormalisées :
- Utiliser BigQuery pour charger et transformer les données. Utilisez cette approche pour le chargement ponctuel de petits volumes de données dans BigQuery à des fins d'analyse. Vous pouvez également utiliser cette approche pour prototyper votre ensemble de données avant d'utiliser l'automatisation avec des ensembles de données plus volumineux ou plusieurs ensembles de données.
- Utiliser Dataflow pour charger, transformer et nettoyer les données : Utilisez cette approche pour charger des données plus volumineuses ou provenant de plusieurs sources, ou pour charger des données de manière incrémentielle ou automatique.
Coûts
Dans ce document, vous utilisez les composants facturables suivants de Google Cloud :
Obtenez une estimation des coûts en fonction de votre utilisation prévue à l'aide du simulateur de coût.
Une fois que vous avez terminé les tâches décrites dans ce document, vous pouvez éviter de continuer à payer des frais en supprimant les ressources que vous avez créées. Pour en savoir plus, consultez la section Effectuer un nettoyage.
Avant de commencer
- Connectez-vous à votre compte Google Cloud. Si vous débutez sur Google Cloud, créez un compte pour évaluer les performances de nos produits en conditions réelles. Les nouveaux clients bénéficient également de 300 $ de crédits gratuits pour exécuter, tester et déployer des charges de travail.
-
Dans Google Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Google Cloud.
-
Vérifiez que la facturation est activée pour votre projet Google Cloud.
-
Activer les API Compute Engine et Dataflow.
-
Dans Google Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Google Cloud.
-
Vérifiez que la facturation est activée pour votre projet Google Cloud.
-
Activer les API Compute Engine et Dataflow.
Utiliser l'ensemble de données MusicBrainz
Ce tutoriel s'appuie sur des instantanés JSON de tables figurant dans la base de données MusicBrainz, construite en PostgreSQL et contenant des informations sur l'ensemble de la musique répertoriée dans MusicBrainz. Parmi les éléments du schéma MusicBrainz, on trouve les suivants :
- Artistes
- Groupes de parution
- Versions
- Enregistrements
- Travaux
- Labels
- Différentes relations entre ces entités
Le schéma MusicBrainz comprend trois tables pertinentes : artist
, recording
et artist_credit_name
. Un crédit d'artiste artist_credit
représente le crédit accordé à l'artiste pour un enregistrement, et les lignes artist_credit_name
associent l'enregistrement à l'artiste correspondant via la valeur artist_credit
.
Ce tutoriel fournit les tables PostgreSQL déjà extraites au format JSON délimité par un retour à la ligne et stockées dans le bucket Cloud Storage public : gs://solutions-public-assets/bqetl
.
Si vous souhaitez effectuer cette étape vous-même, vous devez disposer d'une base de données PostgreSQL contenant l'ensemble de données MusicBrainz et utiliser les commandes suivantes pour exporter chacune des tables :
host=POSTGRES_HOST
user=POSTGRES_USER
database=POSTGRES_DATABASE
for table in artist recording artist_credit_name
do
pg_cmd="\\copy (select row_to_json(r) from (select * from ${table}) r ) to exported_${table}.json"
psql -w -h ${host} -U ${user} -d ${db} -c $pg_cmd
# clean up extra '\' characters
sed -i -e 's/\\\\/\\/g' exported_${table}.json
done
Approche 1 : ETL avec BigQuery
Utilisez cette approche pour charger une petite quantité de données dans BigQuery à des fins d'analyse. Vous pouvez également utiliser cette approche pour prototyper votre ensemble de données avant d'utiliser l'automatisation avec des ensembles de données plus volumineux ou plusieurs ensembles de données.
Créer un ensemble de données BigQuery
Pour créer un ensemble de données BigQuery, chargez les tables MusicBrainz une par une dans BigQuery, puis joignez les tables chargées de sorte que chaque ligne contienne l'association de données souhaitée. Stockez les résultats de la jointure dans une nouvelle table BigQuery. Vous pouvez ensuite supprimer les tables d'origine que vous avez chargées.
Dans la console Google Cloud, ouvrez BigQuery.
Dans le panneau Explorateur, cliquez sur le menu more_vert à côté du nom de votre projet, puis sur Créer un ensemble de données.
Dans la boîte de dialogue Créer un ensemble de données, effectuez les opérations suivantes :
- Dans le champ ID de l'ensemble de données, saisissez
musicbrainz
. - Définissez le paramètre Emplacement des données sur us.
- Cliquez sur Créer un ensemble de données.
- Dans le champ ID de l'ensemble de données, saisissez
Importer des tables MusicBrainz
Pour chaque table MusicBrainz, suivez les étapes ci-dessous pour ajouter une table à l'ensemble de données que vous avez créé :
- Dans le panneau Explorateur de BigQuery, développez la ligne contenant le nom de votre projet pour afficher le nouvel ensemble de données
musicbrainz
. - Cliquez sur le menu more_vert à côté de l'ensemble de données
musicbrainz
, puis sur Créer une table. Dans la boîte de dialogue Créer une table, procédez comme suit :
- Dans la liste déroulante Créer une table à partir de, sélectionnez Google Cloud Storage.
Dans le champ Sélectionner un fichier depuis le bucket GCS, saisissez le chemin d'accès au fichier de données :
solutions-public-assets/bqetl/artist.json
Pour le champ Format de fichier, sélectionnez JSONL (fichier JSON délimité par un retour à la ligne).
Assurez-vous que le champ Projet contient le nom de votre projet.
Assurez-vous que l'ensemble de données est défini sur
musicbrainz
.Pour Table, saisissez le nom de la table,
artist
.Pour Table type (Type de table), laissez l'option Native table (Table native) sélectionnée.
Sous la section Schema (Schéma), cliquez sur Edit as Text (Modifier sous forme de texte).
Téléchargez le fichier de schéma
artist
et ouvrez-le dans un éditeur ou une visionneuse de texte.Remplacez le contenu de la section Schema (Schéma) par le contenu du fichier de schéma que vous venez de télécharger.
Cliquez sur Créer une table.
Attendez quelques instants que le chargement soit terminé.
Une fois le chargement terminé, la nouvelle table apparaît sous l'ensemble de données.
Répétez les étapes 1 à 5 pour créer la table
artist_credit_name
avec les modifications suivantes :Utilisez le chemin suivant pour le fichier de données source :
solutions-public-assets/bqetl/artist_credit_name.json
Utilisez
artist_credit_name
comme nom de table.Téléchargez le fichier de schéma
artist_credit_name
et utilisez son contenu pour le schéma.
Répétez les étapes 1 à 5 pour créer la table
recording
avec les modifications suivantes :Utilisez le chemin suivant pour le fichier de données source :
solutions-public-assets/bqetl/recording.json
Utilisez
recording
comme nom de table.Téléchargez le fichier de schéma
recording
et utilisez son contenu pour le schéma.
Dénormaliser manuellement les données
Pour dénormaliser les données, joignez-les dans une nouvelle table BigQuery comportant une ligne pour chaque enregistrement d'artiste, avec les métadonnées sélectionnées que vous souhaitez conserver pour l'analyse.
- Si l'éditeur de requête BigQuery n'est pas ouvert dans la console Google Cloud, cliquez sur add_box Saisir une nouvelle requête.
Copiez la requête suivante et collez-la dans l'éditeur de requête :
SELECT artist.id, artist.gid AS artist_gid, artist.name AS artist_name, artist.area, recording.name AS recording_name, recording.length, recording.gid AS recording_gid, recording.video FROM `musicbrainz.artist` AS artist INNER JOIN `musicbrainz.artist_credit_name` AS artist_credit_name ON artist.id = artist_credit_name.artist INNER JOIN `musicbrainz.recording` AS recording ON artist_credit_name.artist_credit = recording.artist_credit
Cliquez sur la liste déroulante settings Plus, puis sélectionnez Paramètres de requête.
Dans la boîte de dialogue Paramètres de requête, procédez comme suit :
- Cochez la case Set a destination table for query results (Définir une table de destination pour les résultats de la requête).
- Dans Ensemble de données, saisissez
musicbrainz
et sélectionnez l'ensemble de données dans votre projet. - Dans le champ ID de la table, saisissez
recordings_by_artists_manual
. - Dans la section Destination table write preference (Préférence d'écriture pour la table de destination), choisissez Overwrite table (Écraser la table).
- Cochez la case Allow Large Results (no size limit) (Autoriser un nombre élevé de résultats (aucune limite)).
- Cliquez sur Save (Enregistrer).
Cliquez sur play_circle_filled Exécuter.
À l'issue de la requête, les données sont triées par chanson pour chaque artiste dans la nouvelle table BigQuery, et un échantillon des résultats est affiché dans le volet Résultats de la requête, similaire à celui-ci :
Row id artist_gid artist_name area recording_name length recording_gid vidéo 1 97546 125ec42a... unknown 240 Horo Gun Toireamaid Hùgan Fhathast Air 174106 c8bbe048... FALSE 2 266317 2e7119b5... Capella Istropolitana 189 Concerto Grosso in D minor, op. 2 no. 3: II. Adagio 134000 af0f294d... FALSE 3 628060 34cd3689... Conspirare 5196 Liturgy, op. 42: 9. Praise the Lord from the Heavens 126933 8bab920d... FALSE 4 423877 54401795... Boys Air Choir 1178 Nunc Dimittis 190000 111611eb... FALSE 5 394456 9914f9f9... L’Orchestre de la Suisse Romande 23036 Concert Waltz no. 2, op. 51 509960 b16742d1... FALSE
Approche 2 : ETL dans BigQuery avec Dataflow
Dans cette section du tutoriel, au lieu d'utiliser l'interface utilisateur BigQuery, vous utilisez un exemple de programme pour charger des données dans BigQuery à l'aide d'un pipeline Dataflow. Vous utilisez ensuite le modèle de programmation de Beam pour dénormaliser et nettoyer les données à charger dans BigQuery.
Avant de commencer, prenez un moment pour examiner les concepts et l'exemple de code.
Examiner les concepts
Bien que les données soient d'un volume réduit et qu'elles puissent être importées rapidement à l'aide de l'interface utilisateur de BigQuery, vous pouvez aussi utiliser Dataflow pour l'ETL dans le cadre de ce tutoriel. Utilisez Dataflow pour l'ETL dans BigQuery plutôt que l'interface utilisateur de BigQuery lorsque vous effectuez des jointures massives (entre 500 et 5 000 colonnes représentant plus de 10 To de données), avec les objectifs suivants :
- Vous souhaitez nettoyer ou transformer vos données à mesure qu'elles sont chargées dans BigQuery, au lieu de les stocker et de les joindre ultérieurement. Cette approche a pour effet de réduire les besoins en stockage car les données ne sont stockées dans BigQuery que dans leur état joint et transformé.
- Vous souhaitez effectuer un nettoyage personnalisé des données (qui ne peut être réalisé de manière simple avec SQL).
- Vous souhaitez combiner les données avec des données extérieures à OLTP, telles que des journaux ou des données accessibles à distance, durant le processus de chargement.
- Vous souhaitez automatiser les tests et le déploiement de la logique de chargement des données à l'aide d'une intégration ou d'un déploiement continus (CI/CD).
- Vous prévoyez une itération graduelle et une amélioration du processus ETL au fil du temps.
- Vous souhaitez ajouter des données de manière incrémentielle, plutôt qu'en suivant un processus ETL unique.
Voici un diagramme du pipeline de données créé par l'exemple de programme :
Dans l'exemple de code, de nombreuses étapes du pipeline sont regroupées et/ou encapsulées dans des méthodes pratiques, nommées et réutilisées. Dans le diagramme, les étapes réutilisées sont signalées par des bordures en pointillés.
Examiner le code du pipeline
Le code crée un pipeline qui effectue les étapes suivantes :
Chaque table que vous voulez intégrer à la jointure du bucket Cloud Storage public est chargée dans une collection
PCollection
de chaînes. Chaque élément comprend la représentation JSON d'une ligne de la table.Les chaînes JSON sont converties en représentations d'objets
MusicBrainzDataObject
, puis les représentations d'objets sont organisées suivant l'une des valeurs de colonne, par exemple une clé primaire ou étrangère.La liste fait l'objet d'une jointure sur la base de l'artiste commun.
artist_credit_name
associe un crédit d'artiste à son enregistrement et inclut la clé étrangère d'artiste. La tableartist_credit_name
est chargée en tant que liste d'objetsKV
(Key Value). Le membreK
correspond à l'artiste.La liste fait l'objet d'une jointure à l'aide de la méthode
MusicBrainzTransforms.innerJoin()
.- Cette méthode regroupe les collections d'objets
KV
selon le membre clé que vous souhaitez joindre. Cela produit une collectionPCollection
d'objetsKV
avec une clé longue (la valeur de colonneartist.id
) et l'objetCoGbkResult
(combinaison des groupes par résultat clé) résultant. L'objetCoGbkResult
est un tuple de listes d'objets avec la valeur de clé commune des première et deuxième collectionsPCollections
. Ce tuple est adressable à l'aide du tag tuple formulé pour chaque collectionPCollection
avant l'exécution de l'opération de regroupementCoGroupByKey
dans la méthodegroup
. La méthode fusionne chaque correspondance d'objets dans un objet
MusicBrainzDataObject
qui représente un résultat de jointure.La méthode réorganise la collection dans une liste d'objets
KV
pour commencer la jointure suivante. Cette fois, la valeurK
correspond à la colonneartist_credit
, utilisée pour joindre la table d'enregistrement.La méthode obtient la collection finale résultante d'objets
MusicBrainzDataObject
en joignant ce résultat à la collection chargée des enregistrements organisés parartist_credit.id
.La méthode mappe les objets
MusicBrainzDataObjects
obtenus dansTableRows
.Elle écrit les lignes
TableRows
résultantes dans BigQuery.
- Cette méthode regroupe les collections d'objets
Pour plus d'informations sur les mécanismes de programmation de pipeline Beam, consultez les rubriques spécifiques suivantes sur le modèle de programmation :
PCollection
- Charger des données depuis des fichiers texte (y compris Cloud Storage)
- Transformations telles que
ParDo
et MapElements - Jointure et
GroupByKey
- OI BigQuery
Après avoir examiné les étapes effectuées par le code, vous pouvez exécuter le pipeline.
Créer un bucket Cloud Storage
Exécuter le code de pipeline
Dans Google Cloud Console, ouvrez Cloud Shell.
Définir les variables d'environnement pour le projet et le script de pipeline
export PROJECT_ID=PROJECT_ID export REGION=us-central1 export DESTINATION_TABLE=recordings_by_artists_dataflow export DATASET=musicbrainz
Remplacez PROJECT_ID par l'ID de votre projet Google Cloud.
Assurez-vous que
gcloud
utilise bien le projet que vous avez créé ou sélectionné au début de ce tutoriel :gcloud config set project $PROJECT_ID
Conformément au principe de sécurité du moindre privilège, créez un compte de service pour le pipeline Dataflow et ne lui accordez que les privilèges nécessaires :
roles/dataflow.worker
,roles/bigquery.jobUser
, et le rôledataEditor
sur l'ensemble de donnéesmusicbrainz
:gcloud iam service-accounts create musicbrainz-dataflow export SERVICE_ACCOUNT=musicbrainz-dataflow@${PROJECT_ID}.iam.gserviceaccount.com gcloud projects add-iam-policy-binding ${PROJECT_ID} \ --member=serviceAccount:${SERVICE_ACCOUNT} \ --role=roles/dataflow.worker gcloud projects add-iam-policy-binding ${PROJECT_ID} \ --member=serviceAccount:${SERVICE_ACCOUNT} \ --role=roles/bigquery.jobUser bq query --use_legacy_sql=false \ "GRANT \`roles/bigquery.dataEditor\` ON SCHEMA musicbrainz TO 'serviceAccount:${SERVICE_ACCOUNT}'"
Créez un bucket pour le pipeline Dataflow afin de l'utiliser pour les fichiers temporaires et accordez sur celui-ci, au compte de service
musicbrainz-dataflow
, les privilègesOwner
:export DATAFLOW_TEMP_BUCKET=gs://temp-bucket-${PROJECT_ID} gsutil mb -l us ${DATAFLOW_TEMP_BUCKET} gsutil acl ch -u ${SERVICE_ACCOUNT}:O ${DATAFLOW_TEMP_BUCKET}
Clonez le dépôt contenant le code Dataflow :
git clone https://github.com/GoogleCloudPlatform/bigquery-etl-dataflow-sample.git
Remplacez le répertoire par celui de l'exemple :
cd bigquery-etl-dataflow-sample
Compilez et exécutez la tâche Dataflow :
./run.sh simple
L'exécution de la tâche devrait prendre environ 10 minutes.
Pour voir la progression du pipeline, accédez à la page Dataflow dans la console Google Cloud.
L'état de chaque tâche est indiqué dans la colonne d'état. L'état Réussie indique que la tâche est terminée.
(Facultatif) Pour afficher le graphique de la tâche, ainsi que des informations détaillées sur ses étapes, cliquez sur son nom, par exemple
etl-into-bigquery-bqetlsimple
.Une fois la tâche terminée, accédez à la page BigQuery.
Pour exécuter une requête sur la nouvelle table, dans le volet de l'éditeur de requête, saisissez les éléments suivants :
SELECT artist_name, artist_gender, artist_area, recording_name, recording_length FROM musicbrainz.recordings_by_artists_dataflow WHERE artist_area is NOT NULL AND artist_gender IS NOT NULL LIMIT 1000;
Le volet des résultats affiche un ensemble de résultats semblable à celui-ci :
Row artist_name artist_gender artist_area recording_name recording_length 1 mirin 2 107 Sylphia 264000 2 mirin 2 107 Dependence 208000 3 Gaudiburschen 1 81 Die Hände zum Himmel 210000 4 Sa4 1 331 Ein Tag aus meiner Sicht 221000 5 Dpat 1 7326 Cutthroat 249000 6 Dpat 1 7326 Deloused 178000 Le résultat réel peut différer, car les résultats ne sont pas triés.
Nettoyer les données
L'étape suivante consiste à modifier légèrement le pipeline Dataflow afin de pouvoir charger des tables de recherche et de les traiter en tant qu'entrées secondaires, comme illustré dans le diagramme suivant.
Lorsque vous interrogez la table BigQuery résultante, il est difficile d'identifier l'origine géographique de l'artiste sans avoir à rechercher manuellement l'identifiant numérique de la région dans la table area
de la base de données MusicBrainz. Ainsi, l'analyse des résultats de la requête n'est pas aussi directe qu'elle pourrait l'être.
De même, le genre des artistes est indiqué en tant qu'ID, or la table de genre MusicBrainz ne comprend que trois lignes. Pour résoudre ce problème, vous pouvez ajouter une étape dans le pipeline Dataflow afin d'utiliser les tables area
et gender
de MusicBrainz pour mapper les ID aux étiquettes correspondantes.
Les tables artist_area
et artist_gender
contiennent un nombre de lignes nettement inférieur à celui des tables des artistes ou des données relatives aux enregistrements. Le nombre d'éléments dans ces tables est limité, respectivement, par le nombre de zones géographiques ou de sexes.
En conséquence, l'étape de recherche utilise la fonctionnalité d'entrée secondaire de Dataflow.
Les entrées secondaires sont chargées en tant qu'exportations de fichiers JSON délimitées par des lignes dans le bucket Cloud Storage public contenant l'ensemble de données MusicBrainz. Elles sont utilisées pour dénormaliser les données de table en une seule étape.
Examiner le code ajoutant les entrées secondaires au pipeline
Avant d'exécuter le pipeline, consultez son code pour bien comprendre les nouvelles étapes.
Ce code illustre le nettoyage des données avec des entrées secondaires. La classe MusicBrainzTransforms
permet d'utiliser plus facilement les entrées secondaires pour mapper les valeurs de clés étrangères avec des étiquettes. La bibliothèque MusicBrainzTransforms
fournit par exemple une méthode permettant de créer une classe de recherche interne. La classe de recherche décrit chaque table de conversion et les champs qui doivent être remplacés par des étiquettes et des arguments de longueur variable. keyKey
est le nom de la colonne qui contient la clé pour la recherche et valueKey
est le nom de la colonne qui contient l'étiquette correspondante.
Chaque entrée secondaire est chargée sous la forme d'un objet de mappage unique, utilisé pour rechercher l'étiquette correspondant à un ID.
Le fichier JSON du tableau de conversion est initialement chargé dans MusicBrainzDataObjects
avec un espace de noms vide, puis il est transformé en mappage de la valeur de la colonne Key
à la valeur de la colonne Value
.
Chacun de ces objets Map
est placé dans une Map
suivant la valeur de sa clé de destination destinationKey
, qui est la clé à remplacer par les valeurs recherchées.
Puis, lors de la transformation des objets d'artiste à partir de JSON, la valeur de la clé destinationKey
(qui commence par un nombre) est remplacée par son étiquette.
Pour ajouter le décodage des champs artist_area
et artist_gender
, procédez comme suit :
Dans Cloud Shell, assurez-vous que l'environnement est correctement configuré pour le script de pipeline :
export PROJECT_ID=PROJECT_ID export REGION=us-central1 export DESTINATION_TABLE=recordings_by_artists_dataflow_sideinputs export DATASET=musicbrainz export DATAFLOW_TEMP_BUCKET=gs://temp-bucket-${PROJECT_ID} export SERVICE_ACCOUNT=musicbrainz-dataflow@${PROJECT_ID}.iam.gserviceaccount.com
Remplacez PROJECT_ID par l'ID de votre projet Google Cloud.
Exécutez le pipeline pour créer la table avec la zone décodée et le genre de l'artiste :
./run.sh simple-with-lookups
Comme précédemment, pour voir la progression du pipeline, accédez à la page Dataflow.
L'exécution du pipeline va prendre environ 10 minutes.
Une fois la tâche terminée, accédez à la page BigQuery.
Envoyez la même requête, qui inclut
artist_area
etartist_gender
:SELECT artist_name, artist_gender, artist_area, recording_name, recording_length FROM musicbrainz.recordings_by_artists_dataflow_sideinputs WHERE artist_area is NOT NULL AND artist_gender IS NOT NULL LIMIT 1000;
Dans la sortie, les éléments
artist_area
etartist_gender
sont maintenant décodés :Row artist_name artist_gender artist_area recording_name recording_length 1 mirin Female Japan Sylphia 264000 2 mirin Female Japan Dependence 208000 3 Gaudiburschen Male Germany Die Hände zum Himmel 210000 4 Sa4 Male Hamburg Ein Tag aus meiner Sicht 221000 5 Dpat Male Houston Cutthroat 249000 6 Dpat Male Houston Deloused 178000 Le résultat réel peut différer, car les résultats ne sont pas triés.
Optimiser le schéma BigQuery
Dans la dernière partie de ce tutoriel, vous allez exécuter un pipeline générant un schéma de table plus optimal grâce à des champs imbriqués.
Prenez le temps d'examiner le code utilisé pour générer cette version optimisée de la table.
Le diagramme suivant illustre un pipeline Dataflow légèrement différent, qui imbrique les enregistrements des artistes dans chaque ligne d'artiste au lieu de créer des lignes d'artiste en double.
La représentation actuelle des données est relativement plate. En effet, elle comprend une ligne par enregistrement crédité, qui inclut toutes les métadonnées de l'artiste tirées du schéma BigQuery ainsi que toutes les métadonnées de l'enregistrement et de artist_credit_name
. Cette représentation plate présente au moins deux inconvénients :
- Elle répète les métadonnées
artist
pour chaque enregistrement porté au crédit d'un artiste, ce qui augmente l'espace de stockage nécessaire. - Lorsque vous exportez les données au format JSON, cette représentation exporte un tableau qui répète ces données, plutôt qu'un artiste avec les données d'enregistrement imbriquées (ce que vous souhaitez certainement obtenir).
Sans affecter les performances et sans utiliser de stockage supplémentaire, plutôt que de stocker un enregistrement par ligne, vous pouvez stocker les enregistrements sous la forme d'un champ répété dans chaque enregistrement d'artiste en apportant des modifications relativement simples au pipeline Dataflow.
Au lieu de joindre les enregistrements et leurs informations d'artiste selon artist_credit_name.artist
, le pipeline modifié crée une liste imbriquée d'enregistrements dans un objet d'artiste.
L'API BigQuery a une limite maximale de taille de ligne de 100 Mo pour les insertions groupées (10 Mo pour les insertions en flux continu). Le code limite donc le nombre d'enregistrements imbriqués pour un enregistrement donné à 1 000 éléments, pour vous assurer de ne pas dépasser cette limite. Si un artiste possède plus de 1 000 enregistrements, le code duplique la ligne, y compris les métadonnées artist
, et continue l'imbrication des données d'enregistrement dans la ligne dupliquée.
Le diagramme montre les sources, les transformations et les récepteurs du pipeline.
Dans la plupart des cas, les noms des étapes sont fournis dans le code lors de l'appel de la méthode apply
.
Pour créer ce pipeline optimisé, procédez comme suit :
Dans Cloud Shell, assurez-vous que l'environnement est correctement configuré pour le script de pipeline :
export PROJECT_ID=PROJECT_ID export REGION=us-central1 export DESTINATION_TABLE=recordings_by_artists_dataflow_nested export DATASET=musicbrainz export DATAFLOW_TEMP_BUCKET=gs://temp-bucket-${PROJECT_ID} export SERVICE_ACCOUNT=musicbrainz-dataflow@${PROJECT_ID}.iam.gserviceaccount.com
Exécutez le pipeline pour imbriquer des lignes d'enregistrement dans des lignes d'artiste :
./run.sh nested
Comme précédemment, pour voir la progression du pipeline, accédez à la page Dataflow.
L'exécution du pipeline va prendre environ 10 minutes.
Une fois la tâche terminée, accédez à la page BigQuery.
Interrogez les champs de la table imbriquée dans BigQuery :
SELECT artist_name, artist_gender, artist_area, artist_recordings FROM musicbrainz.recordings_by_artists_dataflow_nested WHERE artist_area IS NOT NULL AND artist_gender IS NOT NULL LIMIT 1000;
Dans le résultat, les champs
artist_recordings
sont affichés sous forme de lignes imbriquées pouvant être développées :Row artist_name artist_gender artist_area artist_recordings 1 mirin Female Japan (5 rows) 3 Gaudiburschen Male Germany (1 row) 4 Sa4 Male Hamburg (10 rows) 6 Dpat Male Houston (9 rows) Le résultat réel peut différer, car les résultats ne sont pas triés.
Exécutez une requête pour extraire des valeurs de la structure
STRUCT
et utilisez ces valeurs pour filtrer les résultats, par exemple pour les artistes dont les enregistrements contiennent le mot "Justin" :SELECT artist_name, artist_gender, artist_area, ARRAY(SELECT artist_credit_name_name FROM UNNEST(recordings_by_artists_dataflow_nested.artist_recordings)) AS artist_credit_name_name, ARRAY(SELECT recording_name FROM UNNEST(recordings_by_artists_dataflow_nested.artist_recordings)) AS recording_name FROM musicbrainz.recordings_by_artists_dataflow_nested, UNNEST(recordings_by_artists_dataflow_nested.artist_recordings) AS artist_recordings_struct WHERE artist_recordings_struct.recording_name LIKE "%Justin%" LIMIT 1000;
Dans le résultat, les champs
artist_credit_name_name
etrecording_name
sont affichés sous forme de lignes imbriquées pouvant être développées, par exemple :Row artist_name artist_gender artist_area artist_credit_name_name recording_name 1 Damonkenutz null null (1 row) 1 Yellowpants (Justin Martin remix) 3 Fabian Male Germany (10+ rows) 1 Heatwave . 2 Starlight Love . 3 Dreams To Wishes . 4 Last Flight (Justin Faust remix) . ... 4 Digital Punk Boys null null (6 rows) 1 Come True . 2 We Are... (Punkgirlz remix by Justin Famous) . 3 Chaos (short cut) . ... Le résultat réel peut différer, car les résultats ne sont pas triés.
Effectuer un nettoyage
Pour éviter que les ressources utilisées lors de ce tutoriel soient facturées sur votre compte Google Cloud, supprimez le projet contenant les ressources, ou conservez le projet et supprimez les ressources individuelles.
Supprimer le projet
- Dans la console Google Cloud, accédez à la page Gérer les ressources.
- Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer.
- Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.
Supprimer des ressources individuelles
Suivez les étapes ci-dessous pour supprimer des ressources individuelles au lieu de supprimer tout le projet.
Supprimer le bucket Cloud Storage
- Dans la console Google Cloud, accédez à la page Buckets de Cloud Storage.
- Cochez la case correspondant au bucket que vous souhaitez supprimer.
- Pour supprimer le bucket, cliquez sur Supprimer , puis suivez les instructions.
Supprimer les ensembles de données BigQuery
Ouvrez l'interface utilisateur Web BigQuery.
Sélectionnez les ensembles de données BigQuery que vous avez créés au cours du tutoriel.
Cliquez sur Supprimerdelete.
Étape suivante
- Apprenez à écrire des requêtes pour BigQuery. L'article Interroger des données explique entre autres comment exécuter des requêtes synchrones et asynchrones et créer des fonctions définies par l'utilisateur (UDF).
- Explorez la syntaxe BigQuery. La syntaxe de type SQL de BigQuery est décrite dans la Documentation de référence sur les requêtes (ancien SQL).
- Découvrez des architectures de référence, des schémas et des bonnes pratiques concernant Google Cloud. Consultez notre Centre d'architecture cloud.