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