Working with Databricks SQL Warehouse on GCP

✅ Enterprise: AWS, Azure, GCP, and On-Premises

✅ Marketplace: AWS, Azure, and GCP

✅ SaaS: AWS, Azure, and GCP

✅ Single Node Installation (SNI)

Overview

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

Prerequisites for creating a GCP SQL Warehouse Connection

Before creating a GCP SQL Warehouse connection with no-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.
Create an external location.
1. Navigate to Catalog in the Databricks workspace.
2. Go to External Locations and click Create External Location.
3. Fill in the required details:
4. Provide the GCS bucket path (e.g., gs://<bucket-name>/<path>/) from which you want to establish connectivity.
5. Storage Credential: Select Create New Credential. You can also use existing storage credentials if they have already been created.
6. Click Create to create the external location. After the external location is created, a message is displayed as ‘Databricks-generated Service Account.’
7. Copy and use the Service Account of storage credentials and grant the required permissions (i.e., Storage Object Viewer/ Storage Admin) to the service account on the Google Cloud Storage (GCS) bucket where you want to export data.
Go to the 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
You must have ‘create table and Use schema’ permissions on the temp catalog.

On this page:

1 Overview
2 Prerequisites for creating a GCP SQL Warehouse Connection
3 Prerequisites for OAuth Connection
4 Creating a GCP SQL Warehouse connection with no-Spark

Prerequisites for OAuth Connection

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

A logged-in user in Databricks Workspace must have Admin access to perform the steps below.
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 the 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 an OAuth2Secret.
Navigate to the Databricks SQL Warehouse, select the SQL Warehouse, click on the permissions tab, and add the service principal with the permission “CAN MANAGE“
Update the JDBC URL for OAuth. For example, if the JDBC URL is:
jdbc:databricks://8259559724528873.3.gcp.databricks.com:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/73be1a9ffe3f21f7;
Change the URL as follows (change AuthMech=11 and add Auth_Flow=1 to the parameters):
jdbc:databricks://8259559724528873.3.gcp.databricks.com:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/73be1a9ffe3f21f7;
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.

For more details about Authentication settings for the Databricks JDBC Driver, refer to the GCP documentation.

Creating a GCP SQL Warehouse connection with no-Spark

To create a GCP SQL Warehouse connection for processing with semantic models in no-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.
Authentication Type	Select Personal Access Token or OAuth. If a Personal Access token is selected, the username and password fields are displayed. User Name: Enter the text token for username. Password: Enter Databricks SQL Personal Access Token’s value for the Databricks SQL workspace If the OAuth option is selected, the Client ID and Client Secret fields are displayed. In the Client ID field, enter the value of the service principal’s Application ID, Client ID, or OAuth2ClientId. In the Client Secret field, enter the value of the secret created for the above service principal or the OAuth2ClientId.
Use as Source	This checkbox is auto-selected. Enter Spark Read Method as JDBC.
Is the default SQL Engine	By default, the checkbox is selected because this connection can only be used to read data (to create datasets) that will be used to process the semantic model.
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 in the screenshot below, click the Test button in the top left to validate the connection settings.
If the connection is valid, click the Save button.