Quick tip: Connecting Apache Superset to SingleStoreDB

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. 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 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:

CREATE DATABASE IF NOT EXISTS 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.

Create a Preset account

We'll create a free account 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: <host>
• PORT: 3306
• DATABASE NAME: crimes_demo
• USERNAME: admin
• PASSWORD: <password>
• 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.

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.

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.

Figure 4. BTP crime type and count.

Figure 5. BTP crime word cloud.

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.