Running SQL Data File through Notebook Python

Marco9898 — Tue, 25 Oct 2022 22:08:59 GMT

I am attempting to run larger sql scripts through Databricks Notbook and export data to a file. For the most part the Notebook works when the sql script is a single SELECT statement. However, if the sql file is more complicated such as involving the use of creating a temporary/volatile table, inserting data to it, updating it, and then exporting the results it fails as shown below.

I am able to run the SQL script without any issues on Snowflake, but when establishing a connection though Databricks it just doesn't want to run and I do not understand the issue.

%python
 
#Full SQL script runs on Snowflake, but seems to have issues here
 
with open('/dbfs/FileStore/myid/prjX_sf.sql', 'r') as sqlFile:  #ERROR, create temp table, insert data, update temp table, select statement
 
#with open('/dbfs/FileStore/myid/prjX_simple.sql', 'r') as sqlFile: #WORKS, select statement only
 
  sql_script = sqlFile.read()
 
 
#Read data from Snowflake table
df = spark.read \
 .format("snowflake") \
 .options(**conn) \
 .option("query", sql_script) \
 .load()
display(df)

net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Py4JJavaError: An error occurred while calling o3645.load.

: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

syntax error line 1 at position 15 unexpected 'DROP'.

syntax error line 285 at position 63 unexpected ')'.

Re: Running SQL Data File through Notebook Python

jose_gonzalez — Fri, 28 Oct 2022 21:58:45 GMT

Hi @Marco Perez,

I would like to share the following doc https://docs.databricks.com/external-data/snowflake.html#query-a-snowflake-table-in-databricks in here you will be able to find example code on how to read data using databricks

Re: Running SQL Data File through Notebook Python

Anonymous — Mon, 28 Nov 2022 04:23:31 GMT

Hi @Marco Perez

Does @Jose Gonzalez response answer your question? If yes, would you be happy to mark it as best so that other members can find the solution more quickly?

We'd love to hear from you.

Thanks!

topic Re: Running SQL Data File through Notebook Python in Data Engineering

Running SQL Data File through Notebook Python

Re: Running SQL Data File through Notebook Python

Re: Running SQL Data File through Notebook Python