Working with Databricks SQL Warehouse on AWS
- Sarabjeet Kaur
Prerequisites for creating AWS SQL Warehouse Connection
Before creating an AWS SQL Warehouse connection without Spark, complete the following prerequisites.
Unity Catalog must be enabled on your Databricks cluster.
To create a storage credential for connecting to AWS S3, refer to Databricks documentation.
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 AWS SQL Warehouse connection with no-Spark
To create AWS 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 Databricks SQL. |
URL | In the URL field, enter the Databricks SQL Warehouse JDBC URL. For more information, see Microsoft documentation. |
Authentication Type | Select Personal Access Token. |
User Name | Enter the text token for username. |
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 AWS 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 JDBC Databricks URL. |
Username | Enter Databricks token. |
Password | Enter Databricks token. |
Authentication Type | Select Personal Access Token. |
Use as Source | This checkbox is auto selected. Enter Spark Read Method as JDBC. |
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. |
Catalog Enabled | Select this checkbox to list different catalog created in the workspace. |
Working with Databricks SQL warehouse with Personal Access Token
Note
Supported for AWS and Azure.
Supported only with premium workspace.
The OAuth connectivity with Databricks SQL Warehouse for AWS no-Spark is not supported.
The serverless type Databricks SQL Warehouse cluster is only supported.
Unity Catalog must be enabled.
Creating Databricks SQL Warehouse connection with Personal Token
From the Toolbox, click Setup, then choose Connections.
From the Actions menu ( ⋮ ), click Add Connection.
Enter a Name for the connection.
From the Category drop-down list, select the Warehouse option.
From the Provider list, select the DatabricksSQL option.
The Driver Class field is prepopulated.
Enter Databricks SQL Warehouse JDBC URL. For more information, see Microsoft documentation.
Select OAuth from the Personal Access Token list.
Enter Username as token.
Enter Databricks SQL Personal Access Token for the Databricks SQL workspace in the Password field.
The Use as Source checkbox is disabled as this is a source connection.
To use this connection as the default SQL engine, select the Is Default SQL Engine checkbox.
Select the Catalog Enabled checkbox. It is mandatory to enabled it for Databricks SQL.
Click the Properties link to view or set properties.
After configuring the settings, click the Save button.
To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.
Copyright Kyvos, Inc. All rights reserved.