Data Warehouse

The Harmoney application can be connected to a data warehouse to store and analyze data. This document provides an overview of the data warehouse setup, including architecture, configuration, and migration of existing data.

Architecture

The data warehouse is built using Apache Kafka as a queueing system, Apache Flink for stream processing and StarRocks as the data storage solution:

• The application sends events to a Kafka topic. Events currently include all modifications to FSP-level data. The events are sent asynchronously, so there is a small delay between the data modification and the event being sent.
• Flink consumes these events from the Kafka topic, processes them, and writes the results to StarRocks.
• StarRocks is used as the data storage solution, allowing for efficient querying and analysis of the data.
  • StarRocks uses S3 storage under the hood, which allows for geo-localized, scalable and cost-effective storage of large datasets.
  • All data is segregated on environment and organization level by using different databases in StarRocks.
• DBT (Data Build Tool) is used to manage the data models and transformations in StarRocks. DBT is executed periodically. The execution frequency is in the order of hours. DBT always does a full reprocess of the bronze, silver and gold layers in StarRocks, so high frequency executions should be avoided.
  • DBT models are used to transform the raw data into a more usable format, allowing for easier analysis and reporting.
  • The DBT models are stored in a Git repository, allowing for version control and collaboration on the data models.
• Superset is used as a BI tool to visualize the data stored in StarRocks.
  • Queries that are created in Superset can be configured in the api for CSV generation purposes.
  • It's possible to configure dashboards in Superset that can be used to visualize the data in StarRocks. Those dashboards can be embedded in the Harmoney application.

Data segregation

The infrastructure resource requirements to deploy a full data warehouse stack are not minimal. It is possible to deploy a full stack for every deployment of the Harmoney application to achieve full segregation until infrastructure-level, but the cost will be significant. Therefore, we have foreseen multiple options to configure segregation. Let's first explore the minimal setup.

Minimal setup

• Single data warehouse stack is deployed
• Each Harmoney api instance points to the same Kafka instance
• Kafka only contains a single topic (HARMONEY_KAFKA_TOPIC)
• Each Harmoney api instance is configured with a different HARMONEY_KAFKA_ENVIRONMENT setting
  • The environment is embedded in each message sent to Kafka
  • The organization ID is embedded in each message sent to Kafka
• Flink reads messages from the single Kafka topic and writes them to multiple StarRocks databases. The databases follow this naming convention: <environment>_org<organization ID>_bronze. For example, events will be written into uat_org1_bronze.events.
  • Flink uses a single StarRocks user to be able to write to all StarRocks databases (and create new databases on the fly).
• Superset reads from StarRocks using one or more users.
  • Specific StarRocks users can be used in datasets in Superset to avoid that a single user needs access to all StarRocks tables.
  • Superset users themselves can be given access to specific datasets.
• Different StarRocks user credentials can be configured per organization in the Harmoney API.

info

In single-tenant mode environment is equal to HARMONEY_KAFKA_ENVIRONMENT. In multi-tenant mode, environment is equal to <HARMONEY_KAFKA_ENVIRONMENT>_<schema>, where schema is the schema of current tenant.

Increasing segregation

Below, there are some suggestions to tighten the segregation in the full data-flow. All suggestions are optional. The first one is strongly encouraged. The others are possible but be advised that the user-management is a manual and error-prone work.

• Split the data warehouse stack between UAT and PROD. This allows verifying infra upgrades on UAT before going to PROD.
• Create a distinct Kafka topic per Harmoney API instance with a specific Kafka user
  • Multiple Flink jobs will have to be deployed for each Kafka topic
  • A specific StarRocks user (with access to a single database) can be configured in each Flink job
• The Harmoney API instance can use the same StarRocks user

Configuration

The following environment variables should be added to the api deployment:

Key	Example Value	Comment
HARMONEY_KAFKA_BOOTSTRAP_SERVERS	kafka:9092	IP-addresses of the Kafka brokers
HARMONEY_KAFKA_ENVIRONMENT	uat	The environment for which the data warehouse is configured. The environment is used together with the organization ID (and the database schema in multi-tenant mode) to segregate data in StarRocks. This property is used both when sending events to Kafka and when querying StarRocks.
HARMONEY_KAFKA_CERT	-----BEGIN CERTIFICATE-----\nMIIFTjCCAzagAwIBAgIUXvNNMnj...	Optional certificate in PEM format if Kafka is configured with a self-signed certificate.
HARMONEY_KAFKA_MECHANISM	SCRAM-SHA-512
HARMONEY_KAFKA_SASL_JAAS_CONFIG	org.apache.kafka.common.security.plain.PlainLoginModule required username="***" password="***";
HARMONEY_KAFKA_SASL_MECHANISM	PLAIN
HARMONEY_KAFKA_SECURITY_PROTOCOL	SASL_SSL
HARMONEY_KAFKA_TOPIC	events	Kafka topic name
DWH_DB_HOST		StarRocks host (and port)
DWH_JDBC_OPTS	allowPublicKeyRetrieval=true&sslMode=REQUIRED&useCursorFetch=true	StarRocks JDBC options to be passed as query parameters.
DWH_DB_MIN_POOL_SIZE	0	StarRocks pool size minimum
DWH_DB_MAX_POOL_SIZE	150	StarRocks pool size maximum

Required configuration on organization level in the vault

Key	Example Value	Comment
DATA_WAREHOUSE_ENABLED	true	Defines if events should be sent to Kafka for this organization
DATA_WAREHOUSE_USERNAME	harmoney_uat_org1_gold	StarRocks username
DATA_WAREHOUSE_PASSWORD	MySuperSecret	StarRocks password

note

The configured user ideally only has read access to a single StarRocks schema. It is not necessary to add a schema in the report queries, as the schema is set on the connection when querying StarRocks. However, setting the schema on the connection does not restrict access to other schemas for that user. It's the same principle as in MySQL client, you can do use schema_1; SELECT * FROM schema_2.foo; as long as the user has access to both schemas.

Migrating data

It's possible to migrate existing history data to the data warehouse. This is done by triggering the DataWarehouseScheduledProvider once for an organization.

• Create the provider via the admin. See below for additional settings.
• Trigger the provider via Organizations > X > Triggers.

This provider is ideally executed right before setting the DATA_WAREHOUSE_ENABLED vault variable to true for the organization.

Settings

Settings Configuration

Examples