Skip to content

How to create index for Map Type Column or one key of it? [version 21.8.10 revision 54449] #31801

New issue
New issue
Closed
Closed
How to create index for Map Type Column or one key of it? [version 21.8.10 revision 54449]#31801
Assignees
kitaisreal
Labels
questionQuestion?question-answered
@phhcer

Description

@phhcer
phhcer
opened on Nov 25, 2021

Test Table:

Create table test.test (
    partition String
    ,map_values Map(String, String)
) ENGINE = ReplicatedMergeTree()
PARTITION BY partition
ORDER BY(map_values[‘key’])
SETTINGS index_granularity = 8192

Test query:

select count(*) from test.test where map_values[‘key’] = ‘1’;

ClickHouse client version 21.8.10.19 (official build)
Connected to ClickHouse server version 21.8.10 revision 54449

It seems that the query would not skipped the indexes.
How could I create index for the map type column or one key of the map type column?

Activity

phhcer
added
questionQuestion?
on Nov 25, 2021
lingtaolf

lingtaolf commented on Dec 9, 2021

@lingtaolf
lingtaolf
on Dec 9, 2021
Contributor

Just like below

create table map_test 
(
    id UInt32,
    map1 Map(String,String),
    Index a map1 TYPE tokenbf_v1(256, 2, 0) GRANULARITY 1
)  
Engine = MergeTree()
Order by id

It only check keys of map .

phhcer

phhcer commented on Dec 14, 2021

@phhcer
phhcer
on Dec 14, 2021
Author

Just like below

create table map_test 
(
    id UInt32,
    map1 Map(String,String),
    Index a map1 TYPE tokenbf_v1(256, 2, 0) GRANULARITY 1
)  
Engine = MergeTree()
Order by id

It only check keys of map .

thx！I will try it.
By the way, where could I find the src code for the skipping index implement of ColumnMap?

phhcer

phhcer commented on Dec 14, 2021

@phhcer
phhcer
on Dec 14, 2021
Author

Could you tell me the path of the project to find it?

lingtaolf

lingtaolf commented on Dec 15, 2021

@lingtaolf
lingtaolf
on Dec 15, 2021
Contributor

Could you tell me the path of the project to find it?

@phhcer #28511 & #29220

kitaisreal
self-assigned this
on Jan 19, 2022
kitaisreal

kitaisreal commented on Jan 21, 2022

@kitaisreal
kitaisreal
on Jan 21, 2022
Contributor

You can create index for map keys, or for map values. Example:

CREATE TABLE test_table
(
    id UInt64,
    value Map(Int64, String),
    index value_key_index mapKeys(value) TYPE bloom_filter GRANULARITY 4
) ENGINE=MergeTree ORDER BY id;

Link to documentation https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#available-types-of-indices.

kitaisreal
added
question-answered
on Jan 21, 2022
kitaisreal
closed this as completedon Jan 21, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

questionQuestion?question-answered

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

    Development

    No branches or pull requests

      Participants

      @kitaisreal@phhcer@lingtaolf

      Issue actions
        How to create index for Map Type Column or one key of it? [version 21.8.10 revision 54449] · Issue #31801 · ClickHouse/ClickHouse