SYS_LOAD_HISTORY - Amazon Redshift
Table columnsSample queries

Amazon Redshift will no longer support the creation of new Python UDFs starting November 1, 2025. If you would like to use Python UDFs, create the UDFs prior to that date. Existing Python UDFs will continue to function as normal. For more information, see the blog post .

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