Error while getting validation error in last data load query

Question

I am loading data to snowflake using an external stage. This operation is performed in a stored procedure and the procedure is called from snowflake Task. The procedure loads the data to a table using COPY INTO query and then checks for validation errors. To get the validation error, I am using _last as job_id.

Query used to copy/load data into snowflake table

COPY INTO conference_created_tmp FROM @conference_created_src/year=2021/month=04/day=15/hour=01/ ON_ERROR = 'SKIP_FILE'

Query used to get validation errors:

SELECT FILE as fileName, ERROR as error
FROM table(validate(conference_created_tmp, job_id => '_last')

Error message:

SQL compilation error: Invalid argument [We couldn't find a copy for this table which occurred during this session ] for table function. Table function argument is required to be a constant.

If I run the copy into command and validation query directly in Snowflake worksheet, it works!!!. But when called from procedure it is giving error.

Procedure code:

CREATE OR REPLACE PROCEDURE TEST_ERROR_LOG()
    RETURNS STRING NOT NULL
    LANGUAGE JAVASCRIPT
AS
$$
    //Copying data from external stage to snowflake table
    const COPY_QUERY = `COPY INTO conference_created_tmp
                            FROM @conference_created_src/year=2021/month=04/day=15/hour=01/
                            ON_ERROR = 'SKIP_FILE'`;

    const LOAD_ERROR_INSERT_QUERY = `INSERT INTO error_log(eventTime, fileName, error)
                                                SELECT '%eventTime%', FILE as fileName, ERROR as error
                                                FROM table(validate(conference_created_tmp, job_id => '_last'))`;

    function log_load_error() {
        let params = {
            "%eventTime%": Date.now()
        };
        let insertQuery = LOAD_ERROR_INSERT_QUERY.replace(/%\w+%/g, function (all) {
            return params[all] || all;
        });
        try {
            snowflake.execute({sqlText: insertQuery});
        } catch (err) {
            throw err;
        }
    }

    function loadDataFromStageToTable() {
        try {
            let resultSet = snowflake.execute({sqlText: COPY_QUERY});
            resultSet.next();
        } catch (err) {
            throw err;
        }
    }

    try {
        loadDataFromStageToTable();
        log_load_error();
    } catch (err) {
        throw err;
    }

    return true;
$$

can you provide an example stored procedure that reproduces the error? — Simon D

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-04-15T14:52:48

Without actual stored procedure code is difficult to be sure, but I guess you want to parametrize table name:

SELECT FILE as fileName, ERROR as error
FROM table(validate(IDENTIFIER(:bind_variable), job_id => '_last');

If yes, then you need to wrap it with IDENFITIER.

Bind Variables as Identifiers
IDENTIFIER( { string_literal | session_variable | bind_variable } )
Literals and variables (session or bind) can be used anywhere an object can be identified by name (queries, DML, DDL, etc.).

Error while getting validation error in last data load query

1 Answers