Consume Share

About JSON Profiles

Delta Sharing Profiles helps you to connect to a share provider and access a Delta Sharing Server. The profile information is stored in a zip file containing the client credentials in JSON format. To access the share data, you must register the shared objects using an authorized JSON profile. You can then use the JSON profile for authenticating with a Delta Sharing Server and access the available data shares.

Download JSON Profiles

When the Share Tool creates a share object, the recipient receives an email notification with a link to download the Delta Sharing Profiles. You can also download the JSON profiles from the Database Actions console:

1. On the Provide Share page, click the three dots next to a recipient and select Copy Profile Activation Link to Clipboard.

2. Open the link in a web browser to access the Delta Profile Download page.

3. Click Get Profile Information to download a zip file containing Delta Sharing JSON profiles.

JSON Profile Types

The image illustrates the JSON profiles and shows how to consume Delta Share using bearerToken and OIDC for Databricks.

Description of the illustration adp-databricks-delta-share-oidc.eps

Delta Sharing supports the following profiles:

• Profile Version 1 using bearerToken

  This JSON file can include tokenEndpoint, clientID, and clientSecret so the token can be refreshed.

• Profile Version 2 using OIDC (PL/SQL only) for Databricks Share

  This JSON file contains OpenID Connect (OIDC) and OAuth client credentials, for example, clientId, clientSecret, and tokenEndpoint. Databricks OIDC profiles use shareCredentialsVersion 2 and type oauth_client_credentials but do not support bearerToken. Autonomous Database uses the tokenEndpoint to get access tokens with shorter validity and refreshes them automatically.

  Profile version 2 provides better security because of no long-lived bearer tokens to distribute, has easier rotation of secrets, and provides more automation.

When you subscribe to a Delta Share Provider in Consume Share, you can use JSON file version 1 or version 2, or upload the zip file directly in the Share Provider JSON field.

{
        "shareCredentialsVersion": 1, 
        "endpoint": "https://myhost.us.example.com/ords/prov/_delta_sharing/", 
        "tokenEndpoint": "http://myhost.us.example.com:1234/ords/pdbdba/oauth/token", 
        "bearerToken": "-xxxxxxxxxxxxxxxxxxxxx", 
        "expirationTime": "20XX-01-13T07:53:11.073Z", 
        "clientID": "xxxxxxxxxxxxxxxxxxxxxx..", 
        "clientSecret": "xxxxxxxxxxxxxxxxxxxx.."
}

{
        "shareCredentialsVersion": 2, 
        "endpoint": "https://myhost.us.example.com/ords/prov/_delta_sharing/", 
        "tokenEndpoint": "http://myhost.us.example.com:1234/ords/pdbdba/oauth/token", 
        "type": "oauth_client_credentials",
        "clientId": "xxxxxxxxxxxxxxxxxxxxxx..", 
        "clientSecret": "xxxxxxxxxxxxxxxxxxxx..",
        "tokenScope": "xxxxxxxxxx"
}

Note the difference in case for client IDs in both profiles. Profile 1 has clientID and Profile 2 has clientId.

Security Enhancements

As a share recipient, you must set up an access control list (ACL) to the share provider’s machine by using the APPEND_HOST_ACE procedure as an ADMIN user, or another privileged user. This allows you to access the share via the Internet.

For example, if you wish to allow a database user, A_SHARE_USER to access the endpoints on a host (Share provider) named, here is a sample of PL/SQL procedure you will need to run in the SQL worksheet editor as an admin. As a prerequisite, extract the host name from the endpoint property in the delta sharing JSON profile, as provided in the example above. The hostname from the example is myhost.us.example.com.

BEGIN 
        dbms_network_acl_admin.append_host_ace(    
        host =>'myhost.us.example.com', 
        lower_port=>443,    
        upper_port=>443,
        ace => xs$ace_type(      
        privilege_list => xs$name_list('http', 'http_proxy'),      
        principal_name =>'A_SHARE_USER',
        principal_type => xs_acl.ptype_db)); 
        COMMIT;
        END;        
/

Following are the parameters with their description:

• host- Specifies the name or the IP address of the host. The host or domain name is not case-sensitive.
• lower port- Specifies the lower port of an optional TCP port range.
• upper port- Specifies the upper port of an optional TCP port range.
• ace – The Access Control Entry.
• privilege list- Specifies the list of network privileges to be granted or denied.
• principal_name- It is the principal (database user or role) to whom the privilege is granted or denied. It is case-sensitive.
• principal_type- Specifies the type of principal you use.

Refer to the PL/SQL Packages and Types Reference document for more details on the DBMS_NETWORK_ACL_ADMIN package subprograms.

PRIV_ORDS_ACL             CONSTANT PLS_INTEGER := 8;

Upload a JSON Profile to Create a Share Provider Subscription

In this process you will load the provider’s JSON profile for configuration and credentials to enable access to the recipients.

1. Open the Consume Share page and click + Subscribe to Share Provider to select Subscribe to Delta Share Provider from the drop-down. This opens the Subscribe to Share Provider window.
   Description of the illustration subscribe-share-provider-version-providersettings.png
2. Specify the following in Provider Settings:

   • Share Source: Select Delta Share Provider JSON.

   • Share Provider JSON: You can share the JSON profile using the following options-

     From File: Select this option and click Delta Share Profile JSON to browse and upload the JSON profile on your system. Alternatively, you can also upload the zip file containing the JSON Profiles directly in the Share Provider JSON field.

     JSON: Select this option to paste the contents of a JSON file in the provided space. Ensure that you copy the entire content including the start and end curly brackets.

   • In Share Provider Details, enter a Provider Name and optionally add a Description.

   Click Next to progress to the Add Shares tab.
3. On the Add Shares tab of the dialog, you will view the list of available shares. Click the share you wish to consume from the Available Shares and select any of the available options:
   Description of the illustration subscribe-share-provider-version-addshares.png
   • >: This option enables you to move the Available Share to Selected Shares.
   • <: Select this option to remove the selected share from Selected Shares.
   • >>: This option allows you to move all the shares to the Selected Shares.
   • <<: Select this option to remove all the selected shares from Selected Shares.

4. Click Subscribe to add the share. A confirmation prompt appears when the provider is created successfully. After successful creation of the provider, you will now view the Link Cloud Object screen of the Data Load page.

5. You can view the name of the provider in the cloud storage location field. The share appears in the source file location with the files you add to the share.
   Description of the illustration link-cloud-object.png

   Expand the Share folder cart, drag and drop the file you share from the source to the Data Link cart.

   Select Start in the Data link cart to run the data link job.

6. View the created tables from Database Actions.
   • Click on Database Actions, in the breadcrumb, to go back to the Database Actions launchpad.
   • Click on the SQL tile.
   • Select the external table, drag and drop it into the worksheet.

     The SQL Select statement for the table appears. This SQL statement can be run to consume the shared data.

     
     Description of the illustration xt_sql.png

ORA-29913: error while processing ODCIEXTTABLEFETCH routine

Databricks providers can share Delta tables when Autonomous AI Database is a Delta Sharing consumer in the following scenarios:

• Scenario 1 - Drop Deletion Vectors from the table

• Scenario 2 - Create a view and share the view

• Scenario 3 - Disable Deletion Vectors in Workspace Settings for future tables

Scenario 1 - Drop Deletion Vectors from the Table

Use this if you are ready to stop using deletionVectors on a specific table.

1. Run this in Databricks.

   ALTER TABLE table_name 
   DROP FEATURE deletionVectors

2. Add the table table_name to the Delta share and configure Autonomous AI Database as a consumer.

This option applies all pending deletes and removes the deletionVectors feature from the table. The table behaves like a regular Delta table without deletionVectors.

Scenario 2 - Create a View and Share the View

Use this when you want to keep deletionVectors on the base table.

1. Run this in Databricks.

   CREATE VIEW view_name AS 
   SELECT * FROM table_name;

2. Add the view view_name to the Delta share instead of the table.

   Autonomous AI Database consumers can run query this:

   SELECT * 
   FROM "MY_SHARE"."MY_SCHEMA"."view_name";

In this scenario, the Databricks base table keeps using Deletion Vectors. Databricks resolves Deletion Vectors when running the view, so Autonomous AI Database does not see them. For non-Databricks recipients, such as Autonomous AI Database, Databricks may materialize the view and charge the provider for compute and storage. The provider can configure a materialization TTL in Databricks to control the balance between cost and data refresh, a shorter or lower TTL for new data or a longer or higher TTL for cost effective.

Scenario 3 - Disable Deletion Vectors in Workspace Settings for Future Tables

Use this to prevent new tables from using Deletion Vectors by default.

1. Open Workspace settings or the Admin console as the Databricks admin user and go to Advanced.

2. Find Auto-Enable Deletion Vectors and set it to Disabled.

3. Save the changes and restart the running compute.

ALTER TABLE table_name 
SET TBLPROPERTIES ('delta.enableDeletionVectors' = FALSE);

In this scenario, all new tables created after this change will not have Deletion Vectors enabled by default. However, the existing tables that already use Deletion Vectors would require the scenarios explained earlier.

Verify if Deletion Vectors are Enabled in Databricks

To check whether Deletion Vectors are enabled for a specific table in Databricks, run this command:

SHOW TBLPROPERTIES table_name ('delta.enableDeletionVectors');