Running SQL Data File through Notebook Python
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ10-25-2022 03:08 PM
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 ')'.
- Labels:
-
SQL
-
SQL Data
-
SQL Data File
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ10-28-2022 02:58 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ11-27-2022 08:23 PM
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!

