# 一、服务启停

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

# 二、客户端连接

## 多行输入

```bash
clickhouse-client -d test -m -n
```

其中，

- -m：可以将SQL分成多行输入；
- -n：可以输入多条SQL。

## 执行.sql查询文件

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

## 查看执行计划

```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.
```

# 三、显示库与表

```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  │              │                    │         │                  │                │
└─────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

# 四、建表与建库

```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中有全部配置参数信息。

```sql
-- 查询参数：
select * from system.settings where name = 'group_by_two_level_threshold_bytes';
-- 设置参数：
set group_by_two_level_threshold_bytes = 1000000;
```

# 六、加载

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

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

# 七、其它

## 执行计划

```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](https://clickhouse.tech/docs/en/)
- [ClickHouse官方中文文档](https://clickhouse.tech/docs/zh/)
- [ClickHouse表引擎到底怎么选](https://developer.aliyun.com/article/739805)