ClickHouse常用操作
一、客户端连接
1.1 客户端连接ck
./clickhouse-client -h 127.0.0.1 --port 9900 -u default --password 123456 -m
1.2 https方式:或jdbc方式
echo 'select 1' | curl 'http://127.0.0.1:8123/?user=default&password=123456&query=' --data-binary @-
1.3 web方式
http://127.0.0.1/#!/sql
http://127.0.0.1:8123
账号:default 密码123456
1.4 jdbc方式操作ck
java -jar clickhouse-executor-1.0.jar -h 127.0.0.1 -P 9527 \
-u default_write \
-p "123456" \
-q "select 1"
二、建表
2.1创建本地表(保留3个月)
CREATE TABLE if not exists bdg_inf.dws_compass_event_local ON CLUSTER production
(
`event_id` Int64 COMMENT '事件id',
`biz_type` Int16 COMMENT '业务线',
`client_type` Int16 COMMENT '客户端类型',
`event_type` Int16 COMMENT '事件类型',
`sdk_version` String COMMENT 'sdk版本',
`distinct_id` String COMMENT '唯一id',
`client_name` String COMMENT '客户端名称',
`partner_id` String COMMENT '',
`create_time` Int64 COMMENT '日志清洗时间',
`dt` String COMMENT '分区字段'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/bdg_inf.dws_compass_event_local', '{replica}')
PARTITION BY (dt)
ORDER BY (event_id, biz_type, client_type, event_type, sdk_version)
TTL parseDateTimeBestEffort(dt) + toIntervalMonth(3)
SETTINGS index_granularity = 8192
2.2 创建分布式表
CREATE TABLE if not exists bdg_inf.dws_compass_event ON CLUSTER production
(
`event_id` Int64 COMMENT '事件id',
`biz_type` Int16 COMMENT '业务线',
`client_type` Int16 COMMENT '客户端类型',
`event_type` Int16 COMMENT '事件类型 1:浏览 2:点击 3:播放 4:展示 5:长按 6:页面响应 7:时间消耗',
`sdk_version` String COMMENT 'sdk版本',
`distinct_id` String COMMENT '唯一id',
`client_name` String COMMENT '客户端名称',
`partner_id` String COMMENT '',
`create_time` Int64 COMMENT '日志清洗时间',
`dt` String COMMENT '分区字段'
)
ENGINE = Distributed('production', 'bdg_inf', 'dws_compass_event_local', rand())
2.3 删除表
drop table bdg_inf.dws_compass_event on cluster production;
drop table bdg_inf.dws_compass_event_local on cluster production;