Run federated queries on Teradata

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

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

• A connection to your Teradata database.
• A foreign catalog that mirrors your Teradata database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Azure Databricks user access to the database.

Before you begin, make sure you meet the requirements in this section.

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.
• Azure Databricks compute must use Databricks Runtime 16.1 or above and Shared or Single user access mode.
• SQL warehouses must be pro or serverless and must use 2024.50 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.

• TLS enabled on the host server. By default, SSLMODE is REQUIRE. For more information, see How to Secure Connections using TLS in the Teradata documentation.

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 an Azure Databricks notebook or the Databricks SQL query editor.

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

1. In your Azure Databricks workspace, click Catalog.

2. At the top of the Catalog pane, click the 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. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.

4. Select a Connection type of Teradata.

5. (Optional) Add a comment.

6. Click Next.

7. On the Authentication page, enter the following connection properties for the Teradata instance:

   • Host: For example, teradata-demo.teradata.com
   • Port: For example, 1025
   • User: For example, teradata_user
   • Password: For example, password123

8. Click Create connection.

9. On the Catalog basics page, enter a name for the 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 Azure Databricks and Unity Catalog.

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

11. Click Create catalog.

12. On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.

13. Change the Owner who will be able to manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.

14. Grant Privileges on the catalog. Click Grant:

    1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
    2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
       • Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
       • Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
       • Manually select the privileges to grant.
    3. Click Grant.

15. Click Next.

16. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.

17. (Optional) Add a comment.

18. Click Save.

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

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

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

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

If you must use plaintext strings in notebook SQL commands, avoid truncating the string by escaping special characters like $ with \. For example: \$.

For information about setting up secrets, see Secret management.

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 Azure 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 an Azure Databricks notebook or the SQL query editor. 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 Azure Databricks workspace, click Catalog to open Catalog Explorer.

2. At the top of the Catalog pane, click the 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 Azure 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 Azure Databricks.

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

The following pushdowns are supported:

• Filters
• Projections
• Limit
• Aggregates
• Cast
• Contains, Startswith, Endswith, Like

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