Utiliser BigQuery avec C#

1. Présentation

BigQuery est l'entrepôt de données analytiques de Google. Entièrement géré, il permet de traiter plusieurs pétaoctets de données à faible coût. BigQuery est une solution NoOps. Vous n'avez donc aucune infrastructure à gérer, et vous n'avez pas besoin de faire appel à un administrateur de base de données. Vous pouvez ainsi vous concentrer sur l'analyse des données pour dégager des insights pertinents, utiliser un langage SQL qui vous est familier et profiter de notre modèle de facturation à l'utilisation.

Dans cet atelier de programmation, vous utiliserez les bibliothèques clientes Google Cloud pour .NET afin d'interroger les ensembles de données publics BigQuery avec C#.

Points abordés

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

Prérequis

  • Un projet Google Cloud Platform
  • Un navigateur tel que Chrome ou Firefox
  • Connaissances de base sur C#

Enquête

Comment allez-vous utiliser ce tutoriel ?

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

Comment évalueriez-vous votre niveau d'expérience avec C# ?

Débutant Intermédiaire Expert

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

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

295004821bab6a87.png

37d264871000675d.png

96d86d3d5655cdbe.png

  • Le nom du projet est le nom à afficher pour les participants au projet. Il s'agit d'une chaîne de caractères non utilisée par les API Google. Vous pourrez toujours le modifier.
  • L'ID du projet est unique parmi tous les projets Google Cloud et non modifiable une fois défini. La console Cloud génère automatiquement une chaîne unique (en général, vous n'y accordez d'importance particulière). Dans la plupart des ateliers de programmation, vous devrez indiquer l'ID de votre projet (généralement identifié par PROJECT_ID). Si l'ID généré ne vous convient pas, vous pouvez en générer un autre de manière aléatoire. Vous pouvez également en spécifier un et voir s'il est disponible. Après cette étape, l'ID n'est plus modifiable et restera donc le même pour toute la durée du projet.
  • Pour information, il existe une troisième valeur (le numéro de projet) que certaines API utilisent. Pour en savoir plus sur ces trois valeurs, consultez la documentation.
  1. Vous devez ensuite activer la facturation dans la console Cloud pour utiliser les ressources/API Cloud. L'exécution de cet atelier de programmation est très peu coûteuse, voire sans frais. Pour désactiver les ressources et éviter ainsi que des frais ne vous soient facturés après ce tutoriel, vous pouvez supprimer le projet ou les ressources que vous avez créées. Les nouveaux utilisateurs de Google Cloud peuvent participer au programme d'essai sans frais 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 d1264ca30785e435.png.

cb81e7c8e34bc8d.png

Si vous démarrez Cloud Shell pour la première fois, un écran intermédiaire s'affiche pour vous expliquer de quoi il s'agit. Si cet écran s'est affiché, cliquez sur Continuer.

d95252b003979716.png

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

7833d5e1c5d18f54.png

Cette machine virtuelle contient tous les outils de développement nécessaires. 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 de programmation dans un navigateur.

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. Activer l'API BigQuery

L'API BigQuery doit être activée par défaut dans tous les projets Google Cloud. Vous pouvez vérifier si c'est le cas avec la commande suivante dans Cloud Shell : Vous devriez être listé dans BigQuery :

gcloud services list

BigQuery devrait s'afficher :

NAME                              TITLE
bigquery-json.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-json.googleapis.com

4. Installer la bibliothèque cliente BigQuery pour C#

Commencez par créer une application console C# simple que vous utiliserez pour exécuter des exemples d'API BigQuery.

dotnet new console -n BigQueryDemo

L'application créée et les dépendances résolues doivent s'afficher :

The template "Console Application" was created successfully.
Processing post-creation actions...
...
Restore succeeded.

Accédez ensuite au dossier BigQueryDemo :

cd BigQueryDemo

Ajoutez le package NuGet Google.Cloud.BigQuery.V2 au projet :

dotnet add package Google.Cloud.BigQuery.V2

info : Adding PackageReference for package 'Google.Cloud.BigQuery.V2' into project '/home/atameldev/BigQueryDemo/BigQueryDemo.csproj'.
log  : Restoring packages for /home/atameldev/BigQueryDemo/BigQueryDemo.csproj...
...
info : PackageReference for package 'Google.Cloud.BigQuery.V2' version '1.2.0' added to file '/home/atameldev/BigQueryDemo/BigQueryDemo.csproj'.

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

5. 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. Vous pouvez interroger de nombreux autres ensembles de données publics. Certains sont hébergés par Google, mais bien d'autres 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 bigquery-public-data:samples dataset. L'une de ces tables s'appelle shakespeare.. Elle contient un index de mots des œuvres de Shakespeare, indiquant le nombre de fois où chaque mot apparaît dans chaque corpus.

Dans cette étape, vous allez interroger la table Shakespeare.

Tout d'abord, ouvrez l'éditeur de code en haut à droite de Cloud Shell :

fd3fc1303e63572.png

Accédez au fichier Program.cs dans le dossier BigQueryDemo et remplacez le code par ce qui suit. Veillez à remplacer projectId par l'ID réel de votre projet :

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "samples", "shakespeare");
            var sql = $"SELECT corpus AS title, COUNT(word) AS unique_words FROM {table} GROUP BY title ORDER BY unique_words DESC LIMIT 10";

            var results = client.ExecuteQuery(sql, parameters: null);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
            }
        }
    }
}

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

Retournez dans Cloud Shell et exécutez l'application :

dotnet run

Une liste de mots et de leurs occurrences devrait s'afficher :

hamlet: 5318
kinghenryv: 5104
cymbeline: 4875
troilusandcressida: 4795
kinglear: 4784
kingrichardiii: 4713
2kinghenryvi: 4683
...

6. Interroger l'ensemble de données GitHub

Pour vous familiariser davantage avec BigQuery, vous allez maintenant exécuter 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 ponctuelles.

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

https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=github_repos&t=commits&page=table

Pour obtenir un aperçu rapide de l'apparence des données, cliquez sur le bouton "Aperçu" :

f706bfe3dfcbd267.png

Accédez au fichier Program.cs dans le dossier BigQueryDemo et remplacez le code par ce qui suit. Veillez à remplacer projectId par l'ID réel de votre projet :

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "github_repos", "commits");
            
            var sql = $"SELECT subject AS subject, COUNT(*) AS num_duplicates FROM {table} GROUP BY subject ORDER BY num_duplicates DESC LIMIT 10";

            var results = client.ExecuteQuery(sql, parameters: null);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["subject"]}: {row["num_duplicates"]}");
            }
        }
    }
}

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

Retournez dans Cloud Shell et exécutez l'application :

dotnet run

Une liste des messages de commit et de leurs occurrences devrait s'afficher :

Update README.md: 2509242
: 1971725
Initial commit: 1942149
Mirroring from Micro.blog.: 838586
update: 575188
Update data.json: 548651
Update data.js: 548339
Add files via upload: 379941
*** empty log message ***: 358528
Can't you see I'm updating the time?: 286863

7. Mise en cache et statistiques

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

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

Accédez au fichier Program.cs dans le dossier BigQueryDemo et remplacez le code par ce qui suit. Veillez à remplacer projectId par l'ID réel de votre projet :

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "github_repos", "commits");
            
            var sql = $"SELECT subject AS subject, COUNT(*) AS num_duplicates FROM {table} GROUP BY subject ORDER BY num_duplicates DESC LIMIT 10";
            var queryOptions = new QueryOptions {
                UseQueryCache = false
            };

            var results = client.ExecuteQuery(sql, parameters: null, queryOptions: queryOptions);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["subject"]}: {row["num_duplicates"]}");
            }

            var job = client.GetJob(results.JobReference);
            var stats = job.Statistics;
            Console.WriteLine("----------");
            Console.WriteLine($"Creation time: {stats.CreationTime}");
            Console.WriteLine($"End time: {stats.EndTime}");
            Console.WriteLine($"Total bytes processed: {stats.TotalBytesProcessed}");
        }
    }
}

Quelques points à noter concernant le code : Tout d'abord, la mise en cache est désactivée en ajoutant des options de requête et en définissant UseQueryCache sur "false". Ensuite, vous avez accédé aux statistiques sur la requête à partir de l'objet Job.

Retournez dans Cloud Shell et exécutez l'application :

dotnet run

Comme avant, vous devriez voir une liste des messages de commit et de leurs occurrences. De plus, vous devriez voir des statistiques sur la requête à la fin.

Update README.md: 2509242
: 1971725
Initial commit: 1942149
Mirroring from Micro.blog.: 838586
update: 575188
Update data.json: 548651
Update data.js: 548339
Add files via upload: 379941
*** empty log message ***: 358528
Can't you see I'm updating the time?: 286863
----------
Creation time: 1533052057398
End time: 1533052066961
Total bytes processed: 9944197093

8. Charger des données dans BigQuery

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

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

Si vous souhaitez en savoir plus sur 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 .

Copying gs://cloud-samples-data/bigquery/us-states/us-states.json...
/ [1 files][  2.0 KiB/  2.0 KiB]                                                
Operation completed over 1 objects/2.0 KiB.

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

less us-states.json

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

Pour charger ce fichier JSON dans BigQuery, accédez au fichier Program.cs dans le dossier BigQueryDemo et remplacez le code par le code suivant. Veillez à remplacer projectId par l'ID réel de votre projet :

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var gcsUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
            var client = BigQueryClient.Create("projectId");
            var dataset = client.GetOrCreateDataset("us_states_dataset");

            var schema = new TableSchemaBuilder 
            {
                { "name", BigQueryDbType.String },
                { "post_abbr", BigQueryDbType.String }
            }.Build();

            var jobOptions = new CreateLoadJobOptions
            {
                SourceFormat = FileFormat.NewlineDelimitedJson
            };

            var table = dataset.GetTableReference("us_states_table");
            var loadJob = client.CreateLoadJob(gcsUri, table, schema, jobOptions);

            loadJob.PollUntilCompleted();
            loadJob.ThrowOnAnyError();
            Console.WriteLine("Json file loaded to BigQuery");
        }
    }
}

Prenez une ou deux minutes 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 et exécutez l'application :

dotnet run

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

Json file loaded to BigQuery

Pour vérifier que l'ensemble de données a bien été créé, vous pouvez accéder à la console BigQuery. Un ensemble de données et un tableau doivent avoir été créés. Si vous passez à l'onglet "Aperçu" du tableau, vous pouvez voir les données réelles :

7d9f7c493acbbf9a.png

9. Félicitations !

Vous avez appris à utiliser BigQuery avec C#.

Effectuer un nettoyage

Afin d'éviter que des frais ne soient facturés sur votre compte Google Cloud Platform pour les ressources utilisées dans ce démarrage rapide, procédez comme suit :

  • Accédez à la console Cloud Platform.
  • Sélectionnez le projet que vous souhaitez arrêter, puis cliquez sur "Supprimer" en haut de la page. Le projet sera alors programmé pour suppression.

En savoir plus

Licence

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