livebook_bigquery.livemd

How to query and visualize data from Google BigQuery dataset using Livebook smart cells in less than 5 minutes

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

Section

credentials = %{
  "auth_provider_x509_cert_url" => "https://www.googleapis.com/oauth2/v1/certs",
  "auth_uri" => "https://accounts.google.com/o/oauth2/auth",
  "client_email" => "[email protected]",
  "client_id" => "116830676421197430315",
  "client_x509_cert_url" =>
    "https://www.googleapis.com/robot/v1/metadata/x509/livebook%40livebook-355621.iam.gserviceaccount.com",
  "private_key" =>
    "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCqZBD249avZuEx\nEyVaX1/CnAQCzOkg1t16uNEMkAknrfImKLZQ397s8d67BbmGFALRGM0I8a9lm1o5\nov41zA5M5AdZPMbzq6Cldm8WSxVkQi88QIHBDMEYn/h+tijnkIlycmEBVXsyk/Uo\n3yhdr7C5cHoOizeSCwf9e6A/Gf1h1rq3PEyBk0Awv/9IDe+BjLNiJbm8SIZDSpCb\nmFQfBLeVWfhuGdZ5IhctMutLnev0GxklGBFKxi0DXrBICr6PJjgSZxlTqbAWwMBG\njR8wuS+Se/HKwgf1qE0hy7hyUCaAxvU9zKJfAfQVWJ97+vjH22kyi4OE4BRx95Ya\n6hazQ4LJAgMBAAECggEAA5oTqzpn/4N123EXiyxktHMSmSIOhAuSuT1GqwSw8too\nGiMmbnl7B4h54Dt332QVJqkm7WtZfTT+YGVICShX/Ytc9eeVX5GHVvQU11RtPnYx\nNC17aLKtssjv6nLMlM+O/kC5ePse/HPY/P0RqABWpKbiZ6QM/8z0pWi7e5X/r5We\n/YpBocT+APH5PyJjAB29VfbWFX12euAbjBzZh6Wb1p1yQKG1jLH0pRar8XRcbAr0\njVRdIGiTWdt3MdUQZS40qWanoGC6kqwtpXQdhFkyTGa3usLbdTkJR+GXNHyy/GkL\nXyI0yh7KrB4EkDJMB0s/uNG9dAZCV6O9zg0BR4JXoQKBgQDXTPTuVFq4+tpfCEnR\nQpDoTODd7tWzAQSkOjHnN17FsxjHBbWWleYNAjD1sPS3/ZT5Erg6gLZZ16L1upEq\nYofBdsHHdxX+bbm62R6NkihNOhwecCqZdBVdPgTtV7BcXHju5H8MieLirB2bTIKz\noo84qnqqI8X0sM8htPpY7RxaYQKBgQDKmcr5XspMUjJepxV4E0h9LjrNSFjAq+LY\nZ+L99rS1Hd2GTaCqKHuRH78VYysho/4mfJi3KdY3MxD5t9AQQpcOBYoydv5fJdP7\n6N4pruFU/ODgIZd14gLTQ0/1hX7Jc6RuHs2uKgQmhEo0cdWXqNx7iun4ZdgcN1Bs\n3DxSJ+IRaQKBgQCs+yDDJ7UocCvpNLCSV8gzTClTRqQdyRCgnj0gB0W209lxdi3a\nih8mjn8lt4I3HZ+GqrqoDmf0MSO2CKq/giCOmMrCZvSd0izqaa15k1ecDLcw4W/o\nkPLlUoIE63JAEu/7CEznlxVah2zWLJfKmi42GgqqDknq+e/BpjWKIx+noQKBgCsa\nczOAtjtslWjL1WZ5e8eeKUZ5aOB88UMnPwTXEvUhiHrFUzRAbqGehnHPamK62Bxq\nD7eQQBH4XFk0gSCkeiyygwdfh5jInIQQtAtcOHZzYohsvSOIVR+LR5ytrR7A6q++\n//VY3Q8wv/TObynX9CiYklSVnlfKcOLoLDEyaQdZAoGAMYoEz7VZ49RrqihgAmGi\nhbp6g3bc+QqSdvyoObFbd3Jrmp0mGcLwKqZpg9i/FN+4rtODoj0+DVnMURmA+HP+\nBTMe3kYVf41rztIbx23rQ0zVM63Xp0TYC7Ovvgqg3iEL5/97gD2pHkvdkj8ucS11\nq763mbFq4Ldy8sIQT0AZFsg=\n-----END PRIVATE KEY-----\n",
  "private_key_id" => "d39c92d74b81ef64b148738d80f08e2023f841af",
  "project_id" => "livebook-355621",
  "token_uri" => "https://oauth2.googleapis.com/token",
  "type" => "service_account"
}

opts = [
  name: ReqBigQuery.Goth,
  http_client: &Req.request/1,
  source: {:service_account, credentials}
]

{:ok, _pid} = Kino.start_child({Goth, opts})

conn =
  Req.new(http_errors: :raise)
  |> ReqBigQuery.attach(
    goth: ReqBigQuery.Goth,
    project_id: "livebook-355621",
    default_dataset_id: ""
  )

:ok

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

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)