populate a Nested column with data from another table

12 views
Skip to first unread message

Ofer Fort

unread,
Dec 13, 2021, 4:44:53 AM12/13/21
to ClickHouse
Hi all, how can I populate a nested column based on another table?
For example fill test_v1.user_attributes with data from test_attributes_v1? 
The list of optional attributes can be a few thousands but each user will have about a hundred.
I would like to later be able to query for user that have a specific combination of attributes, like hair_color:green AND voice:soprano

CREATE TABLE test_v1
(
    userID String,
    created DateTime,
    user_attributes Nested(
        name LowCardinality(String),
        value LowCardinality(String)
    ),
    repos Nested(
        repoID String,
        language LowCardinality(String),
        repo_attributes Nested(
            name LowCardinality(String),
            value LowCardinality(String)
        )
    )
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created)
ORDER BY (userID, created)

CREATE TABLE test_attributes_v1
(
    userID String,
    created DateTime,
    name LowCardinality(String),
    value LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created)
ORDER BY (userID, created)

Thanks!

Denis Zhuravlev

unread,
Dec 14, 2021, 8:09:53 AM12/14/21
to ClickHouse
Reply all
Reply to author
Forward
0 new messages