How to query and visualize data from Google BigQuery using Livebook

Mix.install([
  {:kino_db, "~> 0.2.0"},
  {:req_bigquery, "~> 0.1.0"},
  {:kino_vega_lite, "~> 0.1.4"}
])

Accompanying blog post and video

This notebook is an accompanying material of a blog post from Livebook’s blog.

If you need more details about the topic covered in this notebook, you can read the blog post or you can watch the video.

Creating and configuring a connection to Google BigQuery

There are two parameters needed to configure a connection to Google BigQuery:

• Google cloud project ID
• A JSON file with your service account credentials

To run this notebook, you’ll need to provide your configurations. If you need help with that, you can follow this guide.

Querying a Google BigQuery dataset

After you have configured your connection, you can use that connection to query a Google BigQuery dataset.

Google BigQuery provides multiple public datasets. We’ll use one of those in this example.

result =
  Req.post!(conn,
    bigquery:
      {"""
       select t.year, t.country_name, t.midyear_population
       from bigquery-public-data.census_bureau_international.midyear_population as t
       where year < 2022
       order by year
       """, []}
  ).body

Visualizing the data

The dataset we queried has the yearly population numbers from multiple countries worldwide. We’ll create a chart to visualize how the world population is changing over the years.

VegaLite.new(width: 700, title: "World population")
|> VegaLite.data_from_values(result, only: ["year", "midyear_population"])
|> VegaLite.mark(:bar)
|> VegaLite.encode_field(:x, "year", type: :quantitative)
|> VegaLite.encode_field(:y, "midyear_population", type: :quantitative, aggregate: :sum)