Merge Table Engine

Merge table engine enables the ClickHouse users to read the data simultaneously from multiple tables. The Merge table engine is different from MergeTree family of engines. This table engine allows parallel reading but writing and modifying the data is not supported. All the tables are required to have the same columns. Let us look at an example.

Step 1  – Create the tables that have to be merged

Create the following tables using the Log engine.

CREATE TABLE students_physics
(
StudentID UInt16,
Name String,
Score UInt8
)
Engine=Log;

CREATE TABLE students_chemistry
(
StudentID UInt16,
Name String,
Score UInt8
)
Engine=Log;

Step 2 – Insert data into those tables

Let us insert some randomly generated data into those tables and read the data.

INSERT INTO students_physics SELECT * FROM generateRandom() LIMIT 5;

INSERT INTO students_chemistry SELECT * FROM generateRandom() LIMIT 5;

Step 3 – Create a new table using Merge table engine

CREATE TABLE students_merge as students_physics ENGINE=Merge('default', '^students');

This table engine has two parameters. The first one is the database name and the next one is the regular expression to match the table names in the specified database and the data from those tables will be read in parallel.

In this example, we are going to read the data from the tables that match the pattern (‘^students’)

Step 4 – Read the Data from the Merge table.

SELECT * FROM students_merge;

References

https://clickhouse.com/docs/en/engines/table-engines/special/merge

https://clickhouse.com/docs/en/sql-reference/table-functions/generate#usage-example