Premiers pas avec Google BigQuery
Une connaissance professionnelle m’informe qu’il y a une mission potentielle BigQuery + Python. Cela me donne l’occasion d’investiguer Google Cloud Platform (GCP) et aussi BigQuery qui est :
Un entrepôt de données multicloud sans serveur, hautement évolutif et économique, conçu pour optimiser l’agilité des entreprises”.
Pour des détails techniques sur ce système de bases de données, notamment son architecture en colonnes, on peut se référer à l’article de Geoffrey Garnotel et intitulé BigQuery — Les bases. Ceci dit, db-engines classe BigQuery dans les bases de données relationnelles avec la mention :
Large scale data warehouse service with append-only tables.
La formule de Google permet de tester le produit sans se ruiner :
Les nouveaux clients bénéficient de 300 $ de crédits gratuits à dépenser sur Google Cloud pendant les 90 premiers jours d’utilisation. Tous les clients obtiennent gratuitement jusqu’à 10 Go d’espace de stockage et 1 To de requêtes par mois.
Activation de GCP
J’active donc GCP et me voilà dans l’espace de travail SQL de BigQuery, car BigQuery c’est principalement du SQL standard ANSI.
J’utilise les données publiques fournies par Google Cloud qui sont dans le projet bigquery-public-data. Il y a par exemple la base usa_names avec la table usa_1910_2013 comprenant les prénoms donnés aux bébés nés aux USA. Je ne vais pas être perdu, c’est le dataset que j’utilise pour mes cours de Python.
L’interface de requettage ressemble à celle de MySQL Query Browser avec les tables à gauche, l’éditeur de requêtes en haut et le résultat de la requête en bas.
Les résultats me surprennent un peu, il n’y a que des hommes. En effet, il s’agit du dataset par État et non du National. Il faut modifier un peu la requête pour obtenir les données agrégées. La gagnante est bien Linda donnée presque 100.000 fois en 1947.
Utilisation de BigQuery avec un notebook Jupyter
Ce n’est pas le tout, mais une fois qu’on a des data, pourquoi ne pas profiter de Jupyter pour jouer un peu avec en Python ?
Pour cela, il faut :
- Se créer un compte de service GCP à partir de l’espace IAM et admin.
- Télécharger à partir de se son compte de service un fichier JSON qui contient les clés d’authentification.
- Installer les lib Python google-cloud-bigquery et google.oauth2. Bon, j’en profite pour me créer un nouvel environnement avec la stack data de Python et les lib de Google.
Ensuite BigQuery est facilement accessible depuis un notebook.
# read credentials
from google.oauth2 import service_accountcredentials = service_account.Credentials.from_service_account_file(
'my-file-downloaded-from-google-service-account.json')scoped_credentials = credentials.with_scopes(
['https://www.googleapis.com/auth/cloud-platform'])# create client with credentials
from google.cloud import bigqueryclient = bigquery.Client(credentials=credentials, project=credentials.project_id,)
Et voilà, il n’y a plus qu’à requêter BigQuery depuis chez soi et créer directement un DataFrame à partir des résultats. Ah j’oubliais, il faut aussi installer la librairie pyarrow pour que la conversion en DataFrame fonctionne.
Il s’agit de l’implémentation Python de Apache Arrow :
Apache Arrow defines a language-independent columnar memory format for flat and hierarchical data, organized for efficient analytic operations on modern hardware like CPUs and GPUs.
Dataset n° 1
# run query
query_job = client.query(
"""SELECT
year,
name,
SUM(number) AS total
FROM
`bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
name, year
ORDER BY
total DESC
LIMIT
10;""")# collect results in dataframe
df = query_job.result().to_dataframe()
Dataset n° 2
Changeons de dataset. Tiens, il y a celui sur pypi.org avec les nombres de téléchargements des projets dans la table bigquery-public-data.pypi.file_downloads. Je vais pouvoir calculer quelques statistiques sur mes librairies préférées.
Google nous indique que cette table est partitionnée. Il va donc falloir la requêter en limitant le champ de la requête suivant la colonne timestamp. On écrit donc une boucle sur l’année et on concatène les DataFrames obtenus.
# run query
dfs = []for year in range(2016, 2022): print(year)
query_job = client.query(
"""SELECT
EXTRACT(YEAR FROM timestamp) as year, file.project, count(*) as downloads
FROM
`bigquery-public-data.pypi.file_downloads`
WHERE
EXTRACT(YEAR FROM timestamp) = {} AND
file.project IN ('numpy', 'pandas', 'matplotlib', 'scipy', 'scikit-learn', 'flask')
GROUP BY
year, file.project
ORDER BY
downloads DESC;""".format(year))
df = query_job.result().to_dataframe()
dfs.append(df)
# collect results in dataframe
df = pd.concat(dfs, ignore_index=True)
df
Produisons donc un graphique avec les téléchargements par année et par librairie.
var = df.set_index('project')
order = var.loc[var['year']==2020, 'downloads'].sort_values(ascending=False).indexfig, ax = plt.subplots(figsize=(8, 6))
sns.lineplot(data=df.loc[df['year']<df['year'].max()],
x='year',
y='downloads',
hue='project',
hue_order=order,
ax=ax)
ax.set_title('Number of downloads by year')
ax.set_xticks(range(df['year'].min(), df['year'].max()))
ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, pos: f'{int(x/1e6)}M'));
Le grand gagnant est numpy avec 600M de téléchargements en 2020 et pandas est à 300M, pas mal non plus !
Dataset n° 3
Changeons encore de dataset. Il y a aussi celui des repos github avec les langages utilisés et les licences logicielles des différents repos. On devrait pouvoir produire des statistiques sur les licences utilisées par langage.
Ici la requête est un peu plus complexe :
- Il va falloir effectuer une jointure entre les tables languages et licenses avec repo_name comme clé commune.
- De plus, la table languages contient la colonne language qui est un record, c’est-à-dire une donnée elle-même structurée. On commence donc par extraire les informations nécessaires de la table languages avant d’effectuer la jointure. Pour ce faire, la fonction UNNEST permet d’utiliser directement les différentes valeurs de la colonne language. C’est un peu l’opérateur ** des dictionnaires Python :) Au niveau du SQL, ça nécessite d’imbriquer 2 requêtes. On pourra se référer à l’article de Todd Kerpelman et intitulé How to use SELECT FROM UNNEST to analyze multiple parameters in BigQuery for Analytics.
# run query
query_job = client.query(
"""SELECT
language,
license,
COUNT(*) AS number,
FROM (
SELECT
repo_name,
language.name AS
language
FROM
`bigquery-public-data.github_repos.languages`,
UNNEST(language) AS
language
GROUP BY
repo_name,
language) AS languages
JOIN
`bigquery-public-data.github_repos.licenses` AS licenses
ON
languages.repo_name = licenses.repo_name
GROUP BY
language,
license
ORDER BY
number DESC;""")df = query_job.result().to_dataframe()
df
Ensuite, il faut calculer une table pivot avec le nombre de projets par langage et par licence. On calcule un pourcentage par langage afin d’avoir une information sur les licences utilisées pour chacun des langages. Il est enfin possible de produire une heatmap. Il y a plusieurs centaines de langages dans la table, concentrons nous sur les 15 premiers.
languages = df.groupby('language')['number'].sum().nlargest(15).index
selection = df.loc[df['language'].isin(languages)]tab = selection.pivot_table(values='number',
index='language',
columns='license',
aggfunc='sum')
tab = tab.div(tab.sum(axis=1), axis=0)*100sns.heatmap(tab, cmap='Blues', cbar_kws={'format': '%.0f%%'})
plt.xticks(rotation=45, ha='right');
La licence MIT semble être la plus utilisée. Sauf pour Java (Apache 2.0) et Perl (GPL 2). Il apparaît aussi que Ruby utilise la licence MIT à plus de 70%.
Ceci dit, il y a un biais, les licences copyleft GPL et la licence BSD apparaissent sous différentes versions ou variantes. Ce qui a pour effet de diluer leurs parts respectives. Procédons donc à une agrégation de ces différentes versions avant d’effectuer le pivot dans une nouvelle colonne license2.
Cette fois-ci, il ressort également que C et C++ utilisent principalement des licences de type GPL. De plus, Python est à présent partagé entre la licence MIT (à 38%) et une licence de type GPL (à 34%).
Dataset n° 4
Changeons à nouveau de dataset. Il y a également celui sur stackoverflow. Nous allons pouvoir déterminer le nombre de mentions des librairies par année.
Pour la requête, il y a une petite subtilité. Il faut extraire les tags de stackoverflow qui sont séparés par le caractère | en utilisant la fonction SPLIT et il faut à nouveau utiliser la fonction UNNEST. Ce qui nécessite deux requêtes imbriquées : l’une extrait l’année et effectue le split des tags tandis que l’autre effectue le comptage par tag et par année.
# run query
query_job = client.query(
"""SELECT
lib,
year,
COUNT(*) AS number
FROM (
SELECT
EXTRACT(YEAR FROM creation_date) as year,
SPLIT(tags, "|") AS splitted_tags
FROM
`bigquery-public-data.stackoverflow.stackoverflow_posts`),
UNNEST(splitted_tags) AS lib
WHERE
lib IN ('numpy',
'pandas',
'matplotlib',
'scipy',
'scikit-learn',
'flask')
GROUP BY
lib,
year
ORDER BY
lib,
year;""")# collect results in dataframe
df = query_job.result().to_dataframe()
df
On obtient ensuite assez facilement un graphique. Dommage que le dataset n’ait été mis à jour qu’en 2017. Au final, seules les données jusqu’en 2015 semblent utilisables.
Quid des coûts ?
Depuis l’activation de mon compte, l’information sur le crédit utilisé n’a pas changé et correspond aux 300$ de crédit offert.
État de l’essai gratuit : Il vous reste 250,79 € de crédit et XX jours d’essai gratuit.
Je pense qu’il me faudra attendre 1 mois d’utilisation avant de savoir ce qu’auront coûté les requêtes.
Sinon, Google indique :
- Stockage actif : $20/To au-dessus de 10 Go, par mois.
- Stockage à long terme : $10/To au-dessus de 10 Go, par mois.
- Requêtage : $5/To au-dessus de 1 To, par mois.
Pour le requêtage, Google rajoute :
BigQuery utilise une structure en colonne de données. Vous êtes facturé en fonction du nombre total de données traitées dans les colonnes que vous sélectionnez, et le nombre total de données par colonne est calculé en fonction des types de données de la colonne.
Avec une facturation aux colonnes requêtées, on comprend bien qu’il faut absolument éviter les SELECT * . D’ailleurs, Google précise :
L’utilisation de la requête
SELECT *est le moyen le plus coûteux d'interroger des données.
Ici, une mauvaise pratique est sanctionnée financièrement…
Il est aussi possible de prédéterminer le coût d’une requête avant de l’exécuter. Voir la documentation dans Contrôler les coûts dans BigQuery. On obtient aisément le nombre d’octets traités par la requête. Par exemple, dans le dataset n° 2, la requête pour l’année 2021 seule traite 526 Go, soit une requête à $2,50.
Il existe aussi tout un ensemble de conseils fournis par Google pour optimiser ses requêtes dans Présentation de l’optimisation des performances des requêtes et aussi dans Optimiser le calcul des requêtes portant sur différents aspects : entrées/sorties, calculs, UDF, approximations, jointures, partitions, etc.
Conclusion
Tout ceci nous ramène à l’approche hybride BI Notebook formalisée dans l’article récent de Taylor Brownlow et intitulé : The Analyst’s Workflow is Broken. Elle y prone l’articulation entre un IDE SQL, des notebooks programmables et un outil de dataviz.
Un “sweet spot” possible est BigQuery + Jupyter + matplotlib/seaborn (ou plotly/dash).
