ClickHouse数据查询处理高级技巧

本文介绍ClickHouse查询远程服务、多表联合查询、查询特定分区，以及如何交换表和软删除表。

查询远程ClickHouse服务

如果有多个ClickHouse 集群，可能需要发送远程请求查询。ClickHouse很容易实现，通过内置函数即可实现。

可以在from子句中使用remoteSecure 和 remote ，读取远程服务。语法如下：

cluster('cluster_name', db.table[, sharding_key])
cluster('cluster_name', db, table[, sharding_key])
clusterAllReplicas('cluster_name', db.table[, sharding_key])
clusterAllReplicas('cluster_name', db, table[, sharding_key])

下面举例通过remote函数实现远程查询：

SELECT count()
FROM remote('127.0.0.1:9000', 'default.t1') AS t
WHERE date >= today()

Query id: 1868afed-9689-4605-9675-4be4a7725ea4

┌─count()─┐
│   14191 │
└─────────┘

如果以及配置远程服务作为集群，可以发送查询给整个集群，使用clusterAllReplicas函数，举例：

parallel_replicas

Query id: e53b1851-3246-4d40-8c95-687d3fde5958

Row 1:
──────
hostName():                 host-db-2
database:                   database_name
table:                      table_name
mutation_id:                0000000004
command:                    MATERIALIZE TTL
create_time:                2022-09-19 16:34:24
block_numbers.partition_id: ['all']
block_numbers.number:       [238358]
parts_to_do_names:          ['all_164055_173528_6_184171']
parts_to_do:                1
is_done:                    0
latest_failed_part:         
latest_fail_time:           1970-01-01 00:00:00
latest_fail_reason:         

1 rows in set. Elapsed: 0.017 sec.

查询多表数据

如果需要一次性从多个相同结构表或有共同子集表中查询数据，对于其他RDBMS可能需要使用union all 语法，示例如下：

SELECT max(d1)
FROM
(
    SELECT *
    FROM t_20220919
    UNION ALL
    SELECT *
    FROM t_20220920
    UNION ALL
    SELECT *
    FROM t_20220920
)

在ClickHouse中能使用merge函数轻松实现，merge可以像单表一样查询多个表，语法更简单，且易读易维护：

SELECT max(d1)
FROM merge('_local', 't_202209*')

另外，也能使用regex模型匹配表名称，如果多个表命名按照一定规则，模式匹配可以自动包括在相应查询中。语法如下：

merge('db_name', 'tables_regexp')

举例下面是利用正则查询表中特定字段的值，col是列名称，后面正则是匹配值表达式：

SELECT * FROM tbl WHERE match(col, 'D.+')

查询分区

part 和 partition是ClickHouse数据库的主要组成部分。通常，part 用于存储表的部分数据，是磁盘上的物理文件。另一方面，partition是经常用于表中的性能和数据管理的逻辑结构。

如果需要查询分区表的part 或 partition，可以ClickHouse的虚拟列_part和 _partition_id.

• 查询特定part

SELECT
    count(),
    _part
FROM products
GROUP BY _part

Query id: e3cb8687-82ef-4d5c-9109-01d327d1a1ab

┌─count()─┬─_part─────┐
│     100 │ all_2_2_0 │
│   10000 │ all_3_3_0 │
└─────────┴───────────┘

SELECT avg(cost)
FROM products
WHERE _part = 'all_2_2_0'

Query id: 31a9632d-b902-4abe-bcc9-2d1b37b599cf

┌─────────avg(cost)─┐
│ 2114.058412771225 │
└───────────────────┘

1 row in set. Elapsed: 0.010 sec. 

• 查询特定partition

可以使用虚拟列_partition_id 过滤特定partition ,举例：

SELECT
    count(),
    _partition_id
FROM products
GROUP BY _partition_id

Query id: 50148c02-84a6-4be9-a21a-3f8b4cbc7bbe

┌─count()─┬─_partition_id─┐
│   10100 │ all           │
└─────────┴───────────────┘

1 row in set. Elapsed: 0.001 sec. 

交换表

典型场景中需要交换一个表示至另一个。我们有my_table和my_table_new ,希望 my_table_new 变为 my_table，如果你删除原表，然后重命名新表，但这样有一定风险，因为不能反向操作。ClickHouse提供了两种方式可以实现该功能。

如果两个表都是Atomic数据库，可以直接使用Exchange tables;

EXCHANGE TABLES db1.my_table AND db2.my_table_new ON CLUSTER my_cluster

这时原子交换，意味着并行操作不会失败，也不会出现其中一个表不存在的情况。

如果仍使用Ordinary数据库（提示：应该迁移至Atomic），可以Rename Table :

RENAME TABLE db1.my_table TO db1.my_table_tmp,
            db2.my_table_new TO db1.my_table,
            db1.my_table_tmp TO db2.my_table_new
            ON CLUSTER my_cluster

该命令与Exchange等效，但不是原子的，并发查询可能会报错，提示表不存在。两者都可以一次操作多个交换，但不是同时执行，但Exchange每个交换是原子的。

软删除表

可以直接通过下面语句删除表：

DROP TABLE table_name;

但有两种情况下不能删除表、或不太方便删除表。首先，MergeTree 表大于50GB不能被删除，这是由max_table_size_to_drop 参数限制的，需要修复服务端参数值。另外，删除表时要小心，在某种情况下还需要恢复。基于上述原因，需要软删除表，就是让不能再被查询，实际数据并没有被删除。

在 ClickHouse 中可以使用DETACH TABLE命令：

DETACH TABLE table_name PERMANENTLY;

如果重新启动 ClickHouse 服务器，它将不会加载分离表的数据，因此不会影响服务器的启动时间。如果需要可以使用ATTACH TABLE 进行恢复：

ATTACH TABLE table_name;

总结

上述功能一般RDBMS实现较难，ClickHouse提供的功能可以更高效、更便捷查询处理数据。