Interroger l'ensemble de données Wikipédia dans BigQuery

1. Présentation

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger plusieurs téraoctets de données sans avoir à gérer d'infrastructure, et vous n'avez pas besoin d'un administrateur de base de données. Basé sur le langage SQL qui vous est familier, BigQuery peut bénéficier d'un modèle de paiement à l'usage. BigQuery vous permet de vous concentrer sur l'analyse des données pour en dégager des informations pertinentes.

Dans cet atelier de programmation, vous allez explorer l'ensemble de données Wikipédia à l'aide de BigQuery.

Points abordés

• Utiliser BigQuery
• Comment charger un ensemble de données réel dans BigQuery
• Écrire une requête pour obtenir des informations sur un ensemble de données volumineux

Prérequis

• Un projet Google Cloud
• Un navigateur tel que Google Chrome ou Firefox

Enquête

Comment allez-vous utiliser ce tutoriel ?

<ph type="x-smartling-placeholder"></ph> Lecture seule Je vais le lire et effectuer les exercices

Comment évalueriez-vous votre expérience avec Google Cloud ?

<ph type="x-smartling-placeholder"></ph> Débutant Intermédiaire Expert .

2. Préparation

Activer BigQuery

Si vous ne possédez pas encore de compte Google, vous devez en créer un.

1. Connectez-vous à la console Google Cloud et accédez à BigQuery. Vous pouvez également ouvrir l'interface utilisateur Web de BigQuery directement en saisissant l'URL suivante dans votre navigateur.

https://console.cloud.google.com/bigquery

2. Acceptez les conditions d'utilisation.
3. Avant de pouvoir utiliser BigQuery, vous devez créer un projet. Suivez les instructions pour créer votre projet.

Choisissez un nom de projet et notez l'ID du projet.

L'ID du projet est un nom unique parmi tous les projets Google Cloud. Il sera désigné par le nom PROJECT_ID tout au long de cet atelier de programmation.

Cet atelier de programmation utilise des ressources BigQuery dans les limites du bac à sable BigQuery. Aucun compte de facturation n'est requis. Si vous souhaitez par la suite supprimer les limites du bac à sable, vous pouvez ajouter un compte de facturation en vous inscrivant à l'essai sans frais de Google Cloud.

Vous allez charger le jeu de données Wikipédia dans la section suivante.

3. Créer un ensemble de données

Commencez par créer un ensemble de données dans le projet. Un ensemble de données est composé de plusieurs tables.

1. Pour créer un ensemble de données, cliquez sur le nom du projet dans le volet des ressources, puis sur Créer un ensemble de données:

2. Saisissez lab comme ID de l'ensemble de données:

3. Cliquez sur Créer un ensemble de données pour créer un ensemble de données vide.

4. Charger des données avec le programme de ligne de commande bq

Activer Cloud Shell

1. Dans Cloud Console, cliquez sur Activer Cloud Shell .

Si vous n'avez jamais démarré Cloud Shell auparavant, un écran intermédiaire (en dessous de la ligne de flottaison) vous explique de quoi il s'agit. Dans ce cas, cliquez sur Continuer (elle ne s'affichera plus). Voici à quoi il ressemble :

Le provisionnement et la connexion à Cloud Shell ne devraient pas prendre plus de quelques minutes.

Cette machine virtuelle contient tous les outils de développement dont vous avez besoin. Elle comprend un répertoire d'accueil persistant de 5 Go et s'exécute sur Google Cloud, ce qui améliore nettement les performances du réseau et l'authentification. Vous pouvez réaliser une grande partie, voire la totalité, des activités de cet atelier dans un simple navigateur ou sur votre Chromebook.

Une fois connecté à Cloud Shell, vous êtes en principe authentifié et le projet est défini avec votre ID de projet.

2. Exécutez la commande suivante dans Cloud Shell pour vérifier que vous êtes authentifié :

gcloud auth list

Résultat de la commande

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Remarque : gcloud est l'outil de ligne de commande performant et unifié de Google Cloud. Il est préinstallé dans Cloud Shell. Il est également compatible avec la saisie semi-automatique via la touche de tabulation. Pour en savoir plus, consultez la section de présentation de l'outil de ligne de commande gcloud.

3. Exécutez la commande suivante dans Cloud Shell pour vérifier que la commande gcloud connaît votre projet:

gcloud config list project

Résultat de la commande

[core]
project = <PROJECT_ID>

Si vous obtenez un résultat différent, exécutez cette commande :

gcloud config set project <PROJECT_ID>

Résultat de la commande

Updated property [core/project].

Charger des données dans BigQuery

Pour vous simplifier les choses, certaines des données du 10 avril 2019 issues de l'ensemble de données sur les pages vues Wikimedia sont disponibles sur Google Cloud Storage à l'adresse gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. Le fichier de données est un fichier CSV compressé avec GZip. Vous pouvez charger ce fichier directement à l'aide de l'utilitaire de ligne de commande bq. Dans la commande de chargement, vous décrivez également le schéma du fichier.

Vous pouvez trouver l'ID du projet dans la console API ou à partir de la ligne de commande : "gcloud config list". et recherchez la valeur de "project".

Par ailleurs, lorsque vous exécutez bq pour la première fois, vous pouvez être invité à sélectionner un projet par défaut. Une fois la sélection effectuée, vous pouvez exécuter à nouveau la commande ci-dessus pour charger les données.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

Le chargement des données peut prendre quelques minutes. Attendez la fin de l'opération.

Vous avez utilisé quelques options avancées pour charger le fichier des pages vues:

• Définissez --source_format CSV pour indiquer que le fichier doit être analysé en tant que fichier CSV. Cette étape est facultative, car le format CSV est le format par défaut.
• Définissez --field_delimiter " " pour indiquer qu'un seul espace est utilisé pour délimiter des champs.
• Définissez --allow_jagged_rows pour inclure les lignes comportant le moins de colonnes et ignorer les erreurs lors du chargement du fichier CSV.
• Définissez --quote "" pour indiquer que les chaînes ne sont pas entre guillemets.
• Définissez --max_bad_records 3 pour ignorer au maximum trois erreurs lors de l'analyse du fichier CSV.

Pour en savoir plus sur la ligne de commande bq, consultez la documentation.

5. Prévisualiser l'ensemble de données

Dans la console BigQuery, ouvrez l'une des tables que vous venez de charger.

1. Développez le projet.
2. Développez l'ensemble de données.
3. Sélectionnez le tableau.

Vous pouvez voir le schéma de la table dans l'onglet Schéma. 4. Pour connaître la quantité de données présentes dans le tableau, accédez à l'onglet Détails:

5. Ouvrez l'onglet Aperçu pour afficher une sélection de lignes de la table.

6. Saisir une requête

1. Cliquez sur Saisir une nouvelle requête:

L'éditeur de requête s'affiche:

2. Trouvez le nombre total de vues Wikimedia entre 14h et 15h le 10 avril 2019 en écrivant la requête suivante:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Cliquez sur Run (Exécuter) :

En quelques secondes, le résultat s'affiche en bas de l'écran et vous indique également la quantité de données traitées:

Cette requête a traité 123,9 Mo, alors que la table fait 691,4 Mo. BigQuery ne traite que les octets des colonnes utilisées dans la requête. La quantité totale de données traitées peut donc être nettement inférieure à la taille de la table. Le clustering et le partitionnement permettent de réduire encore plus la quantité de données traitées.

Vous pouvez traiter/interroger jusqu'à 1 To de données sans frais chaque mois. Consultez la page des tarifs de BigQuery pour en savoir plus.

7. Requêtes plus avancées

Trouver des pages vues Wikipédia

L'ensemble de données Wikimedia contient des pages vues pour tous les projets Wikimedia (y compris Wikipédia, Wiktionary, Wikibooks et Wikicitations). Pour limiter la requête aux pages Wikipédia en anglais, ajoutez une instruction WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Notez qu'en interrogeant une colonne supplémentaire, wiki, la quantité de données traitées est passée de 124 Mo à 204 Mo.

BigQuery est compatible avec de nombreuses clauses SQL courantes, telles que CONTAINS, GROUP BY, ORDER BY, ainsi qu'un certain nombre de fonctions d'agrégation. Vous pouvez également utiliser des expressions régulières pour interroger des champs de texte. Essayez-en un:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Pour en savoir plus sur la syntaxe BigQuery, consultez la documentation de référence sur les requêtes BigQuery.

Exécuter des requêtes dans plusieurs tables

Vous pouvez sélectionner une plage de tables pour former l'union à l'aide d'une table générique.

1. Commencez par créer une deuxième table à interroger en chargeant les pages vues pour l'heure suivante dans une nouvelle table:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. Dans l'éditeur de requête, interrogez les deux tables que vous avez chargées en interrogeant les tables avec "pageviews_2019". en tant que préfixe:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Vous pouvez filtrer les tables de manière plus sélective avec la colonne pseudo _TABLE_SUFFIX. Cette requête se limite aux tables correspondant au 10 avril.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Nettoyer

Vous pouvez également supprimer l'ensemble de données que vous avez créé à l'aide de la commande bq rm. Utilisez l'option -r pour supprimer toutes les tables qu'elle contient.

bq rm -r lab

9. Félicitations !

Vous avez utilisé BigQuery et SQL pour interroger l'ensemble de données réel des pages vues sur Wikipédia. Vous pouvez interroger des ensembles de données à l'échelle du pétaoctet.

En savoir plus

• Consultez le subreddit BigQuery pour découvrir comment d'autres entreprises utilisent BigQuery aujourd'hui.
• Découvrez les ensembles de données publics disponibles dans BigQuery.
• Découvrez comment charger des données dans BigQuery.