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 administrateur de base de données ni infrastructure. BigQuery utilise le langage SQL qui vous est familier et propose un modèle de facturation basé sur l'utilisation. 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

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

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

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 le respect des 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.

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 .

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

Créer un ensemble de données pour y stocker vos tables

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

Un ensemble de données BigQuery est un ensemble de tables. Toutes les tables d'un ensemble de données sont stockées dans le même emplacement de 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 intitulé "bq_load_codelab".

bq mk bq_load_codelab

Afficher les propriétés d'un ensemble de données

Vérifiez que vous avez créé l'ensemble de données en affichant ses propriétés à l'aide de 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,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Créer le fichier de données

BigQuery peut charger des données de différents formats, y compris JSON délimité par un retour à la ligne, Avro et CSV. Pour plus de simplicité, utilisez 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 de modification cloudshell. Cela ouvrira une nouvelle fenêtre de navigateur avec un éditeur de code et un panneau Cloud Shell.

cloudshell edit customer_transactions.csv

Remarque:L'éditeur de code nécessite des cookies tiers pour fonctionner, car il se charge depuis un domaine différent de celui de la console Google Cloud.

Dans l'éditeur de code, saisissez des valeurs séparées par une virgule à 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 lors de l'analyse du 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 (deuxième argument positionnel) définit le fichier à charger. En plus des fichiers locaux, la commande bq load permet de 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 d'éléments séparés par une virgule. Pour plus de simplicité, vous avez utilisé une liste d'éléments séparés par une virgule.

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 au format décimal, particulièrement utile pour les valeurs monétaires)
• Fdbk:float: note obtenue à partir d'une enquête de satisfaction sur la transaction
• 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

Sortie :

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'interface utilisateur 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, s'ils se trouvent dans le même emplacement) que vous êtes autorisé à lire.

Exécutez une requête en SQL standard qui associe votre ensemble de données à celui sur les 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 produire 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 a utilisé un ensemble de données public et votre ensemble de données privé. Pour en savoir plus, lisez 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'elle contient.

bq rm -r bq_load_codelab

8. Félicitations !

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

Points abordés

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

Étape suivante

Consultez les articles suivants :

• L'outil de ligne de commande bq
• Autres méthodes pour charger des données dans BigQuery
• D'autres ensembles de données publics disponibles via BigQuery
• Météo, crime et autres types de données dans TIL avec BigQuery.