CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowpipe
Robertino Bonora
Robertino Bonora

Reputation: 108

Snowflake - Error in validation function on a pipe load

I have a pipe defined like this:

CREATE OR REPLACE PIPE PIPE_NAME
AUTO_INGEST=TRUE 
AWS_SNS_TOPIC= 'arn:aws:sns:...'   
AS COPY INTO TABLE1 
FROM '@STAGE/TABLE1'
FILE_FORMAT = (TYPE = 'csv' FIELD_DELIMITER = ';' SKIP_HEADER = 1 EMPTY_FIELD_AS_NULL = TRUE NULL_IF = ('') RECORD_DELIMITER = '\n' FIELD_OPTIONALLY_ENCLOSED_BY = '"' ESCAPE_UNENCLOSED_FIELD ='\\')
ON_ERROR = CONTINUE;

After the copy, that goes well (some rows are bad formatted so this is what i want to validate in the VALIDATE function), i executed this:

SELECT * FROM TABLE(VALIDATE(TABLE1, JOB_ID => '_last'));

But I'm getting this error:

Invalid argument [The specified Job UUID does not load into the specified table] for table function. Table function argument is required to be a constant.

I'm doing something wrong? It's like when you execute the copy into command manually works but when you call the VALIDATE function inside a stored procedures doesn't.

Also, I would love to use the most recent query_id/job_id of the copy into of this specific table and not the '_last' condition, but i haven't been able to found it, any thoughts?

Thank you

Upvotes: 0

Views: 440

Answers (1)

Sergiu
Sergiu

Reputation: 4578

You are getting an error because you are using _last for a different purpose than mentioned in our docs:

If _last is specified instead of query_id, the function validates the last load executed during the current session, regardless of the specified target table.

Now, to find the last load using Snowflake UI (Snowsight) just go to Activity -> Copy History and use a filter based on your Pipe. See how to do it here.

Upvotes: 0

Related Questions