SQL compilation error-- Snowsql validation using python

Question

I am following this tutorial :https://docs.snowflake.com/en/sql-reference/functions/validate.html

to try and 'return errors by query ID and saves the results to a table for future reference'

however for a seamless transfer I don't want to be putting the job id always as it would require me to go to snowflake console- go to history- get the jobid -copy and paste it to python code.

Instead I wanted to go with just the tablename which is a variable and 'last_query_id()' and give me the list errors. Is there any way i can achieve this?

import snowflake.connector
tableName='F58155'

ctx = snowflake.connector.connect(
user='*',
password='*',
account='*')

cs = ctx.cursor()

ctx.cursor().execute("USE DATABASE STORE_PROFILE_LANDING")
ctx.cursor().execute("USE SCHEMA PUBLIC")

try:
    ctx.cursor().execute("PUT file:///temp/data/{tableName}/* @% 
        {tableName}".format(tableName=tableName))
except Exception:
pass

ctx.cursor().execute("truncate table {tableName}".format(tableName=tableName))

ctx.cursor().execute("COPY INTO {tableName} ON_ERROR = 'CONTINUE'  ".format(tableName=tableName, 
FIELD_OPTIONALLY_ENCLOSED_BY = '""', sometimes=',', ERROR_ON_COLUMN_COUNT_MISMATCH = 'TRUE'))

I have tried the below validate function....it is giving me error on this line

the error is "SQL compilation error: syntax error line 1 at position 74 unexpected 'tableName'. syntax error line 1 at position 83 unexpected '}'."

ctx.cursor().execute("create or replace table save_copy_errors as select * from 
table(validate({tableName},'select last_query_id()'))");


ctx.close()

@demircioglu SQL compilation error: syntax error line 1 at position 74 unexpected 'tableName'. syntax error line 1 at position 83 unexpected '}'. — Ashita Ramteke
Your SQL is not constructed properly. You either need .format(tableName=tableName) to bind the variable or use f string notation which is easier to read. So it will look like this ctx.cursor().execute(f"create or replace table save_copy_errors as select * from table(validate({tableName},'select last_query_id()'))");. I also think you need job_id=> after tableName` — demircioglu
@demircioglu Tried this query --- ctx.cursor().execute(f"create or replace table save_copy_errors as select * from table(validate({tableName}, job_id=> 'select last_query_id();'))") . it is giving me an error as this-- "Invalid argument [Invalid Job UUID provided.] for table function. Table function argument is required to be a constant. " — Ashita Ramteke
In this case run a sql to get the job UUID and pass it to the validate sql To get the job_id job_id = ctx.cursor().execute("select last_query_id()").fetchone()[0] And you can you run validate sql as ctx.cursor().execute(f"create or replace table save_copy_errors as select * from table(validate({tableName},hob_id=>'{job_id}'))"); — demircioglu

demircioglu demircioglu · Accepted Answer · 2020-07-23T21:51:32

The line

ctx.cursor().execute("create or replace table save_copy_errors as select * from 
table(validate({tableName},'select last_query_id()'))");

should be replaced with these two

job_id = ctx.cursor().execute("select last_query_id()").fetchone()[0] 

ctx.cursor().execute(f"create or replace table save_copy_errors as select * from
table(validate({tableName},job_id=>'{job_id}'))");

SQL compilation error-- Snowsql validation using python

1 Answers