Charger et interroger des données avec l'outil de ligne de commande bq pour BigQuery

1. Introduction

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger des téraoctets de données sans avoir besoin d'un administrateur de base de données ni d'une infrastructure. BigQuery utilise le langage SQL que vous connaissez déjà et un modèle de facturation à 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 utiliser l'outil de ligne de commande bq pour charger un fichier CSV local dans une nouvelle table BigQuery.

Points abordés

  • Utiliser l'outil de ligne de commande bq pour BigQuery
  • Charger des fichiers de données locaux dans une table BigQuery

Prérequis

2. Configuration

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
  1. Acceptez les conditions d'utilisation.
  2. 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. 1884405a64ce5765.png

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.

Cloud Shell

Vous allez utiliser Cloud Shell, un environnement de ligne de commande exécuté dans Google Cloud.

Activer Cloud Shell

  1. Dans Cloud Console, cliquez sur Activer Cloud Shell 4292cbf4971c9786.png.

bce75f34b2c53987.png

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 :

70f315d7b402b476.png

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

fbe3a0674c982259.png

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.

  1. 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`
  1. 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. Créer un ensemble de données

Créez un ensemble de données pour contenir vos tables.

Qu'est-ce qu'un ensemble de données ?

Un ensemble de données BigQuery est une collection de tables. Toutes les tables d'un ensemble de données sont stockées au même emplacement des données. Vous pouvez également associer des contrôles d'accès personnalisés pour limiter l'accès à un ensemble de données et à ses tables.

Créer un ensemble de données

Dans Cloud Shell, utilisez la commande bq mk pour créer un ensemble de données nommé "bq_load_codelab".

bq mk bq_load_codelab

Afficher les propriétés des ensembles de données

Vérifiez que vous avez créé l'ensemble de données en affichant ses propriétés avec la commande bq show.

bq show bq_load_codelab

La sortie obtenue doit ressembler à ceci :

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Créer le fichier de données

BigQuery peut charger des données à partir de plusieurs formats de données, y compris JSON délimité par un saut de ligne, Avro et CSV. Pour plus de simplicité, vous utiliserez le format CSV.

Créer un fichier CSV

Dans Cloud Shell, créez un fichier CSV vide.

touch customer_transactions.csv

Ouvrez le fichier CSV dans l'éditeur de code de Cloud Shell en exécutant la commande cloudshell edit. Une nouvelle fenêtre de navigateur s'ouvre alors avec un éditeur de code et un panneau Cloud Shell.

cloudshell edit customer_transactions.csv

Dans l'éditeur de code, saisissez des valeurs séparées par des virgules à charger dans BigQuery.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

Enregistrez le fichier CSV en cliquant sur Fichier > Modifier.

5. Charger les données

Utilisez la commande bq load pour charger votre fichier CSV dans une table BigQuery.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

Vous avez utilisé les options suivantes :

  • --source_format=CSV utilise le format de données CSV pour analyser le fichier de données.
  • --skip_leading_rows=1 ignore la première ligne du fichier CSV, car il s'agit d'une ligne d'en-tête.
  • Bq_load_codelab.customer_transactions—the first positional argument—définit la table dans laquelle les données doivent être chargées.
  • ./customer_transactions.csv, le deuxième argument positionnel, définit le fichier à charger. En plus des fichiers locaux, la commande bq load peut charger des fichiers depuis Cloud Storage avec gs://my_bucket/path/to/file URIs.
  • Un schéma, qui peut être défini dans un fichier de schéma JSON ou sous forme de liste séparée par des virgules. (Vous avez utilisé une liste séparée par des virgules pour plus de simplicité.)

Vous avez utilisé le schéma suivant dans la table "customer_transactions" :

  • Id:string : identifiant client
  • Zip:string : code postal américain
  • Ttime:timestamp : date et heure de la transaction
  • Amount:numeric : montant d'une transaction (une colonne numérique stocke les données sous forme décimale, ce qui est utile pour les valeurs monétaires).
  • Fdbk:float : note attribuée à la transaction dans une enquête de satisfaction
  • Sku:string : identifiant de l'article acheté

Obtenir les détails de la table

Vérifiez que la table a été chargée en affichant ses propriétés.

bq show bq_load_codelab.customer_transactions

Résultat :

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. Interroger les données

Maintenant que vos données sont chargées, vous pouvez les interroger à l'aide de l'UI Web de BigQuery, de la commande bq ou de l'API. Vos requêtes peuvent joindre vos données à n'importe quel ensemble de données (ou ensembles de données, à condition qu'ils se trouvent au même emplacement) pour lequel vous disposez de l'autorisation de lecture.

Exécutez une requête SQL standard qui joint votre ensemble de données à l'ensemble de données des codes postaux américains et additionne les transactions par État américain. Utilisez la commande bq query pour exécuter la requête.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

Cette commande devrait générer un résultat semblable à celui-ci :

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

La requête que vous avez exécutée utilisait un ensemble de données public et votre ensemble de données privé. Pour en savoir plus, consultez cette version commentée de la même requête :

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. Effectuer un nettoyage

Supprimez 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'il contient.

bq rm -r bq_load_codelab

8. Félicitations !

Vous avez importé une table dans BigQuery et l'avez interrogée.

Sujets abordés

  • Utiliser l'outil de ligne de commande bq pour interagir avec BigQuery
  • Joindre vos données et un ensemble de données public avec une requête BigQuery.

Étape suivante

Consultez les articles suivants :