Clickhouse create table query throwing syntax error

user_17151630 0
Viewed 2456

I am executing the below-given query but it throwing an error. I am new to clickhouse can you please assist me with how to fix it?

CREATE TABLE rapidTax.mutualFund (
    `id` varchar(255),
    `mutualFundId` varchar(255) DEFAULT NULL,
    `date` date DEFAULT NULL,
    `nav` float DEFAULT NULL,

    INDEX mutualFundIdIndex mutualFundId,
    INDEX dateIndex date
) ENGINE = MergeTree(),
PRIMARY KEY id

Throwing Error:

Code: 62. DB::Exception: Syntax error: failed at position 247 ('dateIndex') (line 8, col 11): dateIndex date ) ENGINE = MergeTree(), PRIMARY KEY id. Expected one of: Comma, Arrow, token. (SYNTAX_ERROR) (version 21.9.4.35 (official build))

clickhouse
1 Answers

Read the documentation. Should be

CREATE TABLE mutualFund (
    `id` String,
    `mutualFundId` Nullable(String) DEFAULT NULL,
    `date` Nullable(Date) DEFAULT NULL,
    `nav` Nullable(Float64) DEFAULT NULL,
    INDEX mutualFundIdIndex(mutualFundId) TYPE bloom_filter GRANULARITY 3,
    INDEX dateIndex date TYPE minmax GRANULARITY 1
) 
ENGINE = MergeTree()
Partition by toYYYYMM(ifNull(date,toDate(0)))
ORDER BY id
PRIMARY KEY id

In reality it will be unusable.

Use this

CREATE TABLE mutualFund (
    `id` String,
    `mutualFundId` LowCardinality(String),
    `date` Date,
    `nav` Nullable(Float64) DEFAULT NULL
) 
ENGINE = MergeTree()
Partition by toYYYYMM(date)
ORDER BY (mutualFundId,date)
PRIMARY KEY (mutualFundId,date)

Store empty strings instead of Nulls for String columns. Store 0(1970-01-01) instead of Nulls for Date columns.

There is no inverse indexes in Clickhouse, but there are skip indexes and orderby projections.

PRIMARY KEY === primary index (not KEY) . There is no unique constraint in CH.

user_116443080
Related