-- start migrations

-- forward migration generic_metrics : 0010_counters_aggregate_table
Local op: CREATE TABLE IF NOT EXISTS generic_metric_counters_aggregated_local (org_id UInt64, project_id UInt64, metric_id UInt64, granularity UInt8, timestamp DateTime CODEC (DoubleDelta), retention_days UInt16, tags Nested(key UInt64, indexed_value UInt64, raw_value String), value AggregateFunction(sum, Float64), use_case_id LowCardinality(String)) ENGINE ReplicatedAggregatingMergeTree('/clickhouse/tables/generic_metrics_counters/{shard}/default/generic_metric_counters_aggregated_local', '{replica}') PRIMARY KEY (org_id, project_id, metric_id, granularity, timestamp) ORDER BY (org_id, project_id, metric_id, granularity, timestamp, tags.key, tags.indexed_value, tags.raw_value, retention_days, use_case_id) PARTITION BY (retention_days, toMonday(timestamp)) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity=2048;
Local op: ALTER TABLE generic_metric_counters_aggregated_local ADD COLUMN IF NOT EXISTS _indexed_tags_hash Array(UInt64) MATERIALIZED arrayMap((k, v) -> cityHash64(concat(toString(k), '=', toString(v))), tags.key, tags.indexed_value);
Local op: ALTER TABLE generic_metric_counters_aggregated_local ADD COLUMN IF NOT EXISTS _raw_tags_hash Array(UInt64) MATERIALIZED arrayMap((k, v) -> cityHash64(concat(toString(k), '=', v)), tags.key, tags.raw_value);
Local op: ALTER TABLE generic_metric_counters_aggregated_local ADD INDEX IF NOT EXISTS bf_indexed_tags_hash _indexed_tags_hash TYPE bloom_filter() GRANULARITY 1;
Local op: ALTER TABLE generic_metric_counters_aggregated_local ADD INDEX IF NOT EXISTS bf_raw_tags_hash _raw_tags_hash TYPE bloom_filter() GRANULARITY 1;
Local op: ALTER TABLE generic_metric_counters_aggregated_local ADD INDEX IF NOT EXISTS bf_tags_key_hash tags.key TYPE bloom_filter() GRANULARITY 1;
Distributed op: CREATE TABLE IF NOT EXISTS generic_metric_counters_aggregated_dist (org_id UInt64, project_id UInt64, metric_id UInt64, granularity UInt8, timestamp DateTime CODEC (DoubleDelta), retention_days UInt16, tags Nested(key UInt64, indexed_value UInt64, raw_value String), value AggregateFunction(sum, Float64), use_case_id LowCardinality(String)) ENGINE Distributed(cluster_one_sh, default, generic_metric_counters_aggregated_local);
-- end forward migration generic_metrics : 0010_counters_aggregate_table




-- backward migration generic_metrics : 0010_counters_aggregate_table
Distributed op: DROP TABLE IF EXISTS generic_metric_counters_aggregated_dist;
Local op: DROP TABLE IF EXISTS generic_metric_counters_aggregated_local;
-- end backward migration generic_metrics : 0010_counters_aggregate_table
-- forward migration generic_metrics : 0011_counters_raw_table
Local op: CREATE TABLE IF NOT EXISTS generic_metric_counters_raw_local (use_case_id LowCardinality(String), org_id UInt64, project_id UInt64, metric_id UInt64, timestamp DateTime, retention_days UInt16, tags Nested(key UInt64, indexed_value UInt64, raw_value String), set_values Array(UInt64), count_value Float64, distribution_values Array(Float64), metric_type LowCardinality(String), materialization_version UInt8, timeseries_id UInt32, partition UInt16, offset UInt64, granularities Array(UInt8)) ENGINE ReplicatedMergeTree('/clickhouse/tables/generic_metrics_counters/{shard}/default/generic_metric_counters_raw_local', '{replica}') ORDER BY (use_case_id, org_id, project_id, metric_id, timestamp) PARTITION BY (toStartOfInterval(timestamp, toIntervalDay(3))) TTL timestamp + toIntervalDay(7);
Distributed op: CREATE TABLE IF NOT EXISTS generic_metric_counters_raw_dist (use_case_id LowCardinality(String), org_id UInt64, project_id UInt64, metric_id UInt64, timestamp DateTime, retention_days UInt16, tags Nested(key UInt64, indexed_value UInt64, raw_value String), set_values Array(UInt64), count_value Float64, distribution_values Array(Float64), metric_type LowCardinality(String), materialization_version UInt8, timeseries_id UInt32, partition UInt16, offset UInt64, granularities Array(UInt8)) ENGINE Distributed(cluster_one_sh, default, generic_metric_counters_raw_local, cityHash64(timeseries_id));
-- end forward migration generic_metrics : 0011_counters_raw_table




-- backward migration generic_metrics : 0011_counters_raw_table
Distributed op: DROP TABLE IF EXISTS generic_metric_counters_raw_dist;
Local op: DROP TABLE IF EXISTS generic_metric_counters_raw_local;
-- end backward migration generic_metrics : 0011_counters_raw_table
-- forward migration generic_metrics : 0012_counters_mv
Local op: CREATE MATERIALIZED VIEW IF NOT EXISTS generic_metric_counters_aggregation_mv TO generic_metric_counters_aggregated_local (org_id UInt64, project_id UInt64, metric_id UInt64, granularity UInt8, timestamp DateTime CODEC (DoubleDelta), retention_days UInt16, tags Nested(key UInt64, indexed_value UInt64, raw_value String), value AggregateFunction(sum, Float64), use_case_id LowCardinality(String)) AS 
                SELECT
                    use_case_id,
                    org_id,
                    project_id,
                    metric_id,
                    arrayJoin(granularities) as granularity,
                    tags.key,
                    tags.indexed_value,
                    tags.raw_value,
                    toDateTime(multiIf(granularity=0,10,granularity=1,60,granularity=2,3600,granularity=3,86400,-1) *
                      intDiv(toUnixTimestamp(timestamp),
                             multiIf(granularity=0,10,granularity=1,60,granularity=2,3600,granularity=3,86400,-1))) as timestamp,
                    retention_days,
                    sumState(count_value) as value
                FROM generic_metric_counters_raw_local
                WHERE materialization_version = 1
                  AND metric_type = 'counter'
                GROUP BY
                    use_case_id,
                    org_id,
                    project_id,
                    metric_id,
                    tags.key,
                    tags.indexed_value,
                    tags.raw_value,
                    timestamp,
                    granularity,
                    retention_days
                ;
-- end forward migration generic_metrics : 0012_counters_mv




-- backward migration generic_metrics : 0012_counters_mv
Local op: DROP TABLE IF EXISTS generic_metric_counters_aggregation_mv;
-- end backward migration generic_metrics : 0012_counters_mv