CODEWITHSUNDEEP
pythonsqlpython-3.xvalidationsnowflake-cloud-data-platform
arr
arr

Reputation: 139

SQL compilation error-- Snowsql validation using python

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

Upvotes: 0

Views: 1692

Answers (1)

demircioglu
demircioglu

Reputation: 3465

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}'))");

Upvotes: 1

Related Questions