Copy Table History: Quick Tip

Posted December 11, 2018 by James Knicely, Vertica Field Chief Technologist

Business Team Meeting Discussion Working Concept
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/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/COPY_TABLE.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm Have fun!