1. Utilities
  2. External Tables
  3. ORACLE_BIGDATA Access Driver
  4. ORACLE_BIGDATA Accessing Delta Sharing

17.6 ORACLE_BIGDATA Accessing Delta Sharing

See how to use the Linux Foundation Projects Delta Sharing data sharing protocol with the Oracle Big Data Access Driver

Parent topic: ORACLE_BIGDATA Access Driver

17.6.1 Delta Sharing Protocol Overview

The ORACLE_BIGDATA Access Driver now supports the Delta Sharing open protocol.

The ORACLE_BIGDATA Access Driver supports the Linux Open Project Delta Sharing protocol, which is an open protocol based on Parquet that provides secure and scalable real-time sharing of large datasets.

Parent topic: ORACLE_BIGDATA Accessing Delta Sharing

17.6.2 Supported Configurations for Delta Sharing Protocol

Oracle supports catalog-based and non-catalog based data, and supports Parquet data formats with the Apache Iceberg table format

Access Protocol: delta-sharing This protocol is required for accessing Delta Share datasets.

Credential Types
  • Bearer Token: This credential type is suitable for temporary access. It requires an explicit token refresh.
  • Client ID/Secret: This credential type enables automatic token refresh. Oracle recommends that you use this type for long-term access to Oracle-managed Delta Share servers.

Parent topic: ORACLE_BIGDATA Accessing Delta Sharing

17.6.3 Creating Credentials for Delta Sharing

Use these examples to see how you can create Bearer Token credential objects or client ID/Secret credential objects to access Delta Sharing.

Example 17-20 Creating Bearer Token Credentials for Delta Sharing

BEGIN
    DBMS_CREDENTIAL.CREATE_CREDENTIAL (
        credential_name => 'DATABRICKS',
        username        => 'BEARER_TOKEN',
        password        => 'faaie590d541265bcab1f2de9813274bf233'
    );
END;

Example 17-21 Creating Client ID/Secret Credentials for Oracle-Managed Delta Sharing 

BEGIN
    DBMS_CREDENTIAL.CREATE_CREDENTIAL (
        credential_name => 'DRIVER_CLIENT_ID',
        username        => '_GEVd3cSVYYJtZ...68Q0VINQ..',   -- client ID
        password        => 'IV3gncgr0p6Mk...1WwmQ2uUg..'   -- client secret
    );
END;

Parent topic: ORACLE_BIGDATA Accessing Delta Sharing

17.6.4 Listing and Describing Delta Share Metadata

See how to use the LIST file format to list and the DESC file format to describe delta shares, schemas, and tables.

Example 17-22 Listing Delta Share Content

You can list the content of delta shares, schemas, and tables by using the LIST file format. The location path determines the level of detail retrieved. For example: 

SELECT DISTINCT url
FROM EXTERNAL (
     ( url VARCHAR2(200) )
     TYPE ORACLE_BIGDATA
     ACCESS PARAMETERS (
          com.oracle.bigdata.fileformat = list,
          com.oracle.bigdata.credential.name = DATABRICKS,
          com.oracle.bigdata.access_protocol = delta_sharing
     )
     LOCATION (
          'https://sharing.delta.io/delta-sharing/#',                          -- Shares
          'https://sharing.delta.io/delta-sharing/#delta_sharing',             -- Share schemas
          'https://sharing.delta.io/delta-sharing/#delta_sharing.default'      -- Share tables
     )
     REJECT LIMIT UNLIMITED
);

URL
--------------------------------------------------------------------------------
https://sharing.delta.io/delta-sharing/#delta_sharing.default.COVID_19_NYT
https://sharing.delta.io/delta-sharing/#delta_sharing.default.boston-housing
https://sharing.delta.io/delta-sharing/#delta_sharing.default.flight-asa_2008
https://sharing.delta.io/delta-sharing/#delta_sharing.default.lending_club
https://sharing.delta.io/delta-sharing/#delta_sharing.default.nyctaxi_2019
https://sharing.delta.io/delta-sharing/#delta_sharing.default.nyctaxi_2019_part
https://sharing.delta.io/delta-sharing/#delta_sharing.default.owid-covid-data
https://sharing.delta.io/delta-sharing/#delta_sharing.default
https://sharing.delta.io/delta-sharing/#delta_sharing

Example 17-23 Describing Delta Share Tables

To retrieve column definitions for a specific delta share table, use the DESC file format. For example: 

SELECT *
FROM EXTERNAL (
    (
        "path"       VARCHAR2(4000 BYTE),
        "oratype"    VARCHAR2(40 BYTE),
        scale         NUMBER,
        precision     NUMBER,
        filetype      VARCHAR2(400),
        compression   VARCHAR2(400),
        "partoflist" NUMBER(1),
        "depth"      NUMBER(19)
    )
    TYPE ORACLE_BIGDATA
    ACCESS PARAMETERS (
        com.oracle.bigdata.credential.name = 'DATABRICKS',
        com.oracle.bigdata.fileformat = desc,
        com.oracle.bigdata.access_protocol = delta_sharing
    )
    LOCATION (
        'https://sharing.delta.io/delta-sharing/#DELTA_SHARING.DEFAULT.BOSTON-HOUSING'
    )
    REJECT LIMIT UNLIMITED
)
ORDER BY "path";

path       oratype          SCALE    PRECISION    FILETYPE    COMPRESSION    partoflist    depth   
ID         NUMBER(10)              0           10 Parquet     snappy                              1
age        BINARY_DOUBLE           0           15 Parquet     snappy                              1
black      BINARY_DOUBLE           0           15 Parquet     snappy                              1
chas       NUMBER(10)              0           10 Parquet     snappy                              1
crim       BINARY_DOUBLE           0           15 Parquet     snappy                              1
dis        BINARY_DOUBLE           0           15 Parquet     snappy                              1
indus      BINARY_DOUBLE           0           15 Parquet     snappy                              1
lstat      BINARY_DOUBLE           0           15 Parquet     snappy                              1
medv       BINARY_DOUBLE           0           15 Parquet     snappy                              1
nox        BINARY_DOUBLE           0           15 Parquet     snappy                              1
ptratio    BINARY_DOUBLE           0           15 Parquet     snappy                              1
rad        NUMBER(10)              0           10 Parquet     snappy                              1
rm         BINARY_DOUBLE           0           15 Parquet     snappy                              1
tax        NUMBER(10)              0           10 Parquet     snappy                              1
zn         BINARY_DOUBLE           0           15 Parquet     snappy                              1

Parent topic: ORACLE_BIGDATA Accessing Delta Sharing

17.6.5 Delta Sharing Access Parameters

Oracle supports ORACLE_BIGDATA access parameters that define Delta Sharing access.

Table 17-7 Iceberg-Specific Access Parameters

Parameter Description Mandatory

com.oracle.bigdata.delta_sharing.token_endpoint

Token endpoint as defined in the JSON profile file.

Required for client ID/secret credentials.

.

Optional

com.oracle.bigdata.access_protocol

Value: delta-sharing

Protocol for Delta shares.

Yes

com.oracle.bigdata.fileformat

Value: parquet

Used when accessing Delta Share tables. This parameter is optional, and the default for Delta Share access.

Value: list

Used to derive metadata of Delta shares, share schemas, and share tables

Value: desc

Used to obtain the column definitions of a Delta Share.

Optional

Parent topic: ORACLE_BIGDATA Accessing Delta Sharing

17.6.6 Examples of Creating External Tables for Delta Sharing

The following examples demonstrate how to create tables using Bearer Token credentials and using client ID/Secret credentials.

Example 17-24 Using Bearer Token Credential

In this example, we create an external table referencing a known Delta Share table:

CREATE TABLE BOSTONHOUSING (
    "ID"        NUMBER(10,0),
    "crim"      BINARY_DOUBLE,
    "zn"        BINARY_DOUBLE,
    "indus"     BINARY_DOUBLE,
    "chas"      NUMBER(10,0),
    "nox"       BINARY_DOUBLE,
    "rm"        BINARY_DOUBLE,
    "age"       BINARY_DOUBLE,
    "dis"       BINARY_DOUBLE,
    "rad"       NUMBER(10,0),
    "tax"       NUMBER(10,0),
    "ptratio"   BINARY_DOUBLE,
    "black"     BINARY_DOUBLE,
    "lstat"     BINARY_DOUBLE,
    "medv"      BINARY_DOUBLE
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_BIGDATA
    DEFAULT DIRECTORY "DATA_PUMP_DIR"
    ACCESS PARAMETERS (
        com.oracle.bigdata.credential.name = 'DATABRICKS',
        com.oracle.bigdata.fileformat = parquet,
        com.oracle.bigdata.access_protocol = delta_sharing
    )
    LOCATION (
        'https://sharing.delta.io/delta-sharing/#DELTA_SHARING.DEFAULT.BOSTON-HOUSING'
    )
)
REJECT LIMIT UNLIMITED
PARALLEL;
Alternatively, user can create table with dbms_cloud package:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE
  ( TABLE_NAME        => 'BOSTONHOUSING'
   ,CREDENTIAL_NAME   => 'DATABRICKS'
   ,FILE_URI_LIST     => 'https://sharing.delta.io/delta-sharing/#DELTA_SHARING.DEFAULT.BOSTON-HOUSING'
   ,COLUMN_LIST       => '"ID"       NUMBER(10)
    ,"crim"     BINARY_DOUBLE
    ,"zn"       BINARY_DOUBLE
    ,"indus"    BINARY_DOUBLE
    ,"chas"     NUMBER(10)
    ,"nox"      BINARY_DOUBLE
    ,"rm"       BINARY_DOUBLE
    ,"age"      BINARY_DOUBLE
    ,"dis"      BINARY_DOUBLE
    ,"rad"      NUMBER(10)
    ,"tax"      NUMBER(10)
    ,"ptratio"  BINARY_DOUBLE
    ,"black"    BINARY_DOUBLE
    ,"lstat"    BINARY_DOUBLE
    ,"medv"     BINARY_DOUBLE'
   ,FORMAT            => '{
       "type" : "parquet",
       "access_protocol" : "delta_sharing"
     }'
  );
END;

Example 17-25 Using Client ID/Secret Credentials

For Oracle-managed Delta Share tables, include the token endpoint for automatic token refresh. For example:

CREATE TABLE DRIVER_REFRESH (
    "DRIVER_ID" INTEGER,
    "NAME"      VARCHAR2(4000 BYTE),
    "POINTS"    INTEGER,
    "TEAM_ID"   INTEGER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_BIGDATA
    DEFAULT DIRECTORY "DATA_PUMP_DIR"
    ACCESS PARAMETERS (
        com.oracle.bigdata.credential.name = 'DRIVER_CLIENT_ID',
        com.oracle.bigdata.delta_sharing.token_endpoint = 'https://abcde...xyz.adb.ap-tokyo-1.oraclecloudapps.com/ords/jason/oauth/token',
        com.oracle.bigdata.fileformat = parquet,
        com.oracle.bigdata.access_protocol = delta_sharing
    )
    LOCATION (
        'https://abcde...xyz.adb.ap-tokyo-1.oraclecloudapps.com/ords/jason/_delta_sharing#DELTA23AIPROD.JASON.DRIVER'
    )
)
REJECT LIMIT UNLIMITED
PARALLEL;

Parent topic: ORACLE_BIGDATA Accessing Delta Sharing