Insert all rows from one table to another of identical structure

Question

I'm trying to move all contents of table to another table with the same structure. There are many rows, so when I try to execute it like this

insert into target_table select * from source_table;

I get this kind of error:

ClickHouse exception, code: 159; Read timed out

I suppose it tries to load whole SELECT data into RAM and then insert it into table, which is not possible in this case. Is there some specialized ways to do so?

I would not like to select and insert data in cycle by small portions either.

Edit: I am searching for an SQL solution.

Answer 1

ClickHouse itself should process such queries properly.

For simple SELECT * it does not need to read a whole data into memory, it should work in a streaming manner.

Most probably your client just gets a timeout while waiting for confirmation of operation finishing from ClickHouse.

So you have the following options:

1. just increase your client connection timeout

2. copy data by multiple inserts - slice data by some conditions.

   INSERT INTO xxx SELECT * FROM table WHERE column BETWEEN 1 and 10000;
   INSERT INTO xxx SELECT * FROM table WHERE column BETWEEN 10001 and 20000;
   ...
   

3. the most efficient way with MergeTree family table and when table structures are exactly the same is to copy data by copying partitions directly from one table to another

   ALTER TABLE dst_table ATTACH PARTITION '2019-01-01' FROM source_table;
   
   -- you get get the list of partitions like that:
   SELECT partition
      FROM system.parts
      WHERE database = '...' and table = 'source_table' and active = 1
      GROUP BY partition;
   

4. if you just need to have another copy of data (for example with another key), you can just fill it with Materialized view automatically

   CREATE MATERIALIZED VIEW str2dst TO dest_table AS SELECT * FROM source_table;
   

5. if you need to move huge amounts of data especially between clusters - clickhouse-copier is the best option.

Answer 2

Yes, select-insert is only a good solution when using small tables.
You can use clickhouse-copier for large tables to move them within the cluster or to another cluster.
Read about it here