Getting started with Oracle Data Sharing, powered by Oracle Autonomous Database

Welcome to Oracle Data Sharing. Whether you are new to delta sharing or are familiar with this technology, this short Quick Start Guide will familiarize you with its background and benefits. It will also guide you on using Oracle Autonomous Database as a Share Provider and Share Recipient and Oracle's implementation of the Delta Sharing protocol-based open API.

 

Suppose you are already familiar with and are interested only in specific tasks and actions. In that case, you can skip to your particular topic of interest using the table of contents below.

 

If this is your first time to use Oracle Database Data Sharing, then we encourage you to read the Release Notes for Limited Availability, which lists current limitations and known issues. Note also that Data Sharing is supported on Autonomous Database Shared on paid instances at this point in time.

 

 

Put your shared data to work!

 

Table of Contents

General Information

What is Data Sharing?

Data sharing is making the same data available to one or many consumers. Nowadays, the ever-growing amount of data has become a strategic asset for any company. Sharing data - within your organization or externally - is an enabling technology for new business opportunities. Sharing data and consuming data from external sources allows to collaborate with partners, establish new partnerships and generate new revenue streams with data monetization.

Why Sharing Data?

Data Sharing allows customers to improve their business and open up new opportunities. 

Data Sharing scenarios:

• One organization shares data across LOBs.
  For example, an HR department can share statistical employee data with the Financial department for cost analysis.

• Business to Business (B2B)
  For example, a retailer shares regional sales and inventory data with a supplier to help improve the supplier's production line and locations.

• Business to Customers (B2C)
  For example, a restaurant shares historical time-based pattern of busyness to allow customers to plan for their reservations.

Traditional ways to share data

Sharing data is nothing new and users did this before. Some examples of how users used to do data sharing in the past are:

• Send data via email
• Share data through an FTP server
• Use application-specific APIs for data extraction
• Leverage vendor specific tools to copy relevant data

While these methods work in general, they come with certain drawbacks:

• Managing separate processes for data extraction, preparation or remote access are labor-intensive.
• Extracting and duplicating data is prone to data staleness.
• Architectures and processes can become very difficult to maintain and hard to scale.
• Redundant data extraction can introduce format compatibility issues.
• Data extraction and preparation must handle sensitive information properly.

The modern way of sharing data

Modern data sharing must be open, secure, real-time, vendor-agnostic, and avoid the pitfalls of extracting and duplicating data for individual consumers of data in a collaborative environment. Delta Sharing is an open protocol for secure real-time data exchange of large datasets that satisfies all these criteria, supported by multiple clients and program languages, and vendor agnostic.

The following graph gives an overview of the open eco system as of December 2022 that supports delta sharing:

The open Delta Sharing protocol is aimed to solve the following problems:

• Share data without copying it to another system
• Producer controls the state of data (version of data)
• Be an open cross-platform solution
• Support a wide range of clients such as Power BI, Tableau, Apache Spark, pandas and Java, and
• Provide flexibility to consume data using the tools of choice for BI, machine learning and AI use cases
• Provide strong security, auditing, and governance
• Scale to massive data sets

Modern data sharing – how it works

At the high level, when a user consumes data made available through delta sharing protocol, the following happens:

• The Delta Sharing Client issues a request to Delta Sharing Server and provides a bearer token.
• If the token is valid, the Delta Sharing Server authenticates the user and provides pre-authenticated links for the Delta Sharing Client.
• The Delta Sharing Client directly accesses the shared data - files in the object store – that correspond to the shared data, e.g., a table.

 

Using Oracle as Data Provider

Providing access to shared data is a two-step process, by which you define data shares and recipients independently of each other. A single data share can consist of multiple shared objects that are shared with one or multiple recipients. Decoupling the share creation and publication from managing its recipients (consumers of data) allows for greatest flexibility.

 

Create and publish a data share: To share data with other Autonomous Databases or Delta Share Clients requires several simple steps. The high-level flow of necessary steps are as follows:

 

1.     Create Cloud Storage
Define the object storage location where Oracle Database Sharing will persist the files representing your data share.

 

2.     Create Data Share
A data share is the logical unit of sharing that consists of one or many objects you want to share, such as tables and views. A Data Share is either a versioned or current data share.

 

3.     Add Tables to a Data Share
At this step, you add objects that you want to make available to share.

 

4.     Publish share
After creating your data share with one or multiple data objects to share you need to publish those, making the data available for recipients.

 

Grant access to a published data share: After a data share itself is created, you need to associate it with the recipient. The high-level flow of necessary steps are as follows:

 

 

 

1.     Create a recipient
A recipient defines an individual consumer that you’re going to give access to one or multiple data shares.

 

2.     Grant access to one or multiple data shares to a recipient.

 

3.     Create an activation link
To start data consumption, a Data Share recipient must have an authentication profile to access data share objects.

 

Produce Data Shares using Database Actions

Note: Support for Data Sharing is not automatically enabled in Database Actions in Limited Availability. To enable your environment to use the new UI in Database Actions you must create a role called ADPBETA and grant this role to any database user that wants to use the new UI.

 

As user ADMIN, issue the following command.

 

Create role adpbeta;

 

To share data with others Autonomous database users your administrator must grant you the privileges to create and publish shares. To grant the privileges to a database user, issue the following command as ADMIN:

 

exec dbms_share.enable_schema('<database user>');

exec ords_admin.enable_schema(true, '<database user>');

grant adpbeta to <database user>;

 

Any user that wants to use Database Actions furthermore needs to be granted role DWROLE.

 

grant dwrole to <database user>;

 

Note: if you are using the UI in Database Actions to grant the role DWROLE you must ensure to select this role as a default role for the user. Without this, Database Actions and therefore Data Share will not function properly.

 

 

1.     Open Database Actions

You can navigate to Database Actions from the OCI Console or using the direct link to Database Actions if it’s known to you. Go to the Data Share menu in Database Actions of your Autonomous Database:

 

 

2.     If this is your first time to use Oracle Data Sharing you will be prompted and asked whether you want to create a new share. If this is not your first time you can create a share either through ‘Provide Share’, either by selecting it on the main screen or from the left-hand side menu.

 

 

A Data Share is the logical unit of sharing that consists of one or multiple objects – e.g. tables or views – that you are going to share.

 

 

Name your share and add a description of your share. While the description is optional it is recommended to briefly describe the purpose of a share.

 

 

3.     Add tables to a share.

 

You can add one or multiple tables to your share. The numbers on the right hand of the table name showing number of rows (relying on statistic gathering).

 

 

Create or choose cloud storage, where shared date will be stored. The cloud storage is a bucket in the object store in your tenancy that can be used for read and write. The cloud storage must be defined with native Oracle OCI authentication.

 

Oracle Database Data Sharing allows you to share data as versioned and current objects. Versioned data shares objects will always be persisted in the object store whereas current objects might be cached for performance reasons at runtime.

 

 

 

4.     Choose type of the share. There are two types available – versioned data (snapshots) and the current data.

At this point you could create your data share, or create and publish your data share already. However, you have not defined whom you want to share the data with, so click next to define a recipient.

 

 

5.     Add a recipient, who supposed to read these shares

 

 

6.     Create share and make it available for named recipients (publish)

 

      First you need to either select one of any existing recipients or create a new one.

 

 

      You now have defined everything that is needed for a Data Recipient to use your data share. All that is needed now is to create and publish it.

 

 

7.     To notify recipients, click on hamburger menu and choose recipients.

 

Recipients (consumers) of a data share do not have to be other Oracle Autonomous Databases, but could be any sort of Delta Sharing Client, such as a Python program. Since you cannot assume that a direct interaction or communication between a Data Provider (Delta Sharing Server) and a Data Consumer.

 

 

 

 

After selecting your recipients, pick the most convenient way to share the authorization profile (either coping a link or sending an email).

 

 

If you want to add additional recipients then you can do so with ‘Manage Recipients’ under ‘Provide Shares’.

 

 

Using Oracle as Data Recipient

Data shared with you through Delta Sharing is not automatically available and discoverable in your Autonomous Database. As of today, the concept of a recipient is designed to be opaque and not tied to a specific recipient and could be anyone or anything that supports the delta share protocol. Consequently it requires several simple steps to make the data available for you within Oracle. The high-level flow of necessary steps are as follows:

1.     Download your share profile.
Profile files are JSON files containing a user's credentials to access a Delta Sharing Server. This enables you to authenticate yourself with the delta sharing server and to expose the data shares you are eligible to access.

2.     Create a share provider.
Using the share profile you create the delta sharing server as data provider in your database.

3.     Register shares made available to you.
After successful authentication with the delta sharing server you can select individual or all available shares and register them as visible data objects.

4.     Create external tables on top of your shares for SQL access.

The following will guide you through these steps in more detail.

Download your profile file

The Data Provider is responsible to inform any Data Recipient with the information to access a data share. This can be done via an automated email, or by manually sharing the information where to download the profile. Using the URL provided to you, download your profile file. Most commonly the URL is provided to you in your welcome email from the data provider. Your download page will look like the following and enables you to download your profile file, a JSON document containing all relevant information for you.

 

The information in the profile file provides the authentication with a delta sharing server of a data provider.

 

 

Consume shared data using Database Actions

Note: Support for Data Sharing is not automatically enabled in Database Actions throughout Limited Availability. To enable your environment to use the new UI in Database Actions you must create a role called ADPBETA and grant this role to any database user that wants to use the new UI.

 

 

To consume a share no special privileges must be granted to a database user for data sharing. It is recommended to start off with the standard developer privileges :

 

grant create session to <database user>;

grant dwrole to <database user>;

grant adpbeta to <database user>;

 

0.     Open Database Actions.

You can navigate to Database Actions from the OCI Console or using the direct link to Database Actions if it’s known to you. Go to the Data Share menu in Database Actions of your Autonomous Database:

1.     Add your profile file as a Share Provider.

Open the detail menu of Data Share, choose ‘Consume Share’ and add your profile file as Share Provider. This will permanently store your profile file in your autonomous database:

Note: Please ensure that you have set up the ACL for the Data Provider’s machine before continuing. See the Data Sharing Limited Availability Release Notes for more information.

On the next screen, put the content of your profile file into a provider details section:

3.     Register Shares made available to you.

In the next menu pick the shares you are interested in and click add:

4.     Create external tables on top of your shares for SQL access.

Expland the share to see all tables that are shared with you. Choose the tables you want to permanantly associate with your autonomous database. After you have chosen the tables of interest, hit start to create these table permanantly as external tables in your autonomous database.

You can now work with the external tables you created on top of the delta shares, just like you do with any other internal or external table. Enjoy SQL-ing your data

  

Additional information and useful links

For internal LA, please subscribe to slack channel #datashare-la. The channel is monitored by Development and Product Management and your first channel to ask questions and to communicate any findings.

Delta Sharing, the open protocol for secure data sharing