Prerequisites
Before creating an Azure SQL Warehouse connection without Spark, complete the following prerequisites.
Unity Catalog must be enabled on your Databricks cluster.
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.
To create storage credentials, first, create an access connector for Azure Databricks. Assign that access connector while creating storage credentials.
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.
Log in to your Azure Data Lake Storage Gen2 account.
Go to Access Control (IAM), click + Add, and select Add role assignment.
Select the Storage Blob Data Contributor role and click Next.
Under Assign access, select a Managed identity.
Click +Select Members, and select Access connector for Azure Databricks or User-assigned managed identity.
Search for your connector name or user-assigned identity, select it, and click Review and Assign.
Fetch the ABFSS path till the parent directory of your respective storage account.
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>
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 role.
Go to semantic model Advanced Properties, and add the following properties:
temp catalog name for create parquet table = kyvos.sqlwarehouse.catalog
temp database name for create parquet table = kyvos.sqlwarehouse.tempdb
You must have 'create table and Use schema' permissions on the temp catalog.
Creating Azure SQL Warehouse connection with no Spark
To create Azure SQL Warehouse connection for processing semantic models without Spark, perform the following steps.
From the Toolbox, click Connections.
From the Actions menu ( ⋮ ) click Add Connection.
Enter the following details:
Parameter/Field | Comments/Description |
Name | Enter a unique name for the connection. |
Category | Select the Warehouse option. There may be more than one warehouse connection. |
Provider | Select DATABRICKSSQL from the list. |
Driver class | This field will be auto selected. The Driver class (com.databricks.client.jdbc.Driver ) is required to connect to the Azure SQL DB. |
URL | In the URL field, enter the Databricks SQL Warehouse JDBC URL. For more information, see Microsoft documentation. |
User Name | Enter Databricks SQL Personal Access Token for the Databricks SQL workspace |
Password | Enter Databricks SQL Personal Access Token’s value for the Databricks SQL workspace |
Use as Source | This checkbox is auto selected. Enter Spark Read Method as JDBC |
Is default SQL Engine | By default, the checkbox is selected as this connection can only be used to read data (creating datasets) on which the semantic model will be created. |
Catalog Enabled | Select this checkbox to list different catalog created in the workspace. |
Properties | Click Properties to view or set properties. |
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.
If the connection is valid, click the Save button.
Connection Details in Kyvos for Sanity Suite
To create Azure SQL Warehouse connection for sanity suite, perform the following steps.
Parameter | Description |
|---|---|
Name | Enter SanityConnection as a name. |
Category | Select the Warehouse option. |
Provider | Select the Generic option. |
Driver | Enter the Driver class as com.databricks.client.jdbc.Driver |
URL | Enter URL as: jdbc:databricks://adb-650081446221384.4.azuredatabricks.net:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/3bc903419b85ed62 |
Username | Enter Databricks token. |
Password | Enter Databricks token. |
Authentication Type | Select the Snowflake option. |
Spark ReadMethod | Select JDBC |
Schema Enabled | Select this checkbox to enable schema. |
Is Default SQL Engine | To enable the connection for raw data, click the Is Default SQL Engine checkbox to set this connection to run the default SQL engine. |
Properties | Click Properties to view or set properties. |