Customize Schema

[Jimerson21]

Hello, nice to greet you.

I was wondering if there is any way to customize the schema that is generated by default for tables in Clickhouse?

To customize for example the ORDER BY, PARTITION BY, as well as the INDEX that there is an option to configure in the config.yaml

This is the current scheme at Clickhouse

CREATE TABLE table_name
(
    `id` UInt64,
    `name` Nullable(String),
    `funnel_stage_id` UInt64,
    `funnel_event_group_id` UInt64,
    `created_at` Nullable(DateTime64(6)),
    `updated_at` Nullable(DateTime64(6)),
    `_version` UInt64,
    
    INDEX _version _version TYPE minmax GRANULARITY 1,
    INDEX idx_id id TYPE bloom_filter GRANULARITY 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id,4294967)
ORDER BY id
SETTINGS index_granularity = 8192;

I would like to be able to customize for example the ORDER BY

CREATE TABLE table_name
(
    `id` UInt64,
    `name` Nullable(String),
    `funnel_stage_id` UInt64,
    `funnel_event_group_id` UInt64,
    `created_at` Nullable(DateTime64(6)),
    `updated_at` Nullable(DateTime64(6)),
    `_version` UInt64,
    
    INDEX _version _version TYPE minmax GRANULARITY 1,
    INDEX idx_id id TYPE bloom_filter GRANULARITY 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id,4294967)
ORDER BY (funnel_stage_id, funnel_event_group_id, created_at);
SETTINGS index_granularity = 8192;

Thank you!

[bakwc]

For indexes there is customization:

indexes:                        # optional
  - databases: '*'
    tables: ['test_table']
    index: 'INDEX name_idx name TYPE ngrambf_v1(5, 65536, 4, 0) GRANULARITY 1'

For order by / partition by not yet, will add later

[Jimerson21]

I understand. This functionality would be very useful.

thank you very much.