1. Service Cloud REST APIREST API for Oracle B2C Service
  2. Use Cases
  3. Perform Bulk Extract

Perform Bulk Extract

In Connect Common Object Model (CCOM) v1.4, you can extract large volumes of data using the bulk extract feature. You can use the Bulk Extract API to retrieve Incident object records from Oracle B2C Service.

This topic contains the following sections:

Architecture of Bulk Extract API

A bulk extract process creates a bulkExtracts object using the HTTP POST operation. The process is triggered based on the following conditions:

  • If the bulkExtracts object is created with Run status, the bulk extract process is triggered immediately.
  • If the bulkExtracts object is created with Pause status, the bulk extract process will not be triggered until the status is updated to Run.

Only one extract job can have Run status at any given time per site. The extract job builds one or more tabular queries. The data collected from the query is formatted, compressed in gzip format, stored on the File Attachment Server, and added to the bulkExtractResults object's exportedData property. Once the query has extracted all the data, the status of the bulkExtractResults object changes from Running to Completed.

Bulk Extract Parameters

The following table lists the parameters used for bulk extract.

Parameter Description Default Value
PAPI_BULK_EXTRACT_ENABLED Indicates whether the Bulk Extract API is enabled. Default is 1 (enabled). 1
PAPI_BULK_EXTRACT_INTERVAL_ROWS Defines the maximum number of rows that individual data extract files may contain. The total number of rows that can be extracted for each bulk extract request is defined in PAPI_BULK_EXTRACT_MAX_ROWS. Minimum is 1,000; maximum is 500,000. 200000
PAPI_BULK_EXTRACT_MAX_ROWS Defines the maximum number of rows extracted for each bulk extract request. Minimum is 2,000,000; maximum is 5,000,000. 2000000
PAPI_BULK_EXTRACT_PURGE_DAYS Specifies the number of days after which the extract data results are deleted from the server. Minimum is 3; maximum is 10.

Note:

Purging deletes all the data extract files from the system.
3

Examples of Bulk Extract

Example 1: Initiate bulk extract for later processing

Use POST with the following URI to create a new bulkExtracts object with Pause status:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtracts

Request body example:

{
 "class": "Incident",
 "name": "ExtractExample",
 "state": {
   "lookupName":"Pause"
   },
 "version": "v1.4"
}

Use PATCH with the following URI to update the bulkExtracts object, created in the request above, and change the status from Pause to Run, thus triggering the extract process:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtracts/2

Request body example:

{
 "state": {
   "lookupName":"Run"
   }
}

Example 2: Initiate bulk extract for immediate processing

Use POST with the following URI to create a new bulkExtracts object with Run status:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtracts

Request body example:

{
 "class": "Incident",
 "name": "ExtractExample",
 "state": {
   "lookupName":"Run"
   },
 "version": "v1.4"
}

Example 3: Request a list of BulkExtractResults associated with a particular BulkExtract object

Use GET with the following URI using the BulkExtract ID you wish to query for: 

https://mysite.example.com/services/rest/connect/v1.4/bulkExtractResults?q=bulkExtract.ID=1

Response Body Example:

{
    "items": [
        {
            "id": 1,
            "lookupName": "1",
            "createdTime": "2019-01-12T05:44:06.000Z",
            "updatedTime": "2019-01-12T05:44:06.000Z",
            "links": [
                {
                    "rel": "canonical",
                    "href": "https://mysite.example.com/services/rest/connect/v1.4/bulkExtractResults/1"
                }
            ]
        }
    ],
    "hasMore": false,
    "links": [
        {
            "rel": "canonical",
            "href": "https://mysite.example.com/rest/connect/v1.4/bulkExtractResults"
        },
        {
            "rel": "describedby",
            "href": "https://mysite.example.com/rest/connect/v1.4/metadata-catalog/bulkExtractResults",
            "mediaType": "application/schema+json"
        },
        {
            "rel": "search-form",
            "href": "https://mysite.example.com/rest/connect/v1.4/bulkExtractResults-search-form"
        },
        {
            "rel": "self",
            "href": "https://mysite.example.com/rest/connect/v1.4/bulkExtractResults?q=bulkExtract.ID=1"
        }
    ]
}

Track Status of Bulk Extract Process

Use GET with the following URI to track the status of the bulk extract process. For example:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtractResults/1/state

Response body example:

{
    "state": {
        "id": 3,
        "lookupName": "Completed"
    }
}

Download the Extracted Data

You can download the extracted data after the status of the bulkExtractResults object changes to Completed. The extracted data is written to CSV files, and the bulkExtractResults object's exportedData property contains a download URL to the extracted files.

The download URL has the following syntax:

https://<server>/services/rest/connect/latest/bulkExtractResults/<bulkExtractResultsId>/exportedData?download

For example:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtractResults/1/exportedData?download

When you retrieve all incident records, the compressed file contains a CSV file such as Interval.0001.Metadata.csv. This CSV file lists the names of the files you can refer, for example Interval_00001.Incident_tables_md.csv. This CSV file provides the list of generated CSV files. It also provides a mapping of each CSV file name with its corresponding record name.

Examples of Filtering

You can use filtering to limit the amount of data that is returned and displayed in your REST client. These examples describe some of the filtering operations that you can perform on the Bulk Extract resource.

Example 1: Extract incidents that have ID greater than 10000 and are assigned to accounts of Staff Group 2

Use POST with the following syntax to create a bulkExtract object:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtracts

Request body example:

{
  "class": "Incident",
  "name": "bulkExtract",
  "state": {
    "lookupName": "Run"
  },
  "version": "v1.4",
  "filters": [
  {
      "leftOperand": "ID",
      "filterOperator": {
          "lookupName": ">"
        },
      "rightOperand": "10000"
  },
  {
	"leftOperand":"AssignedTo.StaffGroup",
	"filterOperator": {
            "lookupName":"="
       },
	"rightOperand":"2"
    }
    ]
}

Example 2: Extract incidents created after May 1, 2018

Use POST with the following syntax to create a bulkExtract object:

https://mysite.example.com/services/rest/connect/v1.4/bulkExtracts

Request body example:

{
  "class": "Incident",
  "name": "bulkExtract",
  "state": {
    "lookupName": "Run"
  },
  "version": "v1.4",
  "filters": [
  {
      "leftOperand": "CreatedTime",
      "filterOperator": {
          "lookupName": ">"
        },
      "rightOperand": "'2018-05-01T00:00:00.000Z'"
  }
    ]
  }

Limitations of Bulk Extract

The bulk extract filter restricts filtering to indexed columns on the top-level table. It allows filtering with indexed columns on top-level object, and does not allow filtering non-indexed columns on top-level object or non-top-level object's properties.

The bulk extract process can fetch a maximum of 100K records per request. There may be performance implications, as this is an asynchronous API request and a resource expensive operation.