Copy data within tables

CrateDB SQL
1

Hi,
what is the best way to copy 100 Million records from one table to another having multiple partition.
Tables are there within 6 nodes having 3 GB of heap size for each node.

Thanks in advance.

2 
INSERT INTO new_table (col_1, col_2) SELECT col_1, col_2 FROM old_table;

should work fine.

3

Do you think this single query without any where clause will copy 100 millions of records from one table to second table ? I think we also need to consider time as there 100 millions of records size in 2 TB. I just want to know that this might create impact on my hard-disk and CPU both and also may impact my running processes.

4

Yes, this is typically no problem.It will take some time for sure with 2TB
Also this operation is typically throttled (maybe even too much right now).

There is also already an optimisation on the way to improve the performance:

An alternative would be to use COPY FROM / TO

1 Like
5

You can improve the performance by setting the number_of_replicas='0' and the refresh_interval=0 temporarily.

1 Like