Below query seems like the simplest way to create a replica of table. But simplicity comes with some cost as well.
Create table TABLE_NAME AS Select * from TABLE_NAME_2;
Above query will :
- NOT create partitions if there are any on TABLE_NAME_2
- run very slow
Instead of above we should follow following 2 way approach :
- CREATE TABLE TABLE_NAME Like TABLE_NAME_2; — This will create partitions as well if any in TABLE_NAME_2.
- Insert Into Table TABLE_NAME Select * from TABLE_NAME_2; — This DML will run faster.
CREATE TABLE test.table_1 AS SELECT * FROM main.table_2; // 72seconds , 9337252records, 875MB used as peak memory CREATE TABLE test.device1 LIKE main.table_2; INSERT INTO TABLE test.table_1 SELECT * FROM main.table_2; // 20seconds, 9337252records, 1.4GB used as peak memory.