Partitionnement et clustering dans BigQuery

1. Introduction

BigQuery est un entrepôt d'analyse de données d'entreprise économique et entièrement géré, à l'échelle du pétaoctet et à faible coût. BigQuery fonctionne sans serveur. Vous n'avez pas besoin de configurer ni de gérer les clusters.

Un ensemble de données BigQuery se trouve dans un projet GCP et contient une ou plusieurs tables. Vous pouvez interroger ces ensembles de données avec SQL.

Dans cet atelier de programmation, vous allez utiliser l'interface utilisateur Web de BigQuery dans la console GCP pour comprendre le partitionnement et le clustering dans BigQuery. Le partitionnement et le clustering des tables de BigQuery aident à structurer vos données pour qu'elles correspondent aux modèles d'accès aux données courants. Le partitionnement et le clustering sont essentiels pour maximiser les performances et les coûts de BigQuery lors de l'interrogation d'une plage de données spécifique. Cela permet d'analyser moins de données par requête, et l'élagage est déterminé avant l'heure de début de la requête.

Remarque:En plus de l'interface utilisateur Web de BigQuery, vous pouvez effectuer des opérations sur des ensembles de données BigQuery à l'aide de l'outil de ligne de commande bq. Pour en savoir plus sur l'outil de ligne de commande bq, consultez la page Outil de ligne de commande bq.

Pour en savoir plus sur BigQuery, consultez la documentation BigQuery.

Points abordés

• Créer et interroger des tables partitionnées et en cluster
• Comparer les performances des requêtes avec des tables partitionnées et en cluster

Prérequis

Pour réaliser cet atelier :

• La dernière version de Google Chrome
• Un compte de facturation Google Cloud Platform

2. Configuration

Pour utiliser BigQuery, vous devez créer un projet GCP ou sélectionner un projet existant.

Créer un projet

Pour créer un projet :

1. Si vous ne possédez pas encore de compte Google (Gmail ou Google Apps), créez-en un.
2. Connectez-vous à la console Google Cloud Platform ( console.cloud.google.com) et créez un projet.
3. Si vous n'avez aucun projet, cliquez sur le bouton "Créer un projet" :

Sinon, créez un projet à partir du menu de sélection de projet:

4. Saisissez un nom de projet, puis sélectionnez Créer. Notez que l'ID du projet est un nom unique parmi tous les projets Google Cloud.

3. Travailler avec des jeux de données publics

BigQuery vous permet de travailler avec des ensembles de données publics, y compris BBC News, les dépôts GitHub, Stack Overflow et l'Agence américaine d'observation océanique et atmosphérique (NOAA, National Oceanic and Atmospheric Administration). Vous n'avez pas besoin de charger ces ensembles de données dans BigQuery. Il vous suffit d'ouvrir les ensembles de données pour les parcourir et les interroger dans BigQuery. Dans cet atelier de programmation, vous allez utiliser l'ensemble de données public Stack Overflow.

Parcourir l'ensemble de données Stack Overflow

Le jeu de données Stack Overflow contient des informations sur les posts, les tags, les badges, les commentaires, les utilisateurs et plus encore. Pour parcourir l'ensemble de données Stack Overflow dans l'interface utilisateur Web de BigQuery, procédez comme suit:

1. Ouvrez l'ensemble de données Stack Overflow. L'interface utilisateur Web de BigQuery s'ouvre dans la console GCP et affiche des informations sur l'ensemble de données Stack Overflow.
2. Dans le panneau de navigation , sélectionnez bigquery-public-data. Le menu se développe pour répertorier les jeux de données publics. Chaque ensemble de données comprend une ou plusieurs tables.
3. Faites défiler la page vers le bas et sélectionnez stackoverflow. Le menu se développe pour répertorier les tables du jeu de données Stack Overflow.
4. Sélectionnez badges pour afficher le schéma de la table des badges. Notez les noms des champs dans le tableau.
5. Au-dessus des noms des champs, cliquez sur Aperçu pour afficher des exemples de données pour le tableau des badges.

Pour en savoir plus sur tous les ensembles de données publics disponibles dans BigQuery, consultez la page Ensembles de données publics Google BigQuery.

Interroger l'ensemble de données Stack Overflow

Parcourir un jeu de données est un bon moyen de comprendre les données avec lesquelles vous travaillez, mais l'interrogation d'ensembles de données est le point fort de BigQuery. Cette section vous explique comment exécuter des requêtes BigQuery. Vous n'avez pas besoin de connaître SQL à ce stade. Vous pouvez copier et coller les requêtes ci-dessous.

Pour exécuter une requête, procédez comme suit:

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.
2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante. BigQuery valide la requête et l'interface utilisateur Web affiche une coche verte sous la zone de texte pour indiquer que la syntaxe est valide.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Sélectionnez Exécuter. La requête renvoie le nombre de posts ou de questions Stack Overflow postés chaque année.

Remarque : Cette requête utilise le nom complet de la table posts_questions, y compris le nom du projet, l'ensemble de données et le nom de la table (bigquery-public-data.stackoverflow.posts_questions).

4. Créer une table

Dans la section précédente, vous avez interrogé des ensembles de données publics que BigQuery met à votre disposition. Dans cette section, vous allez créer une table dans BigQuery à partir d'une table existante. Vous allez créer une table avec des données échantillonnées de la table posts_questions de l'ensemble de données public Stack Overflow, puis l'interroger.

Créer un ensemble de données

Pour créer et charger des données d'une table dans BigQuery, commencez par créer un ensemble de données BigQuery qui contiendra les données en procédant comme suit:

1. Dans le panneau de navigation de la console GCP, sélectionnez le nom du projet créé lors de la configuration.
2. À droite, dans le panneau des détails, sélectionnez Créer un ensemble de données.

3. Dans la boîte de dialogue Créer un ensemble de données, saisissez stackoverflow dans le champ ID de l'ensemble de données. Ne modifiez aucun autre paramètre par défaut et cliquez sur OK.

Créer une table avec les posts StackOverflow 2018

Maintenant que vous avez créé un ensemble de données BigQuery, vous pouvez créer une table dans BigQuery. Pour créer une table avec des données d'une table existante, vous allez interroger le jeu de données Stack Overflow Posts 2018 et écrire les résultats dans une nouvelle table, en procédant comme suit:

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.

2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante pour créer une table, qui est une instruction LDD.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Sélectionnez Exécuter. La requête crée une table questions_2018 dans l'ensemble de données stackoverflow de votre projet avec les données résultant de l'exécution d'une requête sur l'ensemble de données BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Remarque:Cette étape prend environ deux minutes pour créer une table. Veuillez patienter avant de passer à l'étape suivante.

Interroger la nouvelle table avec les posts Stack Overflow 2018

Maintenant que vous avez créé une table BigQuery, exécutez une requête pour renvoyer des posts Stack Overflow avec des questions et des titres, ainsi que quelques autres statistiques telles que le nombre de réponses, de commentaires, de vues et de favoris. Procédez comme suit :

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.
2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Sélectionnez Exécuter. La requête renvoie les questions Stack Overflow créées en janvier 2018 et portant le tag android, ainsi que la question et quelques autres statistiques.
4. Par défaut, BigQuery met en cache les résultats de la requête. Exécutez la même requête. BigQuery a pris beaucoup moins de temps pour renvoyer les résultats, car il renvoie les résultats à partir du cache.
5. Exécutez à nouveau la même requête, mais cette fois en désactivant la mise en cache BigQuery. Nous désactiverons le cache pour le reste de l'atelier pour comparer équitablement les performances avec les tables partitionnées et en cluster. Nous exécuterons ce cache dans les sections suivantes. Dans l'éditeur de requête, cliquez sur Plus et sélectionnez Paramètres de requête.
6. Sous Préférence en matière de cache, décochez la case Utiliser les résultats mis en cache.
7. Dans les résultats de la requête, vous devriez voir le temps nécessaire pour exécuter la requête et le volume de données traitées pour obtenir les résultats.

5. Créer et interroger une table partitionnée

Dans la section précédente, vous avez créé une table dans BigQuery avec des données de la table posts_questions à l'aide de l'ensemble de données public Stack Overflow. Nous avons interrogé cet ensemble de données en désactivant la mise en cache et observé les performances de la requête. Dans cette section, vous allez créer une table partitionnée à partir de la table posts_questions de l'ensemble de données public Stack Overflow et observer les performances des requêtes.

Une table partitionnée est une table spéciale divisée en segments, appelés partitions, qui permettent de gérer et d'interroger facilement les données. Vous pouvez généralement diviser les grandes tables en partitions plus petites en utilisant la date d'ingestion des données, la colonne TIMESTAMP/DATE ou une colonne INTEGER. Nous allons créer une table partitionnée DATE.

Pour en savoir plus sur les tables partitionnées, cliquez ici.

Créer une table partitionnée avec les posts StackOverflow 2018

Pour créer une table partitionnée avec des données d'une table ou d'une requête existante, vous allez interroger l'ensemble de données Stack Overflow 2018 et écrire les résultats dans une nouvelle table. Pour ce faire, procédez comme suit:

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.

2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante pour créer une table, qui est une instruction LDD.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Sélectionnez Exécuter. La requête crée une table questions_2018_partitioned dans l'ensemble de données stackoverflow de votre projet avec les données résultant de l'exécution d'une requête sur l'ensemble de données BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Remarque:Cette étape prend environ deux minutes pour créer une table. Veuillez patienter avant de passer à l'étape suivante.

Interroger la table partitionnée avec les posts Stack Overflow de 2018

Maintenant que vous avez créé une table partitionnée BigQuery, exécutons la même requête, cette fois sur la table partitionnée, pour renvoyer des posts Stack Overflow avec des questions et des titres, ainsi que quelques autres statistiques telles que le nombre de réponses, de commentaires, de vues et de favoris. Procédez comme suit :

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.
2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Sélectionnez Exécuter avec la mise en cache BigQuery désactivée (consultez la section précédente pour désactiver le cache BigQuery). La requête renvoie les questions Stack Overflow créées en janvier 2018 et portant le tag android, ainsi que la question et quelques autres statistiques.
4. Dans les résultats de la requête, vous devriez voir le temps nécessaire pour exécuter la requête et le volume de données traitées pour obtenir les résultats.

Vous devriez constater que les performances de la requête avec la table partitionnée sont meilleures que celles de la table non partitionnée, car BigQuery élimine les partitions, c'est-à-dire qu'elle n'analyse que les partitions requises qui traitent moins de données et s'exécutent plus rapidement. Cela permet d'optimiser les coûts et les performances des requêtes.

6. Créer et interroger une table en cluster

Dans la section précédente, vous avez créé une table partitionnée dans BigQuery avec les données de la table posts_questions de l'ensemble de données public Stack Overflow. Nous avons interrogé cette table en désactivant la mise en cache et observé les performances des requêtes avec les tables partitionnées et non partitionnées. Dans cette section, vous allez créer une table en cluster à partir de la table posts_questions de l'ensemble de données public Stack Overflow et observer les performances des requêtes.

Lorsqu'une table est mise en cluster dans BigQuery, les données de la table sont automatiquement organisées en fonction du contenu d'une ou de plusieurs colonnes de son schéma. Les colonnes que vous spécifiez servent à colocaliser les données associées. Lorsque des données sont écrites dans une table en cluster, BigQuery trie les données à l'aide des valeurs des colonnes de clustering. Ces valeurs permettent d'organiser les données en plusieurs blocs dans le stockage BigQuery. L'ordre des colonnes en cluster détermine l'ordre de tri des données. Lorsque de nouvelles données sont ajoutées à une table ou à une partition spécifique, BigQuery effectue un reclustering automatique en arrière-plan pour restaurer la propriété de tri de la table ou de la partition.

Pour en savoir plus sur l'utilisation de tables en cluster, cliquez ici.

Créer une table en cluster avec les posts Stack Overflow 2018

Dans cette section, vous allez créer une table partitionnée sur creation_date et mise en cluster sur la colonne tags en fonction du schéma d'accès aux requêtes. Pour créer une table en cluster avec des données d'une table ou d'une requête existante, vous allez interroger la table Stack Overflow 2018 et écrire les résultats dans une nouvelle table en procédant comme suit:

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.

2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante pour créer une table, qui est une instruction LDD.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Sélectionnez "Exécuter". La requête crée une table questions_2018_clustered dans l'ensemble de données stackoverflow de votre projet avec les données résultant de l'exécution d'une requête sur la table BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. La nouvelle table est partitionnée en fonction de la date de création et est mise en cluster dans la colonne des tags.

Remarque:Cette étape prend environ deux minutes pour créer une table. Veuillez patienter avant de passer à l'étape suivante.

Interroger la table en cluster avec les posts Stack Overflow de 2018

Maintenant que vous avez créé une table en cluster BigQuery, exécutons à nouveau la même requête, cette fois sur la table partitionnée et en cluster, pour renvoyer des posts Stack Overflow avec des questions et des titres, ainsi que quelques autres statistiques telles que le nombre de réponses, de commentaires, de vues et de favoris. Procédez comme suit :

1. En haut à droite de la console GCP, sélectionnez Saisir une nouvelle requête.
2. Dans la zone de texte de l'éditeur de requête, copiez et collez la requête SQL suivante.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Sélectionnez Exécuter avec la mise en cache BigQuery désactivée (consultez la section précédente pour désactiver le cache BigQuery). La requête renvoie les questions Stack Overflow créées en janvier 2018 et portant le tag android, ainsi que la question et quelques autres statistiques.
4. Dans les résultats de la requête, vous devriez voir le temps nécessaire pour exécuter la requête et le volume de données traitées pour obtenir les résultats.

Avec une table partitionnée et en cluster, la requête a analysé moins de données qu'une table partitionnée ou une table non partitionnée. La façon dont les données sont organisées (partitionnement et clustering) minimise la quantité de données analysées par les nœuds de calcul d'emplacements, améliorant ainsi les performances des requêtes et optimisant les coûts.

7. Nettoyer

À moins que vous ne prévoyiez de continuer à utiliser votre ensemble de données Stack Overflow, vous devez le supprimer et supprimer le projet que vous avez créé pour cet atelier de programmation.

Supprimer l'ensemble de données BigQuery

Pour supprimer l'ensemble de données BigQuery, procédez comme suit :

1. Sélectionnez l'ensemble de données stackoverflow dans le panneau de navigation de gauche de BigQuery .
2. Dans le panneau des détails, sélectionnez Supprimer l'ensemble de données.
3. Dans la boîte de dialogue Supprimer l'ensemble de données, saisissez stackoverflow et sélectionnez Supprimer pour confirmer la suppression de l'ensemble de données.

Supprimer le projet

Pour supprimer le projet GCP que vous avez créé pour cet atelier de programmation, procédez comme suit:

1. Dans le menu de navigation de GCP, sélectionnez IAM et administration.
2. Dans le panneau de navigation, sélectionnez Paramètres.
3. Dans le panneau de détails, vérifiez que votre projet actuel est bien celui que vous avez créé pour cet atelier de programmation, puis sélectionnez Arrêter.
4. Dans la boîte de dialogue Arrêter le projet, saisissez l'ID de votre projet (et non son nom), puis sélectionnez Arrêter pour confirmer.

Félicitations ! Vous savez maintenant

• Utiliser l'interface utilisateur Web de BigQuery pour créer une table à partir de tables existantes
• Créer et interroger des tables partitionnées et en cluster
• Comment le partitionnement et le clustering optimisent les performances et les coûts des requêtes

Notez que vous n'avez pas eu à configurer ni à gérer les clusters pour utiliser des ensembles de données.