Migrate legacy query federation connections to Lakehouse Federation

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 the my-postgres-table in the my-postgres-database database on the postgres-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 the my-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.