ORACLE_BIGDATA Access Driver

原创博 2022-08-04

437

With the ORACLE_BIGDATA access driver, you can access data stored in object stores as if that data was stored in tables in an Oracle Database.

ORACLE_BIGDATA currently supports access to Oracle Object Store, Amazon S3, and Azure Blob Storage. You can also use this driver to query local data, which is useful for testing and smaller data sets.

Using the ORACLE_BIGDATA Access Driver
You can use the ORACLE_BIGDATA driver to access data located in external object stores.
How to Create a Credential for Object Stores
Credential objects enable you to access an external object store.
Object Store Access Parameters
You can use these access parameters to specify properties about the files residing in object stores.
Related Topics

External Tables Examples
Parent topic: External Tables

18.1 Using the ORACLE_BIGDATA Access Driver
You can use the ORACLE_BIGDATA driver to access data located in external object stores.

There are two steps required to access data in an object store:

Create a credential object (not required for public buckets).
A credential object stores object store credentials in an encrypted format. The identity specified by the credential must have access to the underlying data in the object store.

In-line external tables are supported. These external tables are simply expressed as part of a query.
Create an external table or query using an in-line external table. The access driver type must be ORACLE_BIGDATA. The CREATE TABLE statement must reference the credential object, which provides authentication to access the object store. The table you create also requires a LOCATION clause, which provides the URI to the files within the object store.

For public buckets, the CREDENTIAL is not required.

Parent topic: ORACLE_BIGDATA Access Driver

18.2 How to Create a Credential for Object Stores
Credential objects enable you to access an external object store.

To create your credential object, use either the DBMS_CREDENTIAL.CREATE_CREDENTIAL or DBMS_CLOUD.CREATE_CREDENTIAL. This object contains the username and password information needed to access the object store. This credential password must match the authentication token (auth token) created for the username in your cloud service.

Note:You must have the DBMS_CLOUD package installed.
Creating the Credential Object with DBMS_CREDENTIAL.CREATE_CREDENTIAL
The DBMS_CLOUD subprogram DBMS_CREDENTIAL.CREATE_CREDENTIAL enables you to authenticate access to an external object store.
Creating the Credential Object with DBMS_CLOUD.CREATE_CREDENTIAL
The DBMS_CLOUD subprogram DBMS_CLOUD.CREATE_CREDENTIAL enables you to authenticate access to an external object store.
How to Define the Location Clause for Object Storage
Use these examples to see how you can specify the object store URI, depending on its source.
Understanding ORACLE_BIGDATA Access Parameters
To use ORACLE_BIGDATA, you provide information in an access parameter to indicate how to access and parse the data.
Related Topics

My Oracle Support Note 2748362.1
Parent topic: ORACLE_BIGDATA Access Driver

18.2.1 Creating the Credential Object with DBMS_CREDENTIAL.CREATE_CREDENTIAL
The DBMS_CLOUD subprogram DBMS_CREDENTIAL.CREATE_CREDENTIAL enables you to authenticate access to an external object store.

These examples show how to use DBMS_CREDENTIAL.CREATE_CREDENTIAL.

Example 18-1 Cloud Service Credentials

In the following example, my_credential is the Oracle Cloud Infrastructure user name, username is the account username, password is the Oracle Cloud Infrastructure auth token:

execute dbms_credential.create_credential(
credential_name => ‘my_credential’,
username => ‘username’,
password => ‘password’
);
Example 18-2 Native Oracle Cloud Infrastructure Credentials

In the following example, my_credential is the Oracle Cloud Infrastructure user name, user_ocid is the Oracle Cloud Identifier (OCID), tenancy_ocid is the Oracle Cloud tenancy identifier, private_key is the SSH private key, and fingerprint is the public key fingerprint:

execute dbms_credential.create_credential(
credential_name => ‘my_credential’,
username => ‘user_ocid’,
password => ‘’,
key => ‘{“tenancy_ocid”:“tenancy_ocid”,“private_key”:“private_key”,“fingerprint”:“fingerprint”}’);
After you create the credential, specify the credential object name in the parameter com.oracle.bigdata.credential.name, At the time of this release, the credential must be in the same schema as the table

Related Topics

CREATE_CREDENTIAL Procedure
Parent topic: How to Create a Credential for Object Stores

18.2.3 How to Define the Location Clause for Object Storage
Use these examples to see how you can specify the object store URI, depending on its source.

LOCATION is a URI pointing to data in the object store. Currently supported object stores are Oracle Object Store, Amazon S3 and Azure Blob Storage.

In the examples, the following variables are used:

region – tenancy region
host – a server host name
port – a port number assigned to the service, listening on a host
container – name of a container resource
namespace – namespace in a region
bucket – a globally unique name for a resource
objectname – a unique identifier for an object in a bucket
filename – object store filename
Note the following prerequisites for defining the location:

The credential object is required for private object store access. If the credential parameter is omitted, then the object must be in a public bucket.

The user ID associated with this credential must have access to read the data from object storage.

If you are testing access for data in object storage using local storage, then you must specify an Oracle directory object in the location, similar to what you do for ORACLE_LOADER data sources.

Example 18-4 Native Oracle Cloud Infrastructure Object Storage

location (‘https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/objectname’)
Example 18-5 Oracle Cloud Infrastructure Object Storage

location (‘https://swiftobjectstorage.region.oraclecloud.com/v1/namespace/bucket/filename’
Example 18-6 Hosted-Style URI format

location (‘https://bucket.host/objectname’)
Example 18-7 Path-style URI Format

location (‘https://host/bucket/objectname’)
For example, an Amazon path style URI can take the following format:

location (‘https://s3-us-west-2.amazonaws.com/adwc/filename’)
Example 18-8 Azure BLOB Storage Location Format

location (‘https://host:port/container/blob’)
For example, an Azure path style URI can take the following format:

location (‘https://exampleacount.blob.core.windows.net/examplecontainer/exampleblob’)
Parent topic: How to Create a Credential for Object Stores

18.2.4 Understanding ORACLE_BIGDATA Access Parameters
To use ORACLE_BIGDATA, you provide information in an access parameter to indicate how to access and parse the data.

To access the external object store, you define the file format type in the access parameter com.oracle.bigdata.fileformat, using one of the following values: csv, textfile, avro, parquet, or orc:
com.oracle.bigdata.fileformat=[csv|textfile|avro|parquet|orc]
You can also use ORACLE_BIGDATA to access local files for testing, or for simple querying. In this case, the LOCATION field value is the same as what you would use for ORACLE_LOADER. You can use an Oracle directory object followed by the name of the file in the LOCATION field. For local files, a credential object is not required. However, you must have privileges over on the directory object in order to access the file.

ORACLE_BIGDATA Access Driver

评论