clickhouse如何统计哪些表既没有查询也没有导入

思考：

嗯，我现在需要解决的是如何在ClickHouse中统计那些既没有被查询过也没有被导入数据的表。这个问题看起来有点挑战性，不过我可以一步步来思考。

首先，我需要明确需求：找出所有表，这些表在数据库中没有被任何查询操作访问过，同时也没有通过INSERT或其他方式导入过数据。也就是说，这些表是“冷”的，没有被使用过。这对于清理无用数据或优化存储可能很有帮助。

接下来，我需要考虑ClickHouse中有哪些系统表或函数可以提供这方面的信息。记得ClickHouse有几个系统表，比如system.tables、system.parts、system.merges、system.query_log等。可能需要结合这些表的数据来得到结果。

首先，关于表的导入数据情况，也就是是否有INSERT操作。这可能涉及到system.parts表，因为每次插入数据都会生成新的part。如果一个表没有任何part，可能说明它从未被插入过数据。不过，新建的表可能还没有被插入数据，所以需要排除这种情况吗？或者用户可能想包括所有未被插入的表，包括刚创建的？

然后，关于查询的情况，也就是是否有SELECT查询被执行过。这可能需要查询system.query_log，这个表记录了所有的查询操作。但需要注意的是，query_log可能不会永久保留所有日志，特别是如果配置了日志保留策略的话。例如，默认情况下可能只保留最近几天的日志。因此，如果用户的查询是在很久以前执行的，可能无法从query_log中找到记录。这可能影响结果的准确性。

那么，可能的步骤是：

1. 找出所有表，并确定它们是否有数据（即是否有parts）。

2. 找出这些表是否有最近的查询记录。

3. 结合这两个条件，筛选出既没有数据也没有查询的表。

首先，如何判断表是否有数据？可以通过查询system.parts表。例如，对于每个表，检查其对应的database和table名称，在system.parts中是否存在对应的记录。如果parts的数量为0，则说明该表没有数据。

但是，需要注意MergeTree引擎的表可能会有多个parts，而其他引擎如Memory或者Log可能结构不同。不过，假设我们关注的是MergeTree引擎的表，或者其他类似存储引擎的表，这些表的数据存储在parts中。对于其他引擎，可能需要不同的处理方式。但用户的问题可能更关注于数据是否被导入，所以可能需要针对所有表类型来判断是否有数据存在。

不过，可能更简单的方法是检查表是否有任何数据，不管引擎类型。例如，对于MergeTree表，检查parts表；对于其他引擎，可能是否有其他方式。或者，是否有更通用的方法？

另外，ClickHouse中的表可能分为内部表和视图等，需要排除视图，只检查普通表。这可能需要在查询system.tables时过滤掉视图，即system.tables中的engine不为空或者不是视图类型。

然后，关于查询的情况，需要检查是否有SELECT查询在特定时间段内被执行过。这需要查询system.query_log，过滤出对应的database和table，并且查询类型是SELECT。但这里的问题是如何将查询关联到具体的表。因为一个查询可能涉及多个表，比如JOIN操作，所以需要解析每个查询的涉及的表。

但解析每个查询的表可能比较复杂，尤其是在大规模数据下。这可能不太实际。有没有更简便的方法？

或者，是否有其他系统表或函数可以追踪表的访问情况？比如，是否有统计信息或元数据记录表的访问次数？

目前，可能只能通过query_log来获取相关信息。但需要处理的问题包括：

• query_log可能包含大量数据，查询效率可能不高。

• 需要确定时间范围，比如过去一个月内是否有查询。

• 需要解析每个查询涉及的表，这可能比较困难。

或许，可以考虑另一种方法：如果表从未被查询过，那么可能它的元数据中的某些指标未被更新，或者是否有其他标志？

暂时可能只能通过结合system.parts和system.query_log来判断。

那么，具体步骤可能如下：

1. 获取所有用户表的列表，排除视图。可以通过查询system.tables，其中engine不为空（假设视图在system.tables中的engine字段为空？或者是否有其他标识？需要确认。）

例如：

SELECT database, name FROM system.tables WHERE engine != ''

或者可能需要更准确的判断，比如视图的engine可能是视图类型，但具体需要查证。根据ClickHouse的文档，系统表中的tables表中的engine字段对于视图来说是'View'，所以应该排除engine为'View'的表。

所以正确的查询应该是：

SELECT database, name FROM system.tables WHERE engine != 'View'

这样得到所有普通表。

2. 对于每个这样的表，检查是否有数据。可以通过查询system.parts表，按数据库和表名分组，统计是否有parts存在。

例如，对于每个表，是否有：

SELECT count(*) FROM system.parts WHERE database = 'db_name' AND table = 'table_name'

如果count(*) > 0，则有数据，否则没有。

3. 同时，检查是否有查询记录。需要查询system.query_log，过滤出对应的数据库和表，并且查询类型是SELECT或其他操作。但如何关联查询日志中的表？

这里的问题在于，query_log中的每个条目可能有不同的查询，其中涉及的表可能有多个。例如，一个SELECT查询可能涉及多个表的JOIN。因此，需要解析每个查询的文本内容，提取出涉及的表名，然后判断当前表是否在其中。

这在SQL层面处理起来可能比较复杂，尤其是当表数量很多的时候。例如，对于每个表，需要检查是否存在至少一条查询日志，其中该表被提及。

这可能需要用到字符串匹配，或者更复杂的正则表达式，但这可能会非常低效，尤其是在有大量查询日志的情况下。

或者，是否有其他方法？比如，使用ClickHouse的元数据或访问统计信息？

目前看来，可能只能通过query_log来近似判断。

但这种方法有几个问题：

• query_log可能被轮转或压缩，导致旧日志不可用。

• 解析查询中的表名可能不准确，特别是当表别名被使用时。

• 性能问题，因为需要对每个表扫描大量的查询日志。

因此，这种方法可能不太可行，尤其是在生产环境中。

那有没有其他方法？

或许，可以考虑利用ClickHouse的统计信息功能，比如使用system.metrics或system.events，但不确定是否有相关的指标记录表的访问情况。

另一个思路是，如果表从未被查询过，那么可能其对应的元数据中的访问时间戳未被更新。但ClickHouse是否维护这样的元数据？例如，表的最后访问时间？目前似乎没有这样的系统字段。

因此，可能只能通过结合system.parts和query_log来判断。

那么，可能的解决方案是：

• 找出所有非视图表。

• 排除那些在system.parts中有数据的表。

• 然后，在剩下的表中，进一步排除那些在query_log中被查询过的表。

最终剩下的就是既没有数据也没有被查询过的表。

但如何高效地实现这一点？

首先，获取所有非视图表：

WITH all_tables AS (
SELECT database, name
FROM system.tables
WHERE engine != 'View'
)

然后，找出这些表中哪些没有数据：

SELECT
database,
name
FROM all_tables
WHERE NOT EXISTS (
SELECT 1
FROM system.parts
WHERE database = all_tables.database
AND table = all_tables.name
)

这部分可以得到没有数据的表。

接下来，需要从这些表中排除那些曾被查询过的表。这时候需要查询query_log。

假设query_log中有一个字段query_database和query_table，但实际上，query_log中的每个查询可能有不同的结构。例如，对于SELECT查询，可能涉及多个表，但query_log中的字段可能只记录了当前查询的数据库和表，或者需要解析query字段中的内容。

例如，query_log中的每个条目有query字段，其中包含SQL语句。我们可以用正则表达式来提取涉及的表名。

但这样做的话，对于每个表，需要检查是否存在至少一个query_log条目，其中该表出现在查询中。

这可能需要写一个复杂的正则表达式，或者使用ClickHouse的字符串处理函数。

例如，假设我们要找出所有查询中包含特定表名的记录，可以这样做：

SELECT DISTINCT
query_database,
query_table
FROM system.query_log
WHERE
type = 'QueryStart'
AND (event_date >= addDays(now(), -30)) -- 最近30天内的查询
AND query LIKE '%SELECT%FROM%table_name%'

但这种方法的问题在于，table_name可能出现在别名中，或者被字符串包含，导致误判。例如，表名为user，而查询中有字符串'username'，就会误匹配。

因此，这种方法并不可靠。

另一个方法是，解析查询中的FROM子句，提取表名。这可能需要更复杂的处理，比如使用正则表达式来匹配FROM后面的表名，但需要考虑可能的模式，如带别名、子查询、JOIN等情况。

这可能在SQL中难以实现，尤其是对于每个表来说，需要动态生成这样的查询。

因此，这可能不是一个可行的方案。

那么，有没有其他方法？

或许，可以假设，如果表没有被插入数据（即system.parts中没有parts），并且该表所在的数据库没有相关的查询日志，那么可以认为该表未被使用。但这样可能不够准确，因为可能表被查询过但没有被插入数据。

或者，用户可能希望统计的是既没有数据，也没有被查询过的表。这种情况下，即使表有数据但没被查询过，或者被查询过但没有数据，都不符合条件。只有两者都满足才符合条件。

所以，正确的逻辑是：

表必须满足：

1. 没有数据（即system.parts中没有该表的part）

2. 在query_log中没有该表的任何查询记录（无论是SELECT还是其他操作）

但如何判断第二个条件？

或者，用户可能将“没有导入数据”理解为没有INSERT操作，而“没有查询”则指没有SELECT或其他操作。这可能需要分别统计。

但回到原问题，用户的需求是统计既没有查询也没有导入数据的表。这里的“导入数据”可能指的是INSERT操作，而“查询”指的是SELECT或其他读取操作。

因此，可能需要分别检查是否有INSERT操作和SELECT操作，或者综合判断是否有任何类型的查询。

但无论如何，这都需要分析query_log中的记录。

但处理这个问题的复杂度很高，因此可能需要寻找一种近似的方法，或者在特定条件下的解决方案。

可能的替代方案：

假设用户只需要知道哪些表从未有过任何数据（即parts数为0），并且从未被查询过（即query_log中没有相关记录）。这时候，可以分两步：

1. 找出所有没有数据的表。

2. 在这些表中，排除那些在query_log中被查询过的表。

但如何高效地完成第二步？

例如，可以先收集所有没有数据的表，然后检查这些表是否在query_log中出现过。

这可能需要将两个步骤结合起来，例如：

WITH no_data_tables AS (
SELECT
database,
name
FROM system.tables
WHERE engine != 'View'
AND NOT EXISTS (
SELECT 1
FROM system.parts
WHERE database = system.tables.database
AND table = system.tables.name
)
),
queried_tables AS (
SELECT DISTINCT
query_database,
query_table
FROM system.query_log
WHERE
type IN ('QueryStart', 'QueryFinish')
AND event_time >= addDays(now(), -30) -- 最近30天的查询
AND (query LIKE '%SELECT%' OR query LIKE '%INSERT%' OR ...) -- 可选：根据需要调整查询类型
)
SELECT
ndt.database,
ndt.name
FROM no_data_tables ndt
LEFT JOIN queried_tables qt
ON ndt.database = qt.query_database
AND ndt.name = qt.query_table
WHERE qt.query_database IS NULL

这将返回那些没有数据且在过去30天内没有被查询过的表。

但这里的问题有几个：

1. query_log中的query字段可能包含表名，但如何准确提取？例如，使用LIKE语句可能不准确，容易误判。

2. query_log可能包含大量的数据，导致查询速度很慢。

3. 需要确定时间范围，比如过去多久的查询记录需要考虑。

4. 需要处理不同的查询类型，比如SELECT、INSERT、UPDATE等，以确定是否被查询过。

另一个可能的优化是，将query_log中的表名提取出来，建立一个更结构化的视图，但这样可能需要预先处理数据，或者使用物化视图，但这可能不太现实。

或者，可以使用ClickHouse的字符串函数来提取表名。例如，假设FROM子句中的表名通常出现在特定位置，可以用正则表达式提取。例如：

SELECT
arrayStringConcat(regexpExtractAll(query, 'FROM\s+([`\w]+)', 1)) as tables
FROM system.query_log
WHERE ...

然后，将提取到的表名与no_data_tables中的表进行匹配。但这仍然可能存在误匹配的问题。

综上，这个问题的解决方案可能比较复杂，因为需要准确关联查询日志中的表名和系统表中的表名，而现有的系统表可能没有直接提供这种关联。

可能的替代思路是，如果用户可以接受近似的结果，可以仅检查是否有数据，然后假设没有数据的表也没有被查询过。但这样显然不准确，因为可能存在已经被查询过但之后数据被删除的表，导致parts为空，但仍有查询记录。

因此，这可能无法满足用户的需求。

另一个可能性是，用户可能只关心那些从未有过任何INSERT操作，并且从未被查询过的表。在这种情况下，可以分别检查是否有INSERT操作和SELECT操作。

例如，对于没有数据的表，可能是因为从未有INSERT操作，或者INSERT后又被DROP PARTITION等操作清空了数据。所以，即使有INSERT操作，如果现在parts为空，也可能被视为没有数据。

但用户的问题可能更关注于当前状态，即当前没有数据，且没有被查询过。

回到原问题，可能需要采用以下步骤：

1. 找出所有非视图表。

2. 找出这些表中没有parts的表（即没有数据）。

3. 对于这些表，检查query_log中是否有针对它们的查询记录。

但第三步的实现较为困难，因为需要解析query_log中的表名。

可能的折中方案是，忽略查询部分的检查，只检查是否有数据。或者，反过来，如果用户的主要目的是找出未被使用的表，可能结合其他指标，比如最后插入时间或最后查询时间。

例如，ClickHouse的system.parts表中有每个part的创建时间，可以找到表的最后插入时间。如果最后插入时间为NULL或很久以前，则可能认为该表未被导入数据。同时，结合query_log中的最后查询时间，如果两者都很久远，则认为未被使用。

但如何获取表的最后查询时间呢？这可能需要分析query_log，找出针对该表的最后一次查询时间。

这可能同样复杂。

或许，另一种方法是利用MergeTree引擎的系统表。例如，system.parts中的信息可以判断是否有数据，而system.merges中的信息可能提供合并操作的次数和时间，但这可能与查询无关。

总结来说，可能的解决方案是：

• 确定哪些表没有数据（通过system.parts）。

• 确定这些表是否在query_log中被查询过，这可能需要复杂的字符串处理或正则表达式匹配。

但考虑到性能和准确性，这可能不太可行。因此，可能需要寻找其他方法，或者接受一定的限制。

例如，可以假设，如果表在system.parts中没有数据，并且在最近一段时间内（如过去7天）没有查询记录，则认为该表未被使用。这时候，可以结合以下两个条件：

1. 没有数据：system.parts中无记录。

2. 在query_log中，最近一段时间内没有针对该表的查询。

这种情况下，可以编写如下查询：

WITH no_data_tables AS (
SELECT
database,
name
FROM system.tables
WHERE engine != 'View'
AND NOT EXISTS (
SELECT 1
FROM system.parts
WHERE database = system.tables.database
AND table = system.tables.name
)
),
recent_queries AS (
SELECT
query_database,
query_table
FROM system.query_log
WHERE
event_time >= addDays(now(), -7) -- 最近7天的查询
AND type IN ('QueryStart', 'QueryFinish')
AND (query LIKE '%SELECT%' OR query LIKE '%INSERT%' OR query LIKE '%UPDATE%' OR query LIKE '%DELETE%')
)
SELECT
ndt.database,
ndt.name
FROM no_data_tables ndt
LEFT JOIN recent_queries rq
ON ndt.database = rq.query_database
AND ndt.name = rq.query_table
WHERE rq.query_database IS NULL

这将返回在过去7天内没有被查询或修改过，并且没有数据的表。但需要注意，这里的LIKE条件可能不够准确，可能会漏掉某些查询或者包含错误的表。

此外，这种方法还依赖于query_log的保留策略，如果日志被压缩或删除，可能无法查询到历史记录。

因此，这可能是一个可行的近似方案，但存在一定的局限性。

另一个可能的优化是，使用ClickHouse的元数据信息，例如，检查表的创建时间，如果表的创建时间很早，但没有任何数据，可能表明未被使用。但这并不准确，因为表可能被创建后插入数据然后删除。

综上所述，可能需要在现有条件下，结合system.parts和query_log的信息，尽可能接近用户的需求，同时接受一定的限制。

回答：

在ClickHouse中统计既没有查询也没有导入数据的表，可以通过以下步骤实现：

步骤1：确定没有数据的表

首先，从system.tables中筛选出非视图表，并检查system.parts以确认表中是否有数据。

WITH no_data_tables AS (
SELECT
database,
name
FROM system.tables
WHERE engine != 'View' -- 排除视图
AND NOT EXISTS (
SELECT 1
FROM system.parts
WHERE database = system.tables.database
AND table = system.tables.name
)
)

步骤2：检查近期查询记录

通过system.query_log查找近期是否有针对这些表的查询。这里假设检查过去7天的查询，并匹配常见的操作类型（如SELECT、INSERT等）。

recent_queries AS (
SELECT
query_database,
query_table
FROM system.query_log
WHERE
event_time >= addDays(now(), -7) -- 近期时间范围
AND type IN ('QueryStart', 'QueryFinish') -- 查询类型
AND (query LIKE '%SELECT%'
OR query LIKE '%INSERT%'
OR query LIKE '%UPDATE%'
OR query LIKE '%DELETE%')
)

步骤3：关联并筛选结果

将无数据的表与近期查询记录进行左连接，筛选出未被查询的表。

SELECT
ndt.database,
ndt.name
FROM no_data_tables ndt
LEFT JOIN recent_queries rq
ON ndt.database = rq.query_database
AND ndt.name = rq.query_table
WHERE rq.query_database IS NULL;

注意事项

1. 查询日志保留时间：system.query_log可能只保留最近的数据，旧日志可能已被清理。

2. 查询匹配准确性：使用LIKE可能导致误判（如表名出现在字符串中），建议根据实际情况调整正则表达式或过滤条件。

3. 性能考虑：若查询日志庞大，此操作可能较慢，建议在低峰时段执行或限制时间范围。

扩展建议

• 自动化监控：定期运行此查询并记录结果，便于跟踪表的使用情况。

• 结合其他指标：如检查表的最后修改时间（system.parts中的max(part_max_date)）进一步确认是否活跃。

此方法提供了一个近似解决方案，需根据实际环境调整参数和条件以提高准确性。