03---Samples

ClickHouse Integration Samples

This document contains minimal example artifacts for the ClickHouse integration.

1. Migration layout

migrations/
  development/
    global/
      V0001__create_ops_migration_tables.sql
    tenants.txt
    tenants/
      ach/
        bootstrap/
          V1000__create_tenant_database.sql
          V1001__create_tenant_access.sql
        schema/
          V2000__create_batterydata.sql
          V2001__create_reservationdata_tables.sql

2. Global migration

CREATE DATABASE IF NOT EXISTS ops;
 
CREATE TABLE IF NOT EXISTS ops.schema_migrations_global
(
    version    String,
    checksum   String,
    applied_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY (version);
 
CREATE TABLE IF NOT EXISTS ops.schema_migrations_tenant
(
    tenant_db  String,
    version    String,
    checksum   String,
    applied_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY (tenant_db, version);

3. Tenant bootstrap

Database

CREATE DATABASE IF NOT EXISTS tenant_ach;

Access

CREATE ROLE IF NOT EXISTS role_tenant_ach;
CREATE USER IF NOT EXISTS user_tenant_ach;
GRANT role_tenant_ach TO user_tenant_ach;
GRANT SELECT, INSERT, ALTER, CREATE, DROP ON tenant_ach.* TO role_tenant_ach;
 
-- Set tenant user password after apply:
-- ALTER USER user_tenant_ach IDENTIFIED BY 'replace-with-strong-secret';

4. Tenant schema

Example table

CREATE TABLE IF NOT EXISTS tenant_ach.batterydata
(
    ingest_uuid UUID DEFAULT generateUUIDv4(),
    time        DateTime64(3, 'UTC'),
    field       LowCardinality(String),
    location    LowCardinality(String),
    value       Float64
)
ENGINE = MergeTree
ORDER BY (time, ingest_uuid);

Example domain tables

CREATE TABLE IF NOT EXISTS tenant_ach.reservationdata_device
(
    ingest_uuid UUID DEFAULT generateUUIDv4(),
    time        DateTime64(3, 'UTC'),
    city        String,
    country     String,
    device_id   Int64,
    device_name String,
    gateway_id  Int64,
    site_name   String
)
ENGINE = MergeTree
ORDER BY time;
 
CREATE TABLE IF NOT EXISTS tenant_ach.reservationdata_reservations
(
    ingest_uuid       UUID DEFAULT generateUUIDv4(),
    time              DateTime64(3, 'UTC'),
    cancelled_at      DateTime64(3, 'UTC'),
    city              String,
    country           String,
    device_id         Int64,
    device_name       String,
    device_open_count Int64,
    "end"             DateTime64(3, 'UTC'),
    site_name         String,
    "start"           DateTime64(3, 'UTC'),
    state             String,
    user_id           String
)
ENGINE = MergeTree
ORDER BY time;

5. Active tenants

# Authoritative activation list for migration rollout.
# One tenant per line.
ach
public

6. Onboarding commands

# create tenant bootstrap files
migrate bootstrap-tenant --tenant ach
migrate --env production bootstrap-tenant --tenant ach
 
# inspect planned rollout
migrate all --dry-run
migrate --env production all --dry-run
 
# apply tenant SQL
migrate tenant --tenant ach
 
# verify managed state
migrate check --tenant ach
 
# create ClickHouse credentials for Superset
migrate generate-credentials --tenant ach --k8s-secret

7. Generated Secret

apiVersion: v1
kind: Secret
metadata:
  name: superset-tenant-ach-clickhouse-credentials
  namespace: superset
type: Opaque
stringData:
  database: tenant_ach
  username: user_tenant_ach
  password: "<generated-password>"

8. SupersetTenant example

apiVersion: operators.sdk-cloud.de/v1alpha1
kind: SupersetTenant
metadata:
  name: ach
  namespace: superset
spec:
  organization: ach
  dbType: POSTGRESQL
  mode: PRODUCTION
  supersetVersion: "4.1.1"
  supersetConfig:
    clickhouseEnabled: true
    clickhouseConnectionName: "Main"
    clickhouseClientVersion: "0.13.0"
    duckdbEnabled: false

9. Operator defaults

configuration:
  CLICKHOUSE_HOST: "clickhouse.operations.svc.cluster.local"
  CLICKHOUSE_PORT: "8123"
  CLICKHOUSE_SUPERSET_CONNECTION_NAME: "Main"
  CLICKHOUSE_CLIENT_DEFAULT_VERSION: "0.13.0"

10. Verification

Migration tracking

SELECT *
FROM ops.schema_migrations_tenant
ORDER BY tenant_db, applied_at DESC;

Drift check

migrate check --tenant ach

Superset tenant status

kubectl -n superset get supersettenant ach -o yaml

Look for:

• ready ClickHouse Secret
• healthy tenant status
• working ClickHouse datasource in Superset