Embedding Superset Dashboards with ClickHouse Data into .NET Web Application: A Step-by-Step Guide

Introduction

The main idea of this article is to show how you can embed your Superset dashboard using a ClickHouse dataset into a .NET web application. For those who don’t know Superset or ClickHouse, I’ll briefly explain what it is and the main objective of both tools.

Superset

Superset is an open-source data exploration and visualization platform designed to make data exploration and visualization easy for everyone, especially within business intelligence and analytics.

ClickHouse

ClickHouse is an open-source, column-oriented database management system designed for real-time analytics and processing of large volumes of data. ClickHouse is known for its high performance, scalability, and efficiency in handling analytical workloads.

Requirements

• .NET knowledge
• Docker

Let’s start

I prefer to start by pulling and configuring containers in Docker.

Create network

This is important because the Superset container needs access to the ClickHouse container.

$ docker network create --driver bridge local-network 

ClickHouse

$ docker run -d -p 8123:8123 -p 9000:9000 \
                -e "CLICKHOUSE_USER=default" \
                -e "CLICKHOUSE_PASSWORD=admin" \
                --network local-network \
                --name my-clickhouse-server \
                --ulimit nofile=262144:262144 clickhouse/clickhouse-server

The port 8123 is used for the HTTP interface and port 9000 is used for native ClickHouse client connections.

Start

Playground

Command-line client

$ docker exec -it my-clickhouse-server /bin/bash
$ clickhouse client

DBeaver

You can use DBeaver or another client of your choice to test the ClickHouse connection.

Create and insert data

Now that you’ve set up ClickHouse, you can use some datasets from the ClickHouse website.

• https://clickhouse.com/docs/en/getting-started/example-datasets

I decided to use the Covid-19 dataset in this project.

Superset

Start

$ docker run -d -p 8088:8088 \
             -e "SUPERSET_SECRET_KEY=$(openssl rand -base64 42)" \
             -e "TALISMAN_ENABLED=False" \
             --network local-network \
             --name superset apache/superset:3.1.0

Create an account

$ docker exec -it superset superset fab create-admin \
              --username admin \
              --firstname Admin \
              --lastname Admin \
              --email admin@localhost \
              --password admin

Configure

# upgrade db
$ docker exec -it superset superset db upgrade

# optional
$ docker exec -it superset superset load_examples

# initialize
$ docker exec -it superset superset init

Login

user: admin | pass: admin

Connection ClickHouse to Superset

To connect the ClickHouse database to Superset you must install a connector and it is necessary to modify/create a file within the Superset container.

# access the container
$ docker exec -it superset /bin/bash

# write and create file
$ echo "clickhouse-connect>=0.6.8" >> ./requirements/local.txt

# check if it's right
$ cat requirements/local.txt

# install the connector
$ pip install -r ./requirements/local.txt

Restart Superset

$ docker restart superset

Connect to the ClickHouse database

Now you are supposed to see the ClickHouse option.

Fill in the blank fields with the necessary information.

So, you are connected :)

Dataset

Menu > Datasets > + Dataset

Then, click on the “Create Dataset And Create Chart” button.

I created a World Map with the average number of new confirmed cases. You can create any chart you want.

Save it and let’s create a new dashboard.

Embedding

To embed our dashboard, we need to follow a few steps:

• Create a guest user and grant the necessary permissions
• Modify the superset configuration file

Create guest user

http://localhost:8088/users/add

Grant permissions

# permissions
- can read on CssTemplate
- can read on Chart
- can read on Dataset
- can read on Dashboard
- can read on Database
- can read on AdvancedDataType
- can read on DashboardFilterStateRestApi
- can get embedded on Dashboard
- can read on EmbeddedDashboard
- can read on Explore
- can read on ExploreFormDataRestApi
- can read on ExplorePermalinkRestApi
- can dashboard on Superset
- can explore json on Superset
- can explore on Superset
- can slice on Superset

Modify the Superset Configuration file

We should modify some properties defined in the superset/config.py file to enable the embedding feature.

# access the container
$ docker exec -it superset /bin/bash

# go to the superset directory
$ cd superset

# use vim or other text editor
$ vi config.py

Edit the following properties:

# properties
- ENABLE_PROXY_FIX = True
- ENABLE_TEMPLATE_PROCESSING = True
- EMBEDDED_SUPERSET = True
- DASHBOARD_RBAC = True
- ENABLE_ADVANCED_DATA_TYPES = True
- ENABLE_CORS = True
- CORS_OPTIONS = { "allow_headers": ["*"], "origins": ["http://localhost:5240", "http://localhost:8088"], "supports_credentials": True, "resources": ["*"] }
- OVERRIDE_HTTP_HEADERS = { "X-Frame-Options": "ALLOWALL" }
- TALISMAN_ENABLED = False
- SESSION_COOKIE_SAMESITE = None

Save the file and restart the container.

# save and quit (vi command)
$ ESC + :wq!

# exit container
$ exit

$ docker restart superset

Go to the dashboard page

Note that the Embed dashboard is enabled

Click on the “Enable Embedding” button

Copy the generated ID, we will use this ID later in the .NET app.

.NET Web application

I created a simple .NET Web MVC application. If you want, you can follow this tutorial directly from the Microsoft website.

Project Structure

appsettings.json

Paste the dashboard embedded ID on “Superset.Dashboards.overview” property.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "Superset": {
    "BaseAddress": "http://localhost:8088",
    "UserAgent": "EmbeddedSuperset/1.0",
    "Dashboards": {
      "overview": "<ID>" // CHANGE ME
    },
    "Endpoints": {
      "Login": "/api/v1/security/login",
      "CsrfToken": "/api/v1/security/csrf_token/",
      "GuestToken": "/api/v1/security/guest_token/"
    },
    "Users": {
      "Admin": {
        "Username": "admin",
        "Password": "admin",
        "Provider": "db",
        "Refresh": true
      },
      "Guest": {
        "FirstName": "Guest",
        "LastName": "User",
        "Username": "guest"
      }
    }
  }
}

Configuration

namespace Presentation.Configuration
{
    public sealed record SupersetConfiguration
    {
        public const string SectionName = "Superset";
        public string BaseAddress { get; set; }
        public string UserAgent { get; set; }
        public IDictionary<string, string> Dashboards { get; set; }
        public EndpointConfiguration Endpoints { get; set; }
        public UserConfiguration Users { get; set; }
    }

    public sealed record EndpointConfiguration
    {
        public string Login { get; set; }
        public string CsrfToken { get; set; }
        public string GuestToken { get; set; }
    }

    public sealed record UserConfiguration
    {
        public AdminConfiguration Admin { get; set; }
        public GuestConfiguration Guest { get; set; }
    }

    public sealed record AdminConfiguration
    {
        public string Username { get; set; }
        public string Password { get; set; }
        public string Provider { get; set; }
        public bool Refresh { get; set; }
    }

    public sealed record GuestConfiguration
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Username { get; set; }
    }
}

Services

This class is responsible for communication with the Superset API.

Superset API documentation: http://localhost:8088/swagger/v1

namespace Presentation.Services
{
    using Microsoft.Extensions.Options;
    using Microsoft.Net.Http.Headers;
    using Presentation.Configuration;
    using System.Net.Http.Headers;
    using System.Text;
    using System.Text.Json;

    public class SupersetService
    {
        private readonly HttpClient client;
        private readonly SupersetConfiguration supersetConfiguration;

        public SupersetService(
          HttpClient client, 
          IOptions<SupersetConfiguration> supersetConfiguration)
        {
            this.supersetConfiguration = supersetConfiguration.Value;

            this.client = client;
            this.client.BaseAddress = new Uri(this.supersetConfiguration.BaseAddress);
            this.client.DefaultRequestHeaders.Add(HeaderNames.Accept, "application/json");
            this.client.DefaultRequestHeaders.Add(HeaderNames.UserAgent, this.supersetConfiguration.UserAgent);
        }

        public async Task<LoginResult> LoginAsync()
        {
            var loginRequest = new
            {
                username = this.supersetConfiguration.Users.Admin.Username,
                password = this.supersetConfiguration.Users.Admin.Password,
                provider = this.supersetConfiguration.Users.Admin.Provider,
                refresh = this.supersetConfiguration.Users.Admin.Refresh
            };

            var response = await this.client.PostAsync(
                requestUri: this.supersetConfiguration.Endpoints.Login,
                content: new StringContent(
                    content: JsonSerializer.Serialize(loginRequest),
                    encoding: Encoding.UTF8,
                    mediaType: "application/json"));

            response.EnsureSuccessStatusCode();

            var content = await response.Content.ReadAsStringAsync();

            return JsonSerializer.Deserialize<LoginResult>(content);
        }

        public async Task<CsrfTokenResult> GetCsrfTokenAsync(string accessToken)
        {
            var httpRequest = new HttpRequestMessage(HttpMethod.Get, this.supersetConfiguration.Endpoints.CsrfToken);

            httpRequest.Headers.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);

            this.client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);

            var response = await this.client.SendAsync(httpRequest);

            response.EnsureSuccessStatusCode();

            var content = await response.Content.ReadAsStringAsync();

            return JsonSerializer.Deserialize<CsrfTokenResult>(content);
        }

        public async Task<GuestTokenResult> CreateGuestTokenAsync(string csrfToken)
        {
            var referer = $"{this.supersetConfiguration.BaseAddress}{this.supersetConfiguration.Endpoints.CsrfToken}";

            this.client.DefaultRequestHeaders.Add(HeaderNames.Referer, referer);
            this.client.DefaultRequestHeaders.Add("X-CSRFToken", csrfToken);

            var guestTokenRequest = new
            {
                resources = this.supersetConfiguration.Dashboards.Select(x => new { id = x.Value, type = "dashboard" }),
                rls = Array.Empty<object>(),
                user = new
                {
                    first_name = this.supersetConfiguration.Users.Guest.FirstName,
                    last_name = this.supersetConfiguration.Users.Guest.LastName,
                    username = this.supersetConfiguration.Users.Guest.Username
                }
            };

            var response = await this.client.PostAsync(
                requestUri: this.supersetConfiguration.Endpoints.GuestToken,
                content: new StringContent(
                    content: JsonSerializer.Serialize(guestTokenRequest),
                    encoding: Encoding.UTF8,
                    mediaType: "application/json"));

            response.EnsureSuccessStatusCode();

            var content = await response.Content.ReadAsStringAsync();

            return JsonSerializer.Deserialize<GuestTokenResult>(content);
        }
    }

    public record LoginResult(string access_token, string refresh_token);

    public record CsrfTokenResult(string result);

    public record GuestTokenResult(string token);
}

Controllers

This controller has only one method, which is responsible for returning all the information necessary to the frontend to render the dashboard.

namespace Presentation.Controllers
{
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.Extensions.Options;
    using Presentation.Configuration;
    using Presentation.Services;
    using System.Net;
    using System.Text.Json.Serialization;

    [ApiController]
    [Route("api/[controller]")]
    public class SupersetController : ControllerBase
    {
        private readonly SupersetService service;
        private readonly SupersetConfiguration supersetConfiguration;
        private readonly ILogger<SupersetController> logger;

        public SupersetController(
            SupersetService service,
            IOptions<SupersetConfiguration> supersetConfiguration,
            ILogger<SupersetController> logger)
        {
            this.service = service;
            this.supersetConfiguration = supersetConfiguration.Value;
            this.logger = logger;
        }

        [HttpGet]
        public async Task<IActionResult> GetAsync()
        {
            try
            {
                var loginResult = await this.service.LoginAsync();
                var csrfTokenResult = await this.service.GetCsrfTokenAsync(loginResult.access_token);
                var guestTokenResult = await this.service.CreateGuestTokenAsync(csrfTokenResult.result);

                var response = new SupersetResult
                {
                    GuestToken = guestTokenResult.token,
                    Dashboards = this.supersetConfiguration.Dashboards,
                    SupersetDomain = this.supersetConfiguration.BaseAddress,
                    Status = HttpStatusCode.OK
                };

                return Ok(response);
            }
            catch (Exception ex)
            {
                this.logger.LogError(ex, ex.Message);

                return StatusCode((int)HttpStatusCode.InternalServerError, new { status = HttpStatusCode.InternalServerError, message = ex.Message });
            }
        }
    }

    public class SupersetResult
    {
        [JsonPropertyName("status")]
        public HttpStatusCode Status { get; set; }

        [JsonPropertyName("domain")]
        public string SupersetDomain { get; set; }

        [JsonPropertyName("token")]
        public string GuestToken { get; set; }

        [JsonPropertyName("dashboards")]
        public IDictionary<string, string> Dashboards { get; set; }
    }
}

Program.cs

...

builder.Services.Configure<SupersetConfiguration>(builder.Configuration.GetSection(SupersetConfiguration.SectionName));
builder.Services.AddHttpClient<SupersetService>();

...

View

Edit your Views/Home/Index.cshtml file to start using the Superset SDK.

• Source: https://unpkg.com/superset-embedded-sdk
• Add a new div to render the superset dashboard (my-superset-container)
• Add scripts section
• Add a style

@{
  ViewData["Title"] = "Home Page";
}

<div class="text-center">
  <h1 class="display-4">Welcome to Superset Embedded Dashboard</h1>
  <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
  <div id="my-superset-container"></div>
</div>

<style>
  iframe {
    width: 100%;
    height: 700px;
    border: 0;
    overflow: hidden;
  }
</style>

@section Scripts {
    <script src="https://unpkg.com/superset-embedded-sdk"></script>
    <script>
        $(document).ready(async function () {
          const supersetResult = await fetchSupersetData();
          supersetEmbeddedSdk.embedDashboard({
            id: supersetResult.dashboards.overview,
            supersetDomain: supersetResult.domain,
            mountPoint: document.getElementById("my-superset-container"),
            fetchGuestToken: () => supersetResult.token,
            dashboardUiConfig: {
              hideTitle: true,
              filters: {
                  visible: false,
                  expanded: false,
              }
            }
          });
        });
        async function fetchSupersetData() {
          // Call the SupersetController
          const response = await fetch("/api/superset", { method: "GET" });
          const supersetResult = await response.json();
          return supersetResult;
        }
    </script>
}

Running :)

Well done!

References

• https://clickhouse.com/docs/en/getting-started/example-datasets
• https://clickhouse.com/docs/en/integrations/superset
• https://clickhouse.com/docs/en/getting-started/example-datasets/covid19
• https://superset.apache.org/docs/databases/clickhouse
• https://superset.apache.org/docs/creating-charts-dashboards/creating-your-first-dashboard
• https://superset.apache.org/docs/api/

Thanks for taking the time to read this article! I hope you have enjoyed it :)

See you, bye!