spark snowflake connector sql query SQL compilation error

Question

I'm trying to query snowflake using input variable from my function (run via aws glue).. Below are 3 things I've tried but I keep getting the following error:

An error occurred while calling o14310.load. SQL compilation error: error line 1 at position 94

Any ideas what the issue may be? Regular queries not using an input variable runs fine..

def s_counts(df, s_id):
what_to_query = "select R_ID FROM mytable WHERE S_ID ="+s_id
CurrentCount = spark.read.format(SNOWFLAKE_SOURCE_NAME)
   .options(**sfOptions)
   .option("query", what_to_query)
   .load()

CurrentCount = spark.read.format(SNOWFLAKE_SOURCE_NAME)
   .options(**sfOptions)
   .option("query", "select R_ID FROM mytable WHERE S_ID = s_id")
   .load()

CurrentCount = spark.read.format(SNOWFLAKE_SOURCE_NAME)
   .options(**sfOptions)
   .option("query", "select R_ID FROM mytable WHERE S_ID = "+s_id)
   .load()

there is a very high chance that the input variable s_id is not passed properly, and you ended up with an incomplete query select R_ID FROM mytable WHERE S_ID = — pltc
– pltc, Commented Sep 16, 2021 at 3:28
Should the S_ID in your where clause be R_ID i.e. select R_ID FROM mytable WHERE R_ID = instead of select R_ID FROM mytable WHERE S_ID= ? — ggordon
– ggordon, Commented Sep 16, 2021 at 5:21

ggordon · Accepted Answer · 2021-09-16 05:20:47Z

1

Try wrapping s_id in quotes. For example

what_to_query = "select R_ID FROM mytable WHERE S_ID = '{0}'".format(s_id)

or

what_to_query = "select R_ID FROM mytable WHERE S_ID = '%s'" % (s_id)

Let me know if this works for you.

answered Sep 16, 2021 at 5:20

ggordon

10.1k2 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pdangelo4 Over a year ago

Thanks ggordon! The second one worked for me!

ggordon Over a year ago

@pdangelo4 Great! Please mark this answer as the accepted answer so that other stackoverflow users with similar questions will be able to identify suitable answers to their questions.

Collectives™ on Stack Overflow

spark snowflake connector sql query SQL compilation error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related