Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Important
This feature is in Public Preview.
This article describes how to set up Lakehouse Federation to run federated queries on Oracle data that is not managed by Azure Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?.
To connect to your Oracle database using Lakehouse Federation, you must create the following in your Azure Databricks Unity Catalog metastore:
Before you begin, make sure you meet the requirements in this section.
Workspace requirements:
Compute requirements:
Permissions required:
CREATE CONNECTION
privilege on the Unity Catalog metastore attached to the workspace.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.
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.
Note
You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.
Permissions required: Metastore admin or user with the CREATE CONNECTION
privilege.
oracle-demo.123456.rds.amazonaws.com
1521
oracle_user
password123
Run the following command in a notebook or the Databricks SQL query editor:
CREATE CONNECTION <connection-name> TYPE oracle
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 oracle
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.
Note
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.
In the Azure Databricks workspace, click Catalog to open Catalog Explorer.
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.
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.<service-name>
: Service name you want to mirror as a catalog in Azure Databricks.CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (service_name '<service-name>');
The following pushdowns are supported:
When you read from Oracle to Spark, data types map as follows:
Oracle type | Spark type |
---|---|
TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE | TimestampType |
DATE, TIMESTAMP | TimestampType/TimestampNTZType* |
NUMBER, FLOAT | DecimalType |
BINARY FLOAT | FloatType |
BINARY DOUBLE | DoubleType |
CHAR, NCHAR, VARCHAR2, NVARCHAR2 | StringType |
spark.sql.timestampType = TIMESTAMP_LTZ
(default). They are mapped to TimestampNTZType if spark.sql.timestampType = TIMESTAMP_NTZ
.Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Migrate Oracle workloads to Oracle Database@Azure - Training
Learn how to migrate your Oracle workloads to Oracle Database@Azure.
Certification
Microsoft Certified: Azure Data Fundamentals - Certifications
Demonstrate foundational knowledge of core data concepts related to Microsoft Azure data services.