CODEWITHSUNDEEP
snowflake-cloud-data-platform
cerberus
cerberus

Reputation: 561

Error while getting validation error in last data load query

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;
$$

Upvotes: 2

Views: 734

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176314

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

Upvotes: 0

Related Questions