Working with Databricks SQL Warehouse on Azure

This section details how to configure and use a Databricks SQL Warehouse connection on Azure in Kyvos to access data, create datasets, and process semantic models using Databricks as the SQL engine with No-Spark.

• Prerequisites for creating Azure SQL Warehouse Connection

• Prerequisites for OAuth Connection

• Creating Azure SQL Warehouse connection with no-Spark

• Using properties for exporting data to a storage container in user’s specific account

• Working with Databricks SQL warehouse with Personal Token and OAuth

Before creating an Azure SQL Warehouse connection without Spark, complete the following prerequisites.

1. Unity Catalog must be enabled on your Databricks cluster.

2. In Unity Catalog, create storage credentials and an external location with appropriate access permissions for both the source and destination locations. You must have permission to create storage credentials and external locations.

3. To create storage credentials, first, create an access connector for Azure Databricks. Assign that access connector while creating storage credentials.

4. Grant the managed identity access to the storage account. You must have the Owner or User Access Administrator Azure RBAC role to grant permission to access your storage account.

   1. Log in to your Azure Data Lake Storage Gen2 account.

   2. Go to Access Control (IAM), click + Add, and select Add role assignment.

   3. Select the Storage Blob Data Contributor role and click Next.

   4. Under Assign access, select a Managed identity.

   5. Click +Select Members, and select Access connector for Azure Databricks or User-assigned managed identity.

   6. Search for your connector name or user-assigned identity, select it, and click Review and Assign.

5. Fetch the ABFSS path till the parent directory of your respective storage account.

6. On the Unity Catalog page, go to external locations and create a new external location by providing an external location name, newly created Storage credential, and fetched ABFSS URL. The URL format should be in abfss://my-container-name@my-storage-account.dfs.core.windows.net/<path>

7. Test the newly created external location by clicking the 'Test connection' button. This will validate the connection with the external location path. Through the permissions tab, assign your user the CREATE EXTERNAL TABLE and WRITE FILESrole.

8. Go to semantic model Advanced Properties, and add the following properties:

   1. temp catalog name for create parquet table = kyvos.sqlwarehouse.catalog

   2. temp database name for create parquet table = kyvos.sqlwarehouse.tempdb

9. You must have 'create table and Use schema' permissions on the temp catalog.

To create Databricks SQL Warehouse connection with OAuth, you need to complete the following prerequisites.

1. Logged-in user in Databricks Workspace must have Admin access to perform the below steps.

2. Create a Service Principal

   1. Go to the Databricks Workspace User Profile, click Settings, then Identity and access. Under Service Principal [Manage], click Add Service Principal, and note generated Application ID or Client ID; this will be OAuth2ClientId.

   2. Generate a secret for this Service Principal and note it down as it is OAuth2Secret.

3. Navigate to the Databricks SQL Warehouse, select the SQL Warehouse, click on permissions tab and add the service principal with the permission “CAN MANAGE“

4. Update the JDBC URL for OAuth. For example, if the JDBC URL is:

Change the URL as follows (change AuthMech=11 and add Auth_Flow=1 to the parameters):

4. Grant Permission to process semantic model using the above connection: To process semantic models using the above connection, grant permission on the external location (create an external location if it does not already exist for the storage account being used in your environment, such as "dev_testing"). For this service principal:

   • Click Catalog.

   • Click the Settings icon.

   • Click the Storage Location Name.

   • Create external table and write files access

5. Add the following properties to semantic model Advanced Properties:

   • kyvos.sqlwarehouse.catalog = <catalog name for temporary tables created while cubes building>

   • kyvos.sqlwarehouse.tempdb = <temporary database for temporary tables created while cubes building>

For more detail about Authentication settings for the Databricks JDBC Driver, refer to AWS documentation.

To create Azure SQL Warehouse connection for processing semantic models without Spark, perform the following steps.

1. From the Toolbox, click Connections.

2. From the Actions menu ( ⋮ ) click Add Connection.

3. Enter the following details: 

4. After you finish configuring the settings using the table shown below the screenshot, click the Test button from the top left to validate the connection settings.

5. If the connection is valid, click the Save button. 

Open

From Kyvos 2026.1 onwards, users can export data during semantic model processing to a storage container in their specified directory instead of Kyvos-owned storage.

Kyvos supports Databricks SQL warehouse to enable the execution of ROLAP and MOLAP queries.

• Creating Databricks SQL Warehouse connection with Personal Token

• Creating Databricks SQL Warehouse connection with OAuth

1. From the Toolbox, click Setup, then choose Connections.

2. From the Actions menu ( ⋮ ), click Add Connection.

3. Enter a Name for the connection.

4. From the Category drop-down list, select the Warehouse option.

5. From the Provider list, select the DatabricksSQL option.

6. The Driver Class field is prepopulated.

7. Enter Databricks SQL Warehouse JDBC URL. For more information, see Microsoft documentation.

8. Select OAuth from the Personal Access Token list.

9. Enter Username as token.

10. Enter Databricks SQL Personal Access Token for the Databricks SQL workspace in the Password field.

11. The Use as Source checkbox is disabled as this is a source connection.

12. To use this connection as the default SQL engine, select the Is Default SQL Engine checkbox.

13. Select the Catalog Enabled checkbox. It is mandatory to enabled it for Databricks SQL.

14. Click the Properties link to view or set properties.

15. After configuring the settings, click the Save button. 

16. To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.

Open

You can create Databricks SQL Warehouse connection with OAuth machine-to-machine (M2M) authentication type. See Prerequisites for more details.

1. From the Toolbox, click Setup, then choose Connections.

2. From the Actions menu ( ⋮ ), click Add Connection.

3. Enter a Name for the connection.

4. From the Category drop-down list, select the Warehouse option.

5. From the Provider list, select the DatabricksSQL option.

6. The Driver Class field is prepopulated.

7. Enter Databricks SQL Warehouse JDBC URL. For more information, see Microsoft documentation.

8. Select OAuth from the Authentication Type list. Upon selecting this option, the Client ID and Client Secret fields are displayed.

9. In the Client ID field, enter the value of the service principal’s Application ID, Client ID, or OAuth2ClientId.

10. In the Client Secret field, enter the value of the secret created for the above service principal or the OAuth2ClientId.

11. The Use as Source checkbox is disabled as this is a source connection.

12. To use this connection as the default SQL engine, select the Is Default SQL Engine checkbox.

13. Select the Catalog Enabled checkbox. It is mandatory to enabled it for Databricks SQL.

14. Click the Properties link to view or set properties.

15. After configuring the settings, click the Save button. 

16. To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.

Open