一、服务启停

snippet.bash
#启动服务
$ service clickhouse-server start
#停止服务
$ service clickhouse-server stop

二、客户端连接

多行输入

snippet.bash
clickhouse-client -d test -m -n

其中，

执行.sql查询文件

snippet.bash
clickhouse-client -d test --multiquery < ~/1.sql

查看执行计划

snippet.bash
[clickhouse@bogon ~]$ clickhouse-client -d tpch1s --send_logs_level=trace  <<<"select count(*) from ORDERS;" >/dev/null
[bogon] 2020.09.30 08:43:42.440923 [ 30161 ] {dd65127b-403c-4615-a402-25b6f8451925} <Debug> executeQuery: (from [::1]:21200) select count(*) from ORDERS; 
[bogon] 2020.09.30 08:43:42.443507 [ 30161 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> ContextAccess (default): Access granted: SELECT(O_ORDERSTATUS) ON tpch1s.ORDERS
[bogon] 2020.09.30 08:43:42.443821 [ 30161 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> InterpreterSelectQuery: WithMergeableState -> Complete
[bogon] 2020.09.30 08:43:42.445372 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> MergingAggregatedTransform: Reading blocks of partially aggregated data.
[bogon] 2020.09.30 08:43:42.445604 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> MergingAggregatedTransform: Read 1 blocks of partially aggregated data, total 1 rows.
[bogon] 2020.09.30 08:43:42.445714 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> Aggregator: Merging partially aggregated single-level data.
[bogon] 2020.09.30 08:43:42.445817 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> Aggregator: Merged partially aggregated single-level data.
[bogon] 2020.09.30 08:43:42.445894 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> Aggregator: Converting aggregated data to blocks
[bogon] 2020.09.30 08:43:42.446022 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> Aggregator: Converted aggregated data to blocks. 1 rows, 8.00 B in 4.6309e-05 sec. (21594.07458593362 rows/sec., 168.70 KiB/sec.)
[bogon] 2020.09.30 08:43:42.446413 [ 30930 ] {dd65127b-403c-4615-a402-25b6f8451925} <Trace> PipelineExecutor: Thread finished. Total time: 0.00111135 sec. Execution time: 0.000941201 sec. Processing time: 0.000147057 sec. Wait time: 2.3092e-05 sec.
[bogon] 2020.09.30 08:43:42.449743 [ 30161 ] {dd65127b-403c-4615-a402-25b6f8451925} <Information> executeQuery: Read 1 rows, 4.01 KiB in 0.008650271 sec., 115 rows/sec., 463.32 KiB/sec.
[bogon] 2020.09.30 08:43:42.449857 [ 30161 ] {dd65127b-403c-4615-a402-25b6f8451925} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.

三、显示库与表

snippet.sql
bogon :) SHOW DATABASES;
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ DEFAULT                        │
│ system                         │
│ test                           │
└────────────────────────────────┘
 
bogon :) SHOW TABLES;
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_metrics           │
│ users                          │
└────────────────────────────────┘
 
bogon :) DESC aggregate_function_combinators
┌─name────────┬─TYPE───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ name        │ String │              │                    │         │                  │                │
│ is_internal │ UInt8  │              │                    │         │                  │                │
└─────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

四、建表与建库

snippet.sql
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE t1(a Int64, b String) ENGINE = MergeTree() ORDER BY (a) PRIMARY KEY (a);
DROP TABLE t1;

五、查询与设置参数

system.settings中有全部配置参数信息。

snippet.sql
-- 查询参数：
SELECT * FROM system.settings WHERE name = 'group_by_two_level_threshold_bytes';
-- 设置参数：
SET group_by_two_level_threshold_bytes = 1000000;

六、加载

加载源数据表使用CSV格式，使用竖线（ | ）分割列。

snippet.bash
clickhouse-client -d tpch1s --format_csv_delimiter="|" --query "INSERT INTO ORDERS FORMAT CSV" < /opt/tpch/data/1s/orders.tbl

七、其它

执行计划

snippet.sql
EXPLAIN SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM LINEITEM GROUP BY L_SUPPKEY);
┌─EXPLAIN───────────────────────────────────────────────┐
│ Expression (Projection)                               │
│   Expression (BEFORE ORDER BY AND SELECT)             │
│     Aggregating                                       │
│       Expression (BEFORE GROUP BY)                    │
│         Expression (Projection)                       │
│           Expression (BEFORE ORDER BY AND SELECT)     │
│             Aggregating                               │
│               Expression (BEFORE GROUP BY)            │
│                 ReadFromStorage (READ FROM MergeTree) │
└───────────────────────────────────────────────────────┘

八、参考