Introduction

how-to

The Couchbase Apache Superset Connector lets you visualize data from Tabular Analytics Views (TAV) in Apache Superset.

It works by connecting Capella Columnar to Apache Superset using SQLAlchemy, allowing you to create interactive visualizations from your tabular data.

Install the Couchbase SQLAlchemy Connector

To install the Couchbase SQLAlchemy Connector, open the command prompt (cmd), and enter the following:

pip install couchbase-sqlalchemy

Install Apache Superset

Download and install Apache Superset following the official documentation.

Setting a Secure Connection

Couchbase strongly recommends that you secure your Superset connection using SSL. This ensures encryption of the communication between Superset and the Couchbase Server/Capella.

Capella Columnar

For details on creating a Capella Columnar instance, first see Creating a Capella Columnar Cluster.

You’ll need to access the Capella administration console to get the connection string for your Columnar database.

Sign in to your Capella instance as an Organization owner or Project owner.
Select Columnar from the top-level page menu.
Select your Columnar cluster from the list.
Select Settings from the top-level page menu.
Select Connection String from the left-hand menu.
Make a note of the connection string.

Next, you need to add the IP address of the machine from which you’re running Superset, so that Capella allows the machine to access the Columnar data.

Click on Allowed IP Addresses in the left-hand menu.
Click the Add Allowed IP button.
Enter the IP address of the Superset host machine.

You can use the Add Current IP Address button to fill in the address of the machine currently running the web console.

Now, you will need to create a user account for Superset to access the columnar data.

Return to the Settings page, then click on Access Control in the left-hand menu.
Click on Create Account.
Add a username and password for the new account.
Make sure you set assign sys_view_reader to the list of roles.
Click Create Account to finish setting up the user account.

Configure the Connection in Superset

Launch Apache Superset and set up a new database connection. Select Couchbase as the database type and provide the necessary connection details.

Capella Columnar

Open the Superset portal.
Add Database Connection:
Go to: Settings → Database Connections → Add Database
Select Couchbase from the list of databases.

Fill connection details:

Host	Fill in the connection string you copied here.
Port	Not required.
Database Name	Optional; specify the database name if needed.
Username	Enter the username you created in the User Account section.
Password	Enter the password you allocated to the user here.
Display Name	Enter a name for this connection.
Additional Parameters	Not required.
SSL	Must be enabled.

Create Tabular Analytics View

In Couchbase Analytics, define Tabular Analytics Views. These views act as non-materialized views, specifying schemas along with primary and foreign keys for collections or subqueries.

Use the Couchbase Superset Connector

Create an External Collection using video_games_sales.json file into Columnar.

On querying Select * from Default.Default.video_game_sales limit 1.

[
  {
   "video_game_sales": {
     "_columnar_ID": "691c68ce-1dd3-9e94-7a47-c041eb87fe8c",
     "rank": "74",
     "name": "Animal Crossing: New Leaf",
     "platform": "3DS",
     "year": "2012",
     "genre": "Simulation",
     "publisher": "Nintendo",
     "na_sales": "2.01",
     "eu_sales": "2.32",
     "jp_sales": "4.36",
     "other_sales": "0.41",
     "global_sales": "9.09"
   }
 }
]

To create a tabular analytics view from the video_game_sales collection, execute the following command:

CREATE OR REPLACE ANALYTICS VIEW
video_game_sales_1 (_columnar_ID string, rank int, name string, platform string, year string, genre string, publisher string, na_sales double, eu_sales double, jp_sales double, other_sales double, global_sales double)
DEFAULT NULL PRIMARY KEY (__id)
NOT ENFORCED AS
SELECT video_game_sales.* FROM video_game_sales;

Visualization

To visualize the data, follow these steps in Apache Superset:

Navigate to the Dataset section in the Superset dashboard.
From the DATABASE drop-down, choose the Display Name of the Couchbase connection you configured in Superset.
Choose Default as the schema.
Look for the view named video_game_sales_1 to proceed with the data visualization.

Sample Chart 1 - Bar Chart Legacy

Sample Chart 2 - Tree Map