Oracle AI Data Catalog

The Oracle AI Data Catalog (AICAT) is an Oracle-managed Iceberg REST catalog service. The catalog service is designed to manage Iceberg tables efficiently. It provides APIs for handling CRUD operations on tables, managing metadata, and handling transaction.

AICAT works within Oracle Autonomous AI Database and is available to all databases inside that tenancy. The catalog service uses the tenant database schema for storing the metadata of the tables.

• Supported Storage Types
  
• Quick Start
  
• Enable Access to Private Data Sources from Autonomous AI Database
  The Autonomous AI Database from where you are accessing AICAT should be configured to use a private endpoint to be able to communicate with private database sources. Otherwise when you try to create and test such a connection, you might get a "failed to connect" error.

You need to create or select an existing Autonomous AI Database instance the Oracle AI Data Catalog will run on. This database instance will be used as the catalog metadata repository.

To enable Oracle AI Data Catalog (AICAT) on an Autonomous AI Database Serverless instance you need to set the ADB$TOOLS OCI tag to AI_CAT. You can then connect to the catalog service using the generated service URL.

Note the following:

• Setting the OCI tag AI_CAT on an ADB-S instance enables the AI Data Catalog service for that database.
• The service supports up to 32 ECPUs in the initial release. ECPU consumption is billed at the standard Autonomous AI Database rate when the catalog service compute resources are active and in use. See ECPU Compute Model Billing Information for information about ECPU billing for additional VMs and compute resources.
• The AICAT service is configured with a default idle timeout of 2 hours (120 minutes). This configuration keeps the service readily available and responsive for normal usage patterns while avoiding unnecessary billing when the service is idle. This also ensures that when the service remains inactive beyond the configured idle timeout period, the associated compute resources are automatically stopped and are no longer billed.
• If the AICAT service has timed out, any subsequent request to the service automatically triggers a restart of the catalog service. In most cases, the service becomes available within approximately 30 seconds.

You can enable AICAT on Lakehouse and Autonomous Transaction Processing (ATP) database types. Oracle recommends that you use ATP databases because catalog operations such as metadata management, API calls, authorization checks, and catalog updates would be better optimized using ATP.

To enable AICAT and obtain the instance URL:

1. Login into OCI and go to the Autonomous AI Database Serverless instance that you have selected as the catalog metadata repository.
2. Navigate to Tags.
3. Set the following tag:
   • Tag Key name: ADB$TOOLS
   • Tag Value: AI_CAT

   This enables AICAT for the Autonomous AI Database Serverless instance.

4. To obtain the endpoint, go to ADBS in OCI and select the Tools configuration tab. Edit the displayed URL to the <database name>/catalog format.

   For example, change https://test1234.adb.us-phoenix-1.oraclecloudapps.com/ords/apex to https://test1234.adb.us-phoenix-1.oraclecloudapps.com/catalog.

A splash screen appears that lists the steps you need to take to get started with using AICAT along with links to the help topics.

To register storage use the PL/SQL ORACLE_AI_DATA_CATALOG.REGISTER_STORAGE_<VENDOR>() procedure.

Note that the invoker must be an ADMIN user or a user with PDB_DBA role assigned. See Manage User Roles and Privileges on Autonomous AI Database for more information on granting roles and adding or updating privileges for a user.

Oracle AI Data Catalog supports following storage providers:

• Oracle Cloud Infrastructure with S3 compatibility
• Azure Storage (ADLS and BLOB)
• ZFS Storage

To update the storage credentials, see Update Storage Credentials. If you want to unregister the storage credentials see Unregister Storage Credentials.

begin
   oracle_ai_data_catalog.register_storage_oci(
   p_warehouse => 's3://<bucket>',
   p_endpoint => 'https://<namespace>.compat.objectstorage.<region>.oci.customer-oci.com',
   p_region     => '<region>',
   p_access_key => '<access-key-id>',
   p_secret_key => '<secret-key-id>'
   );
end;
/

begin oracle_ai_data_catalog.register_storage_azure(
    p_warehouse            => 'abfss://<container>',
    p_endpoint             => 'https://<account>.dfs.core.windows.net',
    p_storage_account_name => '<storage_account_name>',
    p_storage_account_key  => '<storage_account_key>'
);
end;
/

begin oracle_ai_data_catalog.register_storage_azure(
    p_warehouse            => 'abfss://<container>',
    p_endpoint             => 'https://<account>.blob.core.windows.net',
    p_storage_account_name => '<storage_account_name>',
    p_storage_account_key  => '<storage_account_key>'
);
end;
/

begin
   oracle_ai_data_catalog.register_storage_zfs(
   p_warehouse  => 's3a://icebergs3/iceberg_warehouse_s3/',
   p_endpoint   => 'https://<account>.us.oracle.com',
   p_accesskey => '<access-key-id>',
   p_secret_key => '<secret-key-id>'
   );
end;
/

• Update Storage Credentials
  
• Unregister Storage Credentials
  

AICAT supports registering existing Apache Iceberg tables that are stored in Object Storage. Once registered, the Iceberg table metadata and table definitions become discoverable and accessible through AICAT.

Before registering Iceberg tables in AICAT, make sure that:

• AICAT is already enabled on an Autonomous Database Serverless instance. See Enable Oracle AI Data Catalog.
• You are connected to the Autonomous Database Serverless instance as the ADMIN user or a user with the PDB_DBA or AICAT_USER role.
• You have registered your storage with AICAT. See Register Storage Credentials.
• The Object Storage bucket contains valid Iceberg table data and metadata in the correct format.

  For example,

  Bucket/Warehouse: N-Warehouse
  -NAMESPACE 
    -TABLE_NAME_NAMESPACE
      - data
        - parquet files.
      - metadata
        - json files

  Here:

  • NAMESPACE represents the Iceberg namespace/schema.
  • TABLE_NAME_NAMESPACE represents the Iceberg table
  • data contains the table data files (typically Parquet)
  • metadata contains Iceberg metadata and snapshot JSON files required for table management and querying
• You have access permissions to the bucket and objects.

To register your existing Iceberg tables with AICAT:

1. Connect to your AICAT instance using your database authentication credentials.

   Use the following command to generate an access token:

   TOKEN=$(curl -k -s --location '<URI>/v1/auth/token' \
   --header 'Content-Type: application/x-www-form-urlencoded' \
   --data-urlencode 'grant_type=client_credentials' \
   --data-urlencode 'client_id=<dbUser>' \
   --data-urlencode 'client_secret=<dbPwd>' \
   --data-urlencode 'scope=PRINCIPAL_ROLE:ALL' | jq -r '.access_token')
   

   Here,

   • <URI> is the Catalog server URL.
   • <dbUser> is your database username.
   • <dbPwd> is your database password.

   If the token value returns as null, rerun the request using the -v option for verbose output to help troubleshoot authentication or connectivity issues.

   Example:

   curl -v -k --location '<URI>/v1/auth/token'

2. Determine the Iceberg metadata file location for the table you want to register in the catalog. You can obtain the metadata location in one of the following ways:
   • Load the table metadata from a previous catalog server.
   • Manually browse the warehouse or Object Storage bucket and identify the current Iceberg metadata JSON file.
   For example:

   Namespace           : SILVER
   Table               : COMPETITOR_PRICING_SILVER
   Metadata Location   : s3://0A-Warehouse/SILVER/COMPETITOR_PRICING_SILVER/metadata/00002-f2662b11-3268-4792-90f9-b8f4b851d629.metadata.json

   The metadata JSON file contains the Iceberg table definition, schema, snapshots, manifests, and table state information required for catalog registration.

   To create namespace SILVER if it does not already exist, use the following command:

   curl -k --location '<URI>/v1/namespaces' \
   --header "Authorization: Bearer $TOKEN" \
   --header 'Content-Type: application/json' \
   --data '{"namespace": ["SILVER"], "properties": {}}'

3. To register the table, run the following command:

   curl -k --location '<URI>/v1/namespaces/SILVER/register' \
   --header 'Content-Type: application/json' \
   --header "Authorization: Bearer $TOKEN" \
   --data '{
       "name":"COMPETITOR_PRICING_SILVER",
       "metadata-location": "s3://0A-Warehouse/SILVER/COMPETITOR_PRICING_SILVER/metadata/00002-f2662b11-3268-4792-90f9-b8f4b851d629.metadata.json",
       "overwrite": false
   }'

4. To load the newly registered table, run the following command:

   curl -k --location '<URI>/v1/namespaces/SILVER/tables/COMPETITOR_PRICING_SILVER' \
   --header "Authorization: Bearer $TOKEN" \
   --header 'Content-Type: application/json'

5. Similarly, register other tables.

To allow a database user to access the AICAT service (see Choose Catalog Repository Database), the user should be an ADMIN user or you need to authorize the user with the AICAT_USER or PDB_DBA role.

You can authenticate users using a token API call with user credentials. To obtain the access token, send the following request:

POST https://public_lb_host/catalog/v1/auth/token

Example request parameter using cURL:

curl -k -s --location '<URI>/catalog/v1/auth/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=client_credentials' \
--data-urlencode 'client_id=<dbUser>' \
--data-urlencode 'client_secret=<dbPwd>' \
--data-urlencode 'scope=PRINCIPAL_ROLE:ALL' | jq -r '.access_token'

When the token expires, you will receive a 401 response. Use the same endpoint to obtain a new token.

Here's a sample response token:

{
    "access_token": "<token>",
    "issued_token_type": "urn:ietf:params:oauth:token-type:access_token",
    "token_type": "bearer",
    "expires_in": 3600
}

Use the obtained token as your Bearer token in the Authorization header:

Authorization: Bearer <your_token>

See REST API for Oracle AI Data Catalog for detailed information about each catalog API REST endpoint.

The AICAT Iceberg REST Catalog is the default REST-based Iceberg catalog embedded within AICAT. It is designed to efficiently manage Iceberg tables by providing REST APIs for table lifecycle operations, including create, read, update, and delete (CRUD), along with metadata management and transaction handling. The AICAT Iceberg REST Catalog follows the standard Apache Iceberg REST Catalog API specification. You must be an ADMIN user or as a user that is granted the PDB_DBA or the AICAT_USER role.

See REST API for Oracle AI Data Catalog for detailed information about each catalog API REST endpoint.

AICAT integrates with multiple query engines to request and retrieve information. You can use AICAT with query engines such as Apache Spark, add external tables over AICAT tables, mount AICAT on DBMS catalog, as well as create Apache Iceberg tables using Oracle Data Transforms.

If you encounter any issues, file a service request at Oracle Cloud Support or contact your support representative.

-- setup ACL Rules --
-- for XT to communicate to ai catalog instance
-- for XT to communicate to object storage bucket, OCI in this example.

BEGIN
dbms_network_acl_admin.append_host_ace(
host => '*.oraclecloudapps.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));

dbms_network_acl_admin.append_host_ace(
host => '*.oci.customer-oci.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));
END;
/

-- create storage and catalog access credentials --
begin
dbms_cloud.create_credential(
  credential_name => '<catalogAuthCreds>',
  username        => '<dbUserName>',
  password        => '<dbUserPwd>'
);
end;
/

begin
  dbms_cloud.create_credential(
    credential_name => '<storageCredentialName>',
    username        => '<s3accessKey>',
    password        => '<s3secretKey>'
  );
end;
/


-- XT Creation with AI Cat Syntax --
-- <catalogURI> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog
-- <authUri> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1/auth/token

begin
dbms_cloud.create_external_table(
  table_name      => '<extTableName>',
  credential_name => '<storageCredentialName>',
  format          => '{
    "access_protocol": {
      "protocol_type": "iceberg",
      "protocol_config": {
        "iceberg_catalog_type": "oracle_ai_data_catalog",
        "rest_catalog_endpoint":"<catalogURI>",
        "rest_authentication": {
          "rest_auth_cred": "<catalogAuthCreds>",
          "rest_auth_endpoint":"<authUri>"
        },
        "table_path": ["<namespaceName>", "<tableName>"]
      }
    }
  }'
);
end;
/

select * from <extTableName>;

-- setup ACL Rules --
-- for XT to communicate to ai catalog instance
-- for XT to communicate to object storage bucket, OCI in this example.

BEGIN
dbms_network_acl_admin.append_host_ace(
host => '*.oraclecloudapps.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));

dbms_network_acl_admin.append_host_ace(
host => '*.oci.customer-oci.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));
END;
/

-- create bearer token and catalog access credentials --
-- <authUri> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1/auth/token
BEGIN
    DBMS_SHARE.CREATE_BEARER_TOKEN_CREDENTIAL(
        CREDENTIAL_NAME => '<bearerTokenCredName>',
        BEARER_TOKEN    => 'BEARER_TOKEN',
        TOKEN_ENDPOINT  => '<authUri>',
        CLIENT_ID       => '<dbUserName>',
        CLIENT_SECRET   => '<dbUserPwd>',
        TOKEN_SCOPE     => 'PRINCIPAL_ROLE:ALL'
    );
END;
/

begin
  dbms_cloud.create_credential(
    credential_name => '<storageCredentialName>',
    username        => '<s3accessKey>',
    password        => '<s3secretKey>'
  );
end;
/


-- XT Creation with AI Cat Syntax --
-- <catalogURIForIceberg> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1
-- <authUri> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1/auth/token

-- DBMS_CATALOG with AI CAT --
BEGIN
dbms_catalog.mount_iceberg(
catalog_name             => '<catalogName>',
endpoint                 => '<catalogURIforIceberg>',
catalog_credential       => '<bearerTokenCredentialName>',
data_storage_credential  => '<storageCredentialName>',
catalog_type             => 'ICEBERG_ORACLE');
END;
/

-- sample queries once catalog is mounted
-- show mounted catalogs
SELECT c.catalog_name AS mounted_catalog_name, c.catalog_type AS mounted_catalog_type
FROM user_mounted_catalogs c
ORDER BY c.catalog_name;

-- show schemas
select schema_name from dbms_catalog.get_schemas('<catalogName>');

-- show tables
select table_name from dbms_catalog.get_tables('<catalogName>', '<schemaName>');

-- query existing tables
select FIRST_NAME, LAST_NAME from "<schemaName>"."<tableName>"@<catalogName> where rownum < 10;

-- create new table
CREATE ICEBERG TABLE  "<schemaName>"."<tableName>"
(   type  STRING,
    address   STRING
)
WITHIN CATALOG "<catalogName>"
STORAGE LOCATION "s3://<ociBucketNameUsedInStep#2>/<schemaName>/<tableName>/";

-- insert into table;
create table TESTLOCAL (type varchar2(10), address varchar2(10));
insert into TESTLOCAL values ('Alan', 'Matthews');
commit;

-- insert into iceberg table via ai catalog from local table
INSERT INTO "<schemaName>"."<tableName>"@<catalogName> select * from TESTLOCAL;

-- query the table in ai cat
select * from "<schemaName>"."<tableName>"@<catalogName>;

select DBMS_CATALOG.GENERATE_TABLE_SELECT('<catalogName>', '"<schemaName>"', '"<tableName>"') from dual;