Monitoring COPY Statement Events (Part 1)
Jim_Knicely
- Select Field - Administrator
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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm
Have fun!
0