Copy command not writing in load_streams table

mpedroso

Hey guys,

I'm loading some data into the vertica database using "copy local" and the uploads are not writing to the load_streams table. Can someone help me?

Copy command example:

COPY ftt_dim_empresa from local 'C:\SD\dm_empresa.csv' DELIMITER '|' DIRECT;

mpedroso

Thanks for the support.

In fact, I have run some tests here and the information is recorded when the data volume is larger and consequently the time exceeds 1s. However, it's important to get the metrics for the smaller uploads as well, is there any way?

Jim_Knicely

You can get the number of accepted and rejected rows via the GET_NUM_ACCEPTED_ROWS and GET_NUM_REJECTED_ROWS functions, respectively:

dbadmin=> COPY test_local FROM LOCAL '/home/dbadmin/small_local.txt' DIRECT STREAM NAME 'FAST_LOAD';
 Rows Loaded
-------------
           3
(1 row)

dbadmin=> SELECT get_num_accepted_rows();
 get_num_accepted_rows
-----------------------
                     3
(1 row)

dbadmin=> SELECT get_num_rejected_rows();
 get_num_rejected_rows
-----------------------
                     1
(1 row)

mpedroso

I saw in the documentation that the LOAD_SOURCES table does not save the COPY LOCAL command information. The same thing happens for a LOAD_STREAMS? If yes, is there any way to monitor and get as COPY LOCAL metrics?

Jim_Knicely

Hi,

Running a COPY LOCAL does record info in the LOAD_STREAMS and LOAD_SOURCES table:

dbadmin=> COPY test_local FROM LOCAL '/home/dbadmin/test_local.txt' DIRECT STREAM NAME 'TEST_LOCAL_DIRECT';
 Rows Loaded
-------------
    60000000
(1 row)

dbadmin=> \x
Expanded display is on.

dbadmin=> SELECT * FROM load_streams WHERE stream_name = 'TEST_LOCAL_DIRECT';
-[ RECORD 1 ]----------+----------------------------------
session_id             | v_test_db_node0001-233418:0x3a03f
transaction_id         | 45035996274080107
statement_id           | 7
stream_name            | TEST_LOCAL_DIRECT
schema_name            | public
table_id               | 45035996275735582
table_name             | test_local
load_start             | 2018-09-24 08:23:47.654068-04
load_duration_ms       | 21959
is_executing           | f
accepted_row_count     | 60000000
rejected_row_count     | 6
read_bytes             | 120000012
input_file_size_bytes  | 120000012
parse_complete_percent | 100
unsorted_row_count     | 60000000
sorted_row_count       | 60000000
sort_complete_percent  | 100

dbadmin=> SELECT * FROM load_sources WHERE stream_name = 'TEST_LOCAL_DIRECT';
-[ RECORD 1 ]------------+----------------------------------
session_id               | v_test_db_node0001-233418:0x3a03f
transaction_id           | 45035996274080107
statement_id             | 7
stream_name              | TEST_LOCAL_DIRECT
schema_name              | public
table_oid                | 45035996275735582
table_name               | test_local
node_name                | v_test_db_node0001
source_name              | /home/dbadmin/test_local.txt
input_size               | 120000012
peak_cooperating_threads | 1
portion_offset           |
portion_size             |
read_bytes               | 120000012
rows_produced            | 60000000
rows_rejected            | 6
parse_complete_percent   | 100
is_executing             | f
failure_reason           |

Note that these system tables record info. from every COPY statement that takes more than 1-second to run. The 1-second duration includes the time to plan and execute the statement.

mpedroso

Nice! Thanks.