Premiers pas avec Google BigQuery

Activation de GCP

Interface de requêtage de Google BigQuery
Top 10 des prénoms US par année

Utilisation de BigQuery avec un notebook Jupyter

  1. Se créer un compte de service GCP à partir de l’espace IAM et admin.
  2. Télécharger à partir de se son compte de service un fichier JSON qui contient les clés d’authentification.
  3. 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.
# read credentials
from google.oauth2 import service_account
credentials = 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 bigquery
client = bigquery.Client(credentials=credentials, project=credentials.project_id,)
# 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()
# 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
var = df.set_index('project')
order = var.loc[var['year']==2020, 'downloads'].sort_values(ascending=False).index
fig, 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'));
Nombre de téléchargements des projets par année
  1. Il va falloir effectuer une jointure entre les tables languages et licenses avec repo_name comme clé commune.
  2. 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
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)*100
sns.heatmap(tab, cmap='Blues', cbar_kws={'format': '%.0f%%'})
plt.xticks(rotation=45, ha='right');
Les licences logicielles en fonction des langages
Les licences logicielles agrégées en fonction des langages
# 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
Mention des librairies dans stackoverflow par année
  • 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.

Conclusion

Le “sweet spot” selon Taylor Brownlow

--

--

Python & Data Science — Expertise & Audit du SI — Bioinformatics

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Francis Wolinski

Francis Wolinski

Python & Data Science — Expertise & Audit du SI — Bioinformatics