Monitoring COPY Statement Events (Part 1): Quick Tip

Posted January 23, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Programmer
A COPY command is typically composed of two phases. The first phase includes reading the data from the source, parsing the data, segmenting the data and sending it to the nodes after it is segmented.

You can monitor the load events of this phase of the COPY command via the data collector table DC_LOAD_EVENTS.

Example: dbadmin=> SELECT DISTINCT component, description dbadmin-> FROM data_collector dbadmin-> WHERE table_name = 'dc_load_events'; component | description ------------+------------------------------------------------- LoadEvents | History of important events during load parsing (1 row) dbadmin=> COPY bf FROM '/home/dbadmin/bf.txt' ON ANY NODE DIRECT; Rows Loaded ------------- 1000000 (1 row) dbadmin=> SELECT time::time, RIGHT(node_name, 1) node, uri, load_id, event_type, event_description, rows_accepted, rows_rejected dbadmin-> FROM dc_load_events dbadmin-> WHERE session_id = current_session() dbadmin-> ORDER BY time; time | node | uri | load_id | event_type | event_description | rows_accepted | rows_rejected -----------------+------+----------------------+---------+-------------+-------------------------------------------------------+---------------+--------------- 08:49:35.869309 | 3 | | | PARSEBEGIN | Parsers have been setup and will begin execution now. | | 08:49:35.870678 | 2 | | | PARSEBEGIN | Parsers have been setup and will begin execution now. | | 08:49:35.873322 | 3 | /home/dbadmin/bf.txt | 10 | SOURCEBEGIN | Data in this source will be processed now. | | 08:49:35.873409 | 3 | /home/dbadmin/bf.txt | 11 | SOURCEBEGIN | Data in this source will be processed now. | | 08:49:35.873474 | 1 | | | PARSEBEGIN | Parsers have been setup and will begin execution now. | | 08:49:35.874465 | 2 | /home/dbadmin/bf.txt | 12 | SOURCEBEGIN | Data in this source will be processed now. | | 08:49:35.874619 | 2 | /home/dbadmin/bf.txt | 13 | SOURCEBEGIN | Data in this source will be processed now. | | 08:49:35.8771 | 1 | /home/dbadmin/bf.txt | 45 | SOURCEBEGIN | Data in this source will be processed now. | | 08:49:35.877439 | 1 | /home/dbadmin/bf.txt | 46 | SOURCEBEGIN | Data in this source will be processed now. | | 08:49:36.658454 | 1 | /home/dbadmin/bf.txt | 45 | SOURCEDONE | All data in this source has been parsed | 166667 | 0 08:49:36.671138 | 1 | /home/dbadmin/bf.txt | 46 | SOURCEDONE | All data in this source has been parsed | 166666 | 0 08:49:36.671667 | 3 | /home/dbadmin/bf.txt | 10 | SOURCEDONE | All data in this source has been parsed | 166667 | 0 08:49:36.681855 | 2 | /home/dbadmin/bf.txt | 12 | SOURCEDONE | All data in this source has been parsed | 166667 | 0 08:49:36.691716 | 2 | /home/dbadmin/bf.txt | 12 | SOURCEDONE | All data in this source has been parsed | 166666 | 0 08:49:36.706006 | 3 | /home/dbadmin/bf.txt | 11 | SOURCEDONE | All data in this source has been parsed | 166667 | 0 (15 rows) Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/EnablingAndDisablingDataCollector.htm

Have fun!