Livebook has built-in integrations with many data sources, including Amazon Athena.

In this blog post, you'll learn how to use Livebook to connect to Amazon Athena, execute a SQL query against it, and visualize the data.

You can also run this tutorial inside your Livebook instance by clicking the button below:

Connecting to Amazon Athena using the Database connection Smart cell

To connect to Amazon Athena, you'll need the following info from your AWS account:

• AWS access key ID
• AWS secret access key
• Athena database name
• S3 bucket to write query results to

Now, let's create an Amazon Athena connection using a Database connection Smart cell. Click the options "Smart > Database connection > Amazon Athena":

Once you've done that, you'll see a Smart cell with input fields to configure your Amazon Athena connection:

Fill in the following fields to configure your connection:

• Add your AWS access key ID
• Add your AWS secret access key
• Add your Athena database name
• Add your S3 bucket in the "Output Location" field

Now, click the "Evaluate" icon to run that Smart cell. Once you've done that, the Smart cell will configure the connection and assign it to a variable called conn.

Querying Amazon Athena using the SQL Query Smart cell

Before querying Athena, we need to have an Athena table to query from. So, let's create one.

We'll create an Athena table based on a public dataset published on AWS Open Data. We'll use the GHCN-Daily dataset, which contains climate records measured by thousands of climate stations worldwide. Let's create an Athena table called stations with metadata about those climate stations.

To do that, Add a new SQL Query Smart cell by clicking the options "Smart > SQL Query":

Copy and paste the SQL code below to the SQL Query cell:

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')

Now, click the "Evaluate" icon to run that Smart cell. Now we have an Athena table to query from.

Add a new SQL Query Smart cell and copy and paste the following SQL query to it:

select * from default.stations order by station_id

Evaluate that cell. It will query your Athena table and assign the results to a result2 variable. You'll see the result of that query in a tabular format like this:

Visualizing geographic coordinates data using the Map Smart cell

Notice that the table we created has each climate station's latitude and longitude information. We can visualize that data with a map visualization using the Map Smart cell. Let's do that.

Add a Map Smart cell by clicking the options "Smart > Map":

Your Map Smart cell will look something like this:

Use your Map Smart cell to configure:

• the layer name
• the data source
• the coordinates format
• the longitude field
• the latitude field

Once you've configured the Smart cell, you can evaluate it, and it will build a map for you. It will look something like this:

That's it! Using Livebook Smart cells, you can connect to an Amazon Athena database, execute a SQL query against it and visualize the result.

Good news, SpawnFest will have a specific category for Livebook-based projects this year!

SpawnFest is an annual 48-hour online software development contest in which teams from around the world get exactly one weekend to create the best BEAM-based applications they can.

Over the years, we've seen many fantastic projects coming out of SpawnFest. We're excited to partner with them this year to bring that energy to the Livebook community. We're sponsoring a category for Livebook-based projects. The idea is to incentivize the community to develop more ideas on how to use Livebook while also supporting a community event.

There are two kinds of projects you'll be able to build:

• a Livebook notebook
• a Livebook Smart Cell

You could build all sorts of cool stuff using a Livebook notebook. Here are a few ideas for what to build with a Livebook notebook:

• integrating and playing with APIs
• data exploration and visualization
• machine learning models
• interactive data apps

With Smart Cells, you can build a custom notebook cell that could be reused by your notebooks or other community members.

If you want to see some examples of custom Smart Cells, here's a Smart Cell that allows querying the GitHub GraphQL API. And here's another one that allows connecting to a remote node in an Elixir cluster and optionally send a :erpc call.

And if you'd like a few ideas of what to build using a Smart Cell, here are a few:

• a Smart Cell that sends a message to Slack
• a Smart Cell where you could fill in some input fields, and then it sends an HTTP request to some API
• a Smart Cell that builds a connection to a database, service, or server

How to get started with Livebook

If you haven't used Livebook yet, it's easier than ever to get started because of our new desktop app. You can download and install it in a couple of minutes.

Once you have installed it, you can use the built-in notebooks to learn how to use Livebook. You can do that by opening Livebook and going to Explore section:

In the Explore section, you'll find many notebooks that will help you to learn how to use Livebook. We currently have builtin notebooks about:

• learning Elixir
• how to use Livebook itself
• how to create charts (using VegaLite)
• how to plot maps using geospatial and tabular data (using MapLibre)
• how to build interactive notebooks (using Kino)
• how to build a custom Smart Cell

This year SpawnFest will be on October 15th and 16th. Visit the event's website to learn more about it and how to register.

We're looking forward to seeing what you'll build using Livebook! =D

Querying and visualizing data from a database is a common and recurring task. That's the kind of thing you don't want to repeat yourself, writing the same code repeatedly. That's where Livebook Smart cells come in. It helps you to automate any workflow you want.

Livebook has built-in Smart cells that help you query and visualize data from multiple databases, like PostgreSQL, MySQL, Google BigQuery, AWS Athena, and SQLite.

This article explains how to use Livevook Smart cells to query and visualize data from a Google BigQuery dataset.

If you like video format, here's a video version of this article:

You can also run this tutorial inside your Livebook instance:

Connecting to Google BigQuery using the Database connection Smart cell

Before connecting to a Google BigQuery dataset, you need to create a Google Cloud service account and a service account key. Follow the steps in this guide and download the generated service account key JSON file. That file contains the info needed to configure a connection to Google BigQuery.

Now, create a new notebook. Then, let's create a Google BigQuery connection using a Database connection smart cell. Click the options "Smart > Database connection > Google BigQuery":

Once you've done that, you'll see a Smart cell with input fields to configure your Google BigQuery connection:

Configure your Google BigQuery connection by following the instructions inside the Smart cell:

• Add your Google Cloud Project ID
• Upload your JSON credentials file

Pro tip: if you have an authenticated gcloud CLI in the machine running your Livebook instance, then Livebook will automatically pick up your Google Cloud credentials and use it for access. No need to upload the JSON file. 😉

Now, click the "Evaluate" icon to run that Smart cell. Once you do that, the Smart cell will configure the connection and assign it to a variable called conn.

Querying Google BigQuery using the SQL Query smart cell

Now, let's use your Google BigQuery connection to query a Google BigQuery public dataset.

Add a new SQL Query smart cell by clicking the options "Smart > SQL Query":

Copy the following query to the cell:

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

And execute the cell. The Smart cell will execute the query and assign its result to a variable called result. It also will show you the result of the query in a table format, like this:

Visualizing data from Google BigQuery using the Chart smart cell

Now we can visualize the result from that query using a Chart smart cell.

Add a new Chart smart cell by clicking the option "Smart > Chart":

Your newly created Chart smart cell will look something like this:

Now, let's use that cell to visualize the results from our query.

The query we wrote returned the population per year per country from the International Census Data, which is published as a Google BigQuery public dataset. We can use that data to visualize how the world population has been growing over the years.

Use your Chart smart cell to configure:

• the chart's title
• the chart's width
• the chart's type
• the x-axis and its type
• the y-axis, its type, and aggregate

Once you've configured the cell, you can evaluate it, and it will build a chart for you. It will look something like this:

That's it! Using Livebook Smart cells, you can connect to a Google BigQuery dataset, execute a SQL query and visualize the results with a chart. And since this is quite a common task, Smart cells enable you to do that without writing a single line of code! 

And, if you need more customization in any part of the process, easy peasy. You can easily convert a Smart cell to a code cell and edit the code generated for you. To do that, click in a Smart cell, and then click the "Convert to Code cell" icon:

When you click the "Convert to Code cell" icon, it will transform your Smart cell into a Code cell. You'll be able to see the code that was running behind the Smart cell and edit it as you like:

We want Livebook to be accessible to as many people as possible. Before this release, installing Livebook on your machine could be considered easy, especially if you already had Elixir installed.

But imagine someone who's not an Elixir developer. They had to either install Docker or Elixir before getting started with Livebook. And if they are in their first steps as developers, even using a terminal could be demanding.

That's why we built Livebook desktop. It's the simplest way to install Livebook on your machine.

Livebook desktop doesn't require the person to have Elixir previously installed on their machine. And it works both on the Mac and Windows.

We hope that the desktop app enables way more people to try Livebook. 

For example, let's say you want to help your friend to start learning Elixir. You can tell that person to download Livebook desktop and follow the built-in "Distributed portals with Elixir" notebook, a fast-paced introduction to Elixir.

Or maybe for the data scientist that heard about Livebook and wants to try it. That person can download Livebook desktop and learn how to do data visualization in Livebook with the built-in "Plotting with VegaLite" and  "Maps with MapLibre" notebooks.

If you want to try the Livebook desktop app, it's just one click away.

We hope you enjoy it! 😊

Livebook v0.6 is out with a number of exciting features! Join us, as we go from a database connection to charting the data in a few simple steps:

Below is a quick overview of the biggest features in this new version.

Smart cells

Livebook v0.6 introduces a new type of cells to help you automate and master entire coding workflows! Smart cells are UI components for performing high-level tasks, such as charting your data:

We already have a couple Smart cells - including connection to PostgreSQL and MySQL - but the best part is that anyone can create new Smart cells and share with the community. We can't wait to automate workflows from HTTP requests to Machine Learning models!

Package search

The new version also brings a much more integrated dependency management, including the new package search and the setup cell:

Error highlighting

You will also find better error reporting! Spot typos and syntax errors right away with the new squiggly lines:

Streamlined diagrams

In the previous release we introduced support for Mermaid.js diagrams in Markdown cells. It's now even easier with the new Diagram button:

There's more

There are many other notable features, including "code zen" and persistent configuration. Check out the technical release notes for the complete list of changes.

Livebook v0.5 is out with a number of goodies! We have recorded a video showing how to use those features to build chat apps, multiplayer games, and more:

In case you can't watch it, here is a rundown of the biggest features.

Flowcharts with Mermaid.js

Many teams are using Livebook for documentation. v0.5 improves on this use case by allowing users to embed Mermaid.js diagrams and visualizations:

Define your Mermaid.js definitions inside ```mermaid blocks and you are good to go!

Custom widgets

You can now add your own widgets to Livebook, known as Kinos in Livebook terminology. Build chat apps, multiplayer games, interactive maps, and more! It doesn't matter what is your domain area, you can now make Livebook as powerful as you want.

We have revamped our Explore guides to include a complete course on Kino with several examples:

Other improvements

• You can now increase the editor font-size and pick a high-contrast theme. We also improved the contrast and accessibility in several places.
  
  
• The code editor now supports auto-completion of structs fields and shows additional metadata about functions, such as which version they were added and deprecation notices.

Check out the technical release notes for the complete list of changes.

We are glad to announce Livebook, an open source web application for writing interactive and collaborative code notebooks in Elixir and implemented with Phoenix LiveView. Livebook is an important step in our journey to enable the Erlang VM and its ecosystem to be suitable for numerical and scientific computing.

José Valim has recorded a screencast that highlights some Livebook features, which you can watch below. It also showcases the Axon library, for building Neural Networks in Elixir, as well as some improvements coming in Elixir v1.12:

Features

If you can’t yet watch the video, here is a summary of Livebook features:

• A deployable web app built with Phoenix LiveView where users can create, fork, and run multiple notebooks.
  
  
• Each notebook is made of multiple sections: each section is made of Markdown and Elixir cells. Code in Elixir cells can be evaluated on demand. Mathematical formulas are also supported via KaTeX.
  
  
• Persistence: notebooks can be persisted to disk through the .livemd format, which is a subset of Markdown. This means your notebooks can be saved for later, easily shared, and they also play well with version control.
  
  
• Sequential evaluation: code cells run in a specific order, guaranteeing future users of the same Livebook see the same output. If you re-execute a previous cell, following cells are marked as stale to make it clear they depend on outdated notebook state.
  
  
• Custom runtimes: when executing Elixir code, you can either start a fresh Elixir process, connect to an existing node, or run it inside an existing Elixir project, with access to all of its modules and dependencies. This means Livebook can be a great tool to provide live documentation for existing projects.
  
  
• Explicit dependencies: if your notebook has dependencies, they are explicitly listed and installed with the help of the Mix.install/2 command in Elixir v1.12+.
  
  
• Collaborative features allow multiple users to work on the same notebook at once. Collaboration works either in single-node or multi-node deployments - without a need for additional tooling.