Hedon

# 5. ClickHouse SQL 操作

ClickHouse 的 SQL 语法与标准 SQL 非常相似，本文着重记录与标准 SQL 不一致的地方。

# 5.1 Insert

基本与标准 SQL（MySQL）一直：

  • 标准

    INSERT INTO [table_name] VALUES(...),(....)

  • 从表到表的插入

    INSERT INTO [table_name] SELECT a,b,c FROM [table_name_2]

# 5.2 Update、Delete

ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看做 Alter 的一种。

虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很 “重”的操作，而且不支持事务。

“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。 所以尽量做批量的变更，不要进行频繁小数据的操作。

  • 删除操作

    ALTER TABLE t_order_smt DELETE WHERE sku_id = 'sku_001';

  • 修改操作

    ALTER TABLE t_order_smt UPDATE total_amount=toDecimal32(2000.00, 2) WHERE id=102;

注意

由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

# 5.3 Select

ClickHouse 的查询操作与标准 SQL 差别不大：

  • 支持子查询
  • 支持 CTE(Common Table Expression 公用表表达式) with 子句
  • 支持各种 JOIN，但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句，ClickHouse 也会视为两条新 SQL
  • 窗口函数：https://clickhouse.com/docs/en/sql-reference/window-functions/
  • 不支持自定义函数
  • GROUP BY 增加了 with rollup \ with cube \ with total 用来计算小计和总计

  1. 创建表

    CREATE TABLE t_order_sql
(
    `id` UInt32,
    `sku_id` String,
    `total_amount` Decimal(16, 2),
    `create_time` Datetime
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY (id)
ORDER BY (id, sku_id);

  2. 插入数据

    INSERT INTO t_order_sql VALUES
(101,'sku_001',1000.00,'2020-06-01 12:00:00') , 
(102,'sku_002',2000.00,'2020-06-01 11:00:00'), 
(102,'sku_004',2500.00,'2020-06-01 12:00:00'), 
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'), 
(102,'sku_002',600.00,'2020-06-02 12:00:00');

  3. 查询数据

    
SELECT *
FROM t_order_sql

Query id: 17e30757-4c74-4734-9f69-1b47c288601f

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

6 rows in set. Elapsed: 0.007 sec.

  4. with rollup：从右至左去掉维度进行小计

    SELECT id, sku_id, sum(total_amount) FROM t_order_sql GROUP BY id, sku_id with rollup;

    输出：先去掉 sku_id 这个维度，再去掉 id 这个维度

    SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_sql
GROUP BY
    id,
    sku_id
    WITH ROLLUP

Query id: dc0f6810-8bc5-43d1-8fbf-63d9554efd31

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 102 │ sku_004 │           2500.00 │
│ 102 │ sku_002 │          16600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 102 │        │          19100.00 │
│ 101 │        │           1000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          20100.00 │
└────┴────────┴───────────────────┘

6 rows in set. Elapsed: 0.005 sec.

  5. with cube：从右至左去掉维度进行小计，再从左至右去掉维度进行小计

    SELECT id, sku_id, sum(total_amount) FROM t_order_sql GROUP BY id, sku_id with cube;

    输出：

    SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_sql
GROUP BY
    id,
    sku_id
    WITH CUBE

Query id: 843d959b-ca2e-4b23-86e2-70152bbbf4a4

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 102 │ sku_004 │           2500.00 │
│ 102 │ sku_002 │          16600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 102 │        │          19100.00 │
│ 101 │        │           1000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│  0 │ sku_004 │           2500.00 │
│  0 │ sku_001 │           1000.00 │
│  0 │ sku_002 │          16600.00 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          20100.00 │
└────┴────────┴───────────────────┘

9 rows in set. Elapsed: 0.008 sec.

  6. with totals：只计算合计

    SELECT id, sku_id, sum(total_amount) FROM t_order_sql GROUP BY id, sku_id with totals;

    输出：

    SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH TOTALS

Query id: 43e624e1-64e6-4748-a653-284a99dd86bd

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 102 │ sku_004 │           5000.00 │
│ 102 │ sku_002 │          33200.00 │
│ 101 │ sku_001 │           2000.00 │
└─────┴─────────┴───────────────────┘

Totals:
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          40200.00 │
└────┴────────┴───────────────────┘

3 rows in set. Elapsed: 0.010 sec.

# 5.4 Alter

  1. 新增字段

    ALTER TABLE tablename ADD COLUMN newcolname String after oldcolname;

  2. 修改字段类型

    ALTER TABLE tablename MODIFY COLUMN colname String;

  3. 删除字段

    ALTER TABLE tablename DROP COLUMN colname;

# 5.5 导出数据

clickhouse-client --query "select sql" --format CSVWithNames > $output_file_path

更多格式参考：https://clickhouse.com/docs/en/interfaces/formats/

上次更新: 8/13/2022, 10:59:27 AM

← 6. ClickHouse 副本集群 →