9

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.

Andrew
  • 123
  • 1
  • 1
  • 6

2 Answers2

16

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.

filimonov
  • 1,666
  • 1
  • 9
  • 20
4

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

swayamraina
  • 2,958
  • 26
  • 28
  • Thank you, this is a way. My bad I haven't mentioned it in original post, but my case is not DBA, but embedding this operation in program code, so I am looking for some sort of SQL query, if possible. – Andrew Mar 13 '19 at 10:53