Service: ClickHouse - EyevinnOSC/community GitHub Wiki

ClickHouse

ClickHouse (serviceId clickhouse-clickhouse) is a fast, resource-efficient, open-source columnar database and real-time data warehouse (license Apache-2.0). In Eyevinn Open Source Cloud (OSC) it is a long-running service instance.

What it can do

  • Run real-time analytical SQL queries over large datasets (the example below queries two million rows in about a second).
  • Load data from files and S3-compatible storage with the s3(...) table function.
  • Be queried with the ClickHouse client (clickhouse client) over the TCP address and port the instance exposes.
  • Support features such as dictionaries and JOINs; see the ClickHouse documentation for the full SQL reference.

Creating an instance

ClickHouse has no dependencies on other OSC services. Store the password as a service secret first with create-service-secret (serviceId clickhouse-clickhouse, a secret name and the value). Then call create-service-instance with serviceId: "clickhouse-clickhouse":

{
  "name": "myclickhouse",
  "User": "myuser",
  "Password": "{{secrets.clickhousepwd}}"
}

Wait for the instance to be running with wait-for-service-ready or get-instance-status, then use describe-service-instance to get the IP address and port.

Options

Option Required Type Meaning
name Yes string Name of the instance. Letters, digits and underscore (^\w+$); OSC limits names to 20 characters.
Db No string Name of a database to create at first start if it does not exist (upstream env CLICKHOUSE_DB). Example: analytics.
User No string Username of the ClickHouse user to create at first start (upstream env CLICKHOUSE_USER, upstream default default). Use together with Password. Example: admin.
Password No string, sensitive Password for the user named in User (upstream env CLICKHOUSE_PASSWORD). Choose a strong value. If neither user nor password is set, upstream disables network access for the default user. Pass it as {{secrets.NAME}}.

A {{secrets.NAME}} reference must be the whole field value. Changing a secret later does not restart a running instance.

Using the instance

Connecting with the ClickHouse client

Install the client (for example brew install clickhouse) and connect with the address and port from describe-service-instance. The URL has the form clickhouse://myuser:[email protected]:10523:

% clickhouse client clickhouse://myuser:[email protected]:10523
Connected to ClickHouse server version 24.12.3.

Example: load and query the New York taxi data

Create a table and insert two million rows of the New York taxi data, then run analytical queries. Run the statements in the client.

CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;

Insert the dataset:

INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0

Verify with SELECT count() FROM trips (about 2 million rows) and SELECT DISTINCT(pickup_ntaname) FROM trips (190 values, in about a second). Example analysis queries:

SELECT round(avg(tip_amount), 2) FROM trips
SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count

Resources