Searching across nodes for tables with a wildcard
When to use it
This is useful when there are tables that have similar naming conventions and similar columns but are not replicated. An example is searching the system database for entries in the query log tables.
The query_log table is not replicated, and only queries that are executed on a specific node get logged. Data may also roll to a different table For example, data may be inserted into query_log_0, query_log_1, etc. Since one node may roll at a different time than others, it is useful to try to find the data we're looking for in tables that are not exactly named the same.
In essence, we need to do something like this, but in ClickHouse syntax:
SELECT column1, column2 FROM my_db.my_table_*
For this, we can use the clusterAllReplicas() to search all the nodes and the merge() table function to be able to use a regex pattern to search the multiple tables.
The following example shows how to query all tables with the prefix query_log:
Note that the columns you select must exist on each of the tables being queried or you may encounter an error such as:
Alternatively, you can use the EXCEPT clause to exclude any columns that may not be present on different tables.
example: