snippet.bash

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

snippet.bash

clickhouse-client -d test -m -n

其中，

• -m：可以将SQL分成多行输入；
• -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) │
└───────────────────────────────────────────────────────┘

• ClickHouse Document
• ClickHouse官方中文文档
• ClickHouse表引擎到底怎么选