ClickHouse within GitLab

ClickHouse::Client.select('SELECT 1', :main)
# => [{"1"=>1}]

bundle exec rake gitlab:clickhouse:migrate

bundle exec rake gitlab:clickhouse:rollback:main STEP=N

bundle exec rake gitlab:clickhouse:rollback:main VERSION=0

# 20230811124511_create_issues.rb
# frozen_string_literal: true

class CreateIssues < ClickHouse::Migration
  def up
    execute <<~SQL
      CREATE TABLE issues
      (
        id UInt64 DEFAULT 0,
        title String DEFAULT ''
      )
      ENGINE = MergeTree
      PRIMARY KEY (id)
    SQL
  end

  def down
    execute <<~SQL
      DROP TABLE sync_cursors
    SQL
  end
end

rows = ClickHouse::Client.select('SELECT * FROM events', :main)

raw_query = 'SELECT * FROM events WHERE id > {min_id:UInt64}'
placeholders = { min_id: Integer(100) }
query = ClickHouse::Client::Query.new(raw_query: raw_query, placeholders: placeholders)

rows = ClickHouse::Client.select(query, :main)

puts query.to_redacted_sql

ClickHouse::Client.select('SELECT 1; SELECT 2', :main)

# ClickHouse::Client::DatabaseError: Code: 62. DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 9 (end of query): ; SELECT 2. . (SYNTAX_ERROR) (version 23.4.2.11 (official build))

subquery = ClickHouse::Client::Query.new(raw_query: 'SELECT id FROM events WHERE id = {id:UInt64}', placeholders: { id: Integer(10) })

raw_query = 'SELECT * FROM events WHERE id > {id:UInt64} AND id IN ({q:Subquery})'
placeholders = { id: Integer(10), q: subquery }

query = ClickHouse::Client::Query.new(raw_query: raw_query, placeholders: placeholders)
rows = ClickHouse::Client.select(query, :main)

# ClickHouse will replace the placeholders
puts query.to_sql # SELECT * FROM events WHERE id > {id:UInt64} AND id IN (SELECT id FROM events WHERE id = {id:UInt64})

puts query.to_redacted_sql # SELECT * FROM events WHERE id > $1 AND id IN (SELECT id FROM events WHERE id = $2)

puts query.placeholders # { id: 10 }

bundle exec rake gitlab:clickhouse:migrate
```0

## Inserting data

The ClickHouse client supports inserting data through the standard query interface:

```shell
bundle exec rake gitlab:clickhouse:migrate
```1

Inserting data this way is acceptable if:

- The table contains settings or configuration data where we need to add one row.
- For testing, test data has to be prepared in the database.

When inserting data, we should always try to use batch processing where multiple rows are inserted at once. Building large `INSERT` queries in memory is discouraged because of the increased memory usage. Additionally, values specified within such queries cannot be redacted automatically by the client.

To compress data and reduce memory usage, insert CSV data. You can do this with the internal [`CsvBuilder`](https://gitlab.com/gitlab-org/gitlab/-/tree/master/gems/csv_builder) gem:

```shell
bundle exec rake gitlab:clickhouse:migrate
```2

NOTE:
It's important to test and verify efficient batching of database records from PostgreSQL. Consider using the techniques described in the [Iterating tables in batches](../iterating_tables_in_batches.md).

## Iterating over tables

You can use the `ClickHouse::Iterator` class for batching over large volumes of data in ClickHouse. The iterator works a bit differently than existing tooling for the PostgreSQL database (see [iterating tables in batches docs](../iterating_tables_in_batches.md)), as the tool does not rely on database indexes and uses fixed size numeric ranges.

Prerequisites:

- Single integer column.
- No huge gaps between the column values, the ideal columns would be auto-incrementing PostgreSQL primary keys.
- Duplicated values are not a problem if the data duplication is minimal.

Usage:

```shell
bundle exec rake gitlab:clickhouse:migrate
```3

In case you want to iterate over specific rows, you could add filters to the query builder object. Be advised that efficient filtering and iteration might require a different database table schema optimized for the use case. When introducing such iteration, always ensure that the database queries are not scanning the whole database table.

```shell
bundle exec rake gitlab:clickhouse:migrate
```4

### Min-max strategies

As the first step, the iterator determines the data range which will be used as condition in the iteration database queries. The data range is
determined using `MIN(column)` and `MAX(column)` aggregations. For some database tables this strategy causes inefficient database queries (full table scan). One example would be partitioned database tables.

Example query:

```shell
bundle exec rake gitlab:clickhouse:migrate
```5

Alternatively a different min-max strategy can be used which uses `ORDER BY + LIMIT` for determining the data range.

```shell
bundle exec rake gitlab:clickhouse:migrate
```6

Example query:

```shell
bundle exec rake gitlab:clickhouse:migrate
```7

## Implementing Sidekiq workers

Sidekiq workers leveraging ClickHouse databases should include the `ClickHouseWorker` module.
This ensures that the worker is paused while database migrations are running,
and that migrations do not run while the worker is active.

```shell
bundle exec rake gitlab:clickhouse:migrate
```8

## Testing

ClickHouse is enabled on CI/CD but to avoid significantly affecting the pipeline runtime we've decided to run the ClickHouse server for test cases tagged with `:click_house` only.

The `:click_house` tag ensures that the database schema is properly set up before every test case.

```shell
bundle exec rake gitlab:clickhouse:migrate
```9

## Multiple databases

By design, the `ClickHouse::Client` library supports configuring multiple databases. Because we're still at a very early stage of development, we only have one database called `main`.

Multi database configuration example:

```shell
bundle exec rake gitlab:clickhouse:rollback:main STEP=N
```0

## Observability

All queries executed via the `ClickHouse::Client` library expose the query with performance metrics (timings, read bytes) via `ActiveSupport::Notifications`.

```shell
bundle exec rake gitlab:clickhouse:rollback:main STEP=N
```1

Additionally, to view the executed ClickHouse queries in web interactions, on the performance bar, next to the `ch` label select the count.

### Getting help

For additional information or specific questions, please reach out to the ClickHouse Datastore working group in the `#f_clickhouse` Slack channel, or mention `@gitlab-org/maintainers/clickhouse` in a comment on GitLab.com.