BigQuery pour les experts en entrepôts de données

Mis à jour en septembre 2017

Cet article explique comment utiliser BigQuery comme entrepôt de données. Pour commencer, il met en correspondance les concepts courants des entrepôts de données et ceux de BigQuery, puis décrit comment effectuer des tâches d'entreposage standard.

Comparaison des modèles de service

Le tableau suivant met en correspondance les concepts d'entrepôt de données standard et ceux de BigQuery :

Entrepôt de données BigQuery
Entrepôt de données Le service BigQuery remplace la configuration matérielle typique d'un entrepôt de données traditionnel. En d'autres termes, il sert de base collective pour toutes les données analytiques d'une organisation.
Magasin de données Les ensembles de données sont des collections de tables pouvant être divisées en fonction des secteurs d'activité ou d'un domaine analytique donné. Chaque ensemble de données est lié à un projet Google Cloud Platform (GCP).
Lac de données Votre lac de données peut contenir des fichiers dans Cloud Storage ou Google Drive, ou des données transactionnelles dans Cloud Bigtable. BigQuery peut définir un schéma et émettre des requêtes directement sur des données externes en tant que sources de données fédérées.
Tables et vues Les tables et les vues fonctionnent de la même manière dans BigQuery que dans un entrepôt de données traditionnel.
Autorisations Cloud Identity and Access Management (Cloud IAM) permet d'accorder la permission d'effectuer des actions spécifiques dans BigQuery.

Ensembles de données

BigQuery organise les tables de données en unités appelées ensembles de données. Ces ensembles de données s'étendent à votre projet GCP. Lorsque vous référencez une table depuis la ligne de commande, dans des requêtes SQL ou dans du code, vous y faites référence à l'aide de la construction suivante :

project.dataset.table

Ces multiples champs d'application (projet, ensemble de données et table) peuvent vous aider à structurer vos informations de manière logique. Pour séparer des tables appartenant à différents domaines d'analyse, vous pouvez utiliser plusieurs ensembles de données. Pour isoler les ensembles de données les uns des autres en fonction de vos besoins, vous pouvez également utiliser la portée au niveau du projet.

Voici un aperçu de la structure de BigQuery :

Présentation de la structure de BigQuery

Provisionner et dimensionner le système

Il n'est pas nécessaire de provisionner des ressources avant d'utiliser BigQuery, contrairement à de nombreux systèmes de SGBDR. BigQuery alloue des ressources de stockage et de requête de manière dynamique en fonction de vos habitudes d'utilisation.

  • Les ressources de stockage sont allouées au fur et à mesure que vous les consommez et libérées lorsque vous supprimez des données ou des tables.
  • Les ressources de requête sont allouées en fonction du type de requête et de sa complexité. Chaque requête utilise un certain nombre d'emplacements, qui sont des unités de calcul comprenant une certaine quantité de processeurs et de mémoire RAM.

Pour exploiter BigQuery, vous n'avez pas besoin de vous engager pour une utilisation minimale. Le service alloue et facture les ressources en fonction de votre utilisation réelle. Par défaut, tous les clients BigQuery ont accès à 2 000 emplacements pour les opérations de requête. Vous pouvez également réserver un nombre fixe d'emplacements pour votre projet. Pour plus de détails sur l'approche à utiliser, consultez la section Coûts.

Gestion de l'espace de stockage

BigQuery stocke les données en interne dans un format de colonne propriétaire appelé Capacitor, qui présente de nombreux avantages pour les charges de travail de l'entrepôt de données. BigQuery s'appuie sur un format propriétaire, car il peut évoluer en même temps que le moteur de requête qui, lui, tire parti d'une connaissance approfondie de la structure des données pour optimiser l'exécution des requêtes. BigQuery détermine le nombre optimal de fragments physiques et leur encodage à l'aide de modèles d'accès aux requêtes.

Les données sont stockées physiquement sur le système de fichiers distribué de Google, appelé Colossus, qui garantit la durabilité en tirant parti de l'encodage d'effacement pour stocker des fragments redondants des données sur plusieurs disques physiques. De plus, les données sont dupliquées dans plusieurs centres de données.

Vous pouvez également exécuter des requêtes BigQuery sur des données extérieures au stockage BigQuery, telles que des données stockées dans Cloud Storage, Google Drive ou Cloud Bigtable, à l'aide de sources de données fédérées. Cependant, ces sources n'étant pas optimisées pour les opérations BigQuery, elles risquent de ne pas fonctionner aussi bien que les données stockées dans l'emplacement de stockage BigQuery.

Maintenance

BigQuery est un service entièrement géré, ce qui signifie que l'équipe d'ingénieurs de BigQuery s'occupe des mises à jour et de la maintenance. Les mises à niveau ne doivent pas occasionner de temps d'arrêt ni nuire aux performances du système.

De nombreux systèmes traditionnels requièrent des processus de vide consommant beaucoup de ressources afin de s'exécuter à différents intervalles pour remanier et trier les blocs de données, et récupérer de l'espace. BigQuery n'intègre aucun équivalent au processus de vide, car le moteur de stockage gère et optimise en permanence la manière dont les données sont stockées et dupliquées. De plus, étant donné que BigQuery n'utilise pas d'index sur les tables, vous n'avez pas besoin de reconstruire les index.

Sauvegarde et récupération

BigQuery se charge de la sauvegarde et de la reprise après sinistre au niveau du service. De plus, en conservant un historique complet des modifications des tables sur sept jours, BigQuery vous permet d'interroger un instantané des données à un moment précis. Vous pouvez facilement annuler les modifications sans avoir à demander une récupération à partir des sauvegardes. (Lorsqu'une table est explicitement supprimée, son historique est vidé après 2 jours. Au moment de la rédaction de cet article, la fonctionnalité d'instantané n'est compatible qu'avec l'ancien SQL.)

Gérer les workflows

Cette section traite des tâches d'administration, telles que l'organisation des ensembles de données, l'octroi d'autorisations et l'intégration du travail dans BigQuery. Elle aborde également la gestion des charges de travail simultanées, la surveillance de l'état de votre entrepôt de données et l'audit de l'accès des utilisateurs.

Organiser les ensembles de données

Vous pouvez segmenter des ensembles de données en projets distincts en fonction de la classe de données ou de l'unité commerciale, ou les regrouper en projets communs pour des raisons de simplicité.

Vous pouvez inviter un analyste de données à collaborer sur un ensemble de données existant dans n'importe quel rôle limité que vous définissez. Ainsi, lorsqu'un analyste de données se connecte à l'interface utilisateur Web de BigQuery, il ne voit que les ensembles de données qui ont été partagés avec lui pour l'ensemble des projets. Les activités que les analystes de données peuvent exécuter sur des ensembles de données varient suivant leur rôle dans chaque ensemble de données.

Accorder des autorisations

Dans un système SGBDR classique, vous accordez des autorisations pour afficher ou modifier des tables en créant des autorisations SQL et en les appliquant à un utilisateur donné dans le système de base de données. En outre, certains systèmes SGBDR vous permettent d'accorder des autorisations aux utilisateurs d'un annuaire externe, tel que LDAP. Le modèle BigQuery pour la gestion des utilisateurs et des autorisations ressemble à ce dernier modèle.

BigQuery fournit des rôles prédéfinis pour contrôler l'accès aux ressources. Vous pouvez également créer des rôles Cloud IAM personnalisés comprenant l'ensemble d'autorisations que vous avez défini, puis attribuer ces rôles à des utilisateurs ou à des groupes. Vous pouvez attribuer un rôle à une adresse e-mail Google ou à un groupe G Suite.

Un aspect important de l'exploitation d'un entrepôt de données consiste à permettre à différents groupes d'utilisateurs un accès partagé mais contrôlé aux mêmes données. Par exemple, les services des finances, des ressources humaines et du marketing accèdent aux mêmes tables, mais leurs niveaux d'accès diffèrent. Les outils d'entreposage de données traditionnels rendent cela possible en appliquant une sécurité au niveau des lignes. Vous pouvez obtenir les mêmes résultats dans BigQuery en définissant des vues autorisées et des autorisations au niveau de la ligne.

Intégrer

Par le passé, l'intégration de nouveaux analystes de données impliquait un délai d'exécution important. Pour permettre aux analystes d'exécuter des requêtes simples, il fallait leur indiquer l'emplacement des sources de données et configurer des connexions, des outils ODBC et des droits d'accès. Grâce à GCP, vous pouvez dynamiser considérablement la productivité d'un analyste.

Pour intégrer un analyste dans GCP, vous accordez l'accès aux projets pertinents, vous lui présentez la console Google Cloud Platform et l'interface utilisateur Web BigQuery, et vous partagez certaines requêtes pour l'aider à se familiariser avec les données :

  • La console GCP fournit une vue centralisée de toutes les ressources de votre environnement GCP. Les ressources les plus pertinentes pour les analystes de données sont certainement les buckets Cloud Storage, dans lesquels ils peuvent collaborer sur des fichiers.
  • L'interface utilisateur Web de BigQuery présente la liste des ensembles de données auxquels l'analyste a accès. Suivant le rôle que vous leur avez attribué, les analystes peuvent effectuer des tâches dans la console GCP, telles que l'affichage des métadonnées, la prévisualisation des données, l'exécution, la sauvegarde et le partage de requêtes.

Gérer des charges de travail et la simultanéité

BigQuery limite le taux maximal de requêtes entrantes et applique des quotas appropriés par projet. Les règles spécifiques varient en fonction de la disponibilité des ressources, du profil utilisateur, de l'historique d'utilisation du service et d'autres facteurs. Pour en savoir plus, consultez les règles relatives aux quotas de BigQuery.

BigQuery propose deux types de priorités pour les requêtes : les requêtes interactives et les requêtes par lot. Par défaut, BigQuery exécute des requêtes interactives, ce qui signifie qu'elles sont exécutées dès que possible. Les requêtes interactives sont prises en compte dans le calcul des quotas de requêtes. Les requêtes par lot sont mises en file d'attente et exécutées dès que des ressources inactives sont disponibles, généralement en quelques minutes.

La hiérarchisation fine des requêtes interactives ou par lot n'est pas disponible avec BigQuery. Étant donné la vitesse et l'échelle auxquelles BigQuery fonctionne, les nombreux problèmes de charge de travail traditionnels ne sont tout simplement pas applicables. Si vous avez besoin d'une hiérarchisation explicite des requêtes, vous pouvez isoler vos charges de travail sensibles dans un projet comportant un nombre explicite d'emplacements réservés. Contactez votre représentant Google pour vous aider à obtenir le statut de client à taux fixe.

Surveiller et auditer

Vous pouvez surveiller BigQuery à l'aide de Stackdriver, où divers graphiques et alertes sont définis en fonction de métriques BigQuery. Par exemple, vous pouvez surveiller le débit du système à l'aide de la métrique Durée de la requête, ou visualiser les tendances de la demande de requête en fonction de la métrique Emplacements alloués. Lorsque vous devez planifier à l'avance une requête exigeante, vous pouvez utiliser la métrique Emplacements disponibles. Pour une surveillance proactive de la santé du système, vous pouvez créer des alertes en fonction de seuils que vous définissez. Stackdriver fournit un portail Web en libre-service. Vous pouvez contrôler l'accès au portail via des comptes Stackdriver.

BigQuery crée automatiquement des journaux d'audit sur les actions de l'utilisateur. Vous pouvez exporter les journaux d'audit vers un autre ensemble de données BigQuery, par lot ou sous forme de flux de données, puis visualiser les journaux à l'aide de votre outil d'analyse préféré. Pour plus d'informations, consultez la page Analyser des journaux d'audit à l'aide de BigQuery.

Gérer des données

Cette section aborde les considérations liées à la conception d'un schéma, la dénormalisation, le fonctionnement du partitionnement et les méthodes de chargement de données dans BigQuery. Pour finir, elle décrit comment gérer les modifications dans l'entrepôt tout en conservant des temps d'arrêt nuls sur les opérations d'analyse.

Concevoir un schéma

Pour concevoir un schéma optimal pour BigQuery, suivez ces instructions :

  • Dénormalisez une table de dimension de plus de 10 Go, sauf si vous obtenez des preuves tangibles que les coûts liés à la manipulation des données et aux opérations UPDATE et DELETE surpassent les avantages des requêtes optimales.
  • Gardez sous forme normalisée une table de dimension inférieure à 10 Go, à moins que la table ne passe rarement par les opérations UPDATE et DELETE.
  • Tirez pleinement parti des champs imbriqués et répétés des tables dénormalisées.

Dénormalisation

La méthode conventionnelle de dénormalisation des données consiste à écrire un fait, ainsi que toutes ses dimensions, dans une structure de table plate. Par exemple, pour les transactions de vente, vous écrivez chaque fait dans un enregistrement, ainsi que les dimensions correspondantes, telles que les informations relatives aux commandes et aux clients.

En revanche, la méthode recommandée pour la dénormalisation des données tire parti de la compatibilité native de BigQuery avec les structures imbriquées et répétées dans les données d'entrée JSON ou Avro. L'expression d'enregistrements à l'aide de structures imbriquées et répétées peut fournir une représentation plus naturelle des données sous-jacentes. Dans le cas d'une commande client, la partie externe d'une structure JSON contient les informations relatives à la commande et au client, tandis que la partie interne contient les postes individuels de la commande, représentés par des éléments imbriqués et répétés.

{
  "orderID": "ORDER",
  "custID": "EMAIL",
  "custName": "NAME",
  "timestamp": "TIME",
  "location": "LOCATION",
  "purchasedItems": [
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    },
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    }
  ]
}

L'expression d'enregistrements à l'aide de champs imbriqués et répétés simplifie le chargement de données à l'aide de fichiers JSON ou Avro. Une fois que vous avez créé un tel schéma, vous pouvez effectuer des opérations SELECT, INSERT, UPDATE et DELETE sur n'importe quel champ à l'aide d'une notation à points (par exemple, Order.Item.SKU). Pour obtenir des exemples, consultez la documentation relative à BigQuery.

Avantages de la dénormalisation

BigQuery est essentiellement un moteur d'analyse. Il prend en charge les actions DML, mais n'est pas conçu pour être utilisé comme magasin OLTP (OnLine Transaction Processing). La discussion sur la modification des données fournit des instructions pour gérer les modifications tout en maintenant des temps d'arrêt nuls sur les opérations d'analyse et en optimisant les performances du traitement analytique en ligne (OLAP). Si les structures de données normalisées ou partiellement normalisées, telles que le schéma en étoile ou le flocon de neige, conviennent aux opérations de mise à jour/suppression, elles ne sont pas optimales pour les charges de travail OLAP. Lors de l'exécution d'opérations OLAP sur des tables normalisées, plusieurs tables doivent être jointes pour effectuer les agrégations requises. Les JOINTURES sont possibles avec BigQuery et parfois recommandées sur de petites tables. Cependant, elles ne sont généralement pas aussi performantes que les structures dénormalisées.

Le graphique suivant compare les performances des requêtes utilisant des JOINTURES à des filtres simples en fonction de la taille de la table. Les performances des requêtes montrent une décroissance beaucoup plus prononcée en présence de JOINTURES.

Comparaison de la durée des requêtes lors de l'utilisation des jointures et des filtres

Inconvénients de la dénormalisation

Les schémas dénormalisés ne sont pas optimaux en termes de stockage, mais le faible coût de stockage de BigQuery compense les préoccupations concernant l'inefficacité. Pour comprendre pourquoi le stockage n'est pas un facteur important, il suffit de comparer les coûts aux gains de vitesse d'interrogation.

Le maintien de l'intégrité des données constitue un défi lorsque l'on travaille avec un schéma dénormalisé. En fonction de la fréquence et de l'étendue des modifications, le maintien de l'intégrité des données peut nécessiter un temps de traitement plus long, voire parfois une intervention humaine pour les tests et la vérification.

Partitionner les tables

Avec BigQuery, il est possible de partitionner les tables par date. Vous activez le partitionnement pendant le processus de création de table. BigQuery crée automatiquement de nouvelles partitions basées sur la date, sans nécessiter de maintenance supplémentaire. De plus, vous pouvez spécifier un délai d'expiration pour les données des partitions.

Les nouvelles données insérées dans une table partitionnée sont écrites sur la partition brute au moment de l'insertion. Pour contrôler explicitement la partition sur laquelle les données sont chargées, la tâche de chargement peut spécifier une partition de date particulière.

Charger des données

Afin que les données puissent être chargées dans BigQuery pour les charges de travail analytiques, elles sont généralement stockées dans un produit Cloud Storage dans un format natif. Au cours des premières étapes de la migration vers GCP, le modèle courant consiste à utiliser les outils existants d'extraction, de transformation et de chargement (ETL) afin de transformer les données vers un schéma idéal pour BigQuery. Une fois les données transformées, elles sont transférées vers Cloud Storage sous forme de fichiers CSV, JSON ou Avro, puis chargées dans BigQuery à l'aide de tâches de chargement ou bien en flux continu. Vous pouvez également transférer des fichiers vers Cloud Storage dans le schéma natif du stockage de données existant sur site. Ce schéma est chargé dans un ensemble de tables de transfert dans BigQuery, puis transformé en schéma idéal pour BigQuery à l'aide des commandes SQL BigQuery. Ces deux approches sont représentées ici :

Première méthode de chargement de données

Deuxième méthode de chargement de données

À mesure que vous élargirez votre empreinte dans GCP, vous allez probablement capturer vos données sources directement dans Cloud Bigtable, Cloud Datastore ou Cloud Spanner. Vous pourrez ensuite vous servir de Cloud Dataflow pour les extraire, les transformer et les charger par lot ou par flux dans BigQuery.

Capturer le code source directement

Utiliser des tâches de chargement

Cette section part du principe que les données sont stockées dans Cloud Storage en tant que collection de fichiers et dans un format compatible. Pour plus d'informations sur chaque format de données, ainsi que sur les exigences et fonctionnalités spécifiques à prendre en compte lors du choix d'un format, consultez la page Formats de données BigQuery.

Outre le format CSV, vous pouvez également utiliser des fichiers de données avec des délimiteurs autres que des virgules à l'aide de l'indicateur --field_delimiter. Pour plus d'informations, consultez la section Indicateurs de chargement de l'outil de ligne de commande bq.

Avec BigQuery, le chargement de fichiers compressés gzip est disponible mais pas aussi rapide que le chargement de fichiers non compressés. En cas d'urgence ou lorsque le transfert de fichiers non compressés vers Cloud Storage est soumis à des contraintes de bande passante ou de temps, effectuez un test de chargement rapide pour déterminer la meilleure solution.

Les tâches de chargement étant asynchrones, vous n'avez pas besoin de maintenir une connexion client pendant leur exécution. Plus important encore, les tâches de chargement n'affectent pas les autres ressources BigQuery.

Une tâche de chargement crée une table de destination s'il n'en existe pas déjà une.

BigQuery détermine le schéma de données comme suit :

Vous pouvez spécifier explicitement un schéma en le transmettant en tant qu'argument à la tâche de chargement. Les tâches de chargement en cours peuvent être ajoutées à la même table à l'aide de la même procédure que celle du chargement initial, mais le schéma ne doit pas nécessairement être transmis avec chaque tâche.

Si les fichiers CSV contiennent toujours une ligne d'en-tête qui doit être ignorée après le chargement initial et la création de la table, vous pouvez vous servir de l'indicateur --skip_leading_rows pour ignorer cette ligne. Pour plus d'informations, consultez la section Indicateurs de chargement de l'outil de ligne de commande bq.

BigQuery définit des limites quotidiennes sur le nombre et la taille des tâches de chargement que vous pouvez effectuer par projet et par table. De plus, il limite la taille des fichiers de chargement et des enregistrements. Pour plus d'informations, consultez les règles relatives aux quotas.

Vous pouvez lancer des tâches de chargement via l'interface utilisateur Web de BigQuery. Pour automatiser le processus, vous pouvez configurer une fonction Cloud Functions afin d'écouter un événement Cloud Storage associé à l'arrivée de nouveaux fichiers dans un bucket donné, puis lancer la tâche de chargement BigQuery.

Utiliser des insertions en flux continu

Pour une approche alternative et complémentaire, vous pouvez également transférer des données directement dans BigQuery. Les données transférées en continu sont mises à disposition immédiatement et peuvent être interrogées en temps réel parallèlement aux données de table existantes.

Dans les situations pouvant tirer profit d'informations en temps réel, telles que la détection des fraudes ou la surveillance des métriques du système, le flux continu peut s'avérer un facteur de différenciation important. Cependant, contrairement aux tâches de chargement, qui sont gratuites dans BigQuery, le transfert de données en flux continu est payant. Par conséquent, il est important d'utiliser cette méthode dans les situations où les avantages surpassent les coûts.

Lorsque vous transmettez des données en continu aux tables BigQuery, vous envoyez vos enregistrements directement à BigQuery à l'aide de l'API BigQuery. Si vous utilisez Logging, le service de journalisation de GCP, vous pouvez également transférer en continu les journaux de votre projet GCP directement dans BigQuery, y compris les journaux de requêtes d'App Engine et les informations des journaux personnalisés envoyées à Logging.

Gérer le changement

De nombreux entrepôts de données fonctionnent dans le cadre de contrats de niveau de service (SLA), exigeant peu ou pas de temps d'arrêt. Tandis que Google gère les temps d'activité de BigQuery, vous contrôlez la disponibilité et la réactivité de vos ensembles de données selon l'approche que vous avez adoptée pour refléter les modifications apportées aux données.

Toutes les modifications de table dans BigQuery sont compatibles ACID. Ceci s'applique aux opérations DML, aux requêtes avec des tables de destination et aux tâches de chargement. Une table soumise à des insertions, des mises à jour et des suppressions lors de la diffusion des requêtes utilisateur gère la simultanéité de manière transparente et passe d'un état à un autre de manière atomique. Par conséquent, la modification d'une table ne nécessite pas de temps d'arrêt. En revanche, le processus interne peut nécessiter une phase de test et de validation avant de mettre à disposition les données actualisées à des fins d'analyse. De plus, étant donné que les opérations DML se livrent concurrence avec les charges de travail analytiques pour exploiter les emplacements, il peut être préférable de les isoler. Pour ces raisons, vous avez la possibilité d'introduire des temps d'arrêt. Cet article utilise le terme "temps d'arrêt d'analyse" pour éviter toute confusion avec le temps d'arrêt du service BigQuery.

Pour gérer les temps d'arrêt d'analyse, vous pouvez appliquer la plupart des techniques anciennes et éprouvées. Cette section explore plus en détail certains des défis et remèdes connus.

Fenêtre à durée flexible

Contrairement à un lac de données, un entrepôt de données traditionnel ne conserve les données que pendant une durée déterminée, par exemple les 5 dernières années. Lors de chaque cycle de mise à jour, de nouvelles données sont ajoutées à l'entrepôt, et les données les plus anciennes sont supprimées, en maintenant la durée fixe. Ce concept a été essentiellement utilisé pour contourner les limites des technologies plus anciennes.

BigQuery est conçu pour évoluer et peut s'adapter à mesure que la taille de l'entrepôt augmente. Il n'est donc pas nécessaire de supprimer les données plus anciennes. En conservant l'intégralité de l'historique, vous pouvez fournir plus d'informations sur votre entreprise. Si les coûts de stockage posent problème, vous avez la possibilité de tirer parti des tarifs de stockage à long terme de BigQuery en archivant les données les plus anciennes, afin de ne les utiliser qu'en cas de besoin, dans le cadre d'une analyse particulière. Si vous avez toujours de bonnes raisons de supprimer les données plus anciennes, vous pouvez utiliser la compatibilité native de BigQuery avec les tables partitionnées par date et l'expiration des partitions. En d'autres termes, BigQuery peut supprimer automatiquement les anciennes données.

Modifier les schémas

Si un entrepôt de données est conçu et développé, il est courant de modifier les schémas de table en ajoutant, en mettant à jour ou en supprimant des colonnes, voire en ajoutant ou supprimant des tables entières. À moins que ces modifications ne se présentent sous la forme d'une colonne ou d'une table ajoutée, elles risquent de briser les requêtes et les rapports enregistrés qui font référence à une table supprimée, à une colonne renommée, etc.

Une fois l'entrepôt de données en production, ces modifications sont soumises à un contrôle strict. Vous pouvez décider de gérer les modifications de schéma mineures au cours d'une interruption d'analyse, mais la plupart des modifications apportées pour refléter les schémas sont planifiées sous forme de mises à niveau de version. Vous concevez, développez et testez la mise à niveau en parallèle pendant que la version précédente de l'entrepôt de données diffuse les charges de travail d'analyse. La même approche s'applique pour modifier les schémas d'un entrepôt de données BigQuery.

Dimensions à évolution lente

Un schéma de données normalisé minimise l'impact sur les dimensions à évolution lente (SCD, Slowly Changing Dimensions) en isolant la modification dans les tables de dimension. Il est généralement préférable à un schéma dénormalisé, où les SCD peuvent provoquer de nombreuses mises à jour de la table de faits plate. Toutefois, comme indiqué dans la section relative à la conception du schéma, il est conseillé d'utiliser la normalisation avec précaution avec BigQuery.

Lorsqu'il est question de SCD, il n'existe pas de solution unique. Il est important de comprendre la nature de la modification et d'appliquer la solution ou les combinaisons de solutions les plus pertinentes à votre problème. Le reste de cette section présente quelques solutions et décrit comment les appliquer aux types de SCD.

Technique 1 : permuter les vues

Cette technique repose sur deux vues de données : la vue "principale" et la vue "fictive". L'astuce consiste à masquer la table active et à exposer la vue "principale" aux utilisateurs. Lors des cycles de mise à jour, la vue "fictive" est créée/mise à jour, puis subit des tests de correction de données, tandis que les utilisateurs travaillent dans la vue "principale". Au moment de la permutation, la vue "principale" est remplacée par la vue "fictive". L'ancienne vue "principale", devenue la vue "fictive", peut être supprimée jusqu'au prochain cycle de mise à jour ou conservée pour certains flux de travail en fonction des règles et processus définis par l'organisation.

Les deux vues peuvent reposer sur une table commune et être différenciées par une colonne, par exemple "view_type", ou basées sur des tables distinctes. L'ancienne méthode n'est pas recommandée, car les opérations LMD (Data Manipulation Language, DML) sur la vue "fictive" de la table peuvent ralentir les requêtes des utilisateurs sur la vue "principale" sans offrir de réels avantages.

Bien que la permutation des vues n'entraîne pas de temps d'arrêt d'analyse, son coût est plus élevé, car pendant le cycle de mise à jour, deux copies des données existent. Plus important encore, si les cycles de mise à jour interviennent selon une périodicité supérieure à 90 jours, cette approche peut empêcher votre entreprise de tirer parti des tarifs de stockage à long terme. Le cycle de 90 jours est déterminé en fonction des règles de tarification en vigueur au moment de la rédaction de cet article. Assurez-vous de vérifier les dernières règles en vigueur.

Parfois, différents segments de données évoluent à leur propre rythme. Par exemple, les données sur les ventes en Amérique du Nord sont mises à jour quotidiennement, tandis que celles de la région Asie Pacifique sont mises à jour toutes les deux semaines. Dans de telles situations, il est préférable de partitionner la table en fonction du facteur déterminant du changement, le pays dans cet exemple. La permutation des vues est ensuite appliquée aux partitions concernées et non à l'ensemble de l'entrepôt de données. Au moment de la rédaction de cet article, le partitionnement n'est possible que sur la base d'un attribut de données personnalisé, tel que Pays, en scindant explicitement les données en plusieurs tables.

Technique 2 : charger la partition sur place

Lorsqu'il est possible d'isoler les modifications de données au moyen d'une partition et que de brèves interruptions d'analyse sont tolérées, la permutation des vues risque d'être excessive. Dans ce cas, il est possible d'organiser les données des partitions affectées dans d'autres tables BigQuery ou de les exporter vers des fichiers Cloud Storage, où elles peuvent être remplacées pendant les temps d'arrêt d'analyse.

Pour remplacer des données dans une partition cible par des données provenant d'une requête dans une autre table :

bq query --use_legacy_sql=false --replace \
         --destination_table 'flight_data.fact_flights_part$20140910' \
         'select * from `ods.load_flights_20140910`

Pour remplacer des données dans une partition cible en les chargeant à partir de Cloud Storage :

bq load  --replace \
         --source_format=NEWLINE_DELIMITED_JSON
         'flight_data.fact_flights_part$20140910' \
         gs://{bucket}/load_flights_20140910.json
Technique 3 : masquer les données de mise à jour

Une petite dimension à évolution rapide constitue un candidat idéal pour la normalisation. Dans cette technique, les mises à jour d'une telle dimension sont organisées dans une table ou une vue isolée qui est liée de manière conditionnelle au reste des données :

SELECT f.order_id as order_id, f.customer_id as customer_id,
    IFNULL(u.customer_first_name, f.customer_first_name) as customer_first_name,
    IFNULL(u.customer_last_name, f.customer_last_name) as customer_last_name
FROM fact_table f
LEFT OUTER JOIN pending_customer_updates u
ON f.customer_id = u.customer_id
SCD de type 1 : remplacer la valeur d'attribut

Les SCD de type 1 écrasent la valeur d'un attribut en la remplaçant par de nouvelles données sans conserver l'historique. Par exemple, si le produit "crème hydratante géniale" faisait partie de la catégorie "santé et beauté" et qu'il est désormais classé dans la catégorie "cosmétiques", la modification se présente comme suit :

Avant :

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC crème hydratante géniale - 2,95 L santé et beauté

Après :

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC crème hydratante géniale - 2,95 L santé et beauté
cosmétiques

Si l'attribut se trouve dans une table de dimension normalisée, la modification est particulièrement isolée. Vous mettez simplement à jour la ligne affectée dans la table de dimension. Pour les tables de dimension plus petites avec des mises à jour fréquentes de type 1, utilisez la technique 3 : masquer les données de mise à jour.

Si l'attribut est intégré à la table de faits d'une manière dénormalisée, la modification est plutôt généralisée. Vous devrez alors mettre à jour toutes les lignes de faits où l'attribut est répété. Dans ce cas, utilisez soit la technique 2 : charger la partition sur place, soit la technique 1 : permuter les vues.

SCD de type 2 : modifier la valeur d'attribut et conserver l'historique

Cette méthode permet de suivre des données d'historique illimitées en créant plusieurs enregistrements pour une clé naturelle donnée, à l'aide de clés de substitution distinctes. Par exemple, la même modification illustrée dans l'exemple de SCD de type 1 sera traitée comme suit :

Avant :

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC crème hydratante géniale - 2,95 L santé et beauté 31-Jan-2009 NULL

Après :

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC crème hydratante géniale - 2,95 L santé et beauté 31-Jan-2009 18-JUL-2017
124 ABC crème hydratante géniale - 2,95 L cosmétiques 19-JUL-2017 NULL

Si l'attribut se trouve dans une table de dimension normalisée, la modification est isolée. Il vous suffit alors de mettre à jour la ligne précédente et d'en ajouter une nouvelle dans la table de dimension. Pour les tables de dimension plus petites avec des mises à jour fréquentes de type 1, utilisez la technique 3 : masquer les données de mise à jour.

Si l'attribut est intégré à la table de faits d'une manière dénormalisée, la situation peut s'avérer plus favorable, tant que vous ne conservez pas les dates de début et de fin explicites de la valeur, mais que vous vous fiez aux dates de transaction. Comme la valeur précédente reste vraie pour la date et l'heure auxquelles les transactions précédentes ont eu lieu, il n'est pas nécessaire de modifier les lignes précédentes de la table de faits. La table de faits ressemblerait alors à ceci :

TRANSACTION_DATE PRD_SK PRD_ID PRD_DESC PRD_CATEGORY UNITS AMOUNT
18-JUL-2017 123 ABC crème hydratante géniale - 2,95 L santé et beauté 2 25.16
19-JUL-2017 124 ABC crème hydratante géniale - 2,95 L cosmétiques 1 13.50

Interroger les données

BigQuery est compatible avec les requêtes SQL standards et la révision SQL:2011 du langage SQL définie par l'ANSI. La référence SQL de BigQuery fournit une description complète de la totalité des fonctions, opérateurs et fonctionnalités regex compatibles.

Étant donné que BigQuery est compatible avec les champs imbriqués et répétés dans le cadre du modèle de données, sa compatibilité avec SQL a été spécifiquement étendue à ces types de champs. Par exemple, en utilisant l'ensemble de données public GitHub, vous pouvez émettre la commande UNNEST, permettant l'itération sur un champ répété :

SELECT
  name, count(1) as num_repos
FROM
  `bigquery-public-data.github_repos.languages`, UNNEST(language)
GROUP BY name
ORDER BY num_repos
DESC limit 10

Requêtes interactives

L'interface utilisateur Web de BigQuery permet l'interrogation interactive des ensembles de données et fournit une vue consolidée des ensembles de données de tous les projets auxquels vous avez accès. La console fournit également plusieurs fonctionnalités utiles, telles que la sauvegarde et le partage de requêtes ad-hoc, le réglage et la modification de requêtes d'historique, l'exploration de tables et de schémas ainsi que la collecte de métadonnées de table. Pour en savoir plus, consultez la page Interface utilisateur Web de BigQuery.

capture d'écran de l'interface Web bigquery

Requêtes automatisées

Il est courant d'automatiser l'exécution des requêtes en fonction d'un planning/événement, et de mettre en cache les résultats à des fins de consommation ultérieure.

Si vous utilisez Airflow pour orchestrer d'autres activités automatisées et que vous connaissez déjà l'outil, utilisez l'API Apache Airflow for BigQuery à cette fin. Ce billet de blog vous guide tout au long du processus d'installation d'Airflow et de création d'un flux de travail par rapport à BigQuery.

Pour des orchestrations plus simples, vous pouvez vous fier aux tâches cron. Ce billet de blog vous explique comment encapsuler une requête en tant qu'application App Engine et l'exécuter en tant que tâche périodique planifiée.

Optimisation des requêtes

Chaque fois que BigQuery exécute une requête, il analyse la colonne complète. BigQuery n'est pas compatible avec les index, et ne les utilise donc pas. Les coûts liés aux requêtes et aux performances de BigQuery étant basés sur la quantité de données analysées au cours d'une requête, vous devez concevoir les requêtes de sorte qu'elles ne référencent que les colonnes pertinentes. Lorsque vous utilisez des tables partitionnées par date, veillez à ce que seules les partitions pertinentes soient analysées. Pour ce faire, définissez des filtres de partition basés sur PARTITIONTIME ou PARTITIONDATE.

Pour comprendre les caractéristiques de performance après l'exécution d'une requête, consultez l'explication détaillée du plan de requête. Elle décrit les étapes franchies par la requête, le nombre de lignes d'entrée/sortie traitées à chaque étape ainsi que le profil de minutage de chaque étape. Les résultats fournis dans l'explication peuvent vous aider à comprendre et à optimiser vos requêtes.

Capture d'écran des résultats bigquery

Sources externes

Vous pouvez exécuter des requêtes sur des données externes à BigQuery à l'aide de sources de données fédérées, mais cette approche affecte les performances. N'utilisez des sources de données fédérées que si les données doivent être gérées en externe. Vous pouvez également utiliser la fédération de requêtes pour exécuter un processus ETL à partir d'une source externe vers BigQuery. Cette approche vous permet de définir le processus ETL à l'aide d'une syntaxe SQL familière.

Fonctions définies par l'utilisateur

BigQuery est également compatible avec les fonctions définies par l'utilisateur (User defined fonctions, UDF) pour les requêtes dont la complexité dépasse les capacités de SQL. Ces fonctions permettent d'étendre les fonctions SQL intégrées. Elles prennent une liste de valeurs, sous forme de tableaux ou de structures, et renvoient une valeur unique qui peut également se trouver dans ce même format. Les fonctions définies par l'utilisateur sont écrites en JavaScript et peuvent inclure des ressources externes, telles que le chiffrement ou d'autres bibliothèques.

Partage des requêtes

BigQuery permet aux collaborateurs de sauvegarder et de partager des requêtes entre membres d'équipe. Cette fonctionnalité peut s'avérer particulièrement utile dans les exercices d'exploration de données ou pour se familiariser rapidement avec un nouvel ensemble de données ou un nouveau modèle de requête. Pour plus d'informations, consultez la page Enregistrer et partager des requêtes.

Analyser des données

Cette section présente diverses manières de vous connecter à BigQuery et d'analyser les données. Pour tirer pleinement parti de BigQuery en tant que moteur d'analyse, vous devez stocker les données dans l'espace de stockage BigQuery. Toutefois, selon votre cas d'utilisation spécifique, il peut être bénéfique d'analyser des sources externes, soit seules, soit jointes à des données stockées dans BigQuery.

Outils prêts à l'emploi

Pour extraire des données analytiques de BigQuery et créer des représentations visuelles interactives sophistiquées, vous pouvez utiliser Data Studio, disponible en version bêta au moment de la rédaction de cet article, ainsi que de nombreux outils partenaires déjà intégrés à BigQuery.

Si vous devez choisir un outil, le rapport Magic Quadrant de Gartner et le rapport G2 Score de G2 Crowd proposent une comparaison complète des fournisseurs. Le rapport Gartner est disponible sur de nombreux sites partenaires, tels que Tableau.

logos des partenaires

Développement personnalisé

Pour créer des applications et des plates-formes personnalisées sur BigQuery, vous pouvez recourir aux bibliothèques clientes disponibles pour les langages de programmation les plus courants, ou utiliser directement l'API REST de BigQuery.

Pour obtenir un exemple concret, reportez-vous à ce tutoriel, qui décrit comment utiliser les bibliothèques Python pour se connecter à BigQuery et générer des tableaux de bord interactifs personnalisés.

Connecteurs tiers

Pour vous connecter à BigQuery depuis une application qui n'est pas intégrée de manière native à BigQuery au niveau de l'API, vous pouvez utiliser les pilotes JDBC et ODBC pour BigQuery. Ces pilotes fournissent un pont permettant aux applications héritées ou difficilement modifiables, telles que Microsoft Excel, d'interagir avec BigQuery. Si les outils ODBC et JDBC peuvent interagir avec BigQuery à l'aide de SQL, ils ne sont pas aussi expressifs que le traitement direct avec l'API.

Coûts

La plupart des entrepôts de données desservent plusieurs entités commerciales au sein de l'organisation. L'analyse du coût de fonctionnement par entité commerciale constitue un défi courant. Pour obtenir des conseils sur la ventilation de votre facture et l'attribution d'un coût à une consommation, consultez l'article Visualiser la facturation GCP à l'aide de BigQuery et Data Studio.

BigQuery comporte trois principales catégories de coûts : les coûts de chargement, de stockage et de requête. Cette section présente chaque catégorie en détail.

Stocker des données

Les tarifs du stockage sont calculés au prorata par Mo/s.

Si une table n'a pas été modifiée pendant 90 jours consécutifs, elle est classée dans la catégorie de stockage à long terme et le tarif du stockage de cette table chute automatiquement de 50 % à 0,01 $ par Go et par mois. Lorsqu'une table est considérée comme un espace de stockage à long terme, cela n'a aucune incidence sur les performances, la durabilité, la disponibilité ou toute autre fonctionnalité. Lorsque les données d'une table sont modifiées, BigQuery réinitialise le chronomètre de la table. Toutes les données de la table reviennent alors au tarif de stockage normal. Les actions qui ne manipulent pas directement des données, telles que l'interrogation et la création de vues, ne réinitialisent pas le chronomètre.

Pour en savoir plus, consultez les tarifs de stockage BigQuery.

Charger des données

Vous pouvez charger des données dans BigQuery à l'aide d'une tâche de chargement classique, sans aucuns frais. Une fois les données chargées, vous payez pour le stockage, comme indiqué ci-dessus.

Les insertions en flux continu sont facturées en fonction de la quantité de données transférées en continu. Pour plus d'informations, consultez les coûts des insertions en flux continu répertoriés dans la section Tarifs du stockage BigQuery.

Interroger les données

Pour les requêtes, BigQuery propose deux modèles de tarification : à la demande et au forfait.

Tarifs à la demande

Dans le modèle à la demande, BigQuery facture la quantité de données ayant fait l'objet d'un accès lors de l'exécution de la requête. Étant donné que BigQuery utilise un format de stockage en colonnes, seules les colonnes pertinentes à la requête font l'objet d'un accès. Si vous n'exécutez des rapports que sur une base hebdomadaire ou mensuelle, et que vous avez interrogé moins de 1 To de données, vous constaterez peut-être que le coût des requêtes sur votre facture est très bas. Pour en savoir plus sur la facturation des requêtes, consultez les tarifs des requêtes BigQuery.

Pour vous aider à déterminer au préalable la quantité de données qu'une requête va analyser, vous pouvez utiliser l'outil de validation des requêtes dans l'interface utilisateur Web. Dans le cas d'un développement personnalisé, vous pouvez définir l'indicateur dryRun dans la requête d'API et empêcher BigQuery d'exécuter la tâche, pour renvoyer plutôt des statistiques sur la tâche, telles que le nombre d'octets à traiter. Pour en savoir plus reportez-vous à l'API Query.

API Query

Tarifs forfaitaires

Les clients qui préfèrent une plus grande cohérence des dépenses mensuelles peuvent choisir d'activer les tarifs forfaitaires. Pour en savoir plus, consultez la section Tarifs forfaitaires BigQuery.

Étapes suivantes

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…