How to query and visualize data from Amazon Athena using Livebook

Mix.install([
  {:kino_db, "~> 0.2.0"},
  {:req_athena, "~> 0.1.2"},
  {:kino_maplibre, "~> 0.1.4"}
])

Accompanying resources

This notebook has an accompanying blog post with step-by-step details on how to use the notebook, if you have any doubts.

Connecting to Amazon Athena using the Database connection Smart cell

Querying Amazon Athena using the SQL Query Smart cell

result =
  Req.post!(conn,
    athena:
      {"""
       CREATE EXTERNAL TABLE IF NOT EXISTS default.stations  (
         station_id string, 
         latitude double, 
         longitude double, 
         elevation double,
         name string)
       ROW FORMAT SERDE 
         'org.apache.hadoop.hive.serde2.RegexSerDe' 
       WITH SERDEPROPERTIES ( 
         'input.regex'='([^ ]*) *([^ ]*) *([^ ]*) *([^ ]*) *(.+)$') 
       STORED AS INPUTFORMAT 
         'org.apache.hadoop.mapred.TextInputFormat' 
       OUTPUTFORMAT 
         'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
       LOCATION
         's3://livebook-blog/amazon-athena-integration/'
       TBLPROPERTIES (
         'typeOfData'='file')
       """, []},
    cache_query: true
  ).body

result2 =
  Req.post!(conn,
    athena: {"select * from default.stations order by station_id", []},
    cache_query: true
  ).body

Visualizing geographic coordinates data using the Map Smart cell

MapLibre.new()
|> MapLibre.add_table_source("result2", result2, {:lng_lat, ["longitude", "latitude"]})
|> MapLibre.add_layer(
  id: "stations",
  source: "result2",
  type: :circle,
  paint: [circle_color: "#000000", circle_radius: 5, circle_opacity: 1]
)