Lakehouse Federation for MySQL

Preview

This article describes how to set up Lakehouse Federation to run federated queries on MySQL data that is not managed by Databricks. To learn more about Lakehouse Federation, see Run queries using Lakehouse Federation.

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

A connection to your MySQL database.
A foreign catalog that mirrors your MySQL 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 Databricks Runtime cluster or SQL warehouse to the target database systems. See Networking recommendations for Lakehouse Federation.
Databricks clusters must use Databricks Runtime 13.1 or above and the shared cluster 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.

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 MySQL.
Enter the following connection properties for your MySQL instance.
- Host: For example, mysql-demo.lb123.us-west-2.rds.amazonaws.com
- Port: For example, 3306
- User: For example, mysql_user
- Password: For example, password123
(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.

CREATE CONNECTION <connection-name> TYPE mysql
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 mysql
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 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.
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 catalog in Databricks.
<connection-name>: The connection object that specifies the data source, path, and access credentials.

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>;

Supported pushdowns

The following pushdowns are supported on all compute:

Filters
Projections
Limit
Functions: partial, only for filter expressions. (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 on SQL warehouse compute version 2023.40 and above:

Aggregates
Sorting, when used with limit

The following pushdowns are not supported:

Joins
Windows functions

Data type mappings

When you read from MySQL to Spark, data types map as follows:

MySQL type	Spark type
bigint (if not signed), decimal	DecimalType
tinyint*, int, integer, mediumint, smallint	IntegerType
bigint (if signed)	LongType
float	FloatType
double	DoubleType
char, enum, set	CharType
varchar	VarcharType
json, longtext, mediumtext, text, tinytext	StringType
binary, blob, varbinary, varchar binary	BinaryType
bit, boolean	BooleanType
date, year	DateType
datetime, time, timestamp**	TimestampType/TimestampNTZType

tinyint(1) signed is treated as a boolean and converted into BooleanType. Seehttps://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html . * When you read from MySQL, MySQL Timestamp is mapped to Spark TimestampType if preferTimestampNTZ = false (default). MySQL Timestamp is mapped to TimestampNTZType if preferTimestampNTZ = true.

Limitations

The MySQL connector does not support case sensitive table in this release. All table names are converted to lowercase in Unity Catalog, which means that lookups must use lowercase names. Reach out to your Databricks contact for details about planning for support.