Categories:

# COPY_HISTORY¶

This table function can be used to query Snowflake data loading history along various dimensions within the last 14 days. 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.

## 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¶

• For Snowpipe data loads, this function returns results only for the pipe owner (i.e. the role with the OWNERSHIP privilege on the pipe) or a role with the following minimum permissions:

Privilege

Object

Notes

USAGE

Database and schema that store the pipe

MONITOR

Pipe

Alternatively, the global MONITOR EXECUTION privilege is supported.

SELECT

Table in the pipe defintion

• 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.

• The function only includes COPY INTO commands that executed to completion, with or without errors.

• The historical data for COPY INTO commands is removed from the system when a table is dropped.

## 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.

TIMESTAMP_LTZ

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 the source file loaded.

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: Load in progress, Loaded, Load failed, Partially loaded, or Load skipped.

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.

Date and time when the INSERT request for the file loaded through the pipe was received; NULL for COPY statement loads.
select *