macOS通过docker 安装clickhouse过程

brew install --cask docker

// 修改镜像
{
  "builder": {
    "gc": {
      "enabled": true,
      "defaultKeepStorage": "20GB"
    }
  },
  "features": {
    "buildkit": true
  },
  "experimental": false,
  "debug": true,
  "registry-mirrors": [
    "https://4cupt54x.mirror.aliyuncs.com"
  ]
}

//下载ck
docker pull yandex/clickhouse-client
docker pull yandex/clickhouse-server

# 默认直接启动即可
docker run -d \
--name clickhouse-server \
-p 9000:9000 \
-p 8123:8123 \
-p 9009:9009 \
--ulimit nofile=262144:262144 \
yandex/clickhouse-server

docker run -it --rm --link clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server

CREATE DATABASE  test

CREATE TABLE t_order
(
    OrderID UInt32,
    UserID UInt64,
    EventDate Date
)
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (OrderID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

INSERT INTO t_order VALUES (11111, 11111, now()), (22222, 22222, now())

CREATE TABLE t_order
(
    OrderID UInt32,
    UserID UInt64,
    driverIds Array(String),
    Driver Nested
    (
        ID String,
        DriverName String
    ),
    EventDate DateTime
)
ENGINE MergeTree() PARTITION BY toYYYYMMDD(EventDate) ORDER BY (OrderID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

INSERT INTO t_order VALUES (11111, 11111,['33','44','55'], ['33','44','55'],['n33','n44','n55'],now()), (22222, 22222,['201','202','203'],['201','202','203'],['n201','n202','n203'], now())

Select * from t_order where has(driverIds,'33')

INSERT INTO t_order VALUES (33333, 33333,['33','44','55'], ['33','44','55'],['n33','n44','n55'],now())

Select * from (Select OrderID, Driver.DriverID as driverId, Driver.DriverName as driverName from t_order_d ARRAY JOIN Driver) where driverId  = '33'

CREATE TABLE t_order1
(
    OrderID UInt32,
    UserID UInt64,
    driverIds Array(String),
    Driver Nested
    (
        ID String,
        DriverName String
    ),
    EventDate DateTime,
 INDEX driverId_idx Driver.ID TYPE minmax GRANULARITY 5
)
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (OrderID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

INSERT INTO t_order1 VALUES (11111, 11111,['33','44','55'], ['33','44','55'],['n33','n44','n55'],now()), (22222, 22222,['201','202','203'],['201','202','203'],['n201','n202','n203'], now())

CREATE TABLE t_order3
(
    OrderID UInt32,
    UserID UInt64,
    driverIds Array(String),
    Driver Nested
    (
        ID String,
        DriverName String
    ),
    EventDate DateTime
)
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate)

INSERT INTO t_order2 VALUES (11111, 11111,['33','44','55'], ['33','44','55'],['n33','n44','n55'],now()), (22222, 22222,['201','202','203'],['201','202','203'],['n201','n202','n203'], now())

查看执行计划
1、设置日志级别
set send_logs_level='trace'
2、执行sql
select * from t_order1 where OrderID=11111

CREATE TABLE t_1
(
    OrderID UInt32,
    UserID UInt64,
    driverIds Array(String),
    Driver Nested
    (
        ID String,
        DriverName String
    ),
    EventDate DateTime
)
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (OrderID) SAMPLE BY OrderID SETTINGS index_granularity=8192

INSERT INTO t_1 VALUES (11111, 11111,['33','44','55'], ['33','44','55'],['n33','n44','n55'],now()), (22222, 22222,['201','202','203'],['201','202','203'],['n201','n202','n203'], now())

Select * from t_1 where UserID = 11111

CREATE TABLE t_2
(
    OrderID UInt32,
    UserID UInt64,
    driverIds Array(String),
    Driver Nested
    (
        ID String,
        DriverName String
    ),
    EventDate DateTime,
 INDEX UserID_idx UserID TYPE minmax GRANULARITY 5
)
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (OrderID) SAMPLE BY OrderID SETTINGS index_granularity=8192

INSERT INTO t_2 VALUES (11111, 11111,['33','44','55'], ['33','44','55'],['n33','n44','n55'],now()), (22222, 22222,['201','202','203'],['201','202','203'],['n201','n202','n203'], now())

Select * from t_2 where UserID = 11111

CREATE TABLE t_order
(
    traceId String,
    appid String,
    requestId String,
    createTime DateTime64,
 createTime DateTime64,
 sendTime DateTime64,
 eventTime DateTime64,
    scene String,
)
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (OrderID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192