Lakehouse Federation: Migrate legacy query federation connections
If you have set up legacy query federation connections, Databricks recommends that you migrate them to use Lakehouse Federation.
Legacy query federation involved creating tables in Databricks that referenced an external data source. To “move” those tables into Unity Catalog using Lakehouse Federation, you must create a Lakehouse Federation connection and foreign catalog for the database that includes the table. You can then grant user access to the catalog, or to schemas and tables in the catalog, using Unity Catalog.
A single foreign catalog may be able to cover multiple tables that you have set up for legacy query federation.
In the following example:
The “Legacy syntax” tab shows the syntax that was used to create a table named
postgresql_table
in Databricks that references themy-postgres-table
in themy-postgres-database
database on thepostgres-demo.lb123.us-west-2.rds.amazonaws.com:5432
server.The “Lakehouse Federation” tab shows the creation of a connection to the
postgres-demo.lb123.us-west-2.rds.amazonaws.com:5432
server, followed by the creation of a foreign catalog,my-postgres-catalog
that maps to themy-postgres-database
database.
CREATE TABLE postgresql_table
USING postgresql
OPTIONS (
dbtable 'my-postgres-table',
host 'postgres-demo.lb123.us-west-2.rds.amazonaws.com',
port '5432',
database 'my-postgres-database',
user 'postgres_user',
password 'password123'
);
--Create a connection:
CREATE CONNECTION postgres-connection TYPE postgresql
OPTIONS (
host 'postgres-demo.lb123.us-west-2.rds.amazonaws.com',
port '5432',
user 'postgres_user',
password 'password123'
);
--Create a foreign catalog that mirrors the database:
CREATE FOREIGN CATALOG my-postgres-catalog USING CONNECTION postgres-connection
OPTIONS (database 'my-postgres-database');
The foreign catalog will surface my-postgres-table
and all of the other tables in my-postgres-database
, and you can use Unity Catalog to manage access to those tables from your Databricks workspace.
Note
Your original query federation configuration may include options that are not available in Lakehouse Federation. You might not need those options when you move to Lakehouse Federation, but if you do need them, you can continue to use the legacy query federation connection rather than migrating.
Detailed instructions for creating connections and foreign catalogs are available for each supported connection type. See the article for your connection type, listed in the table of contents in this documentation site’s left navigation pane.