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

CREATE TABLE students_chemistry
StudentID UInt16,
Name String,
Score UInt8

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;



Was this article helpful?

Related Articles


In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.