Run federated queries on another Databricks workspace

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 Connect to external databases and catalogs.

To connect to a Databricks catalog in another workspace using Lakehouse Federation, you must create the following in your Azure 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 Azure Databricks user access to the data.

Before you begin

Workspace requirements:

• Workspace enabled for Unity Catalog. Workspaces created after November 9, 2023 are enabled for Unity Catalog automatically, including automatic metastore provisioning. You don't need to create a metastore manually unless your workspace predates automatic enablement and hasn't been enabled for Unity Catalog. See Get started with Unity Catalog.

Compute requirements:

• Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.
• Azure Databricks compute must use Databricks Runtime 13.3 LTS or above and Standard or Dedicated access mode.
• SQL warehouses must be pro or serverless and must use 2023.40 or above.

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. In workspaces that were enabled for Unity Catalog automatically, workspace admins have the CREATE CONNECTION privilege by default.
• 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. In workspaces that were enabled for Unity Catalog automatically, workspace admins have the CREATE CATALOG privilege by default.

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

You must also have an active cluster or SQL warehouse in the Azure 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 an Azure Databricks notebook or the Databricks SQL query editor.

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

Catalog Explorer

1. In your Azure Databricks workspace, click Catalog.
2. At the top of the Catalog pane, click the Add icon and select Create a connection from the menu.
3. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.
4. Select a Connection type of Databricks.
5. (Optional) Add a comment.
6. Click Next.
7. On the Authentication page, 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.
   • Personal access token: A Azure Databricks personal access token that enables access to the target workspace.. To learn how to get a token, see Authenticate with Azure Databricks personal access tokens (legacy). For connections, Databricks recommends using a personal access token for a service principal.
   • 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.
8. Click Create connection.
9. On the Catalog basics page, enter the catalog name in the other Azure Databricks workspace that can be mapped to a catalog object in this metastore.
10. (Optional) Click Test connection to confirm that it works.
11. Click Create catalog.
12. On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.
13. Change the Owner who will be able to manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
14. Grant Privileges on the catalog. Click Grant:
    1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
    2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
       • Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
       • Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
       • Manually select the privileges to grant.
    3. Click Grant.
15. Click Next.
16. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.
17. (Optional) Add a comment.
18. Click Save.

SQL

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 Azure Databricks personal access token that enables access to the target workspace. To learn how to get a token, see Authenticate with Azure Databricks personal access tokens (legacy). 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 Azure 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 an Azure Databricks notebook or the Databricks SQL query editor. You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs and Unity Catalog commands.

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

Catalog Explorer

1. In your Azure Databricks workspace, click Catalog to open Catalog Explorer.

2. At the top of the Catalog pane, click the Add icon and select Add a catalog from the menu.

   Alternatively, from the Quick access page, click the Catalogs button, and then click the Create catalog button.

3. Follow the instructions for creating foreign catalogs in Create catalogs.

SQL

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 table lists the pushdown operations supported for Databricks-to-Databricks, along with the compute required for each.

Pushdown	Supported compute
Aggregates	All compute
Arithmetic operators (such as +, -, *, %, /) — not supported if ANSI mode is disabled	All compute
Bitwise operators (&, |, ^ and ~)	All compute
Boolean operators (such as =, <=>, <, <=, >, >=)	All compute
Contains, Startswith, Endswith	All compute
Date, Time, and Timestamp parts (EXTRACT DAY, MONTH, YEAR, QUARTER, WEEK, HOUR, MINUTE, SECOND; filter expressions only)	All compute
Filters	All compute
Limit	All compute
Mathematical functions (SIN, COS, ABS, FLOOR; filter expressions only; ABS is not supported if ANSI mode is disabled)	Databricks Runtime 15.4 and above, and SQL warehouses
COALESCE function (partial support, filter expressions only)	Databricks Runtime 15.4 and above, and SQL warehouses
Offset	All compute
Projections	All compute
Sorting, when used with limit	All compute
String functions (UPPER, LOWER, CONCAT, TRIM, CHAR_LENGTH; filter expressions only)	Databricks Runtime 15.4 and above, and SQL warehouses
Table samples (TABLESAMPLE BERNOULLI or default sampling, without replacement)	All compute
Joins	Not supported
Window functions	Not supported

Data type mappings

Data types typically map one-to-one when you use Databricks-to-Databricks federation. However, the following data types map to StringType: