CREATE TABLE xhep.xhep_consumer
(
    `when_us` UInt64,
    `src_ip` String,
    `src_port` UInt16,
    `dst_ip` String,
    `dst_port` UInt16,
    `proto` String,
    `node` UInt32,
    `correlation_id` String,
    `payload_type` String,
    `payload` String,
    `sip` String,
    `headers` String
)
ENGINE = RabbitMQ(ch_reader)
SETTINGS rabbitmq_host_port = '192.168.51.201:5672', rabbitmq_vhost = 'xhep', rabbitmq_exchange_name = 'xhep', rabbitmq_queue_base = 'xhep', rabbitmq_format = 'JSONEachRow', rabbitmq_num_consumers = '1', rabbitmq_num_queues = '6', rabbitmq_max_block_size = 10000, rabbitmq_flush_interval_ms = 6000, rabbitmq_queue_settings_list = 'x-queue-type=classic,x-overflow=reject-publish,x-max-length-bytes=128000000,x-max-length=10000000'

CREATE TABLE xhep.xhep_local
(
    `when_us` UInt64 CODEC(DoubleDelta),
    `whenDateUTC` Date MATERIALIZED toDate(intDiv(when_us, 1000000), 'UTC'),
    `payload_type` LowCardinality(String),
    `payload` String CODEC(ZSTD(3)),
    `i_cgpn` LowCardinality(String) DEFAULT '',
    `i_original_cdpn` LowCardinality(String) DEFAULT ''
)
ENGINE = ReplicatedMergeTree('/clickhouse/{installation}/{cluster}/tables/{shard}/xhep/xhep_local', '{replica}')
PARTITION BY toYYYYMMDD(whenDateUTC)
ORDER BY (payload_type, intDiv(when_us, 1000000 * 900) * 900, i_original_cdpn, i_cgpn)
TTL whenDateUTC + toIntervalDay(14)
SETTINGS ttl_only_drop_parts = 1, storage_policy = 'hdd_jbod', index_granularity = 8192