Skip to content

Engine in primary key and order by after pull #190

Description

@gempir

I pulled tables from my prod clickhouse cluster and then generate my baseline migration.

Which pulled this weird invalid table (Engine is somehow 3x in there)

const bi_price_history_product_calculated_daily = table({
	database: "bi",
	name: "price_history_product_calculated_daily",
	engine:
		"ReplicatedReplacingMergeTree('/clickhouse/tables/{cluster}/bi/price_history_product_calculated_daily_new', '{replica}', _version)",
	columns: [
		{ name: "day", type: "Date" },
		{ name: "csin", type: "String" },
[...]
		{ name: "min_price", type: "UInt32" },
		{ name: "_version", type: "UInt64", default: "fn:now64()" },
	],
	primaryKey: [
		"csin",
		"day)) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{cluster}/bi/price_history_product_calculated_daily_new', '{replica}', _version)",
	],
	orderBy: [
		"csin",
		"day)) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{cluster}/bi/price_history_product_calculated_daily_new', '{replica}', _version)",
	],
	partitionBy: "toYYYYMM(day)",
	ttl: "day + toIntervalYear(5)",
	settings: {
		deduplicate_merge_projection_mode: "'rebuild'",
		index_granularity: "8192",
	},
	projections: [
		{
			name: "by_csin_day",
			query: "SELECT csin, day, min_price ORDER BY csin, day",
		},
	],
});

The source table is

CREATE TABLE bi.price_history_product_calculated_daily
(
    `day` Date,
    `csin` String,
    `min_price` UInt32,
[...]
    `_version` UInt64 DEFAULT now64(),
    PROJECTION by_csin_day
    (
        SELECT
            csin,
            day,
            min_price
        ORDER BY
            csin,
            day
    )
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{cluster}/bi/price_history_product_calculated_daily_new', '{replica}', _version)
PARTITION BY toYYYYMM(day)
ORDER BY (day, csin)
TTL day + toIntervalYear(5)
SETTINGS index_granularity = 8192, deduplicate_merge_projection_mode = 'rebuild';

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions