Rendre disponible des données d’un ensemble BigQuery à l’aide de Cloud Run

Publié le jeudi 18 février 2021
#BigQuery #Google Cloud #Cloud Run #COVID-19 #Ensemble de données publiques #Microservice
This post thumbnail

Rendre disponible des données d’un ensemble BigQuery à l’aide de Cloud Run

Introduction

Cet article introduit comment créer une interface de programmation (API) pour rendre disponible un ensemble de données publiques de BigQuery à l’aide de Cloud Run.

Cette approche permet de créer des microservices pour permettre l’accès à des données entreposées dans l’entrepôt BigQuery sans avoir à connaître et utiliser la console de BigQuery. D’autres services, applications peuvent alors intégrer les microservices à l’aide des API ainsi rendus disponibles. Pour cet article, je vais utiliser Node.js car il existe une librairie Javascript pour interagir avec BigQuery.

Aussi, il est possible de sécuriser l’API pour restreindre l’accès aux services. Il existe diverses possibilités pour le faire.

Pour cet article, je vais utiliser l’ensemble de données publiques COVID-19 de BigQuery. Il sera possible de l’interroger pour obtenir les nouveaux cas, soit : pour un pays, une région ou bien une sous-région. L’ensemble de données classe les données par pays, sous-région 1, sous-région 2. Pour le Canada, les sous-régions 1 sont les provinces et les sous-régions 2 les régions administratives de la province. Par exemple, il est possible de suivre l’évolution des données pour le Bas-Saint-Laurent au Québec. Cette belle région où demeurent mes parents.

La requête BigQuery SQL suivante me permettrait d’obtenir les données du le mois de janvier 2021 pour la provinde du Québec:

SELECT  
    date, country_code, country_name, 
    subregion1_code, subregion1_name, 
    subregion2_name, 
    cumulative_confirmed, cumulative_recovered, cumulative_deceased, cumulative_tested, 
    new_confirmed,
     AVG(new_confirmed) 
     OVER (PARTITION BY subregion2_name ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) 
     AS avg_new_confirmed_7days
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data` 
    WHERE country_code = 'CA'
    AND subregion1_code = 'QC'
    AND subregion2_name IS NULL
    AND date > '2021-01-01'
    AND date < '2021-02-01'
    LIMIT 1000

Déploiement d’un service Cloud Run à l’aide de VS Code

Pour déployer le service, je vais utiliser l’extension Cloud Code. Il y a un excellent tutoriel qui présente comment créer et déployer un service Cloud Run avec Node.js. Le lien est en annexe à la fin de l’article.

Cependant, il est possible d’automatiser le déploiement en continu lors de la publication du code sur un référentiel tel que BitBucket ou GitHub à l’aide de Cloud Build ou bien de GitHub Action. Cela sera le sujet d’un article à suivre.

Librairie Javascript BigQuery pour Node.js

Google rend disponible des librairies clientes BigQuery pour les langages suivants :

  • C#
  • Go
  • Java
  • Node.js
  • PHP
  • Python
  • Ruby

Pour l’installer la librairie cliente pour Node.js dans le projet, il suffit de l’installer avec la commande npm install :

npm install @google-cloud/bigquery

Requête BigQuery paramétrée

Pour réduire les risques d’injection SQL avec des données provenant de l’utilisateur, BigQuery offre la possibilité d’utiliser des requêtes paramétrées. Cela permet de s’assurer que seul le remplacement des paramètres sera effectué lors de l’exécution de la requête. Pour spécifier un paramètre nommé, il suffit d’utiliser le caractère @ suivi d’un identifiant. Et par la suite, de passer la valeur de l’identifiant dans la section params de l’objet JavaScript qui contiendra la requête et ses paramètres.

Requêtes paramétrées

Pour ce projet, nous allons avoir deux types de requêtes. Le premier qui va obtenir les données des nouveaux cas et calculer une moyenne mobile. Le second type va retourner les valeurs uniques de pays, sous-région 1 et sous-région 2 dont des données sont disponibles dans l’ensemble.

Requête pour obtenir les nouveaux cas pour une province pour une période données:

const sqlQuery = `SELECT
    date, country_code, country_name, subregion1_code, subregion1_name, 
    subregion2_name, cumulative_confirmed, cumulative_recovered, 
    cumulative_deceased, cumulative_tested, new_confirmed,
    AVG(new_confirmed) OVER (PARTITION BY subregion2_name ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS avg_new_confirmed_7days
    FROM \`bigquery-public-data.covid19_open_data.covid19_open_data\`
    WHERE country_code = @country_code 
    AND subregion1_code = @region_code 
    AND subregion2_name IS NULL
    AND date >= @from AND date <= @to
    LIMIT 1000 `;

    options = {
      query: sqlQuery,
      // Location must match that of the dataset(s) referenced in the query.
      location: 'US',
      params: {
        from: fromParam,
        to: toParam,
        country_code: countryCode,
        region_code: regionCode,
      },
    };
    // Run the query
    const [rows] = await bigqueryClient.query(options);

Requête pour obtenir la liste des pays ayant des données inscrites dans la période sélectionnée :

const sqlQuery = `SELECT DISTINCT 
  country_code, country_name, 
  FROM \`bigquery-public-data.covid19_open_data.covid19_open_data\` 
  WHERE  date >= @from AND date <= @to
  ORDER BY country_code ASC
  LIMIT 1000 `;

const options = {
    query: sqlQuery,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    params: {
      from: fromParam,
      to: toParam,
    },
  };

  // Run the query
  const [rows] = await bigqueryClient.query(options);

Le code complet du service est disponible sur GitHub.

Autorisation de l’API BigQuery

Pour permettre à Cloud Run d’effectuer des requêtes BigQuery, il faut autoriser l’api BigQuery soit via la console Google Cloud à l’onglet API & Services / Library, rechercher BigQuery et activer l’API.

Activer l'API BigQuery via la Console Cloud Activer l'API BigQuery via la Console Cloud

API requête de type list

Afin de permettre l’exploration des données, on ajoute un paramètre “list” optionnel pour obtenir une liste de pays, de sous-région 1 ou sous-région 2 avec une plage de date pour vérifier s’il existe des données dans l’ensemble de données.

Par exemple, l’appel suivant :

https://[url du service]/CA?list=true&from=2021-01-01&to=2021-01-31

va retourner en format JSON la liste des provinces du Canada qui ont fourni au moins une donnée durant le mois de janvier 2021.

[{"country_code":"CA","country_name":"Canada","subregion1_code":"AB","subregion1_name":"Alberta"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"BC","subregion1_name":"British Columbia"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"MB","subregion1_name":"Manitoba"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"NB","subregion1_name":"New Brunswick"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"NL","subregion1_name":"Newfoundland and Labrador"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"NS","subregion1_name":"Nova Scotia"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"NT","subregion1_name":"Northwest Territories"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"NU","subregion1_name":"Nunavut"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"ON","subregion1_name":"Ontario"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"PE","subregion1_name":"Prince Edward Island"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"SK","subregion1_name":"Saskatchewan"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"YT","subregion1_name":"Yukon"}]

Si l’on ajoute le code de province, l’appel suivant :

https://[url du service]/CA/QC?list=true&from=2021-01-01&to=2021-01-31

va retourner en format JSON la liste des régions du Québec pour lesquelles il y a au moins une donnée disponible durant le mois de janvier 2021.

[{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Abitibi-Témiscamingue"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Bas-Saint-Laurent"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Capitale-Nationale"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Chaudière-Appalaches"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Côte-Nord"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Estrie"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Gaspésie-Îles-de-la-Madeleine"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Lanaudière"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Laurentides"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Mauricie"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Montréal"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Montérégie"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Nord-du-Québec"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Nunavik"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Outaouais"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Saguenay"},
{"country_code":"CA","country_name":"Canada","subregion1_code":"QC","subregion1_name":"Quebec","subregion2_name":"Terres-Cries-de-la-Baie-James"}]

Il est possible d’obtenir la liste de tous les pays qui ont fourni des données durant une période donnée à l’aide d’une requête. Par exemple :

https://[url du service]/?list=true&from=2021-01-01&to=2021-01-31

Exemple d'une partie des données obtenus :

[{"country_code":"AD","country_name":"Andorra"},
{"country_code":"AE","country_name":"United Arab Emirates"},
{"country_code":"AF","country_name":"Afghanistan"},
{"country_code":"AG","country_name":"Antigua and Barbuda"},
{"country_code":"AI","country_name":"Anguilla"},
{"country_code":"AL","country_name":"Albania"},
...]

API requête pour obtenir les nouveaux cas

L’API développée permet d’obtenir les données au niveau d’un pays, d’une sous-région 1 ( province pour le Canada ) ou sous région 2 ( région administrative provinciale pour le Québec).

Voici un exemple de requête pour obtenir les nouveaux cas quotidiens pour la province du Québec du 1er février 2021 au 10 février 2021 :

https://[url du service]/CA/QC?from=2021-02-01&to=2021-02-10

Exemple d'une partie des données obtenus :

[...
{"date":{"value":"2021-02-04"},"country_code":"CA","country_name":"Canada",
"subregion1_code":"QC","subregion1_name":"Quebec",
"subregion2_name":null,
"cumulative_confirmed":266672,"cumulative_recovered":243769,"cumulative_deceased":9941,
"cumulative_tested":null,"new_confirmed":1093,
"avg_new_confirmed_7days":1067.857142857143},
...]

Une nouvelle requête pour obtenir cette fois-ci les données pour la région du Bas-Saint-Laurent :

https://[url du service]/CA/QC/Bas-Saint-Laurent?from=2021-02-01&to=2021-02-10

[...,
{"date":{"value":"2021-02-04"},"country_code":"CA","country_name":"Canada",
"subregion1_code":"QC","subregion1_name":"Quebec",
"subregion2_name":"Bas-Saint-Laurent",
"cumulative_confirmed":1475,"cumulative_recovered":null,"cumulative_deceased":30,
"cumulative_tested":null,"new_confirmed":0,
"avg_new_confirmed_7days":1.8571428571428572},
...]

Sécurité

Pour l’instant, nous allons rendre le service disponible publiquement. Pour ce faire, nous allons le faire lors de la création du service à l’aide du plugin Cloud Run de VS Code.

À la section Authentification, il suffit de bien cocher l’option : Allow unauthenticated invocations

Activer l'accès publique à l'API Activer l'accès publique à l'API à l'aide du plugin Cloud Run

Si jamais, vous oubliez, il est possible de le faire par la suite à l’aide de la Console Google Cloud. Les instructions sont disponibles dans le document Managing access using IAM : https://cloud.google.com/run/docs/securing/managing-access

Dans de futurs articles, nous allons explorer d’autres méthodes pour restreindre l’accès au service avec Cloud Identity and Access Management (IAM), Cloud Endpoints ou Cloud Apigee.

Conclusion

Cet article démontre comment rendre des données d’un ensemble de données publiques de BigQuery à l’aide d’une interface de programmation (API) à l’aide de Cloud Run. Nous avons vu qu’il est possible d’effectuer des requêtes BigQuery par programmation en Node.js à l’aide de la librairie Javascript BigQuery.

Dans un prochain article, nous allons voir comment faire le déploiement automatisé dans un environnement de test à l’aide de Google Cloud Build lors de la publication du code dans un référentiel de données Github et BitBucket.

Le code de l’article est disponible sur GitHub :

Références

https://konato.com/covid19-bigquery-article1/

https://cloud.google.com/run

https://cloud.google.com/bigquery/docs/reference/libraries?hl=fr#client-libraries-install-nodejs

https://cloud.google.com/code/docs/vscode/quickstart-cloud-run

https://cloud.google.com/run/docs/securing/managing-access

https://cloud.google.com/run/docs/authenticating/end-users

Code source du projet : https://github.com/konato/covid-19-cloud-run-bigquery-api