Connecting to LHO data in Databricks with Lakehouse Federation

Owned by Gary Nakanelua
Last updated: May 19, 2024
3 min read

Overview

All data collected by the Blueprint Lakehouse Optimizer (LHO) is stored in the SQL database configured in your LHO installation. Leveraging Databricks Lakehouse Federation, deeper analysis and reporting of the LHO data is readily available to Databricks users.

What is Lakehouse Federation?

Source: https://docs.databricks.com/en/query-federation/index.html

Lakehouse Federation is the query federation platform for Databricks. The term query federation describes a collection of features that enable users and systems to run queries against multiple data sources without needing to migrate all data to a unified system.

Databricks uses Unity Catalog to manage query federation. You configure read-only connections to popular database solutions using drivers that are included on Pro SQL Warehouses, Serverless SQL Warehouses, and Databricks Runtime clusters. Unity Catalog’s data governance and data lineage tools ensure that data access is managed and audited for all federated queries made by the users in your Databricks workspaces.

Before you begin

Source: https://docs.databricks.com/en/query-federation/sql-server.html

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.3 LTS or above and shared or single-user 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.

Create a connection

Source: https://docs.databricks.com/en/query-federation/sql-server.html

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.

 

  1. In your Databricks workspace, click  Catalog.

  2. In the left pane, expand the External Data menu and select Connections.

  3. Click Create connection.

  4. Enter a user-friendly Connection name.

  5. Select a Connection type of SQL Server.

  6. Enter the following connection properties for your SQL Server instance.

    • Host

    • Port

    • 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

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

  8. (Optional) Add a comment.

  9. Click Create.

 

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.

 

  1. In your Databricks workspace, click  Catalog.

  2. Click the Create Catalog button.

  3. On the Create a new catalog dialog, enter a name for the catalog and select a Type of Foreign.

  4. Select the Connection that provides access to the database that you want to mirror as a Unity Catalog catalog.

  5. Enter the name of the Database that you want to mirror as a catalog.

  6. Click Create.

 

Next Steps

Congratulations, the foreign catalog you created from the Lakehouse Federation connection now appears in your catalog listing. With this foreign catalog, users have read-only access to LHO data to perform analysis and reporting with the rich data collected and aggregated by LHO.

 

We’ve provided some example Databricks notebooks to get you started. If you have leveraged the Unity Catalog migration assessment in LHO, then a rich set of data is available for deeper analysis. Example notebooks are available in the Blueprint Github here.