0

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

Robertino Bonora
  • 20
  • 5

1 Answers1

0

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.

Sergiu
  • 4,039
  • 1
  • 13
  • 21
  • Hi @Sergiu thanks for the reply. Ok with the _last option, i actually wasn't sure of using it but thanks for the clarification. On the other hand i'm using snowsight but that only in develop environment after this i need to deploy my scripts using bitbucket, terraform, etc. So is not an option to look the query id for the last copy of a table. I'm looking for another option, a programmatically one. – Robertino Bonora Jun 02 '23 at 13:09
  • to be more specific, the use case is that i'm copying a csv from an external stage into a tabla but this csv could have bad rows so i'm using the on_error = 'continue' to proceed with the load, but after this i want to obtain the rows that fail (using the validate function) of that specific copy into statement of that specific table. I created a error log table with the same structure of the Validate result table and inserting the validate result in that table but not working till now – Robertino Bonora Jun 02 '23 at 13:11
  • You can get it programatically with a connector/driver and running a query like `select * from table(information_schema.copy_history(TABLE_NAME=>'DB.SCHEMA.TABLE', START_TIME=> DATEADD(days, -2, CURRENT_TIMESTAMP())));` as documented [here](https://docs.snowflake.com/en/sql-reference/functions/copy_history). – Sergiu Jun 02 '23 at 13:22
  • mm yeah but i don't have the query id to put on the validate function in the copy_history right? And thats the hole point, i need the last query id for the copy to put it on the validate function to obtain the rows that failed to load in the copy into of that specific table – Robertino Bonora Jun 02 '23 at 13:28
  • Right, I missed that. Have you tried [QUERY_HISTORY](https://docs.snowflake.com/en/sql-reference/functions/query_history) with filters? – Sergiu Jun 02 '23 at 14:14
  • query_history not work in this case because when i upload a csv file in the s3 bucket and the pipe is automatic triggered by the sns topic and the pipe execute the copy, that doesn't get registered in the query_history, as much as i see only select, truncate, drop, create, execute statements but not the copy into, that should be on the copy_history but it isn't, what a shitt* information snowflake have – Robertino Bonora Jun 02 '23 at 14:35
  • I see what you mean, what about [VALIDATE_PIPE_LOAD](https://docs.snowflake.com/en/sql-reference/functions/validate_pipe_load)? It should show errors per pipe. – Sergiu Jun 02 '23 at 15:54
  • hi, yeah gives an error but isn't what i'm expecting, the validate is the one i need but i don't know why is not working, at least no working with the snowpipe – Robertino Bonora Jun 04 '23 at 16:41