Does anyone know if it is possible to set a snowflake table as an external table in databricks hive metastore?

Question

I'm working on a project in which we have some tables in Azure Data Lake Gen 2 and we are managing them from databricks. For being able to query the tables from databricks it is needed to add them to an existing database in databricks hive metastore. The syntax is as follows:

CREATE TABLE IF NOT EXISTS <DATABASE>.<TABLE_NAME> USING DELTA LOCATION <PATH_TO_TABLE>

Now, I need to do the same with some tables we have in snowflake. I am able to bring tables to databricks with spark connector.

sfOptions = {
"sfURL" : "<account>.snowflakecomputing.com",
"sfUser" : "<user>",
"sfPassword" : "<password>",
"sfDatabase" : "<database>",
"sfRole": "<role>",
"sfWarehouse" : "<cluster>"
}

df = spark.read.format("net.snowflake.spark.snowflake")\
.option("column_mapping","name")\
.options(**sfOptions) \
.option('dbtable', "<schema>.<table_name>").load()

But, what I need to do is slightly different. I need to bring the snowflake tables as databricks external tables because I want to merge them with delta lake tables, querying directly from a databricks notebook.

Thanks in advance.

Answer 1

Hello @A K, Srinivaasan ,

Thanks for the question and using MS Q&A platform.

I don't have a Snowflake instance to test this, but you can try below and see if that works.

Please note*: This works only on serverless and pro SQL warehouse (experimental feature). Experimental features are provided as-is and are not supported by Databricks through customer technical support channels.*

Here is the related documentation: Query federation for Snowflake in Databricks SQL

DROP TABLE IF EXISTS hive_metastore.default.snowflake_table;   

CREATE TABLE hive_metastore.default.snowflake_table   

USING snowflake   

OPTIONS (   

  dbtable '<table-name>',   

  sfUrl '<database-host-url>',   

  sfUser secret('snowflake_creds', 'my_username'),   

  sfPassword secret('snowflake_creds', 'my_password'),   

  sfDatabase '<database-name>', sfSchema '<schema-name>',   

  sfWarehouse '<warehouse-name>'   

);  

Hope this info helps. Do let us know how it goes.

Thank you