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())));