Clickhouse

ClickHouse is a high-performance, column-oriented SQL database management system for online analytical processing (OLAP).

This guide walks you through configuring your ClickHouse database to work seamlessly with Supermetal.

Features

FeatureNotes
ReplacingMergeTree

Async Inserts

Schema Evolution

Soft Delete(s)

Prerequisites

Before you begin, ensure you have:

  • Supported ClickHouse Implementations:
    • ClickHouse Cloud
    • ClickHouse Server:
      • 21.8 or higher.
      • Ensure that the ClickHouse HTTP interface (default port 8123) is enabled.

      Cluster

      ClickHouse Cluster deployment is not yet supported.

  • Database Admin Access: Sufficient privileges to create users, grant permissions. This might require admin privileges.
  • Network Connectivity: Ensure that Supermetal's services can reach your ClickHouse database. You may need to configure security groups.

Setup

Create a user & grant permissions

Visit SQL console

Log in to ClickHouse Cloud to access the SQL console and select your service.

Create a user

CREATE USER IF NOT EXISTS 'supermetal_user' IDENTIFIED WITH sha256_password BY 'strong-password';

Password Security

Replace 'strong-password' with a secure, unique password for the supermetal_user. Store this password securely, as you'll need it when configuring the target in Supermetal.

Create database

CREATE DATABASE IF NOT EXISTS target_database;

Script Variables

Replace target_database with the name of the database you want to use for storing data.

Grant permissions

GRANT CREATE DATABASE ON target_database.*, CREATE TABLE ON target_database.*, CREATE TEMPORARY TABLE ON *.*, S3 ON *.*, AZURE ON *.*, INSERT ON target_database.*, ALTER TABLE ON target_database.*, SELECT ON target_database.*, TRUNCATE ON target_database.* TO 'supermetal_user';

Script Variables

Replace target_database with the name of the database created in the previous step.

Connection Details

You'll need the following connection details to configure the target in Supermetal:

  • HTTPs URL (e.g. https://<host>.<region>.clickhouse.cloud:8443)
  • Username and password you created

IP Whitelist

You may need to add the IP address of the machine running Supermetal to the ClickHouse Cloud IP whitelist from the Service Settings -> Security -> IP Access List.

Create a user

Login into ClickHouse server as an admin user to create a user for supermetal.

CREATE USER IF NOT EXISTS 'supermetal_user' IDENTIFIED WITH sha256_password BY 'strong-password';

Password Security

Replace 'strong-password' with a secure, unique password for the supermetal_user. Store this password securely, as you'll need it when configuring the target in Supermetal.

Create database

CREATE DATABASE IF NOT EXISTS target_database;

Script Variables

Replace target_database with the name of the database you want to use for storing data.

Grant permissions

GRANT CREATE DATABASE ON target_database.*, CREATE TABLE ON target_database.*, CREATE TEMPORARY TABLE ON *.*, S3 ON *.*, AZURE ON *.*, INSERT ON target_database.*, ALTER TABLE ON target_database.*, SELECT ON target_database.*, TRUNCATE ON target_database.* TO 'supermetal_user';

Script Variables

Replace target_database with the name of the database created in the previous step.

Connection Details

You'll need the following connection details to configure the target in Supermetal:

  • HTTP interface URL (e.g. https://hostname:port)
  • Username and password you created

Table Engine(s)

Supermetal automatically creates tables in ClickHouse with appropriate engines

  • Tables without primary keys use the MergeTree engine
  • Tables with primary keys use the ReplacingMergeTree engine with version tracking (_sm_version)

Data Types Mapping

Apache Arrow DataTypeClickHouse TypeNotes
Int8Int8
Int16Int16
Int32Int32
Int64Int64
UInt8UInt8
UInt16UInt16
UInt32UInt32
UInt64UInt64
Float16Float32
Float32Float32
Float64Float64
Decimal128(p,s)Decimal(p,s)
Decimal256(p,s)Decimal(p,s)
Apache Arrow DataTypeClickHouse TypeNotes
Date32Date32
Date64DateTime64(3)
Timestamp(s, [tz])DateTime(0, [tz])
Timestamp(ms, [tz])DateTime64(3, [tz])
Timestamp(us, [tz])DateTime64(6, [tz])
Timestamp(ns, [tz])DateTime64(9, [tz])
Time32(ms)DateTime64(3)
Time64(us)DateTime64(6)
Time64(ns)DateTime64(9)
Duration, IntervalStringNot supported by ClickHouse
Apache Arrow DataTypeClickHouse TypeNotes
Utf8, LargeUtf8String
Apache Arrow DataTypeClickHouse TypeNotes
BooleanBoolean
Apache Arrow DataTypeClickHouse TypeNotes
StructTuple
MapMap
Utf8 JSON Extension (arrow.json)JSONJSON Data Type is not supported (as it cannot ingest arbitrary json arrays)
Apache Arrow DataTypeClickHouse TypeNotes
Binary, LargeBinary, FixedSizeBinaryStringClickHouse does not have a binary type
Apache Arrow DataTypeClickHouse TypeNotes
List<T>, LargeList<T>, FixedSizeList<T>Array(T)

Nullability

Nullable columns are marked as nullable in ClickHouse using the Nullable(Type) wrapper.

Type Compatibility

Supermetal preserves ClickHouse's Arrow data type mappings.

Last updated on

On this page

FeaturesPrerequisitesSetupCreate a user & grant permissionsTable Engine(s)Data Types Mapping