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. Grâce à BigQuery, vous pouvez interroger plusieurs téraoctets de données sans avoir à gérer d'infrastructure ni faire appel à un administrateur de base de données. Basé sur le langage SQL que vous connaissez déjà et le 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
• Charger un ensemble de données réelles dans BigQuery
• Écrire une requête pour obtenir des insights sur un grand ensemble de données

Prérequis

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

Enquête

Comment allez-vous utiliser ce tutoriel ?

Je vais le lire uniquement Je vais le lire et effectuer les exercices

Quel est votre niveau d'expérience avec Google Cloud ?

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 son ID.

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 supprimer les limites du bac à sable ultérieurement, vous pouvez ajouter un compte de facturation en vous inscrivant à l'essai sans frais de Google Cloud.

Dans la section suivante, vous allez charger le jeu de données Wikipédia.

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 "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 s'affiche en dessous de la ligne de flottaison, décrivant de quoi il s'agit. Si tel est le cas, cliquez sur Continuer. Cet écran ne s'affiche qu'une seule fois. 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 plus de commodité, certaines données du 10 avril 2019 de l'ensemble de données sur les pages vues de 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 sur la ligne de commande : "gcloud config list", puis recherchez la valeur de "project".

De plus, lorsque vous exécutez bq pour la première fois, il peut vous être demandé de sélectionner un projet par défaut. Une fois la sélection effectuée, vous pouvez réexécuter 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 de 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 les champs.
• Définissez --allow_jagged_rows pour inclure les lignes avec 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 afficher le schéma de la table dans l'onglet Schéma. 4. Pour connaître la quantité de données contenues dans la table, accédez à l'onglet Détails :

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

6. Rédiger une requête

1. Cliquez sur Saisir une nouvelle requête :

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

2. Pour trouver le nombre total de vues Wikimedia entre 14h et 15h le 10 avril 2019, écrivez la requête suivante :

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Cliquez sur Exécuter :

Après quelques secondes, le résultat s'affiche en bas de l'écran. Il 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 davantage la quantité de données traitées.

Vous pouvez traiter/interroger jusqu'à 1 To de données sans frais chaque mois. Pour en savoir plus, consultez la page Tarifs de BigQuery.

7. Requêtes plus avancées

Trouver les pages vues Wikipédia

L'ensemble de données Wikimedia contient les pages vues pour tous les projets Wikimedia (y compris Wikipédia, Wiktionnaire, Wikibooks et Wikiquote). Limitez la requête aux pages Wikipédia en anglais en ajoutant 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 et un certain nombre de fonctions d'agrégation. De plus, vous pouvez également utiliser des expressions régulières pour interroger les champs de texte. Essayez-en une :

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.

Interroger plusieurs tables

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

1. Commencez par créer une deuxième table à interroger en chargeant les vues de page de 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" comme 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 pseudo-colonne _TABLE_SUFFIX. Cette requête limite les résultats 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 les tables qu'il contient.

bq rm -r lab

9. Félicitations !

Vous avez utilisé BigQuery et SQL pour interroger l'ensemble de données réelles sur les vues de pages 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 d'autres utilisations actuelles de BigQuery.
• Trouvez les ensembles de données publics disponibles dans BigQuery.
• Découvrez comment charger des données dans BigQuery.