Guide BigQuery pour Data Engineer

BigQuery : Les meilleures pratiques que tout ingénieur de données devrait connaître

Google BigQuery est un entrepôt de données analytiques géré, à l’échelle du pétaoctet, qui permet aux entreprises d’analyser rapidement toutes leurs données. BigQuery est un outil puissant pour les ingénieurs de données, mais connaissez-vous les meilleures pratiques à suivre lors du développement de pipelines de données avec BigQuery ? Cet article couvrira les principales pratiques pour les ingénieurs de données travaillant avec BigQuery, vous aidant à concevoir et implémenter efficacement des pipelines de données BigQuery. Si vous découvrez BigQuery, notamment le SQL, on propose 3 niveaux de formations BigQuery pour prendre en main BigQuery et vous initier au SQL.

 

Partitionnement des données BigQuery

Une table partitionnée est divisée en segments distincts, appelés partitions, qui facilitent la gestion et l’interrogation des données. Cette division de la table en partitions plus petites permet d’améliorer les performances des requêtes et de maîtriser les coûts, car le nombre d’octets traités par une requête est réduit.

Amélioration des performances et réduction des coûts

En partitionnant une grande table, vous pouvez optimiser significativement les performances de vos requêtes. Par exemple, au lieu de scanner une table entière pour une requête donnée, BigQuery peut se concentrer uniquement sur les partitions pertinentes, ce qui réduit le volume de données à traiter. Cela se traduit par une exécution plus rapide des requêtes et des coûts inférieurs, puisque BigQuery facture en fonction du volume de données analysées.

Définition de la colonne de partition

Pour partitionner une table, il est nécessaire de spécifier une colonne de partition qui servira à segmenter les données. Typiquement, les colonnes de type Date/DateTime ou Integer sont utilisées pour le partitionnement. En choisissant judicieusement la colonne de partition, vous pouvez aligner la structure des données avec les cas d’utilisation les plus fréquents, tels que l’analyse de séries temporelles ou de segments spécifiques de données.

Processus d’élimination des partitions

Lorsque vous exécutez une requête avec un filtre sur la colonne de partitionnement, BigQuery peut exploiter ce filtre pour analyser uniquement les partitions pertinentes et ignorer les autres. Ce processus, appelé élimination des partitions, permet d’optimiser l’utilisation des ressources et de réduire le temps de traitement des requêtes. Par exemple, si votre table de ventes est partitionnée par date et que vous interrogez uniquement les ventes du mois dernier, BigQuery n’analysera que les partitions correspondant à ce mois.

Stockage et métadonnées des partitions

Dans une table partitionnée, les données sont stockées dans des blocs physiques, chaque bloc correspondant à une partition de données. BigQuery conserve également diverses métadonnées pour chaque table partitionnée, incluant les propriétés de tri et les modifications apportées. Ces métadonnées jouent un rôle crucial en permettant à BigQuery d’estimer plus précisément le coût d’une requête avant son exécution, en se basant sur les partitions qui seront scannées.

Exemple concret

Prenons l’exemple d’une table contenant des logs de serveur web. En partitionnant cette table par date de log, vous pouvez exécuter des requêtes pour analyser les logs d’une journée spécifique sans avoir à scanner les logs de toute l’année. Cela non seulement accélère les requêtes, mais réduit également les coûts associés au traitement des données.

En résumé, le partitionnement des tables dans BigQuery est une technique puissante pour améliorer les performances des requêtes et optimiser les coûts. En choisissant judicieusement la colonne de partition et en exploitant les métadonnées, vous pouvez tirer le meilleur parti de vos ressources et garantir une gestion efficace de vos données.

Astuce : Choisir le bon type de partitionnement

Pour choisir la bonne stratégie de partitionnement, il est essentiel de comprendre les motifs de lecture de la table et d’inclure la partition sur la colonne souvent utilisée dans les conditions de filtre. Par exemple, pour une table de transactions financières, il pourrait être judicieux de partitionner par date de transaction. Cela permet aux requêtes filtrant sur la date de transaction de n’accéder qu’à une partie des données, améliorant ainsi l’efficacité et réduisant les coûts.

Quand utiliser le partitionnement ?

Envisagez de partitionner une table dans les scénarios suivants :

Amélioration des performances des requêtes

Vous souhaitez améliorer les performances des requêtes en analysant uniquement une partie d’une table. Par exemple, partitionner une table de transactions par date permet de scanner uniquement les données d’une période spécifique.

Gestion des quotas de table

Votre opération de table dépasse un quota de table standard et vous pouvez limiter les opérations de table à des valeurs de colonne de partition spécifiques, ce qui autorise des quotas de tables partitionnées plus élevés. Par exemple, partitionner une grande table de logs pour éviter de dépasser les quotas de traitement.

Estimation des coûts des requêtes

Vous souhaitez déterminer le coût des requêtes avant leur exécution. BigQuery fournit des estimations du coût de la requête avant que celle-ci ne soit exécutée sur une table partitionnée. Estimez les coûts des requêtes en éliminant une table partitionnée, puis en exécutant une simulation de requête pour estimer les coûts des requêtes. Cela est particulièrement utile pour budgétiser et optimiser l’utilisation de BigQuery.

Fonctionnalités de gestion au niveau de la partition

Définition d’un délai d’expiration de partition

Définir un délai d’expiration de partition pour supprimer automatiquement des partitions entières après un certain délai. Cela est utile pour gérer des tables de logs ou des données temporaires qui n’ont pas besoin d’être conservées indéfiniment.

Écriture de données dans une partition spécifique

Écrire des données dans une partition spécifique à l’aide de jobs de chargement sans affecter les autres partitions de la table. Cela permet de gérer des opérations de chargement de données sans perturber l’ensemble des données.

Suppression de partitions spécifiques

Supprimer des partitions spécifiques sans analyser l’intégralité de la table. Par exemple, vous pouvez supprimer des données obsolètes sans impacter les partitions contenant des données récentes et pertinentes.

Quand envisager le clustering plutôt que le partitionnement

Scénarios d’utilisation du clustering

Envisagez le clustering d’une table plutôt que de la partitionner dans les cas suivants :

Précision supérieure au partitionnement

Vous avez besoin de plus de précision que le partitionnement ne le permet. Le clustering permet de trier les données à un niveau de granularité plus fin, ce qui peut améliorer les performances des requêtes complexes.

Requêtes avec filtres ou agrégations multiples

Vos requêtes utilisent généralement des filtres ou des agrégations sur plusieurs colonnes. Par exemple, si vos requêtes filtrent souvent sur des colonnes comme date et produit, le clustering sur ces colonnes peut améliorer l’efficacité des requêtes.

Cardinalité élevée des colonnes

La cardinalité du nombre de valeurs dans une colonne ou dans un groupe de colonnes est importante. Le clustering est plus efficace lorsque les colonnes utilisées ont une variété significative de valeurs, car cela réduit la quantité de données scannées.

Besoin de précisions sur les coûts

Vous n’avez pas besoin d’estimations de coût strictes avant d’exécuter la requête. Contrairement au partitionnement, le clustering ne fournit pas d’estimations de coût avant l’exécution des requêtes, mais il peut offrir des gains de performance substantiels.

Gestion des petites partitions

Le partitionnement génère une petite quantité de données par partition (moins de 10 Go, environ). La création de nombreuses petites partitions augmente les métadonnées de la table et peut affecter les temps d’accès aux métadonnées lors de l’interrogation de la table. Le clustering peut aider à éviter ce problème en organisant les données de manière plus compacte.

Limitation des partitions

Le partitionnement entraîne un grand nombre de partitions au-delà des limites imposées sur les tables partitionnées. Le clustering permet de gérer efficacement de grands ensembles de données sans créer trop de partitions.

Fréquence des modifications des partitions

Vos opérations LMD modifient fréquemment (par exemple, toutes les quelques minutes) la plupart des partitions de la table. Dans ce cas, le clustering de tables vous permet d’accélérer les requêtes en mettant en cluster les données dans des colonnes spécifiques en fonction des propriétés de tri définies par l’utilisateur, réduisant ainsi la nécessité de re-partitionner fréquemment les données.

En résumé, le choix entre le partitionnement et le clustering dépend des besoins spécifiques de vos requêtes et de la structure de vos données. Utilisez le partitionnement pour des cas d’utilisation où les données peuvent être naturellement divisées en segments distincts, et envisagez le clustering pour des scénarios nécessitant une organisation plus fine et des requêtes plus complexes.

Plus de détail sur Google : https://cloud.google.com/bigquery/docs/partitioned-tables?hl=fr

Clustering des données BigQuery

Le clustering dans BigQuery permet aux ingénieurs de données de spécifier l’ordre dans lequel les données sont stockées. Lorsque nous utilisons le clustering, les données sont triées selon les valeurs des colonnes spécifiées pour la stratégie de clustering, ce qui aide BigQuery à ne scanner que les enregistrements nécessaires lors de l’exécution des manipulations de données sur la table.

Le clustering est particulièrement efficace pour les tables de grande taille où les requêtes impliquent fréquemment des opérations de filtrage ou de jointure sur les mêmes colonnes. En organisant les données de manière ordonnée, le clustering réduit la quantité de données à analyser, ce qui améliore les performances des requêtes.

Amélioration des performances avec les tables en cluster dans BigQuery

Les tables en cluster dans BigQuery sont des tables dont l’ordre de tri est défini par l’utilisateur à l’aide de colonnes en cluster. Les tables en cluster peuvent améliorer les performances des requêtes et réduire leurs coûts.

Colonnes en cluster : définition et avantages

Dans BigQuery, une colonne en cluster est une propriété de table définie par l’utilisateur qui trie les blocs de stockage en fonction des valeurs des colonnes en cluster. Les blocs de stockage sont dimensionnés de manière adaptative en fonction de la taille de la table. Une table en cluster conserve les propriétés de tri dans le contexte de chaque opération qui la modifie.

Avantages des colonnes en cluster

Les requêtes qui filtrent ou agrégent les colonnes en cluster analysent uniquement les blocs pertinents en fonction des colonnes en cluster, et non l’intégralité de la table ou de la partition de la table. Par conséquent, BigQuery risque de ne pas pouvoir estimer avec précision les octets à traiter par la requête ni les coûts de la requête, mais il tente de réduire le nombre total d’octets lors de l’exécution.

Mise en cluster des tables avec plusieurs colonnes

Lorsque vous mettez une table en cluster à l’aide de plusieurs colonnes, l’ordre des colonnes détermine lesquelles sont prioritaires lorsque BigQuery trie et regroupe les données dans des blocs de stockage.

Exemple de disposition des blocs de stockage

L’exemple suivant compare la disposition du bloc de stockage logique d’une table sans cluster à la disposition des tables en cluster comportant une ou plusieurs colonnes en cluster :

• Table sans cluster : Les données sont stockées sans ordre particulier, ce qui peut entraîner des scans complets de la table pour certaines requêtes.
• Table en cluster avec une colonne : Les données sont triées et regroupées par les valeurs de la colonne en cluster, réduisant ainsi le nombre de blocs de stockage à analyser pour les requêtes filtrant cette colonne.
• Table en cluster avec plusieurs colonnes : L’ordre des colonnes de cluster est crucial, car BigQuery trie les données en priorité par la première colonne, puis par la deuxième, et ainsi de suite. Cela permet d’optimiser davantage les performances des requêtes en réduisant encore le nombre de blocs de stockage à scanner.

Astuces pour utiliser efficacement les tables en cluster

Choisir les bonnes colonnes de cluster

Sélectionnez des colonnes fréquemment utilisées dans les filtres et les jointures pour maximiser l’efficacité du clustering. Par exemple, dans une table de transactions, les colonnes customer_id et transaction_date peuvent être de bons choix.

Optimiser l’ordre des colonnes de cluster

L’ordre des colonnes de cluster doit refléter la fréquence et la priorité des filtres dans vos requêtes. Par exemple, si transaction_date est souvent utilisé comme filtre principal, il devrait être la première colonne de cluster.

Surveillance et ajustement des performances

Utilisez les outils de surveillance de BigQuery pour analyser les performances des requêtes sur les tables en cluster. Ajustez les colonnes de cluster et leur ordre en fonction des motifs d’utilisation observés pour améliorer constamment l’efficacité des requêtes.

En appliquant ces meilleures pratiques, vous pouvez tirer pleinement parti des tables en cluster dans BigQuery pour optimiser les performances des requêtes et réduire les coûts associés à l’analyse des données.

Exemple : Utilisation du clustering pour optimiser les requêtes

Supposons que vous ayez une table de ventes avec des colonnes comme date, produit et montant. En utilisant le clustering sur date et produit, les requêtes filtrant ces colonnes seront beaucoup plus rapides. Toutefois, assurez-vous que l’ordre des colonnes dans la stratégie de clustering correspond à celui des colonnes dans les requêtes utilisateur pour une efficacité maximale. Par exemple, une requête cherchant à obtenir les ventes d’un produit spécifique sur une période donnée bénéficiera grandement de cette structure de données.

Astuce : Ordre des colonnes dans le clustering

Une des meilleures pratiques est de choisir les colonnes pour le clustering qui sont fréquemment utilisées dans les filtres, les groupements ou les jointures. Si les colonnes de jointure sont également des colonnes de filtrage fréquentes, elles devraient être en tête de liste pour le clustering. Par exemple, pour une table de commandes, les colonnes customer_id et order_date sont souvent idéales pour le clustering.

Plus de détail sur Google : https://cloud.google.com/bigquery/docs/clustered-tables?hl=fr

Tables externes BigQuery

Les tables externes permettent aux ingénieurs de données d’accéder aux données qui ne sont pas stockées directement dans BigQuery. Par exemple, accéder aux données depuis Google Cloud Storage peut être réalisé via des tables externes. Seuls les métadonnées liées à ces tables sont stockées dans BigQuery, alors que les données réelles restent dans le système externe.

Cette fonctionnalité est particulièrement utile lorsque les données sont volumineuses et fréquemment mises à jour, ou lorsqu’elles résident déjà dans des systèmes de stockage externes. Les tables externes permettent une intégration fluide avec des sources de données telles que Google Cloud Storage, Google Drive, ou même des bases de données Cloud SQL.

Utilisation des sources de données externes avec BigQuery

BigQuery offre la possibilité d’interroger directement des sources de données externes sans nécessiter le stockage de ces données dans BigQuery. Cela permet de tirer parti des données situées dans d’autres services cloud comme Google Cloud Storage ou des bases de données Cloud SQL, sans avoir à les migrer.

Avantages des sources de données externes

L’intégration de sources de données externes présente plusieurs avantages, notamment pour les processus ELT (Extraction, Chargement, Transformation). En chargeant et en nettoyant les données en une seule étape, vous pouvez ensuite écrire les résultats directement dans BigQuery avec une requête CREATE TABLE … AS SELECT. De plus, cela permet de réaliser des jointures entre des tables BigQuery et des données externes fréquemment mises à jour, sans avoir à actualiser constamment les données dans BigQuery.

Les tables externes dans BigQuery ressemblent aux tables standard en termes de gestion des métadonnées et des schémas, mais leurs données résident en dehors de BigQuery. Ces tables sont pratiques pour accéder à des données stockées dans des systèmes externes tout en les gérant comme des tables BigQuery standard.

Types de tables externes

• Tables BigLake : Ces tables permettent d’interroger des données structurées dans des entrepots de données externes avec des délégations d’accès. Par exemple, vous pouvez accéder à des données sur Amazon S3 ou Azure Blob Storage via BigQuery Omni.
• Tables d’objets : Utilisées pour analyser des données non structurées dans Cloud Storage, ces tables permettent d’effectuer des analyses avec des fonctions à distance ou des inférences avec BigQuery ML.
• Tables externes non BigLake : Ces tables permettent d’interroger des données structurées dans des magasins de données externes divers, nécessitant des autorisations spécifiques sur les sources de données.

Requêtes fédérées

Les requêtes fédérées permettent d’envoyer des instructions de requête directement à des bases de données externes comme Spanner ou Cloud SQL et de recevoir les résultats sous forme de table temporaire dans BigQuery. Cette méthode utilise l’API BigQuery Connection pour établir des connexions et exécuter des requêtes en utilisant le dialecte SQL propre à la base de données externe.

Avantages des requêtes fédérées

Les requêtes fédérées sont particulièrement utiles lorsque vous devez combiner des données de différentes sources sans les déplacer physiquement. Elles permettent également d’exploiter les capacités spécifiques des bases de données externes tout en intégrant facilement les résultats dans BigQuery pour une analyse plus approfondie.

L’utilisation des sources de données externes avec BigQuery enrichit considérablement les capacités analytiques de votre infrastructure de données. En intégrant des données de divers services cloud et bases de données externes, vous pouvez effectuer des analyses plus complètes et précises, tout en optimisant les coûts et les performances. Que vous utilisiez des tables externes ou des requêtes fédérées, BigQuery offre la flexibilité et la puissance nécessaires pour répondre à vos besoins analytiques.

Astuce : Optimisation des tables externes

Pour bénéficier des avantages du partitionnement avec les tables externes, utilisez le format de partitionnement Hive par défaut lors du stockage des données dans Cloud Storage. De plus, préférez des formats de données compressés comme Avro ou Parquet pour réduire la taille de stockage et améliorer les performances de lecture. Par exemple, utiliser Avro pour stocker des logs de transactions permet non seulement de compresser les données mais aussi de maintenir une structure schématique claire et efficace.

Exemple : Accès aux données de stockage cloud

Imaginez que vous stockiez de grandes quantités de données de logs d’application dans Google Cloud Storage. Plutôt que d’importer constamment ces données dans BigQuery, vous pouvez créer des tables externes pointant vers ces fichiers. Cela permet de lancer des requêtes analytiques directement sur les données de logs, tout en réduisant les coûts et en maintenant des performances élevées.

Comparaison des fonctionnalités des sources de données externe

Le tableau suivant compare le comportement des sources de données externes :

	Tables BigLake	Tables d’objets	Tables externes autres que BigLake	Requêtes fédérées
Utilise la délégation d’accès	Oui, via un compte de service	Oui, via un compte de service	Non	Oui, via un compte utilisateur de base de données (Cloud SQL uniquement)
Peut être basée sur plusieurs URI sources	Oui	Oui	Oui (Cloud Storage seulement)	Non applicable
Mappage des lignes	Les lignes représentent le contenu des fichiers.	Les lignes représentent les métadonnées des fichiers.	Les lignes représentent le contenu des fichiers.	Non applicable
Accessible par d’autres outils de traitement de données à l’aide de connecteurs	Oui (Cloud Storage seulement)	Non	Oui	Non applicable
Peut être jointe à d’autres tables BigQuery	Oui (Cloud Storage seulement)	Yes	Oui	Yes
Accessible en tant que table temporaire	Oui (Cloud Storage seulement)	Non	Oui	Yes
Fonctionne avec Amazon S3	Yes	Non	Non	Non
Fonctionne avec Azure Storage	Yes	Non	Non	Non
Fonctionne avec Bigtable	Non	Non	Oui	Non
Compatible avec Spanner	Non	Non	Non	Yes
Compatible avec Cloud SQL	Non	Non	Non	Yes
Fonctionne avec Google Drive	Non	Non	Oui	Non
Compatible avec Cloud Storage	Yes	Oui	Oui	Non

Vues matérialisées

Dans BigQuery, les vues matérialisées sont des vues précalculées qui mettent régulièrement en cache les résultats d’une requête pour améliorer les performances et l’efficacité. BigQuery exploite les résultats précalculés des vues matérialisées et, dans la mesure du possible, ne lit que les modifications des tables de base pour calculer les résultats à jour. Les vues matérialisées peuvent être interrogées directement ou peuvent être utilisées par l’optimiseur BigQuery pour traiter les requêtes sur les tables de base.

Les vues matérialisées permettent de pré-calculer les résultats d’une requête qui change peu fréquemment et de les stocker sous forme de table. Elles sont intermédiaires entre les tables et les vues et stockent à la fois les données et la requête utilisée pour obtenir ces données.

Les vues matérialisées sont particulièrement utiles pour les requêtes complexes et les rapports qui nécessitent des agrégations de données fréquentes. Elles permettent de réduire la charge sur le système en évitant de recalculer les mêmes résultats à chaque requête.

Les requêtes qui utilisent des vues matérialisées sont généralement plus rapides et consomment moins de ressources que les requêtes qui extraient les mêmes données uniquement à partir des tables de base. Les vues matérialisées peuvent améliorer considérablement les performances des charges de travail caractérisées par des requêtes courantes et répétées.

Caractéristiques principales des vues matérialisées

Aucune maintenance nécessaire

Les vues matérialisées sont précalculées en arrière-plan lorsque les tables de base changent. Toutes les modifications incrémentielles apportées aux données depuis les tables de base sont automatiquement ajoutées aux vues matérialisées, sans aucune action de l’utilisateur.

Données actualisées

Les vues matérialisées renvoient des données actualisées. Si les modifications apportées aux tables de base peuvent invalider la vue matérialisée, les données sont lues directement à partir des tables de base. Si les modifications apportées aux tables de base n’invalident pas la vue matérialisée, les autres données sont lues à partir de la vue matérialisée, et seules les modifications sont lues à partir des tables de base.

Réglage intelligent

Si une partie d’une requête sur la table de base peut être résolue en interrogeant la vue matérialisée, BigQuery redirige la requête pour utiliser la vue matérialisée afin d’améliorer les performances et l’efficacité.

Cas d’utilisation

Les vues matérialisées peuvent optimiser les requêtes avec des coûts de calcul élevés et des résultats de petite taille. Les processus bénéficiant de vues matérialisées incluent les opérations de traitement analytique en ligne (OLAP) qui nécessitent un traitement important à l’aide de requêtes prévisibles et répétées, telles que celles provenant de processus d’extraction, de transformation et de chargement (ETL), ou de l’informatique décisionnelle.

Les cas d’utilisation suivants mettent en évidence la valeur des vues matérialisées :

Pré-agréger des données : Les vues matérialisées sont idéales pour l’agrégation de données en flux continu, permettant ainsi d’accélérer les rapports et les analyses sans recalculer les agrégations à chaque requête.

Préfiltrer les données : Elles permettent d’exécuter des requêtes qui ne lisent qu’un sous-ensemble particulier de la table, réduisant ainsi le volume de données scanné et améliorant les temps de réponse.

Pré-joindre des données : Les jointures de requêtes, en particulier entre les grandes et les petites tables, sont optimisées, réduisant les coûts de calcul et améliorant les performances des requêtes complexes.

Regrouper les données : Les vues matérialisées permettent d’exécuter des requêtes pouvant tirer parti d’un schéma de clustering différent des tables de base, optimisant ainsi les analyses et les rapports basés sur des regroupements spécifiques.

Exemple : Utilisation des vues matérialisées pour les rapports

Par exemple, si vous avez une requête complexe qui agrège les ventes par région et par mois, une vue matérialisée peut stocker ces résultats pré-calculés, ce qui permet d’accélérer les rapports mensuels. Vous pouvez également activer le rafraîchissement automatique pour mettre à jour les données lorsque les tables sous-jacentes changent. Cela est particulièrement utile pour les tableaux de bord qui doivent afficher des données agrégées en temps réel ou presque en temps réel.

Astuce : Configuration et rafraîchissement des vues matérialisées

Pour optimiser les performances, configurez les vues matérialisées pour qu’elles se rafraîchissent automatiquement à des intervalles réguliers ou dès que les données sous-jacentes changent. Cela garantit que les utilisateurs disposent toujours des données les plus récentes sans avoir à exécuter des requêtes coûteuses. Par exemple, pour une vue matérialisée sur les ventes hebdomadaires, configurez un rafraîchissement quotidien pour capturer les nouvelles ventes ajoutées.

Comparaison avec d’autres techniques BigQuery

Le tableau suivant récapitule les similitudes et les différences entre la mise en cache, les requêtes programmées, les vues logiques et les vues matérialisées de BigQuery.

Composant	Cache	Scheduled queries	Vues logiques	Vues matérialisées
Optimiser le calcul	Oui	Non	Non	Oui
Compatibilité des requêtes	tout	All	Tout	Limitée1
Partitionnement et filtrage par cluster	Non	Oui	ND	Oui
Actualisation incrémentielle	Non	Non	Non	Oui
Stockage supplémentaire	Non	Oui	Non	Oui
Réécriture de requêtes	Non	Non	Non	Oui
Frais de maintenance	Non	Oui	ND	Oui
Obsolescence des données	Never	Oui	Jamais	Facultatif 2

Fenêtre de fonctionnalité temporelle (time travel)

Prévention des défaillances

BigQuery prévoit une période de sécurité pour prévenir les pertes de données en cas de défaillance. Pendant cette période, les données supprimées sont automatiquement conservées pendant sept jours supplémentaires après la fenêtre de fonctionnalité temporelle. Cela permet aux utilisateurs de récupérer des données en cas d’urgence.

Conservation des données

Les données supprimées sont maintenues pendant une période de sécurité de sept jours. Cette période commence après la fin de la fonctionnalité temporelle, offrant une protection supplémentaire pour garantir que les données peuvent être récupérées si nécessaire. Cette mesure est essentielle pour prévenir les pertes de données accidentelles ou les erreurs de manipulation des données.

Récupération des données

Pour récupérer des données à partir de cette période de sécurité, les utilisateurs doivent contacter le support client de Google Cloud. Les données peuvent être récupérées au niveau de la table à partir du moment représenté par le code temporel de la suppression de cette table.

Étapes de récupération des données

1. Identifier les données à récupérer : Déterminer les tables et les périodes spécifiques pour lesquelles les données doivent être restaurées.
2. Contacter le support client : Soumettre une demande au support client de Google Cloud en fournissant les détails nécessaires.
3. Récupération par le support : Le support client procède à la récupération des données à partir des espaces de stockage sécurisés.

Restrictions de la période de sécurité

Il est important de noter que la période de sécurité n’est pas configurable. Cela signifie que les utilisateurs doivent être conscients de cette limite et planifier en conséquence pour éviter toute perte de données après cette période.

Une fois la période de sécurité de sept jours écoulée, même le support client de Google Cloud ne peut plus récupérer les données supprimées. Il est donc crucial de réagir rapidement en cas de suppression accidentelle de données et de contacter immédiatement le support si une récupération est nécessaire.

Meilleures pratiques

• Surveiller régulièrement les données : Mettre en place des systèmes de surveillance pour détecter rapidement toute suppression accidentelle de données.
• Maintenir des sauvegardes régulières : Créer des sauvegardes régulières des données critiques pour éviter toute perte en cas de dépassement de la période de sécurité.
• Former les équipes : Assurer que toutes les personnes impliquées dans la gestion des données comprennent les procédures de récupération et les limitations de la période de sécurité.

Exemple : Utilisation de la fenêtre de fonctionnalité temporelle

Par exemple, si un script de mise à jour a accidentellement modifié ou supprimé des enregistrements dans une table critique, vous pouvez utiliser la fonctionnalité de time travel pour récupérer l’état des données tel qu’il était avant l’exécution du script. Cela simplifie considérablement le processus de restauration et minimise les interruptions de service.

Instantané de table BigQuery (snapshots)

Le snapshot dans BigQuery permet de créer une copie en lecture seule d’une table à un moment spécifique. Les snapshots de table sont utiles pour l’analyse des données et les audits. Vous pouvez créer des snapshots des données actuelles ou des sept derniers jours.

Les snapshots sont parfaits pour conserver des versions historiques des données sur des périodes plus longues, au-delà de la capacité de la fenêtre de fonctionnalité temporelle (time travel). Ils sont particulièrement utiles pour les audits, la conformité réglementaire et les analyses historiques.

Un instantané de table BigQuery préserve le contenu d’une table (appelée table de base) à un moment donné. Vous pouvez enregistrer un instantané d’une table actuelle ou créer un instantané d’une table dans l’état où elle se trouvait à tout moment au cours des sept derniers jours. Un instantané de table peut présenter un délai d’expiration. Lorsque le délai configuré s’est écoulé depuis la création de l’instantané de la table, BigQuery supprime l’instantané. Vous pouvez interroger un instantané de table comme vous le feriez avec une table standard. Les instantanés de table sont en lecture seule. Toutefois, vous pouvez créer (restaurer) une table standard à partir d’un instantané de table, puis modifier la table restaurée.

Avantages des instantanés de table

Conserver un enregistrement pendant plus de sept jours

Avec la fonction temporelle de BigQuery, vous ne pouvez accéder aux données d’une table que sur les sept derniers jours. Les instantanés de table vous permettent de conserver les données d’une table à un moment précis aussi longtemps que vous le souhaitez.

Réduire les coûts de stockage

BigQuery ne stocke que les octets qui diffèrent entre un instantané et sa table de base. Par conséquent, un instantané de table utilise généralement moins d’espace de stockage qu’une copie complète de la table.

Clones de table

Si vous avez besoin de copies légères et modifiables de vos tables, envisagez d’utiliser des clones de table.

Contrôle des accès aux instantanés de table

Le contrôle des accès aux instantanés de table est semblable à celui des tables. Pour en savoir plus, consultez la page Contrôler l’accès aux ressources avec IAM.

Interroger des instantanés de table

Vous interrogez les données d’un instantané de table de la même manière que vous interrogez d’autres types de tables BigQuery. Pour en savoir plus, consultez la page Interroger des données BigQuery.

Coûts de stockage

Lorsqu’un instantané de table est créé, il n’y a initialement aucun coût de stockage pour cet instantané. Les coûts de stockage s’appliquent aux instantanés de table, mais BigQuery ne facture que les données d’un instantané de table qui ne sont pas déjà facturées dans une autre table :

Données ajoutées après la création de l’instantané

Si de nouvelles données sont ajoutées à la table de base après la création de l’instantané, vous ne payez pas le stockage de ces données dans l’instantané.

Données modifiées ou supprimées

Si des données existant également dans un instantané de table sont modifiées ou supprimées dans la table de base, les frais suivants s’appliquent :

• Le stockage des instantanés de table des données modifiées ou supprimées vous est facturé.
• Si la table de base est facturée en tant que stockage physique, les frais de fonctionnalité temporelle et de sécurité ne sont pas facturés sur la table de base. Lorsque l’instantané est supprimé, la fonctionnalité temporelle et la sécurité intégrée vous sont facturées.
• Si plusieurs instantanés contiennent les données modifiées ou supprimées, vous ne payez que l’espace de stockage utilisé par l’instantané le plus ancien.

Exemple : Utilisation des snapshots pour les audits

Par exemple, pour des besoins d’audit mensuel, créez un snapshot à la fin de chaque mois pour conserver une version des données de ce mois. Cela permet aux équipes d’audit de vérifier les transactions passées sans affecter les opérations courantes. De plus, les snapshots peuvent être utilisés pour restaurer les données à un état antérieur en cas de besoin.

Copier et cloner

BigQuery offre différentes façons de copier les données d’une table. La fonction COPY permet de créer une copie simple des données tandis que la fonction CLONE crée une copie optimisée en termes de stockage, légère et modifiable.

La copie et le clonage de tables sont essentiels pour les environnements de développement et de test, où les ingénieurs de données doivent travailler avec des ensembles de données représentatifs sans risquer de perturber les données de production.

Un clone de table est une copie en écriture légère d’une autre table (appelée table de base). Seulement le stockage des données qui diffèrent entre le clone de la table et la table de base vous est facturé. Initialement, vous ne payez donc pas de coût de stockage pour un clone de table. En dehors du modèle de facturation du stockage et de métadonnées supplémentaires pour la table de base, un clone de table est similaire à une table standard : vous pouvez l’interroger, en créer une copie, la supprimer, etc.

Cas d’utilisation courants des clones de table

Les clones de table offrent plusieurs avantages et peuvent être utilisés dans divers scénarios :

1. Développement et tests
   • Créez des copies des tables de production pour les utiliser dans les environnements de développement et de test. Cela permet de tester les modifications sans affecter les données de production.
2. Bacs à sable analytiques
   • Créez des bacs à sable pour permettre aux utilisateurs de générer leurs propres analyses et manipulations de données sans copier physiquement toutes les données de production. Seules les données modifiées sont facturées, ce qui optimise les coûts de stockage.

Indépendance du clone de table

Une fois le clone de table créé, il est indépendant de la table de base. Aucune modification apportée à la table de base ou au clone de table n’est reflétée dans l’autre. Si vous avez besoin de copies de vos tables en lecture légères, envisagez d’utiliser des instantanés de table.

Métadonnées des clones de table

Un clone de table possède les mêmes métadonnées qu’une table standard, ainsi que les éléments supplémentaires suivants :

• Projet, ensemble de données et nom de la table de base du clone de table
• Heure de l’opération du clone de table
  • Si la Fonction temporelle a été utilisée pour créer le clone de table, il s’agit de l’horodatage de la fonction temporelle.

Pour en savoir plus, consultez la section INFORMATION_SCHEMA.TABLES.

Opérations de clones des tables

En règle générale, les clones de table sont utilisés de la même manière que les tables standards, y compris pour les opérations suivantes :

Opérations standards

• Requête : Vous pouvez interroger les clones de table comme n’importe quelle autre table.
• Contrôle des accès : Appliquez des politiques de contrôle d’accès aux clones de table.
• Obtenir des métadonnées : Récupérez des métadonnées sur les clones de table.
• Partitionnement et filtrage par cluster : Utilisez des stratégies de partitionnement et de clustering pour optimiser les performances des requêtes.
• Utiliser des schémas : Appliquez et gérez les schémas des clones de table.
• Suppression : Supprimez les clones de table lorsque vous n’en avez plus besoin.

Création de clones de table

La création d’un clone de table diffère de celle d’une table standard. Pour en savoir plus, consultez la section Créer des clones de table.

Coûts de stockage

Les coûts de stockage s’appliquent aux clones de table, mais BigQuery ne facture que les données d’un clone de table qui ne sont pas déjà facturées dans une autre table :

• Création initiale
  • Lorsqu’un clone de table est créé, il n’y a initialement aucun coût de stockage pour ce clone de table.
• Ajout ou modification de données
  • Si des données sont ajoutées ou modifiées dans un clone de table, le stockage des données ajoutées ou mises à jour vous est facturé.
• Modification ou suppression de données dans la table de base
  • Si des données existant également dans le clone de table sont modifiées ou supprimées dans la table de base, le stockage des données modifiées ou supprimées dans le clone de table vous sera facturé. Si plusieurs clones contiennent les données modifiées ou supprimées, vous ne payez que l’espace de stockage utilisé par le clone le plus ancien.
• Ajout de données à la table de base après la création du clone
  • Si des données sont ajoutées à la table de base après la création du clone de table, le stockage de ces données dans le clone de table ne vous est pas facturé, mais il l’est dans la table de base.

Astuce : Environnements de développement et de test

Utilisez la fonction CLONE pour copier des données d’environnements de production vers des environnements de développement et de test, permettant aux développeurs de travailler avec des données réelles sans affecter la production. Par exemple, avant de déployer une nouvelle fonctionnalité, clonez les tables pertinentes pour tester la fonctionnalité avec des données réelles, garantissant ainsi que tout fonctionne comme prévu avant la mise en production.

Exemple : Comparaison des fonctions COPY et CLONE

Si vous avez besoin de copier une table de ventes pour un environnement de test, utilisez la fonction CLONE pour créer une version légère et modifiable de la table. Cela permet aux équipes de développement de tester des modifications sans impact sur la table originale. En revanche, si vous avez besoin d’une copie complète pour une analyse indépendante, utilisez la fonction COPY.

Mise en cache des résultats

BigQuery met en cache par défaut les résultats des requêtes exécutées pendant 24 heures. Cela est réalisé en conservant les résultats des requêtes dans une table anonyme qui expire automatiquement après 24 heures.

La mise en cache des résultats est particulièrement utile pour les requêtes qui sont exécutées fréquemment avec peu ou pas de modifications entre les exécutions. Elle permet de réduire le coût et le temps d’exécution des requêtes répétitives, améliorant ainsi l’efficacité des analyses de données.

Exemple : Réduire les coûts et le temps d’exécution

Activez la mise en cache pour les requêtes que vous exécutez fréquemment, ce qui permet de réduire le coût et le temps d’exécution des requêtes répétitives. Par exemple, si vous exécutez quotidiennement une requête pour générer un rapport de ventes, la mise en cache des résultats vous évitera de scanner les mêmes données à chaque fois, réduisant ainsi les coûts de traitement.

Astuce : Utilisation stratégique de la mise en cache

Pour optimiser l’utilisation de la mise en cache, identifiez les requêtes fréquemment exécutées et configurez-les pour qu’elles bénéficient de la mise en cache des résultats. Cela peut inclure des rapports hebdomadaires, des tableaux de bord ou des analyses récurrentes. En outre, surveillez les performances des requêtes pour ajuster les configurations de mise en cache en fonction des besoins.

Eviter le SELECT *

De nombreux ingénieurs de données utilisent la commande SELECT * pour analyser les données, mais cela entraîne une analyse de la totalité des données de la table, ce qui est coûteux.

BigQuery facture en fonction de la quantité de données analysées, il est donc essentiel de limiter les scans inutiles. En sélectionnant uniquement les colonnes nécessaires, vous pouvez réduire considérablement le coût des requêtes et améliorer les performances.

Exemple : Sélectionner uniquement les colonnes nécessaires

Pour optimiser les coûts et les performances, sélectionnez uniquement les colonnes nécessaires dans vos requêtes. Par exemple, au lieu d’utiliser SELECT * FROM ventes, spécifiez les colonnes dont vous avez besoin : SELECT date, produit, montant FROM ventes. BigQuery utilise un stockage en colonnes, ce qui signifie que seules les colonnes sélectionnées seront scannées, réduisant ainsi le volume de données traitées.

Astuce : Auditer et optimiser les requêtes existantes

Passez en revue vos requêtes existantes pour identifier les opportunités de remplacement de SELECT * par des sélections de colonnes spécifiques. Utilisez les outils de surveillance et d’audit de BigQuery pour analyser les requêtes coûteuses et ajuster les sélections de colonnes en conséquence.

Prévisualisation des données BigQuery

BigQuery permet de prévisualiser les données directement depuis l’interface utilisateur sans exécuter de requêtes. La prévisualisation est entièrement gratuite et permet de voir l’ensemble des données de la table.

Cette fonctionnalité est extrêmement utile pour valider rapidement l’importation de nouvelles données, vérifier la structure des tables et explorer le contenu des tables sans encourir de coûts de requête.

Exemple : Utilisation de la prévisualisation

Utilisez cette fonctionnalité pour vérifier rapidement les données nouvellement importées ou pour explorer la structure d’une table sans encourir de coûts de requête. Par exemple, après avoir importé des données de ventes, prévisualisez les premières lignes pour vous assurer que toutes les colonnes sont correctement importées et que les données sont complètes et exactes.

Astuce : Validation rapide des données

La prévisualisation est idéale pour effectuer des validations rapides lors de la phase de développement ou de test. Avant de lancer des requêtes complexes ou de configurer des pipelines de données, utilisez la prévisualisation pour vérifier que les données sont conformes aux attentes, ce qui permet de détecter et de corriger les erreurs à un stade précoce.

Historique des requêtes

BigQuery fournit un historique de toutes les requêtes exécutées par tous les utilisateurs et comptes de service dans le projet. Cet historique peut être consulté via l’interface de BigQuery sous les onglets Historique personnel et Historique de projet.

L’historique des requêtes est un outil puissant pour le suivi des performances, l’optimisation des requêtes et la gestion des coûts. Il permet aux équipes de données de comprendre les motifs d’utilisation et d’identifier les requêtes inefficaces ou coûteuses.

Astuce : Suivi des performances et débogage

Utilisez l’historique des requêtes pour suivre les performances des requêtes, identifier les échecs et optimiser les requêtes lentes. Par exemple, si une requête particulière prend beaucoup de temps à s’exécuter, l’historique des requêtes peut fournir des insights sur les étapes spécifiques qui ralentissent l’exécution. Les détails au niveau du projet peuvent aider à comprendre l’utilisation globale et à gérer les coûts.

Exemple : Gestion des accès et audit

L’historique des requêtes peut également être utilisé pour auditer l’accès aux données et gérer les autorisations. Par exemple, en analysant les requêtes exécutées par différents utilisateurs, vous pouvez identifier les accès non autorisés ou les utilisations inappropriées des ressources. Cela permet de renforcer la sécurité et d’assurer la conformité aux politiques de gouvernance des données.

Surveillance de BigQuery

Il est crucial de surveiller l’utilisation de BigQuery pour garantir une utilisation efficace et efficiente. Utilisez la console BigQuery ou l’API BigQuery pour surveiller votre utilisation.

La surveillance permet de suivre des métriques clés telles que le nombre de requêtes exécutées, la quantité de données traitées et les coûts associés. Elle aide également à identifier les goulots d’étranglement et à optimiser les performances des requêtes.

Astuce : Surveiller les métriques clés

Surveillez des métriques telles que le nombre de requêtes exécutées, la quantité de données traitées et le coût d’utilisation pour identifier les opportunités d’optimisation et de réduction des coûts. Par exemple, configurez des alertes pour détecter des pics inhabituels d’utilisation ou des coûts élevés, et enquêtez immédiatement pour comprendre et résoudre les problèmes sous-jacents.

Exemple : Utilisation des outils de surveillance

Utilisez les outils de surveillance intégrés de BigQuery pour visualiser les tendances d’utilisation et identifier les requêtes coûteuses. Par exemple, le tableau de bord BigQuery Monitoring peut afficher des graphiques détaillés des coûts et des performances, vous permettant de prendre des décisions informées pour optimiser les ressources et réduire les dépenses.

Détails d’exécution et graphe d’exécution

BigQuery fournit des détails d’exécution et des graphes d’exécution pour toutes les requêtes exécutées. Ces outils montrent les opérations et étapes effectuées, le temps pris par chaque étape et le nombre d’enregistrements traités.

Les détails d’exécution et les graphes d’exécution sont essentiels pour le débogage des requêtes et l’optimisation des performances. Ils offrent une vue granulaire des étapes de traitement, permettant d’identifier rapidement les goulots d’étranglement et d’optimiser les requêtes.

Astuce : Débogage des performances des requêtes

Utilisez ces informations pour identifier et résoudre les problèmes de performance des requêtes. Par exemple, si une étape particulière prend beaucoup de temps, vous pouvez optimiser cette partie de la requête. Analysez les graphes d’exécution pour visualiser les dépendances entre les étapes et ajuster les requêtes en conséquence pour améliorer l’efficacité.

Exemple : Analyse des étapes de traitement

Par exemple, si une requête complexe impliquant plusieurs jointures et agrégations est lente, utilisez les détails d’exécution pour identifier les étapes qui consomment le plus de temps. Si vous constatez que l’étape de jointure prend beaucoup de temps, envisagez de réindexer les colonnes de jointure ou de revoir la stratégie de partitionnement pour améliorer les performances.

Chiffrement des données

BigQuery chiffre automatiquement les données au repos avec des clés de chiffrement gérées. Il permet également aux ingénieurs de données de fournir des clés de chiffrement personnalisées.

Le chiffrement des données est crucial pour garantir la sécurité des informations sensibles et la conformité aux réglementations en matière de protection des données. BigQuery offre des options flexibles pour le chiffrement, permettant aux organisations de renforcer la sécurité selon leurs besoins spécifiques.

Astuce : Sécuriser les données sensibles

Utilisez des clés de chiffrement personnalisées pour les données sensibles afin de renforcer la sécurité et de répondre aux exigences de conformité spécifiques. Par exemple, pour des données médicales ou financières, configurez des clés de chiffrement spécifiques à chaque projet ou table pour assurer une protection maximale.

Exemple : Implémentation du chiffrement personnalisé

Si votre organisation gère des données sensibles telles que des informations de santé, configurez des clés de chiffrement gérées par le client (CMEK) pour ces tables. Cela vous permet de contrôler et de gérer directement les clés de chiffrement, garantissant que seules les personnes autorisées peuvent accéder ou déchiffrer les données.

Conclusion

J’espère que cet article vous a été utile. Si vous avez des questions ou des commentaires, n’hésitez pas à les laisser ci-dessous. Si besoin de mon expertise Data Marketing, visitez mon site qui présente mes prestations. Faites-moi également part des autres meilleures pratiques que vous utilisez avec BigQuery. Ces pratiques visent à améliorer l’efficacité, la sécurité et les performances de vos pipelines de données, contribuant ainsi à une gestion des données plus robuste et plus optimisée.