Copy Table History

Jim_Knicely

The COPY_TABLE function copies one table to another… FAST!

Down the road I might want to know if a table was created as a copy using the COPY_TABLE function. To find out, I can query the data collector table DC_COPY_TABLES.

Example:

dbadmin=> SELECT COUNT(*) FROM big_fact;
   COUNT
-----------
200000000
(1 row)

dbadmin=> \timing on
Timing is on.

dbadmin=> SELECT copy_table('big_fact', 'big_fact_copy');
                              copy_table
----------------------------------------------------------------------
Created table big_fact_copy.
Copied table big_fact to big_fact_copy

(1 row)

Time: First fetch (1 row): 179.852 ms. All rows formatted: 179.901 ms

dbadmin=> \timing off
Timing is off.

dbadmin=> SELECT table_name, description
dbadmin->   FROM data_collector
dbadmin->  WHERE component = 'CopyTables';
   table_name   |                       description
----------------+----------------------------------------------------------
dc_copy_tables | History of all tables copied by copy_table meta-function
(1 row)

dbadmin=> SELECT * FROM dc_copy_tables;
             time              |     node_name      | from_table |   to_table    | commit_epoch | table_created
-------------------------------+--------------------+------------+---------------+--------------+---------------
2018-12-10 08:10:37.086735-05 | v_test_db_node0001 | big_fact   | big_fact_copy |         2606 | t
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/COPY_TABLE.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm

Have fun!