Quick tip: Connecting Apache Superset to SingleStoreDB
Tags: singlestoredb, apachesuperset, visualization
Published: 2022-10-13 03:11:20

## Abstract
Apache Superset is an open-source data exploration and visualisation tool. A quick way to test it with SingleStoreDB without installing it is to use [Preset](https://preset.io/). We can set up a connection from Preset to SingleStoreDB Cloud in a few minutes. In this article, we'll see how.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **Crimes Demo Group** as our Workspace Group Name and **crimes-demo** as our Workspace Name. We'll make a note of our **password** and **host** name. Finally, we'll create a new database using the **SQL Editor**:

```sql
CREATE DATABASE crimes_demo;
```

We'll also need some data, and we can use the BTP CSV file we downloaded from the Police UK website, as described in a [previous article](https://dev.to/veryfatboy/quick-tip-using-ubers-h3-to-visualise-british-transport-police-crime-data-1e0c).

## Create a Preset account
We'll create a free [account](https://manage.app.preset.io/starter-registration/) on the Preset website. Once logged in, we'll create a new Workspace. Clicking on the Workspace will then take us to a Workspace setup guide consisting of five steps:

1. Create your workspace
2. Connect your data
3. Create a chart
4. Create a dashboard
5. Invite teammates

## Create a SingleStoreDB connection
The **Create your workspace** is complete, and clicking on **Connect your data** displays a **Connect a database wizard**. We'll select MySQL, and then we are asked to complete a form with various database connection details. Here is what we need to enter:

- **HOST:** &lt;host&gt;
- **PORT:** 3306
- **DATABASE NAME:** crimes_demo
- **USERNAME:** admin
- **PASSWORD:** &lt;password&gt;
- **DISPLAY NAME:** SingleStoreDB
- **SSL:** Option is enabled

We'll replace the `<host>` and `<password>` with the values from our SingleStoreDB Cloud account.

Next, we'll click **CONNECT**. If the connection is successful, it will appear in the list of databases. Several options, such as **DML** and **CSV upload**, will be disabled. We can change these settings by hovering our mouse in the **Actions** column to reveal an **Edit** icon, as shown in Figure 1.

![Figure 1. Actions > Edit.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/doagvbd3xke6j251q1hv.png)

<center>Figure 1. Actions > Edit.</center>

Selecting **Edit** will display a form with tabs for **BASIC** and **ADVANCED** settings. Selecting **ADVANCED** allows us to modify settings in four sections:

1. SQL Lab
2. Performance
3. Security
4. Other

Various check boxes are presented under these four sections, and we can modify the settings to suit our needs. However, for complete control, we'll need to select **Expose database in SQL Lab**. The option to **Allow file uploads to database** will enable us to upload our BTP CSV file. When ready, we'll click **FINISH**.

## Upload CSV file data to SingleStoreDB
On the databases page, there is an option to upload a CSV file, as shown in Figure 2.

![Figure 2. Upload CSV.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k5ftey1s09la7n6hwwgm.png)

<center>Figure 2. Upload CSV.</center>

Selecting **Upload CSV** displays a form with a large number of fields. We only need to complete these fields:

- **Table Name:** crimes
- **CSV File:** Select **Choose file** and locate the BTP CSV file.

Next, we'll scroll to the bottom of the page and click **SAVE**.

In the SingleStoreDB cloud, we'll also see a new table called **crimes** in our **crimes_demo** database.

## Bonus: Create visualisations

On our **Datasets** page, we should see a new row labelled **crimes**. Selecting **crimes** displays a page that allows us to create new charts. We can experiment with our data and create a range of visualisations. A few examples are shown in Figures 3, 4 and 5.

![Figure 3. BTP crime location map.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5z07g9j9q66bpv8i9or7.png)

<center>Figure 3. BTP crime location map.</center>

![Figure 4. BTP crime type and count.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/chr7za8aihwklez7nkc8.png)

<center>Figure 4. BTP crime type and count.</center>

![Figure 5. BTP crime word cloud.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hl8t258qzaokpf5eejop.png)

<center>Figure 5. BTP crime word cloud.</center>

Charts can be saved and used on Dashboards.

Data exploration is also possible using **SQL > SQL Lab**.

## Summary
Connecting Apache Superset to SingleStoreDB is easy and can be achieved in a few minutes. Apache Superset also provides several widely-used data visualisation tools and can provide quick insights into data.