20、如何用ClickHouse bitmap加速人群计算

clickhouse-client --port 9002 --query="INSERT INTO cdp.cdp_user_all FORMAT CSV" --input_format_allow_errors_ratio=0.1 --input_format_allow_errors_num=0 < cdp_user_data.csv

Received exception from server (version 24.3.2):
Code: 252. DB::Exception: Received from localhost:9002. DB::Exception: Too many partitions for single INSERT block (more than 100). The limit is controlled by 'max_partitions_per_insert_block' setting. Large number of partitions is a common misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data manipulation (DROP PARTITION, etc).. (TOO_MANY_PARTS)
(query: INSERT INTO cdp.cdp_user_all FORMAT CSV)

<profiles>
    <!-- Default settings. -->
    <default>
        <max_partitions_per_insert_block>10000</max_partitions_per_insert_block>
    </default>
</profiles>

clickhouse-client --port 9002 --query="INSERT INTO cdp.cdp_user_event_all FORMAT CSV" --input_format_allow_errors_ratio=0.1 --input_format_allow_errors_num=0 --format_csv_delimiter='#' < cdp_user_event_data_2_3.csv

Code: 288. DB::Exception: Double-distributed IN/JOIN subqueries is denied (distributed_product_mode = 'deny'). You may rewrite query to use local tables in subqueries, or use GLOBAL keyword, or set distributed_product_mode to suitable value. (DISTRIBUTED_IN_JOIN_SUBQUERY_DENIED) (version 24.3.2.23 (official build))

SELECT u.unique_user_id
FROM
(
    SELECT unique_user_id
    FROM cdp_user_all
    WHERE gender = 2
) AS u
INNER JOIN
(
    SELECT unique_user_id
    FROM cdp_user_event_all
    WHERE event_type = 'browse'
    AND page_id IN (1100442, 1749628, 1960722, 1869590, 1674494)
) AS e ON u.unique_user_id = e.unique_user_id
INNER JOIN
(
    SELECT unique_user_id
    FROM cdp_user_tag_all
    WHERE tag_id = 1
    AND tag_value = '中等消费能力'
) AS t1 ON u.unique_user_id = t1.unique_user_id
INNER JOIN
(
    SELECT unique_user_id
    FROM cdp_user_tag_all
    WHERE tag_id = 2
    AND tag_value = '高活跃度'
) AS t2 ON u.unique_user_id = t2.unique_user_id;

CREATE TABLE cdp_user_tag_bitmap_local on cluster default
(
    tag_id         UInt64 COMMENT 'tag唯一标识符',
    tag_value      String COMMENT 'tag值',
    tag_name       String COMMENT 'tag名称',
    unique_user_id AggregateFunction(groupBitmap,UInt64) COMMENT '使用位图存储用户全局唯一ID，ONE-ID',
    tag_category   UInt8 COMMENT 'tag分类'
    ) ENGINE = AggregatingMergeTree()
    PARTITION BY tag_id
    ORDER BY (tag_id,tag_value,tag_name,tag_category);
    
CREATE TABLE cdp_user_tag_bitmap_all on cluster default as cdp_user_tag_bitmap_local
    ENGINE = Distributed(default, 'cdp', 'cdp_user_tag_bitmap_local', tag_id);

INSERT INTO cdp_user_tag_bitmap_all (tag_id, tag_value, tag_name, unique_user_id, tag_category)
SELECT
    tag_id,
    tag_value,
    tag_name,
    bitmapBuild(cast([unique_user_id] as Array(UInt64))),
    tag_category
FROM cdp_user_tag_all
GROUP BY tag_id, tag_value, tag_name, tag_category, unique_user_id;

INSERT INTO cdp_user_tag_bitmap_all (tag_id, tag_value, tag_name, unique_user_id, tag_category)
SELECT
    5,
    '高端手机',
    '手机偏好',
    bitmapBuild(cast([unique_user_id] as Array(UInt64))),
    5
FROM cdp_user_event_all
WHERE event_type = 'browse'
  AND page_id IN (1100442, 1749628, 1960722, 1869590, 1674494)

SELECT arrayJoin(bitmapToArray(bitmapAnd(bitmapAnd(bitmapAnd(user_0,user_1),user_2),user_3))) as user_list
FROM
    (
        SELECT 1 as join_id, groupBitmapMergeState(unique_user_id) as user_0
        FROM cdp_user_tag_bitmap_all
        WHERE tag_id = 4 and tag_value = '2'
    ) t0
        INNER JOIN
    (
        SELECT 1 as join_id, groupBitmapMergeState(unique_user_id) as user_1
        FROM cdp_user_tag_bitmap_all
        WHERE tag_id = 1
          AND tag_value = '中等消费能力'
    ) AS t1 ON t0.join_id = t1.join_id
        INNER JOIN
    (
        SELECT 1 as join_id, groupBitmapMergeState(unique_user_id) as user_2
        FROM cdp_user_tag_bitmap_all
        WHERE tag_id = 2
          AND tag_value = '高活跃度'
    ) AS t2 ON t0.join_id = t2.join_id
        INNER JOIN
    (
        SELECT 1 as join_id, groupBitmapMergeState(unique_user_id) as user_3
        FROM cdp_user_tag_bitmap_all
        WHERE tag_id = 5
          AND tag_value = '高端手机'
    ) AS t3 ON t0.join_id = t3.join_id;