Retrieve df from spark.sql : [PARSE_SYNTAX_ERROR] Syntax error at or near 'SELECT'

Question

I'm using a databricks notebook and I'd like to retrieve a dataframe from an SQL execution in Spark. I have:

statement = f""" USER {db}; SELECT * FROM {table}
"""

df = spark.sql(statement)
display(df)

However, unlike when I fire off the same statement in an SQL cell in the notebook, I get the following error:

[PARSE_SYNTAX_ERROR] Syntax error at or near 'SELECT': extra input 'SELECT'(line 1...

Where am I going wrong?

Vamsi Bitra · Accepted Answer · 2022-12-14 13:56:59Z

1

I tried to reproduce the same in my environment and got below results:

This my sample demo table Persons.

enter image description here

Create dataframe by using this code as shown in the below image.

df = sqlContext.sql("select * from Persons")
display(df)

enter image description here

answered Dec 14, 2022 at 13:56

Vamsi Bitra

2,7642 gold badges5 silver badges15 bronze badges

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

4 Comments

pymat Over a year ago

thank you. However even when using sqlContext.sql I get the same error message. I'm looking into whether the "use" is causing an issue.

Vamsi Bitra Over a year ago

I have tried with db and got same error i.imgur.com/mSCj2ge.png. Don't use use db; while selecting using spark sql. It automatically detects the schema.

Vamsi Bitra Over a year ago

Instead of using db, you can do like this i.imgur.com/e1DItKv.png

pymat Over a year ago

I had to use without "use", also the dataframe returned is a pyspark one, so I had to use df = df.toPandas() to make the conversion. Thanks

Collectives™ on Stack Overflow

Retrieve df from spark.sql : [PARSE_SYNTAX_ERROR] Syntax error at or near 'SELECT'

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related