Bulk Data Operations: Update

Overview

Bulk Data Operations (BDO) let you import, update, export, and delete large batches of records (or submission) data. In this how-to guide, you'll learn how to use your environment's GraphQL sandbox to make an API APIs (application programming interfaces) are a set of protocols and definitions developers use to build and integrate application software. APIs act as the connective tissue between products and services. call and bulk update your data. You can also make request using curl, Postman, or any other service that supports POST request.

The GraphQL sandbox is not available at production-level environments. Use Postman or another API tool instead.

To learn more about accessing and authenticating your environment's GraphQL sandbox, view our How to: Use the GraphQL Sandbox article.

Uploading Files Using Bulk Data Operations

Ingestion bulk data operations accept data in the shape of a file. For the operation to run, the file needs to be in the Unqork cloud. To upload files, you must use one of the following operations: synchronous, or, asynchronous. Use the synchronous process when you have a maximum of 100 MB of data you want to upload. If you have up to 6 GB of data you want to upload, use the asynchronous process.

In the future, you can use this API to run bulk data import and bulk data update. Both are ingestion operations that let you retrieve data from an external source. This operation can be configured when you want to add, create, or insert new records and update existing records.

Synchronous File Upload

The first step to upload data synchronous is to create an upload link that represents where it'll be uploaded and stored. Once determined, you can upload your file. Remember, you cannot upload a file greater than 100 MB synchronously.

mutation {
    ads {
        applicationDataOperation {
            createFileUploadLink
        }
    }
}

3.

Click Run.

The response returns the file upload link to be used when updating your data. Below is an example response:

{
    "data": {
        "ads": {
            "applicationDataOperation": {
                "createFileUploadLink": "/files/65b7d9adaeee27a2a85a4b5e"
            }
        }
    }
}

Once the link is created, you'll use it with an API tool, like Postman, to make a multipart API request to upload the file. The call accepts the file as an argument and returns the fileKey that you'll use in your BDO operation.

Synchronous calls timeout after five minutes. If it returns a timeout error, make an asynchronous call instead. To learn more, view of the Asynchronous File Upload section of this article.

You'll need the following elements to make the call:

Below is an example of the request:

POST https://training.unqork.io/api/files/65b7d9adaeee27a2a85a4b5e

Content-Type: multipart/form-data

{
    "file" : {{absolute_path_to_local_file}}
}

Below is an example response from the request:

{
    "fileKey": "unqorkResource/85262f9e-e6f9-4b18-97b7-dec55930eff3.csv"
}

After retrieving the fileKey, navigate to the Create a BDO Update Operation section of this article.

Asynchronous File Upload

This process requires a functioning external file storage (only supports SFTP Cascading Style Sheets (CSS) is a style sheet language used for presenting how a HTML or XML document looks to end-users.) and a service set up in Services Administration. Uploading files asynchronously is an alternative process when you are unable to upload files synchronously.

To learn more about setting up services, view our Services Administration article.

This process requires the name of the service to authenticate with the SFTP server and the file path in the SFTP server. Then, the request returns a fileId that can be used to retrieve the upload details.

You'll need the following elements to make the call:

Below is an example of the request:

POST https://training.unqork.io/fbu/uapi/fileUtils/fileUpload

{
    "serviceName": "large-file-upload"
    "file": {"path": "/performance-test/my_data_2G.csv"}
}

Below is an example response from the request:

{
    "fileId": "65b7f958aeee27a2a85a4b68"
}

Once you've retrieved the fileId, you'll use it to make an API request to retrieve the file upload details. The call returns the status of the upload. Once completed, the response will include the cloudKey key and value.

Asynchronous calls have a one-hour timeout limit. When it times out, it fails with a 408 status code error. When uploading a significantly large file, you might want to adjust the timeout. To request a change to FILE_TRANSFER_API_TIMEOUT, submit a support ticket here: support.unqork.com.

You'll need the following elements to make the call:

Below is an example of the request:

GET https://training.unqork.io/fbu/uapi/fileUtils/file/65b7f958aeee27a2a85a4b68/uploadStatus

{
    "status": "scanning"
}

Below is an example response from the request:

{
    "status": "finished",
    "cloudKey": "unqorkResource/dc7c3fd0-de73-4520-b5bd-e0162dd44b71.csv"
}

After retrieving the cloudKey value, navigate to the Create a BDO Update Operation section of this article.

BDO Update Operation

Using your GraphQL Sandbox, you'll create a BDO update operation that runs immediately as long as no other BDO operations are running. This process requires the following steps:

Create a BDO Update Operation

The first step of the process is to create a BDO update operation that returns the operation ID. Then, you can use the operation ID to retrieve the operation details.

mutation CreateJob(
    $type: AdsJobType!, 
    $collection: String!, 
    $name: String!, 
    $fileLocation: String, 
    $fileFormat: AdsFormat, 
    $options: AdsApplicationDataRecordOptionsInput
) {
    ads {
        applicationDataOperation {
            createJob(
                type: $type, 
                collection: $collection, 
                name: $name, 
                fileLocation: $fileLocation, 
                fileFormat: $fileFormat, 
                options: $options
            ) {
                id
                created
                createdBy
                data
                modified
                modifiedBy
            }
        }
    }
}

{
    "collection":"Enter module ID or workflow ID here",
    // Enter the module or workflow ID that stores you data.
    "name":"Enter name here",
    // Enter a name for the request. For example, "Update 10K records".
    "fileFormat": "Enter file format here",
    // Enter the format of the file being updated. Supported file formats include CSV, JSON, ZIPWITHJSON, and ZIPWITHCSV.
    "fileLocation": "Enter fileKey or cloudKey here",
    // Enter the fileKey or cloudKey you retrieved from the synchronous or asynchronous process eariler.
    "type": "UPDATE",
    "options": {
        "update": {
            "uniqueKey": "",
            // This value is empty by default. Leaving it empty with an upsert of true will insert records by default. Entering any value in this field forces the operation to locate an existing record and slows the operation. When targeting a field inside data object, use data.{{field}}. For example, data.customerNumber.
            "upsert": true,
            // If the record can not be found, setting this value to true inserts a new record into the database.
            "metaOverride": {
                "owner": "employee@test.com",
                // The email address of a user in the environment. This field uses the environment RBAC to determine if this user has access to the submission.
                "created": "2024-01-26T00:00:00.419Z"
                // This field is optional and defaults to the current time if omitted.
            },
            "storeInData": true 
            // Enter the value of true if the incoming record should be fully added to the data object.
        }
    }
}

4.

Click Run.

Below is an example response from the request. You can locate the operation ID using the following path: data.ads.applicationDataOperation.createJob.id.

{
    "data": {
        "ads": {
            "applicationDataOperation": {
                "createJob": {
                    "id": "65b7fb98aeee27a2a85a4b69",
                    "created": 1706556312070,
                    "createdBy": "employee@unqork.com",
                    "data": {
                        "type": "UPDATE",
                        "database": "applicationData",
                        "collection": "65b3ea04954e3bbf54305c34",
                        "name": "Update 10K records",
                        "steps": [
                            {
                                "stepType": "UPDATE",
                                "status": "created",
                                "options": {
                                    "update": {
                                        "headers": [],
                                        "delimiter": ",",
                                        "uniqueKey": "data.customerNumber",
                                        "upsert": true,
                                        "metaOverride": {
                                            "form": "65b3ea04954e3bbf54305c34",
                                            "owner": "employee@test.com",
                                            "created": "2024-01-26T00:00:00.419Z",
                                            "modified": "2024-01-26T00:00:00.419Z"
                                        },
                                        "storeInData": true
                                    }
                                },
                                "files": [
                                    {
                                        "source": {
                                            "name": "13948b65-36ef-4ece-823c-dc3a6c1a7707.csv",
                                            "location": "unqorkResource/13948b65-36ef-4ece-823c-dc3a6c1a7707.csv",
                                            "format": "CSV"
                                        }
                                    }
                                ],
                                "database": "applicationData",
                                "collection": "65b3ea04954e3bbf54305c34"
                            }
                        ]
                    },
                    "modified": 1706556312070,
                    "modifiedBy": "employee@unqork.com"
                }
            }
        }
    }
}

Retrieve Operation Details

Records are only skipped when there is a issue with the row in the incoming file. For example, when the record cannot be parsed.

• Skipped Records: Data not  properly shaped will never process. The only way to review this data is a record comparison before and after the BDO process.

• Failed Records: A back-end process that failed during ingestion. These records are pushed to a file stored in the Unqork cloud under the nonProcessRowsFileKey, where they can be reingested using a separate job.

Next, you'll retrieve the necessary operation details. The returned response includes the following details:

You'll use your GraphQL sandbox to make the following request:

query GetJob($getJobId: String!) {
    ads {
        applicationDataOperation {
            getJob(id: $getJobId) {
                data
                created
                modified
            }
        }
    }
}

{
    "getJobId": "Enter operation ID here" 
    // Enter the operation ID you retireved from the previous step.
}

4.

Click Run.

Below is an example response from the request:

{
    "data": {
        "ads": {
            "applicationDataOperation": {
                "getJob": {
                    "data": {
                        "type": "UPDATE",
                        "database": "applicationData",
                        "collection": "65b3ea04954e3bbf54305c34",
                        "name": "Update 10K records",
                        "steps": [
                            {
                                "stepType": "UPDATE",
                                "id": "65b7fb9eaeee27a2a85a4b6a",
                                "status": "completed",
                                "files": [
                                    {
                                        "source": {
                                            "name": "13948b65-36ef-4ece-823c-dc3a6c1a7707.csv",
                                            "location": "unqorkResource/13948b65-36ef-4ece-823c-dc3a6c1a7707.csv",
                                            "format": "CSV"
                                        }
                                    }
                                ],
                                "options": {
                                    "update": {
                                        "headers": [],
                                        "delimiter": ",",
                                        "uniqueKey": "data.customerNumber",
                                        "upsert": true,
                                        "metaOverride": {
                                            "form": "65b3ea04954e3bbf54305c34",
                                            "owner": "employee@test.com",
                                            "created": "2024-01-26T00:00:00.419Z",
                                            "modified": "2024-01-26T00:00:00.419Z"
                                        },
                                        "storeInData": true
                                    }
                                },
                                "database": "applicationData",
                                "collection": "65b3ea04954e3bbf54305c34",
                                "details": {
                                    "chunks": [
                                        {
                                            "status": "completed",
                                            "chunkQueueId": "1",
                                            "chunkRecord": {
                                                "start": 2,
                                                "end": 10001
                                            },
                                            "docs": {
                                                "total": 10000,
                                                "processed": 10000
                                            },
                                            "auditFileKey": "unqorkResource/audit/65b7fb9eaeee27a2a85a4b6a/1.json.gz",
                                            "startTime": "2024-01-29T19:25:33.126Z",
                                            "endTime": "2024-01-29T19:26:04.743Z"
                                        }
                                    ],
                                    "docs": {
                                        "total": 10000,
                                        "processed": 10000
                                    }
                                }
                            }
                        ],
                        "status": "completed"
                    },
                    "created": 1706556312070,
                    "modified": 1706556372421
                }
            }
        }
    }
}

Generate Links to Download the Updated Data

If an issue occurs with the operation, follow these final steps to generate a link so you can download the log file. It's important to note that you can only generate single link for a single file. If you have multiple files, you'll generate a link for each.

In your GraphQL sandbox, you'll make a call using your fileKey or cloudKey values to retrieve your file and make it a downloadable link.

mutation ($cloudKey: String!) {
    ads {
        applicationDataOperation {
            createFileDownloadLink(inputData: { cloudKey: $cloudKey })
        }
    }
}

{
    "cloudKey": "unqorkResource/export/5aeffa53-9b81-4335-81d0-1cbbdce8bd88/1.json"
    // The location of the file from the previous step.
}

4.

Click Run.

Below is an example response from the request:

{
    "data": {
        "ads": {
            "applicationDataOperation": {
                "createFileDownloadLink": "/files/65b2a625b6489d65576ae3ee"
            }
        }
    }
}

Once you have your createFileDownloadLink, make a GET call using Postman or your browser. Use the following URL format to make the call: https://{{host}}/api/{{createFileDownloadLink}}. For example, https://training.unqork.io/api/files/65b2a625b6489d65576ae3ee.

You must be logged into your environment to make the call. Or, use the unqorkioToken bearer token to make the request.

Resources

• Bulk Data Operations Landing Page

• Import Submissions using Bulk Data Operations

• Export Submissions using Bulk Data Operations

• Create a Download Link for Data Files

• Create an Upload Link for Data Files

• Delete Submissions using Bulk Data Operators

• Call API Endpoints Using a Third-Party Service