SQL compilation error while connecting to snowflake from Databricks

[Kaviprakash_S]

Hi All,

I'm trying to connect to the snowflake database from databricks notebook either to read the data or write the data. However I'm getting an weird error. The code and error are provided as follows,

snowflake_table = (spark.read

  .format("snowflake")

  .option("host", "******.east-us-2.azure.snowflakecomputing.com")

  .option("user", "******")

  .option("password", "*********")

  .option("sfWarehouse", "Compute_WH")

  .option("database", "SalesDevDB")

  .option("schema", "gold") # Optional - will use default schema "public" if not specified.

  .option("dbtable", "test")

  .load()

)

Error:

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

[brickster_2018]

Can you try the below code snippet? And share the stacktrace if the issue persists. 

snowflake_table = spark.read \
  .format("snowflake") \
  .option("host", "******.east-us-2.azure.snowflakecomputing.com") \
  .option("user", "******") \
  .option("password", "*********") \
  .option("sfWarehouse", "Compute_WH") \
  .option("database", "SalesDevDB") \
  .option("schema", "gold") \
  .option("dbtable", "test") \
  .load()

[Kaviprakash_S]

I don't think adding the "\" will work in this case. Infact its not required in this dataframe operation. 

[Kaviprakash_S]

@Kaniz_Fatma Could you please help with this ? 

[Kaniz_Fatma]

Hi @Kaviprakash_S, Sure!

• Verify the host URL, user, and password options match the Snowflake account information.
• Make sure the sfWarehouse, database, and schema options are spelt correctly and match the Snowflake objects.
• Verify that the dbtable option matches the exact name of the Snowflake table you are trying to read from.
• Ensure that the schema option is set correctly if the table is not in the default public schema
• The Databricks version 4.2 or later includes the native Snowflake Connector, which simplifies the connection process.
• If using an older Databricks runtime, you may need to import additional libraries for the Spark connector.
• The user connecting to Snowflake needs the USAGE and CREATE STAGE privileges on the schema containing the table.
• Double-check the Snowflake connection details and table name.
• Verify that the Databricks runtime version supports the native Snowflake Connector.
• Contact Snowflake support if the error continues, as it may be a specific issue with the Snowflake account or table.

By reviewing these points and making any necessary corrections, you should be able to successfully connect to Snowflake and read data into your Databricks notebook.