SYS_LOAD_HISTORY - Amazon Redshift

SYS_LOAD_HISTORY

Use SYS_LOAD_HISTORY to view details of COPY commands. Each row represents a COPY command with accumulated statistics for some of the fields. It contains both running and finished COPY commands.

SYS_LOAD_HISTORY is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
user_id integer The identifier of the user who submitted the copy.
query_id bigint The query identifier of the copy.
transaction_id bigint The transaction identifier.
session_id integer The process identifier of the process running the copy.
database_name text The name of the database the user was connected to when the operation was issued.
status text The status of the copy. Valid values are running, completed, aborted.
table_name text The name of the table copying into.
start_time timestamp The time when the copy began.
end_time timestamp The time when the copy completed.
duration bigint The amount of time (microseconds) spent in the COPY command.
data_source text The Amazon S3 location of files input to copy.
file_format text The source file format. Formats include csv, txt, json, avro, orc, or parquet.
loaded_rows bigint The number of rows copied to a table.
loaded_bytes bigint The number of bytes copied to a table.
source_file_count integer The number of files count in source files.
source_file_bytes bigint The number of bytes in source files.
file_count_scanned integer The number of scanned files from Amazon S3.
file_bytes_scanned bigint The number of bytes scanned from the file in Amazon S3.
error_count bigint The number of errors count.
copy_job_id bigint The copy job identifier. A 0 indicates no job identifier.

Sample queries

The following query shows the loaded rows, bytes, tables, and datasource of specific copy commands.

SELECT query_id, table_name, data_source, loaded_rows, loaded_bytes FROM sys_load_history WHERE query_id IN (6389,490791,441663,74374,72297) ORDER BY query_id, data_source DESC;

Sample output.

query_id | table_name | data_source | loaded_rows | loaded_bytes ----------+------------------+-----------------------------------------------------------------------+-------------+--------------- 6389 | store_returns | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/store_returns/ | 287999764 | 1196240296158 72297 | web_site | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/web_site/ | 54 | 43808 74374 | ship_mode | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/ship_mode/ | 20 | 1320 441663 | income_band | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/income_band/ | 20 | 2152 490791 | customer_address | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/customer_address/ | 6000000 | 722924305

The following query shows the loaded rows, bytes, tables, and datasource of copy commands.

SELECT query_id, table_name, data_source, loaded_rows, loaded_bytes FROM sys_load_history ORDER BY query_id DESC LIMIT 10;

Sample output.

query_id | table_name | data_source | loaded_rows | loaded_bytes ----------+------------------------+-----------------------------------------------------------------------------+-------------+----------------- 491058 | web_site | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/web_site/ | 54 | 43808 490947 | web_sales | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/web_sales/ | 720000376 | 22971988122819 490923 | web_returns | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/web_returns/ | 71997522 | 96597496325 490918 | web_page | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/web_page/ | 3000 | 1320 490907 | warehouse | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/warehouse/ | 20 | 1320 490902 | time_dim | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/time_dim/ | 86400 | 1320 490876 | store_sales | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/store_sales/ | 2879987999 | 151666241887933 490870 | store_returns | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/store_returns/ | 287999764 | 1196405607941 490865 | store | s3://load-test/data-sources/tpcds/2.8.0/textfile/1T/store/ | 1002 | 365507

The following query shows the daily loaded rows and bytes of the copy command.

SELECT date_trunc('day',start_time) AS exec_day, SUM(loaded_rows) AS loaded_rows, SUM(loaded_bytes) AS loaded_bytes FROM sys_load_history GROUP BY exec_day ORDER BY exec_day DESC;

Sample output.

exec_day | loaded_rows | loaded_bytes ---------------------+-------------+------------------ 2022-01-20 00:00:00 | 6347386005 | 258329473070606 2022-01-19 00:00:00 | 19042158015 | 775198502204572 2022-01-18 00:00:00 | 38084316030 | 1550294469446883 2022-01-17 00:00:00 | 25389544020 | 1033271084791724 2022-01-16 00:00:00 | 19042158015 | 775222736252792 2022-01-15 00:00:00 | 19834245387 | 798122849155598 2022-01-14 00:00:00 | 75376544688 | 3077040926571384