The DBMS_CLOUD_PIPELINE package provides procedures for creating, configuring, testing, and starting a pipeline. The pipeline lifecycle and procedures are the same for both load and export pipelines.

For either pipeline type you perform the following steps to create and use a pipeline:

1. Create and configure the pipeline. See Create and Configure Pipelines for more information.

2. Test a new pipeline. See Test Pipelines for more information.

3. Start a pipeline. See Start a Pipeline for more information.

In addition, you can monitor, stop, or drop pipelines:

• While a pipeline is running, either during testing or during regular use after you start the pipeline, you can monitor the pipeline. See Monitor and Troubleshoot Pipelines for more information.

• You can stop a pipeline and later start it again, or drop a pipeline when you are finished using the pipeline. See Stop a Pipeline and Drop a Pipeline for more information.

A load pipeline operates as follows (some of these features are configurable using pipeline attributes):

• Object store files are loaded in parallel into a database table.

  • A load Pipeline uses the object store file name to uniquely identify and load newer files.
  • Once a file in object store has been loaded in the database table, if the file content changes in object store, it will not be loaded again.
  • If the object store file is deleted, it does not impact the data in the database table.

• If failures are encountered, a load pipeline automatically retries the operation. Retries are attempted on every subsequent run of the pipeline's scheduled job.

• In cases where the data in a file does not comply with the database table, it is marked as FAILED and can be reviewed to debug and troubleshoot the issue.

  • If any file fails to load, the pipeline does not stop and continues to load the other files.

• Load pipelines support multiple input file formats, including: JSON, CSV, XML, Avro, ORC, and Parquet.

Migration from non-Oracle databases is one possible use case for a load pipeline. When you need to migrate your data from a non-Oracle database to Oracle Autonomous Database on Dedicated Exadata Infrastructure, you can extract the data and load it into Autonomous Database (Oracle Data Pump format cannot be used for migrations from non-Oracle databases). By using a generic file format such as CSV to export data from a non-Oracle database, you can save your data to files and upload the files to object store. Next, create a pipeline to load the data to Autonomous Database. Using a load pipeline to load a large set of CSV files provides important benefits such as fault tolerance, and resume and retry operations. For a migration with a large data set you can create multiple pipelines, one per table for the non-Oracle database files, to load data into Autonomous Database.

Use an export pipeline for continuous incremental export of data from the database to object store. An export pipeline periodically identifies candidate data and uploads the data to object store.

There are three export pipeline options (the export options are configurable using pipeline attributes):

• Export incremental results of a query to object store using a date or timestamp column as key for tracking newer data.

• Export incremental data of a table to object store using a date or timestamp column as key for tracking newer data.

• Export data of a table to object store using a query to select data without a reference to a date or timestamp column (so that the pipeline exports all the data that the query selects for each scheduler run).

Export pipelines have the following features (some of these are configurable using pipeline attributes):

• Results are exported in parallel to object store.

• In case of any failures, a subsequent pipeline job repeats the export operation.

• Export pipelines support multiple export file formats, including: CSV, JSON, Parquet, or XML.