External Catalogs

Create external catalogs to source data from a location outside Oracle AI Data Platform.

External catalogs connect AI Data Platform to outside data sources, such as Oracle Autonomous AI Lakehouse, Oracle AI Database, and Oracle Autonomous AI Transaction Processing, and third-party stores such as Snowflake, Azure SQL, and MySQL. AI Data Platform discovers and synchronizes metadata from these sources into the Master Catalog, enabling users to browse and query remote schemas and tables without duplicating the underlying data.

External catalogs use the credentials provided during the external catalog creation for querying the external source. For more information on data sources, see Internal Sources.

For example, if you create an external catalog for an Autonomous AI Lakehouse instance where the Autonomous AI Lakehouse user credentials used have access to schema1 but not schema2, only schema1 appears in the external catalog. Users with permissions for the external catalog can only query the schema the Autonomous AI Lakehouse user has access to.

Note:

AI Data Platform doesn't support harvesting data from schemas and tables shared across Pluggable Databases (PDBs) or from Oracle-maintained schemas and tables.

Required Permissions for Autonomous AI Lakehouse and Autonomous AI Transaction Processing

When you create an external catalog in AI Data Platform the user credentials you use to connect must have at least the following permissions:

CREATE SESSION to connect to the database
SELECT access on the required objects (tables/views/external tables) via least-privilege grants or a dedicated read role
READ, WRITE on DIRECTORY DATA_PUMP_DIR

If you're inserting data or creating a new table in the external catalog, ensure the user is part of the DWROLE. For more information, refer to the Oracle Autonomous AI Transaction Processing documentation, Manage User Privileges on Autonomous AI Database - Connecting with a Client Tool.

Limitations

UPDATE operation isn't supported for external catalogs. Use external catalog tables for discovery and query access.

DDL isn't supported, even when the credentials used to create the external catalog has permissions to execute DDL statements.

Table name casing affects how the table is created in Oracle Autonomous AI Lakehouse:

If you provide the table name in uppercase, the table is created using uppercase letters and follows the default case-insensitive behavior in Autonomous AI Lakehouse.
If you provide the table name in lowercase, the table is created as case-sensitive in Autonomous AI Lakehouse. Column names for tables created through Autonomous AI Lakehouse are always created as case-sensitive.
Source column comments are limited to 256 UTF-8 bytes during external catalog ingestion and refresh. The limit is measured in bytes, not characters, so comments containing multibyte characters can exceed the limit when they contain 256 characters or fewer. Exceeding the limit can cause metadata ingestion or refresh to fail. Reduce the source column comment to 256 UTF-8 bytes or fewer, and then retry.

Create an External Catalog

You can connect a catalog from your Oracle AI Data Platform instance to a external source.

Click Create in the left navigation pane and select Catalog. You can also navigate to the Master catalog and click Create Catalog in Master Catalog.
Fill in the name and description fields.
From the Catalog Type drop-down list, select External Catalog.

Select the external source type.

Data Source

Fields

Oracle Autonomous AI Lakehouse

Provide either a wallet file, or the instance configuration.

Oracle Autonomous AI Transaction Processing

Provide either a wallet file, or the instance configuration.

Oracle AI Database

Provide either a wallet file, or the instance configuration.

Oracle Exadata Database Service

Provide host, port, service name/SID.

Snowflake

Provide Snowflake account name, Database name, Warehouse. Select Authentication method as Key Pair, then provide the Private key file and Private Key Pass phrase.

Azure SQL

Provide host, port and database name (optional).

MySQL

Provide host and port.

Google Big Query

Provide host, port, project ID, service account email, private key.

DB2

Provide host, port and database name.

Kafka (Coming soon)

Provide the bootstrap server. Separate multiple servers with a comma.

Fill in the user name and password, if applicable.
SSL is enabled by default. Clear the box to disable SSL.
Click Create.
External catalogs that are extracting data from an external source display a spinning circle icon.

You can also monitor progress from Job Runs.

Create an External Catalog for Private Networks

You can create an external catalog that accesses data sources in a private network.

Click Create in the left navigation pane and select Catalog. You can also navigate to the Master catalog and click Create Catalog in Master Catalog.
Fill in the name and description fields.
From the Catalog Type drop-down list, select External Catalog.

Select the external source type:

Data Source

Fields

Oracle Autonomous AI Lakehouse

Provide either a wallet file, or the instance configuration.

Oracle Autonomous AI Transaction Processing

Provide either a wallet file, or the instance configuration.

Oracle AI Database

Provide either a wallet file, or the instance configuration.

Oracle Exadata Database Service

Provide host, port, service name/SID.

Kafka (Coming soon)

Provide the bootstrap server. Separate multiple servers with a comma.

Fill in the user name and password, if applicable.
SSL is enabled by default. Clear the box to disable SSL.
Select Enable private network.
Select the workspace with the desired private network configuration.
For information on setting up a workspace configured for private networks, see Create a Workspace with Private Network Access Enabled.
Click Create.
External catalogs that are extracting data from an external source display a spinning circle icon.

You can also monitor progress from Job Runs.

How to Connect Oracle Autonomous AI Database on Exadata Cloud@Customer to Oracle AI Data Platform

You can create an external catalog for an Oracle Autonomous AI Transaction Processing, Oracle Autonomous AI Database, or Oracle Autonomous AI Lakehouse database running on Oracle Autonomous AI Database on Exadata Cloud@Customer. When the database is accessible only through a private network, configure an AI Data Platform workspace with private network access and the database SCAN details.

The connection uses TLS with a client credentials wallet.

Requirements

Before you begin, ensure that:

The database is provisioned and available.
You have downloaded the database client credentials wallet.
You know the database SCAN fully qualified domain name and TLS listener port.
The SCAN name can be resolved from the private network used by the AI Data Platform workspace.
Network routes and security rules allow traffic from the workspace network to the database listener port.
You have permission to inspect the required VCN, subnet, and network security groups.
The database user has CREATE SESSION and SELECT privileges on the schemas and objects that you want to expose through the external catalog.
The database user has any additional permissions required for the intended read or write operations.

Only schemas and objects accessible to the specified database user are discovered and added to the external catalog.

Create a workspace with private connectivity

Follow the steps in Create a Workspace with Private Network Access Enabled, with the following changes:

Provide the VCN and Subnet that provide connectivity to your Oracle Exadata Cloud@Customer environment.
Under Advanced network options, select My data sources require access through a SCAN proxy.
Enter the database SCAN fully qualified domain name.
Enter the database TLS listener port.

Create the external catalog

Follow the steps provided in Create an External Catalog for Private Networks with the following selections:

For External source type, select Oracle Autonomous AI Transaction Processing.
For Connection method, select Wallet.
Upload the client credentials wallet downloaded from the database.
Select the required database service from the wallet.
Provide the wallet password, if required.
Select the workspace configured above with access to the database network and SCAN address.
Test the connection to confirm everything is configured correctly.

Validate the Connection

After catalog creation and metadata discovery complete:

Confirm the expected schemas and tables appear in the external catalog.
Start or select a compute cluster in the private workspace.
Query a discovered table using its three-part name:

SELECT *
FROM <catalog_name>.<schema_name>.<table_name>;

Refresh an External Data Catalog

You can refresh an external catalog in Oracle AI Data Platform to sync its contents with the external source.

In the left navigation pane, click Master catalog.
In the Master catalog tree, select the external catalog you want to refresh.
Click Refresh.
The external catalog displays under Master Catalog with a spinning circle icon while it extracts data and updates metadata from the external source.

You can also monitor progress from Job Runs, see Monitor Jobs.

Refresh an External Catalog using SQL

You can use SQL grammar to refresh metadata for an external catalog, as well as its schema and tables, to sync the contents with the external source.

To refresh an external catalog, use the following SQL commands in a notebook:

REFRESH EXTERNAL CATALOG <<catalog_name>>

To refresh a schema in an external catalog, use:

REFRESH SCHEMA IN EXTERNAL CATALOG <<catalog_name.schema_name>>

To refresh a table in a schema in an external catalog, use:

REFRESH TABLE IN EXTERNAL CATALOG <<catalog_name.schema_name.table_name>>

For more information, see SQL Grammar.

Rename an External Catalog

You can rename an external catalog if its contents or purpose has changed.

In the left navigation pane, click Master catalog.
In the Catalogs tab, locate your catalog and click Actions, then click Rename.
Edit the catalog name and press Enter.

Edit an External Catalog Description

You can modify the description for an external catalog if its contents or purpose has changed.

In the left navigation pane, click Master catalog.
In the Catalogs tab, locate your catalog and click Actions, then click Edit description.
Edit the catalog description and click Save.

Change the Password for an External Catalog

You can update the required password for an external catalog so it can continue to access the source.

In the left navigation pane, click Master catalog.
In the Catalogs tab, locate your catalog and click Actions, then click Change password.
Enter the new password for the external catalog and click Save.

Delete an External Catalog

You can delete an external catalog when it's no longer needed.

Deleting an external catalog only deletes the locally stored metadata. Data in the external data source isn't impacted.

In the left navigation pane, click Master catalog.
In the Catalogs tab, locate your catalog and click Actions, then click Delete.
Select Confirm deletion of the catalogs.
Click Delete.

Delete Data from Tables within an External Catalog

You can use the Spark write path to delete rows from an external catalog table when the catalog and table are configured to support pushdown SQL DELETE.

Use Catalog ID to Delete Table Data: Python Example

from pyspark.sql.types import StructType

df = spark.createDataFrame([], StructType([]))


    df.write
      .format("aidataplatform")
      .option("catalog.id", "<catalog>")
      .option("pushdown.sql", "DELETE FROM <schema>.<target_table> WHERE ID = 2")
      .save()

Use Catalog ID to Delete Table Data: Scala Example

val df = spark.emptyDataFrame

df.write
  .format("aidataplatform")
  .option("catalog.id", "<catalog>")
  .option("pushdown.sql", "DELETE FROM <schema>.<target_table> WHERE ID = 2")
  .save()

Ingestion-style Sample for Deleting Table Data: Python Example

from pyspark.sql.types import StructType
df = spark.createDataFrame([], StructType([]))

df.write
  .format("aidataplatform")
  .option("data.asset.type", "ORACLE_ALH")
  .option("tns.alias", alhTns)
  .option("user.name", userName)
  .option("wallet.content", alhWalletContent)
  .option("password", alhPassword)
  .option("pushdown.sql", "DELETE FROM ADMIN.PUSHDOWN_FEATURE WHERE ID = 2")
  .save()

Ingestion-style Sample for Deleting Table Data: Scala Example

val df = spark.emptyDataFrame

df.write
  .format("aidataplatform")
  .option("data.asset.type", "ORACLE_ALH")
  .option("tns.alias", alhTns)
  .option("user.name", userName)
  .option("wallet.content", alhWalletContent)
  .option("password", alhPassword)
  .option("pushdown.sql", "DELETE FROM ADMIN.PUSHDOWN_FEATURE WHERE ID = 2")
  .save()