Run federated queries on Microsoft Azure Synapse

This article describes how to set up Lakehouse Federation to run federated queries on Azure Synapse (SQL Data Warehouse) data that is not managed by Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?.

To connect to an Azure Synapse (SQL Data Warehouse) database using Lakehouse Federation, you must create the following in your Databricks Unity Catalog metastore:

  • A connection to your Azure Synapse (SQL Data Warehouse) database.

  • A foreign catalog that mirrors your Azure Synapse (SQL Data Warehouse) database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Databricks user access to the database.

Before you begin

Workspace requirements:

  • Workspace enabled for Unity Catalog.

Compute requirements:

  • Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.

  • Databricks compute must use Databricks Runtime 13.3 LTS or above and Shared or Single user 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.

  • 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.

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.

Note

You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.

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

  1. In your Databricks workspace, click Catalog icon Catalog.

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

    Alternatively, from the Quick access page, click the External data > button, go to the Connections tab, and click Create connection.

  3. Enter a user-friendly Connection name.

  4. Select a Connection type of SQLDW.

  5. Enter the following connection properties for your Azure Synapse instance.

    • Host: For example, sqldws-demo.database.windows.net.

    • Port: For example, 1433

    • trustServerCertificate: Defaults to false. When set to true, the transport layer uses SSL to encrypt the channel and bypasses the certificate chain to validate trust. Leave this set to the default unless you have a specific need to bypass trust validation.

    • User

    • Password

  6. (Optional) Click Test connection to confirm that it works.

  7. (Optional) Add a comment.

  8. Click Create.

Run the following command in a notebook or the Databricks SQL query editor.

CREATE CONNECTION <connection-name> TYPE sqldw
OPTIONS (
  host '<hostname>',
  port '<port>',
  user '<user>',
  password '<password>'
);

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

CREATE CONNECTION <connection-name> TYPE sqldw
OPTIONS (
  host '<hostname>',
  port '<port>',
  user secret ('<secret-scope>','<secret-key-user>'),
  password secret ('<secret-scope>','<secret-key-password>')
)

For information about setting up secrets, see Secret management.

Create a foreign catalog

A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Databricks and Unity Catalog. To create a foreign catalog, you use a connection to the data source 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 SQL query editor.

Note

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.

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

  2. At the top of the Catalog pane, click the Add or plus icon 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.

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

  • <catalog-name>: Name for the catalog in Databricks.

  • <connection-name>: The connection object that specifies the data source, path, and access credentials.

  • <database-name>: Name of the database you want to mirror as a catalog in Databricks.

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

Supported pushdowns

The following pushdowns are supported:

  • Filters

  • Projections

  • Limit

  • Aggregates (Average, Count, Max, Min, StddevPop, StddevSamp, Sum, VarianceSamp)

  • Functions (Arithmetic and other miscellaneous functions, such as Alias, Cast, SortOrder)

  • Sorting

The following pushdowns are not supported:

  • Joins

  • Windows functions

Data type mappings

When you read from Synapse / SQL Data Warehouse to Spark, data types map as follows:

Synapse type

Spark type

decimal, money, numeric, smallmoney

DecimalType

smallint

ShortType

tinyint

ByteType

int

IntegerType

bigint

LongType

real

FloatType

float

DoubleType

char, nchar, ntext, nvarchar, text, uniqueidentifier, varchar, xml

StringType

binary, geography, geometry, image, timestamp, udt, varbinary

BinaryType

bit

BooleanType

date

DateType

datetime, datetime, smalldatetime, time

TimestampType/TimestampNTZType*

*When you read from Synapse / SQL Data Warehouse (SQLDW), SQLDW datetimes are mapped to Spark TimestampType if preferTimestampNTZ = false (default). SQLDW datetimes are mapped to TimestampNTZType if preferTimestampNTZ = true.