☰
Navigation :

Quickstart with Clickhouse

The reference ClickHouse database can be used to get started using DirectQuery quickly. Here we will go over how to get started using DirectQuery against the included reference ClickHouse database in a local environment.

This section will go over how to start the ClickHouse Database locally as well as the configurations needed to connect to the Database.

For an overview of DirectQuery, please see the DirectQuery Overview section in the User Guide.

Reference ClickHouse Implementation Overview

Atoti FRTB comes with a reference ClickHouse Database packaged within a Docker Container that can be run locally by using Docker.

File Structure

The following files can be found within the frtb-directquery module under src/test/resources/database/clickhouse/:

  • clickhouse_docker_container/
    • input_data
    • run_scripts
    • Dockerfile
    • init-db.sh

clickhouse_docker_container

This directory contains the entire ClickHouse Docker database along with everything needed to get the ClickHouse database up and running.

run_scripts

Scripts that can be used to build, run and stop the Docker Container. This includes scripts that can be run on Windows or Linux machines. Alternatively you can run the Docker commands directly as outlined in the Running the Project section.

input_data

FRTB’s reference data has been converted into a format that can be loaded directly into ClickHouse. This dataset contains all data for the Tables that FRTB DirectQuery requires.

Dockerfile

This file is where the Docker Image runs. As a prerequisite, you must have Docker running locally (you can use Docker Desktop). This file performs the following actions:

  • Create an empty ClickHouse database in the Docker Container
  • Copy the database initialization script init-bd.sh into the Docker Container
  • Create and copy the input CSV files from input_data/ into the Docker Container

The Dockerfile contains some additional documentation on how to start and stop the Docker Container.

init-db.sh

This file contains the DDL of the Database. It creates all the Tables required by FRTB DirectQuery.

This file creates a Database named FRTB as well as a Database Schema named FRTB.

Running the project

To run FRTB connected to the reference ClickHouse implementation will need to:

  1. Build the ClickHouse Docker Container
  2. Start the ClickHouse Docker Container
  3. Start the FRTB Project with DirectQuery enabled and configured for ClickHouse.

1. Build Docker Image

First you will need to build the Docker Image either through the command line, or by running the 1_build script.

Script

Script can be run by double-clicking or through the command line:

./1_build.sh

Command Line

You can build the Docker Image by running the following command from within the clickhouse_docker_container folder:

docker build -t frtb-clickhouse-image .

This builds your Docker Image with the name frtb-clickhouse-image.

2. Start ClickHouse Database

Now you can run the ClickHouse Database either through the command line or by running the 2_run script. You can verify the database is up by going to the URL ‘localhost:8123/play’.

Script

Script can be run by double-clicking or through the command line:

./2_run.sh

Command Line

You can start the Docker Container, for your built Docker Image, by running the following command from within the clickhouse_docker_container folder:

docker run --rm --name frtb-clickhouse-container --ulimit nofile=262144:262144  -p 8123:8123 frtb-clickhouse-image

This runs your Docker Image frtb-clickhouse-image in a Docker Container named frtb-clickhouse-container on port 8123.

3. Start FRTB with ClickHouse enabled

You are now ready to start the FRTB Application with DirectQuery enabled and configured for ClickHouse.

Property Configuration

Some properties will need to be configured when starting the FRTB Application.

application.yaml

Use the following properties in the application.yaml file to connect to the ClickHouse Database:

# ClickHouse Database Connection Parameters
directquery:
  enabled: true
  database:
    type: clickhouse
    clickhouse:
      username:
      password:
      port: 8123
      hostName: localhost
      database: frtb
      schema: frtb
frtb.properties

We will need to define the following properties within the frtb.properties file:

  • ima.disable: We have to disable ima as it is not currently supported with DirectQuery.
# IMA is not currently supported
ima.disable=true

4. Shutdown ClickHouse Database

When we are finished with the ClickHouse Database, we can shut down the Docker Container either through the command line or by running the 3_kill script.

Script

Script can be run by double-clicking or through the command line:

./3_kill.sh

Command Line

You can stop the Docker Container by running the following command:

docker stop frtb-clickhouse-container

This stops your Docker Container frtb-clickhouse-container and frees the port 8123.