> For the complete documentation index, see [llms.txt](https://experience-digital-transform.gitbook.io/experience-digital-transformation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://experience-digital-transform.gitbook.io/experience-digital-transformation/analytics/reference-implementation/visualization-subsystem-apache-superset-architecture-and-integration.md).

# Visualization subsystem: Apache Superset architecture and integration

Apache Superset is an enterprise-ready, open-source data exploration and visualization platform originally developed at Airbnb. It democratizes data analytics, allowing users to build interactive dashboards without writing SQL through a semantic layer that translates UI operations into optimized database queries.

## Containerization and driver injection

Deploying Apache Superset on Server #2 is achieved by cloning the official repository and running Docker Compose. Because the default Superset Docker image is lightweight and contains minimal database drivers, adding the ClickHouse driver requires modifying the build sequence.

The Superset Docker Compose deployment handles local dependencies via a dedicated `requirements-local.txt` file inside the `docker/` folder. Python packages specified in this file are installed into the container from PyPI at runtime. This file is ignored by Git, ensuring local configuration isolation.

Historically, the legacy `clickhouse-sqlalchemy` dialect running on top of `clickhouse-driver` (TCP port 9000) was used. However, Superset and ClickHouse official documentation now recommend the modern `clickhouse-connect` driver, which operates over HTTP (port 8123) and provides robust support for native ClickHouse types, SSL, and advanced syntax.

To prepare the visualization environment, run the following commands:

```bash
git clone --depth=1 https://github.com/apache/superset.git
cd superset
echo "clickhouse-connect>=0.13.0" >> ./docker/requirements-local.txt
echo "MAPBOX_API_KEY=" >> docker/.env-non-dev
export TAG=4.0.2
docker compose -f docker-compose-image-tag.yml up -d
```

After container startup, initialize the metadata database, build default roles, and create the admin user:

```bash
SUPERSET_ID=$(docker ps -aqf "name=superset_app")
docker exec -it ${SUPERSET_ID} superset fab create-admin \
  --username admin --firstname Admin --lastname User \
  --email admin@admin.com --password admin
docker exec -it ${SUPERSET_ID} superset db upgrade
docker exec -it ${SUPERSET_ID} superset init
```

The web UI is accessible on port 8088.

## Database connection and SQLAlchemy specifics

In the Superset web UI, navigate to **Data → Databases** to add ClickHouse. When using `clickhouse-connect`, format the SQLAlchemy URI as follows:

```
clickhousedb://<username>:<password>@<IP_CLICKHOUSE>:8123/<database>
```

For secure connections, append `?secure=true` and use port 8443. (Legacy configurations used `clickhouse+native://` on port 9000.) The modern driver supports advanced features (Feature Score 61/201) such as query cancellation, subqueries, cost estimation, and SSH tunneling.

{% hint style="warning" %}
🐛 Be aware of potential label mutation issues in some connector versions. The `_mutate_label` function can append suffixes (e.g. `count_e2942a`) to prevent column collisions. However, when executing sorting operations like `ORDER BY count DESC`, ClickHouse may throw `Error Code 215: DB::Exception: Column 'count' is not under aggregate function and not in GROUP BY`, because the alias does not match the internal aggregate representation. Resolving this requires adjusting SQLAlchemy engine parameters in Superset or modifying the column label mapping to ensure correct query compilation.
{% endhint %}

## Semantic layer, virtual datasets, and caching

Superset's semantic layer lets analysts define virtual metrics and dimensions over ClickHouse tables. For complex SQL queries, analysts can write queries in SQL Lab (a complete SQL IDE with syntax highlighting) and save them as virtual datasets. These datasets support Jinja templating to inject dynamic variables or implement Row-Level Security (RLS) policies depending on user roles authenticated via SSO (e.g. Keycloak or Auth0).

Proper selection of Time Grains is critical. The connector supports standard grains (second, day, month) and extended grains (e.g. `FIVE_MINUTES`, `WEEK_STARTING_MONDAY`), translating them into native ClickHouse functions like `toStartOfFiveMinutes()`. Defining a correct temporal column allows ClickHouse to utilize its primary key index, skipping irrelevant data parts on disk.

To prevent web-server timeouts under high query volumes, enable asynchronous query mode using Celery workers. Query results are cached in an in-memory Redis instance with configurable TTLs based on data volatility, ensuring rapid dashboard rendering.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://experience-digital-transform.gitbook.io/experience-digital-transformation/analytics/reference-implementation/visualization-subsystem-apache-superset-architecture-and-integration.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.