Vertica Analytics Platform Version 9.2.x Documentation

LOAD_SOURCES

Like LOAD_STREAMS, monitors active and historical load metrics on each node. The LOAD_SOURCES table breaks information down by source and portion. Rows appear in this table only for COPY operations that are profiled or run for more than one second. LOAD_SOURCES does not record information about loads from ORC or Parquet files or COPY LOCAL.

A row is added to this table when the loading of a source or portion begins. Column values related to the progress of the load are updated during the load operation.

The columns uniquely identifying the load source (the various ID and name columns) and IS_EXECUTING always have non-NULL values.

Column Name Data Type Description
SESSION_ID

VARCHAR

Identifier of the session for which Vertica captures load stream information. This identifier is unique within the cluster for the current session but can be reused in a subsequent session.

TRANSACTION_ID 

INTEGER

Identifier for the transaction within a session. If a session is active, but no transaction has begun, this value is NULL.

STATEMENT_ID 

INTEGER

Unique numeric ID for the currently running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.

STREAM_NAME

VARCHAR

Load stream identifier. If the user does not supply a specific name, the STREAM_NAME default value is tablename-ID, where:

  • tablename is the table into which data is being loaded.
  • ID is an integer value. ID is guaranteed to be unique within the current session on a node.

This system table includes stream names for every COPY statement that takes more than 1 second to run. The 1-second duration includes the time to plan and execute the statement.

SCHEMA_NAME

VARCHAR

Schema name for which load information is listed. Lets you identify two streams that are targeted at tables with the same name in different schemas. NULL, if selecting from an external table.

TABLE_OID

INTEGER

A unique numeric ID assigned by the Vertica catalog that identifies the table. NULL, if selecting from an external table.

TABLE_NAME

VARCHAR

Name of the table being loaded. NULL, if selecting from an external table.

NODE_NAME

VARCHAR

Name of the node loading the source.

SOURCE_NAME

VARCHAR

  • Full file path if copying from a file.
  • Value returned by getUri() if the source is a user-defined source.
  • STDIN if loading from standard input.

PORTION_OFFSET

INTEGER

Offset of the source portion, or NULL if not apportioned.

PORTION_SIZE

INTEGER

Size of the source portion, or NULL if not apportioned.

IS_EXECUTING

BOOLEAN

Whether this source is currently being parsed, where t is true and f is false.

READ_BYTES

INTEGER

Number of bytes read from the input file.

ROWS_PRODUCED

INTEGER

Number of rows produced from parsing the source.

ROWS_REJECTED

INTEGER

Number of rows rejected from parsing the source. If CopyFaultTolerantExpressions is true, also includes rows rejected during expression evaluation.

INPUT_SIZE

INTEGER

Size of the input source in bytes, or NULL for unsized sources. For UDSources, this value is the value returned by getSize().

PARSE_COMPLETE_PERCENT

INTEGER

Percent of rows from the input file that have been parsed.

FAILURE_REASON

VARCHAR

For load failures, error message indicating why loading from this source failed or was cancelled:

  • In cases of a node failure, exception, or cancellation, this column contains the associated error message.
  • If ERROR TOLERANCE was not used and a failure occurred, "Source aborted due to exception while loading from (name)".
  • For any other failure, "Exception occurred during load".
PEAK_COOPERATING_THREADS

INTEGER

The peak number of threads parsing this source in parallel.

Privileges

No explicit privileges are required. You only see the records for tables that you have privileges to view.

Examples

The following example shows the values you would see in the LOAD_SOURCES table after some loads with various errors.

=> CREATE TABLE tt (i int);
=> COPY tt FROM '/tmp/load_sources_large.dat';
^CCancel request sent
ERROR 3322:  Execution canceled by operator
				
=> COPY tt FROM '/tmp/load_sources_large.dat' ON ANY NODE;
	Rows Loaded
	-------------
	1000000
	(1 row)

=> COPY tt FROM '/tmp/load_sources_*.txt' ON ANY NODE;
ERROR 2018:  COPY: Could not open file [/tmp/load_sources_perm.txt] for reading; Permission denied
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, 
it must be available at this path on all nodes in the cluster since any node could load the file				

=> COPY tt FROM '/tmp/load_sources_*.txt' ON ANY NODE ERROR TOLERANCE;
WARNING 7177:  Error loading from 'load_sources_perm.txt' on node 'e1'
DETAIL:  ERROR 2018:  COPY: Could not open file [/tmp/load_sources_perm.txt] for reading; Permission denied
WARNING 7176:  Error loading from 1/4 sources
	Rows Loaded
	-------------
	3000
	(1 row)
				
=> SELECT * FROM LOAD_SOURCES;
     session_id      |  transaction_id   | statement_id | stream_name | schema_name |     table_oid     | table_name | node_name |         source_name         | input_size | portion_offset | portion_size | read_bytes | parsed_bytes | rows_produced | rows_rejected | parse_complete_percent | is_executing |                                           failure_reason                                          
---------------------+-------------------+--------------+-------------+-------------+-------------------+------------+-----------+-----------------------------+------------+----------------+--------------+------------+--------------+---------------+---------------+------------------------+--------------+----------------------------------------------------------------------------------------------------
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354168 |              0 |      1354168 |    1048576 |       489777 |         53310 |         26655 |                     36 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354168 |        5416671 |      1354168 |    1048576 |       542407 |         49310 |         24655 |                     40 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354168 |        2708336 |      1354168 |    1048576 |       541838 |         49258 |         24629 |                     40 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354168 |        8125006 |      1354168 |    1048576 |       561390 |         51036 |         25518 |                     41 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354168 |        1354168 |      1354168 |    1048576 |       555788 |         50526 |         25264 |                     41 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354167 |        6770839 |      1354167 |    1048576 |       549467 |         49952 |         24976 |                     40 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354167 |        9479174 |      1354167 |    1048576 |       569858 |         51805 |         25903 |                     42 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            3 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1354167 |        4062504 |      1354167 |    1048576 |       547468 |         49770 |         24885 |                     40 | f            | ERROR 3322:  Execution canceled by operator
 initiator-9959:0x68 | 45035996273705528 |            4 |             | public      | 45035996273743434 | tt         | e0        | /tmp/load_sources_large.dat |    1805557 |        3611115 |      1805557 |    2097152 |      1805561 |        164141 |         82071 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705528 |            4 |             | public      | 45035996273743434 | tt         | e0        | /tmp/load_sources_large.dat |    1805556 |        5416672 |      1805556 |    2097152 |      1805558 |        164142 |         82070 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705528 |            4 |             | public      | 45035996273743434 | tt         | e1        | /tmp/load_sources_large.dat |    1805558 |              0 |      1805558 |    2097152 |      1805559 |        179293 |         89646 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705528 |            4 |             | public      | 45035996273743434 | tt         | e1        | /tmp/load_sources_large.dat |    1805557 |        1805558 |      1805557 |    2097152 |      1805563 |        164142 |         82071 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705528 |            4 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1805557 |        7222228 |      1805557 |    2097152 |      1805564 |        164142 |         82071 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705528 |            4 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_large.dat |    1805556 |        9027785 |      1805556 |    1805556 |      1805556 |        164140 |         82071 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705532 |            1 |             | public      | 45035996273743434 | tt         | e0        | /tmp/load_sources_2.txt     |       7838 |                |              |       7838 |          553 |            88 |            44 |                      7 | f            | Source aborted due to exception while loading from '/tmp/load_sources_perm.txt' on node 'e1'
 initiator-9959:0x68 | 45035996273705532 |            1 |             | public      | 45035996273743434 | tt         | e1        | /tmp/load_sources_1.txt     |       7838 |                |              |       7838 |          215 |            36 |            18 |                      2 | f            | Source aborted due to exception while loading from '/tmp/load_sources_perm.txt' on node 'e1'
 initiator-9959:0x68 | 45035996273705532 |            1 |             | public      | 45035996273743434 | tt         | e1        | /tmp/load_sources_perm.txt  |            |                |              |          0 |            0 |             0 |             0 |                        | f            | ERROR 2018:  COPY: Could not open file [/tmp/load_sources_perm.txt] for reading; Permission denied
 initiator-9959:0x68 | 45035996273705532 |            1 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_3.txt     |       7838 |                |              |       7838 |         2327 |           312 |           156 |                     29 | f            | Source aborted due to exception while loading from '/tmp/load_sources_perm.txt' on node 'e1'
 initiator-9959:0x68 | 45035996273705532 |            2 |             | public      | 45035996273743434 | tt         | e0        | /tmp/load_sources_2.txt     |       7838 |                |              |       7838 |         7838 |          1000 |           500 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705532 |            2 |             | public      | 45035996273743434 | tt         | e1        | /tmp/load_sources_1.txt     |       7838 |                |              |       7838 |         7838 |          1000 |           500 |                    100 | f            |
 initiator-9959:0x68 | 45035996273705532 |            2 |             | public      | 45035996273743434 | tt         | e1        | /tmp/load_sources_perm.txt  |            |                |              |          0 |            0 |             0 |             0 |                        | f            | ERROR 2018:  COPY: Could not open file [/tmp/load_sources_perm.txt] for reading; Permission denied
 initiator-9959:0x68 | 45035996273705532 |            2 |             | public      | 45035996273743434 | tt         | initiator | /tmp/load_sources_3.txt     |       7838 |                |              |       7838 |         7838 |          1000 |           500 |                    100 | f            |
(22 rows)