Utiliser BigQuery avec Python

1. Présentation

BigQuery correspond à l'entrepôt de données d'analyse de Google, entièrement géré à l'échelle du pétaoctet et à faible coût. BigQuery est une solution NoOps : vous n'avez aucune infrastructure à gérer et vous n'avez pas besoin d'un administrateur de base de données. Vous pouvez donc vous concentrer sur l'analyse des données pour dégager des insights pertinents, utiliser le langage SQL qui vous est familier et profiter de notre modèle de paiement à l'usage.

Dans cet atelier de programmation, vous allez interroger des ensembles de données publics BigQuery avec Python à l'aide des bibliothèques clientes Google Cloud pour Python.

Points abordés

• Utiliser Cloud Shell
• Activer l'API BigQuery
• Authentifier les requêtes API
• Installer la bibliothèque cliente Python
• Interroger les œuvres de Shakespeare
• Interroger l'ensemble de données GitHub
• Ajuster les statistiques de mise en cache et d'affichage

Prérequis

• Un projet Google Cloud
• Un navigateur tel que Chrome ou Firefox
• Connaissances de base sur Python

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 Python ?

Débutant Intermédiaire Expert

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

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

2. Préparation

Configuration de l'environnement au rythme de chacun

1. Connectez-vous à la console Google Cloud, puis créez un projet ou réutilisez un projet existant. (Si vous ne possédez pas encore de compte Gmail ou Google Workspace, vous devez en créer un.)

• Le nom du projet est le nom à afficher pour les participants au projet. Il s'agit d'une chaîne de caractères qui n'est pas utilisée par les API Google, et que vous pouvez modifier à tout moment.
• L'ID du projet doit être unique sur l'ensemble des projets Google Cloud et doit être immuable (vous ne pouvez pas le modifier une fois que vous l'avez défini). Cloud Console génère automatiquement une chaîne unique dont la composition importe peu, en général. Dans la plupart des ateliers de programmation, vous devrez référencer l'ID du projet (généralement identifié comme PROJECT_ID), donc s'il ne vous convient pas, générez-en un autre au hasard ou définissez le vôtre, puis vérifiez s'il est disponible. Il est ensuite "gelé" une fois le projet créé.
• La troisième valeur est le numéro de projet, utilisé par certaines API. Pour en savoir plus sur ces trois valeurs, consultez la documentation.

Attention : Un ID de projet doit être unique au niveau mondial. Personne d'autre ne peut l'employer une fois que vous l'avez sélectionné. Vous en êtes le seul utilisateur. Si un projet est supprimé, cet ID ne pourra plus jamais être utilisé.

Remarque : Si vous utilisez un compte Gmail, vous pouvez conserver l'emplacement par défaut défini sur Aucune organisation. Si vous utilisez un compte Google Workspace, choisissez un emplacement pertinent pour votre organisation.

2. Vous devez ensuite activer la facturation dans Cloud Console afin d'utiliser les ressources/API Cloud. L'exécution de cet atelier de programmation est très peu coûteuse, voire sans frais. Pour arrêter les ressources afin d'éviter qu'elles ne vous soient facturées après ce tutoriel, suivez les instructions de nettoyage indiquées à la fin de l'atelier. Les nouveaux utilisateurs de Google Cloud peuvent participer au programme d'essai gratuit pour bénéficier d'un crédit de 300 $.

Démarrer Cloud Shell

Bien que Google Cloud puisse être utilisé à distance depuis votre ordinateur portable, nous allons nous servir de Google Cloud Shell pour cet atelier de programmation, un environnement de ligne de commande exécuté dans le cloud.

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].

3. Activer l'API

L'API BigQuery doit être activée par défaut dans tous les projets Google Cloud. Pour vérifier si c'est le cas, exécutez la commande suivante dans Cloud Shell: BigQuery devrait apparaître:

gcloud services list

Vous devriez voir BigQuery dans la liste:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

Si l'API BigQuery n'est pas activée, vous pouvez utiliser la commande suivante dans Cloud Shell pour l'activer:

gcloud services enable bigquery.googleapis.com

Remarque: En cas d'erreur, revenez à l'étape précédente et vérifiez votre configuration.

4. Authentifier les requêtes API

Pour envoyer des requêtes à l'API BigQuery, vous devez utiliser un compte de service. Le compte de service appartient à votre projet. Il permet à la bibliothèque cliente Python Google Cloud d'envoyer des requêtes à l'API BigQuery. Comme tout autre compte utilisateur, un compte de service est représenté par une adresse e-mail. Dans cette section, vous allez utiliser le SDK Cloud pour créer un compte de service, puis créer des identifiants dont vous aurez besoin pour vous authentifier en tant que compte de service.

Commencez par définir une variable d'environnement PROJECT_ID:

export PROJECT_ID=$(gcloud config get-value core/project)

Ensuite, créez un compte de service pour accéder à l'API BigQuery à l'aide de la commande suivante:

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

Ensuite, créez des identifiants que votre code Python utilisera pour se connecter en tant que nouveau compte de service. Créez ces identifiants et enregistrez-les dans un fichier JSON ~/key.json à l'aide de la commande suivante:

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

Enfin, définissez la variable d'environnement GOOGLE_APPLICATION_CREDENTIALS, qui est utilisée par la bibliothèque cliente BigQuery Python, décrite à l'étape suivante, pour trouver vos identifiants. La variable d'environnement doit être définie sur le chemin d'accès complet au fichier JSON d'identifiants que vous avez créé, à l'aide de la commande suivante :

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Apprenez-en davantage sur l'authentification dans l'API BigQuery.

5. Configurer le contrôle des accès

BigQuery utilise Identity and Access Management (IAM) pour gérer l'accès aux ressources. BigQuery dispose d'un certain nombre de rôles prédéfinis (user, dataOwner, dataViewer, etc.) que vous pouvez attribuer au compte de service créé à l'étape précédente. Pour en savoir plus sur le contrôle des accès, consultez la documentation BigQuery.

Avant de pouvoir interroger des ensembles de données publics, vous devez vous assurer que le compte de service dispose au moins du rôle roles/bigquery.user. Dans Cloud Shell, exécutez la commande suivante pour attribuer le rôle d'utilisateur au compte de service:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

Vous pouvez exécuter la commande suivante pour vérifier que le rôle utilisateur est attribué au compte de service:

gcloud projects get-iam-policy $PROJECT_ID

Le résultat suivant doit s'afficher :

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. Installer la bibliothèque cliente

Installez la bibliothèque cliente BigQuery Python:

pip3 install --user --upgrade google-cloud-bigquery

Vous êtes maintenant prêt à coder avec l'API BigQuery.

7. Interroger les œuvres de Shakespeare

Un ensemble de données public est un ensemble de données stocké dans BigQuery et mis à la disposition du grand public. Il existe de nombreux autres jeux de données publics que vous pouvez interroger. Si certains ensembles de données sont hébergés par Google, la plupart sont hébergés par des tiers. Pour en savoir plus, consultez la page Ensembles de données publics.

En plus des ensembles de données publics, BigQuery propose un nombre limité d'exemples de tables que vous pouvez interroger. Ces tables sont contenues dans l'ensemble de données bigquery-public-data:samples. La table shakespeare de l'ensemble de données samples contient un index de mots représentant des œuvres de Shakespeare. Il indique le nombre de fois où chaque mot apparaît dans chaque corpus.

Au cours de cette étape, vous allez interroger la table shakespeare.

Remarque:Pour afficher les détails de la table shakespeare dans la console BigQuery, cliquez ici.

Tout d'abord, dans Cloud Shell, créez une application Python simple que vous utiliserez pour exécuter les exemples de l'API Translation.

mkdir bigquery-demo
cd bigquery-demo
touch app.py

Ouvrez l'éditeur de code en haut à droite de Cloud Shell:

Accédez au fichier app.py dans le dossier bigquery-demo et remplacez le code par ce qui suit.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

Prenez une minute ou deux pour étudier le code et observer comment la table est interrogée.

Retournez dans Cloud Shell, puis exécutez l'application:

python3 app.py

La liste des mots et leur occurrence doit s'afficher:

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

Remarque: Si une erreur PermissionDenied (403) s'affiche, vérifiez les étapes suivies à l'étape Authentifier les requêtes API.

• Vérifiez que la variable d'environnement des identifiants est définie: echo $GOOGLE_APPLICATION_CREDENTIALS
• Le chemin d'accès complet à votre fichier d'identifiants doit s'afficher: /home/$USER/key.json.
• Ensuite, vérifiez que les identifiants ont été créés: cat $GOOGLE_APPLICATION_CREDENTIALS
• Un message de ce type doit s'afficher: {"type": "service_account","project_id": "PROJECT_ID",...}

Si quelque chose est incorrect, revenez à l'étape Authentifier les requêtes API.

8. Interroger l'ensemble de données GitHub

Pour vous familiariser avec BigQuery, vous allez maintenant lancer une requête sur l'ensemble de données public GitHub. Vous trouverez les messages de commit les plus courants sur GitHub. Vous utiliserez également la console Web de BigQuery pour prévisualiser et exécuter des requêtes ad hoc.

Pour voir à quoi ressemblent les données, ouvrez l'ensemble de données GitHub dans l'UI Web de BigQuery:

Ouvrir la table github_repos

Cliquez sur le bouton "Aperçu" pour obtenir un aperçu des données:

Accédez au fichier app.py dans le dossier bigquery_demo et remplacez le code par ce qui suit.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

Prenez une minute ou deux pour étudier le code et voir comment la table est interrogée pour les messages de commit les plus courants.

Retournez dans Cloud Shell, puis exécutez l'application:

python3 app.py

Vous devriez voir la liste des messages de commit et leurs occurrences:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

Remarque: Cet ensemble de données est mis à jour régulièrement. Vos résultats peuvent donc varier.

9. Mise en cache et statistiques

BigQuery met en cache les résultats des requêtes. Par conséquent, les requêtes suivantes prennent moins de temps. Il est possible de désactiver la mise en cache à l'aide des options de requête. BigQuery assure également le suivi des statistiques sur les requêtes, telles que l'heure de création, l'heure de fin et le nombre total d'octets traités.

Au cours de cette étape, vous allez désactiver la mise en cache et afficher des statistiques sur les requêtes.

Accédez au fichier app.py dans le dossier bigquery_demo et remplacez le code par ce qui suit.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

Quelques points à noter concernant le code. Tout d'abord, la mise en cache est désactivée en introduisant QueryJobConfig et en définissant use_query_cache sur "false". Ensuite, vous avez accédé aux statistiques concernant la requête à partir de l'objet Tâche.

Retournez dans Cloud Shell, puis exécutez l'application:

python3 app.py

Comme précédemment, vous devriez voir la liste des messages de commit et leurs occurrences. En outre, vous devriez également voir quelques statistiques sur la requête à la fin:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. Charger des données dans BigQuery

Si vous souhaitez interroger vos propres données, vous devez les charger dans BigQuery. BigQuery permet de charger des données à partir de nombreuses sources, y compris Cloud Storage, d'autres services Google et d'autres sources lisibles. Vous pouvez même diffuser vos données en flux continu à l'aide d'insertions en flux continu. Pour en savoir plus, consultez la page Charger des données dans BigQuery.

Au cours de cette étape, vous allez charger un fichier JSON stocké sur Cloud Storage dans une table BigQuery. Le fichier JSON se trouve à l'emplacement gs://cloud-samples-data/bigquery/us-states/us-states.json.

Si vous souhaitez connaître le contenu du fichier JSON, vous pouvez utiliser l'outil de ligne de commande gsutil pour le télécharger dans Cloud Shell:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

Vous pouvez voir qu'il contient la liste des États américains et que chaque État est un document JSON sur une ligne distincte:

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

Pour charger ce fichier JSON dans BigQuery, accédez au fichier app.py dans le dossier bigquery_demo et remplacez le code par ce qui suit.

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

Prenez une minute sur deux pour étudier comment le code charge le fichier JSON et crée une table avec un schéma sous un ensemble de données.

Retournez dans Cloud Shell, puis exécutez l'application:

python3 app.py

Un ensemble de données et une table sont créés dans BigQuery.

Pour vérifier que l'ensemble de données a bien été créé, accédez à la console BigQuery. Un nouvel ensemble de données et une nouvelle table doivent s'afficher. Accédez à l'onglet d'aperçu du tableau pour afficher vos données:

11. Félicitations !

Vous avez appris à utiliser BigQuery avec Python.

Nettoyage

Pour éviter que les ressources utilisées dans ce tutoriel soient facturées sur votre compte Google Cloud, procédez comme suit :

• Dans la console Cloud, accédez à la page Gérer les ressources.
• Dans la liste des projets, sélectionnez votre projet, puis cliquez sur Supprimer.
• Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

En savoir plus

• Google BigQuery: https://cloud.google.com/bigquery/docs/
• Python sur Google Cloud: https://cloud.google.com/python/
• Bibliothèques clientes Cloud pour Python: https://googleapis.github.io/google-cloud-python/

Licence

Ce document est publié sous une licence Creative Commons Attribution 2.0 Generic.