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 ')'.
