Copy Table History
Jim_Knicely
- Select Field - Administrator
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!
0