Copy command not writing in load_streams table
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;
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;
0
Best Answers
-
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?
0 -
Jim_Knicely
- Select Field - Administrator
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)5
Answers
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.
Nice! Thanks.