Run federated queries on another Databricks workspace

Preview

This article describes how to set up Lakehouse Federation to run federated queries on Databricks data in another Databricks workspace. To learn more about Lakehouse Federation, see What is Lakehouse Federation.

Important

Databricks-to-Databricks Lakehouse Federation is a good tool for running queries on data managed by another Databricks workspace’s Hive or AWS Glue metastore. For most other scenarios, other Databricks workflows are more efficient:

If Databricks workspaces share the same Unity Catalog metastore, you can manage cross-workspace queries using standard Unity Catalog queries and data governance tools.
If you want read-only access to data in a Databricks workspace attached to a different Unity Catalog metastore, whether in your Databricks account or not, Delta Sharing is a better choice.

There is no need to set up Lakehouse Federation in either of these scenarios.

To connect to a Databricks catalog in another workspace using Lakehouse Federation, you must create the following in your Databricks Unity Catalog metastore:

A cluster or SQL warehouse in a Databricks workspace.
A connection to the cluster or SQL warehouse.
A foreign catalog in your Unity Catalog metastore that mirrors the other Databricks catalog accessible from the cluster or SQL warehouse so that you can use Unity Catalog query syntax and data governance tools to manage Databricks user access to the data.

Before you begin

Workspace requirements:

Workspace enabled for Unity Catalog.

Compute requirements:

Network connectivity from your Databricks Runtime cluster or SQL warehouse to the target database systems. See Networking recommendations for Lakehouse Federation.
Databricks clusters must use Databricks Runtime 13.3 LTS or above and shared or single-user access mode.
SQL warehouses must be Pro or Serverless.

Permissions required:

To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Additional permission requirements are specified in each task-based section that follows.

You must also have an active cluster or SQL warehouse in the Databricks workspace that you are using to configure the connection.

Create a connection

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in a Databricks notebook or the Databricks SQL query editor.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

In your Databricks workspace, click Catalog.
In the left pane, expand the External Data menu and select Connections.
Click Create connection.
Enter a user-friendly Connection name.
Select a Connection type of Databricks.
Enter the following connection properties for the other Databricks instance.
- Host: Workspace instance name. To learn how to get the workspace instance name, see Get identifiers for workspace objects.
- HTTP path: The HTTP path for your SQL warehouse. To get the path, go to SQL > SQL Warehouses in the sidebar, select the SQL warehouse, go to the Connection details tab, and copy the value for HTTP path.
- Personal access token: A Databricks personal access token that enables access to the target workspace.. To learn how to get a token, see Databricks personal access token authentication. For connections, Databricks recommends using a personal access token for a service principal.
(Optional) Click Test connection to confirm that it works.
(Optional) Add a comment.
Click Create.

Run the following command in a notebook or the Databricks SQL query editor, replacing the following:

<connection-name>: User-friendly name for the connection you’re creating.
<workspace-instance>: The target workspace instance. To learn how to get the workspace instance name, see Get identifiers for workspace objects.
<sql-warehouse-path>: The HTTP path for your SQL warehouse. To get the path, go to SQL > SQL Warehouses in the sidebar, select the SQL warehouse, go to the Connection details tab, and copy the value for HTTP path.
<personal-access-token>: A Databricks personal access token that enables access to the target workspace. To learn how to get a token, see Databricks personal access token authentication. For connections, Databricks recommends that you use a service principal’s personal access token.

CREATE CONNECTION <connection-name> TYPE databricks
OPTIONS (
  host '<workspace-instance>',
  httpPath '<sql-warehouse-path>',
  personalAccessToken '<personal-access-token>'
);

We recommend that you use Databricks secrets instead of plaintext strings for sensitive values like credentials. For example:

CREATE CONNECTION <connection-name> TYPE databricks
OPTIONS (
  host '<workspace-instance>',
  httpPath '<sql-warehouse-path>',
  personalAccessToken secret ('<secret-scope>','<secret-key-password>')
)

For information about setting up secrets, see Secret management.

Create a foreign catalog

A foreign catalog mirrors a catalog in the external Databricks workspace so that you can query and manage access to data in that external Databricks catalog as if it were a catalog in your own workspsace. To create a foreign catalog, you use a connection to the external Databricks workspace that has already been defined.

To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG SQL command in a Databricks notebook or the Databricks SQL query editor.

Permissions required: CREATE CATALOG permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

In your Databricks workspace, click Catalog.
Click the Create Catalog button.
On the Create a new catalog dialog, enter a name for the catalog and select a Type of Foreign.
Select the Connection that provides access to the database that you want to mirror as a Unity Catalog catalog.
Enter the target Databricks Catalog name.
Click Create.

Run the following SQL command in a notebook or Databricks SQL editor. Items in brackets are optional. Replace the placeholder values:

<catalog-name>: Name for the foreign catalog that you are creating.
<connection-name>: The connection object that specifies the data source, path, and access credentials.
<external-catalog-name>: Name of the catalog in the external Databricks workspace that you are mirroring.

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (catalog '<external-catalog-name>');

Supported pushdowns

The following pushdowns are supported on all compute:

Filters
Projections
Limit
Functions: only filter expressions are supported (string functions, Mathematical functions, Data, Time and Timestamp functions, and other miscellaneous functions, such as Alias, Cast, SortOrder).

The following pushdowns are supported on Databricks Runtime 13.3 LTS and above and SQL warehouse compute:

Aggregates
The following Boolean operators: =, <, <=, >, >=, <=>
The following mathematical functions (not supported if ANSI is disabled): +, -, *, %, /
The following miscellaneous operators: ^, |, ~
Sorting, when used with limit

The following pushdowns are not supported:

Joins
Windows functions