Get started with Apache Superset and PostgreSQL
Tags: apachesuperset, postgres, dataviz
Published: 2021-08-04 11:54:33

---
title: Get started with Apache Superset and PostgreSQL
published: true
date: 2021-08-04 00:00:00 UTC
tags: apachesuperset,postgresql,dataviz
canonical_url: https://aiven.io/blog/get-started-with-apache-superset-and-postgresql
---
Data visualization can condense huge amounts of raw points into images that can drive business decisions. Apache Superset is emerging as a reliable open source tool for the dataviz world. Read on to find out how to use it to create data visualizations from an existing PostgreSQL table within an Aiven environment.

![Image of a painter drawing a graph](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/begmvtr9thce43pd56f4.png)

"A picture is worth a thousand words" is a common saying in many languages. Similarly, in the data world, a visualization can condense huge amounts of raw points into an image that can be used to create insightful stories driving business decisions. Several tools exist in the dataviz ecosystem, with [Apache Superset](https://superset.apache.org/) getting a lot of traction for its open source status and the advanced list of features it has for data exploration, visualization and sharing.

This blog post shows how to start creating amazing visualizations with Apache Superset building on an [existing PostgreSQL table](https://aiven.io/blog/the-pursuit-of-happiness-with-python-and-postgresql?utm_source=devto&utm_medium=organic&utm_campaign=blog_art&utm_content=post).

## Run Apache Superset on Docker

Docker is a good friend here! It allows us to spin up - in a matter of minutes - complex software infrastructure without needing to understand and implement the setup manually. In cases like this when we're testing a new tool, it's a perfect fit!

We can start with cloning the Apache Superset GitHub repo, and navigating to the `superset` folder from our terminal:

```
git clone https://github.com/apache/superset.git
cd superset
```

Then we can use `docker-compose` to create all the containers needed for Apache Superset:

```
docker-compose -f docker-compose-non-dev.yml up
```

Boom! Our Apache Superset is ready with a nice set of pre-built content we can browse.

Now, let's go to `localhost:8088` in our browser, and login with the safest duo ever (`admin` as user, and `admin` as password).

![Apache Superset default screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kbidh4ge6vmnp08g1p7x.png)

If you're curious, check out the [Apache Superset docker page](https://superset.apache.org/docs/installation/installing-superset-using-docker-compose) for more information.

## Push the dataset to PostgreSQL

On this blog we've already talked about happiness, and [how to push Kaggle's related dataset to PostgreSQL via Python](https://aiven.io/blog/the-pursuit-of-happiness-with-python-and-postgresql?utm_source=devto&utm_medium=organic&utm_campaign=blog_art&utm_content=post). You can use the output mentioned there for the current example, or use your own. The end result in our example is a PostgreSQL instance named `demo-pg`, containing a table called `happiness` with content similar to the one below:

```
survey_yr | country | overall_rank |  score  |  gdp   | generosity | freedom  | social_support | life_exp | gov_trust      
----------+---------+--------------+---------+--------+------------+----------+----------------+----------+----------
       19 | Finland |            1 |  7.7690 | 1.3400 |   0.1530   |  0.5960  |      1.587     |  0.9860  |  0.3930
       19 | Denmark |            2 |  7.60   | 1.3830 |   0.2520   |  0.5920  |      1.573     |  0.9960  |  0.4100
       19 | Norway  |            3 |  7.554  | 1.4880 |   0.2710   |  0.6030  |      1.582     |  1.0280  |  0.3410
```

## Connect Apache Superset to PostgreSQL

Apache Superset supports a [wide range of source databases](https://superset.apache.org/docs/databases/installing-database-drivers), and for PostgreSQL it [uses the psycopg2 library](https://superset.apache.org/docs/databases/postgres). The PostgreSQL support comes out of the box with the default Docker setup.

All we need to do is go to the Apache Superset UI, and define a new datasource pointing to where our PostgreSQL database is:

1. Select **Data**, and then **Databases**.
2. Click the **+ DATABASE** button.
3. At the bottom of the modal window, choose **Connect this database with a SQLAlchemy URI string instead**.
4. Insert `demo-pg` as **DISPLAY NAME** (or your own instance name).
5. Use the PostgreSQL URI as **SQLALCHEMY URI**. You can find the PostgreSQL URI in Aiven's console, in the service detail view, under the **Overview** tab.
6. Test that all the settings are correct by checking your connection, and making sure you get a nice `Connection looks good!` message.
7. Click **ADD** to persist our `demo-pg` database definition.

![demo-pg database entry](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/weitmw02pvezuatql86r.png)

Now, it's time to include the `happiness` table.

1. Back in the Apache Superset UI, switch to the **Datasets** tab.
2. Create a table by filling the **DATASOURCE** with `demo-pg`, the **SCHEMA** with `public` and the table with - no surprises - `happiness`.
3. Click **ADD** to persist the datasource definition.

Ta daaah!

![happiness dataset entry](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i8lkmbjrkdflg5iuv8ye.png)

## Visualize data

Now we are ready to use our artist skills, and create visualizations of our data: representing data this way makes it much easier to process.

1. Back in the Apache Superset UI, let's navigate to **Charts** tab.
2. Click **+CHART**.
3. In the popup window, select the `public.happiness` dataset we created before, and **Pivot Table v2** as visualization.

Let's use the dataset, and create a heatmap of the top 10 happy countries over the years. We can do that with the following configuration:

* `country` for the **ROWS**
* `survey_yr` for the **COLUMNS**
* For the **METRIC** select a **SIMPLE** calculation based on the `overall_rank` column with `AVG` as aggregated function.
* In the **FILTERS** section, add a **SIMPLE** filter based on the `overall_rank` metrics being `<=` than `10`.
* At the bottom, set the **AGGREGATION FUNCTION** as `Average`, this will drive the overall row/column aggregation in the pivot table.

Check out the **Query** parameters overview:

![example of query panel with settings](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ufcom0wv0j4tznzj3q0c.png)

Now, in the **CUSTOMIZE** tab set the following options:
* For **PIVOT TABLE TYPE** select `Table Heatmap`
* For the **ROWS SORT BY** select `value ascending`
* Deselect the **SHOW COLS TOTALS**
* Name the visualization `Country ranking heatmap`

We're ready to press the **RUN** button above **CUSTOMIZE**.

Aaaaand, we end up with a lovely shaded red heatmap showing the countries that have been included in the top 10 at least once in the previous 5 years, ordered by their overall average position.

![Pivot table with heatmap containing the country overall ranking](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jyybl9545r026oduyjqo.png)

To see where these countries are around the world, the same data could be used to create a map.
Try the following settings:

* **World Map** as visualization type
* `country` as **COUNTRY COLUMN**
* `Full Name` as **COUNTRY FIELD TYPE**
* Use a **SIMPLE** calculation based on the `score` column with `AVG` aggregation for the **METRIC FOR COLOR** parameter
* Choose the **COUNTRY COLOR SCHEME**, the screenshot shows `red/yellow/blue` but the choice is yours.

The resulting map clearly indicates that lot of work needs to be done to raise happiness levels in the Global South.

![Picture of a world heatmap](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cotz7wblrb6vsyzkdunj.png)

## Add calculated fields in the dataset

Sometimes the source dataset doesn't contain all the required fields. This is the case in our example too, with the `survey_yr` field containing only the last two digits of the year (ie. `18`, `19`), hence not being recognized by Apache Superset as timestamp, which is stopping us from using any trend visualization.

Fear not! Apache Superset allows us to change the shape of our dataset, without any modification to the original table, by adding calculated columns.

1. In the Apache Superset UI, switch to the **Datasets** panel.
2. Click the **Edit** pencil icon under the **Action** section of the **happiness** dataset.
3. Open the **CALCULATED COLUMNS** tab and create a new item, with the following settings:
       * `2000+survey_yr` as **SQL EXPRESSION**
       * **LABEL** set to `Year`
       * `DATETIME` as **DATA TYPE**
       * `%Y` as **DATETIME FORMAT**
       * **Is temporal** checkbox selected.

Now the `Year` column is available, enabling us to create the trend visualizations like the linechart of the top 5 positions over time.

![Picture of top 5 trendline](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zhvnus04jatd8d6nocwh.png)

Challenge yourself, and try creating this chart yourself as an Apache Superset.
A word of caution: Finland is a pretty happy place - and we think so too!

## Wrapping up

A dataset is only useful when stored properly, and made available for queries. It becomes meaningful when insights are discovered and communicated across the company. The combination of PostgreSQL and Apache Superset offers a best-in-class solution for data storage, discovery and visualization enabling companies to be effective and data-driven.

Some more info:
* [Aiven for PostgreSQL](https://aiven.io/postgresql?utm_source=devto&utm_medium=organic&utm_campaign=blog_art&utm_content=post): to get your managed PostgreSQL instance
* [Apache Superset Documentation](https://superset.apache.org/docs/intro): to browse the configuration details
* [Apache Superset Gallery](https://superset.apache.org/gallery): to check what's possible with Apache Superset