Categories:
Information Schema , Table Functions

COPY_HISTORY

This table function can be used to query Snowflake data loading history along various dimensions. The function returns load activity for both COPY INTO <table> statements and continuous data loading using Snowpipe. The table function avoids the 10,000 row limitation of the LOAD_HISTORY View. The results can be filtered using SQL predicates.

Note

This function returns data loading activity within the last 14 days.

Syntax

COPY_HISTORY(
      TABLE_NAME => '<string>'
       , START_TIME => <constant_expr>
      [, END_TIME => <constant_expr> ] )

Arguments

Required:

TABLE_NAME => 'string'
A string specifying a table name.
START_TIME => constant_expr
Timestamp (in TIMESTAMP_LTZ format), within the last 14 days, marking the start of the time range for retrieving load events.

Optional:

END_TIME => constant_expr

Timestamp (in TIMESTAMP_LTZ format), within the last 14 days, marking the end of the time range for retrieving load events.

Default: CURRENT_TIMESTAMP.

Usage Notes

  • When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Information Schema.

Output

The function returns the following columns:

Column Name Data Type Description
FILE_NAME TEXT Name of the source file and relative path to the file.
STAGE_LOCATION TEXT Name of the stage where the source file is located.
LAST_LOAD_TIME TIMESTAMP_LTZ Date and time of the load record.
ROW_COUNT NUMBER Number of rows loaded from the source file.
ROW_PARSED NUMBER Number of rows parsed from the source file;``NULL`` if STATUS is ‘LOAD_IN_PROGRESS’.
FILE_SIZE NUMBER Size of source file loaded through pipe; NULL for COPY statement loads.
FIRST_ERROR_MESSAGE TEXT First error of the source file.
FIRST_ERROR_LINE_NUMBER NUMBER Line number of the first error.
FIRST_ERROR_CHARACTER_POS NUMBER Position of the first error character.
FIRST_ERROR_COLUMN_NAME TEXT Column name of the first error.
ERROR_COUNT NUMBER Number of error rows in the source file.
ERROR_LIMIT NUMBER If the number of errors reaches this limit, then abort.
STATUS TEXT Status: loaded, load failed, or partially loaded.
TABLE_CATALOG_NAME TEXT Name of the database in which the target table resides.
TABLE_SCHEMA_NAME TEXT Name of the schema in which the target table resides.
TABLE_NAME TEXT Name of the target table.
PIPE_CATALOG_NAME TEXT Name of the database in which the pipe resides.
PIPE_SCHEMA_NAME TEXT Name of the schema in which the pipe resides.
PIPE_NAME TEXT Name of the pipe defining the load parameters; NULL for COPY statement loads.
PIPE_RECEIVED_TIME TIMESTAMP_LTZ Date and time when the INSERT request for the file loaded through the pipe was received; NULL for COPY statement loads.

Examples

Retrieve details about all loading activity in the last hour:

select *
from table(information_schema.copy_history(TABLE_NAME=>'MYTABLE', START_TIME=> DATEADD(hours, -1, CURRENT_TIMESTAMP())));