Ask me anything
How can I help you with Catalyst today?
Bulk Write Records
The Bulk Write operation can fetch thousands of records from a CSV file uploaded to a bucket in Stratus and insert them in a specific table in the Data Store. This data-intense operation can be performed in a single API execution.
After the bulk write operation has executed successfully, a CSV report file containing the details of the job execution will be generated at a download URL.
Catalyst supports three APIs for the bulk write operation: a Create Bulk Write API, a Check Bulk Write Status API to check the status of the job execution manually, and a Download Bulk Write Result API to download the results. You can also configure a callback URL to enable automatic posting of the job status responses in it.
Create a Bulk Write Job
Description
This API triggers the bulk write operation to obtain the records from a CSV file stored in Stratus and exports them to a table in the Data Store, based on the criteria you specify in the input JSON. The CSV file is referred to using its Object URL in Stratus, and the table is referred to using its unique table ID or the table name.
Note:
-
Ensure you have uploaded the required CSV file in Stratus, before you begin executing this operation.
-
The CSV file’s first row must be the header row i.e., the column names must be present as the header row.
-
The CSV file must contain data in the format that adheres to CSV file standards. If the file is not in the standard format, the data written to the table might be corrupted.
-
The Bulk Write API can insert up to 100,000 records in a single API call, irrespective of the environment. If you require more records to be written, you can do so in batches of 100,000 records by sending multiple Bulk Write API requests accordingly.
There are three operations that can be performed using this API:
- Insert: Inserts new rows of data into the table from the CSV file
- Update: Updates existing rows of data in the table. The records are identified either using a unique column in the table, or using their ROWIDs generated in the Data Store.
- Upsert: Enables both insert and update operations at the same time. If the record already exists, the row is updated. If the record does not exist, a new row with the data is inserted.
Request Details
Request URL
{api-domain}/baas/v1/project/{projectId}/bulk/write
Request Headers
Authorization: Zoho-oauthtoken {oauth_token}
Optional Headers
CATALYST-ORG: {org_id}
Environment: Development
Scope
ZohoCatalyst.tables.bulk.CREATE
Request JSON Properties
The input for the Bulk Write API request must be sent in a JSON format. The request parameters are explained below.
Specifies the operation to be executed using the API.
Allowed values: insert, update, upsert
Default operation: insert
The unique column using which the records are identified.
For example, if you specify a unique column called ‘EmployeeID’ as the find_by value, Catalyst will search the records in the Data Store table using the EmployeeID value. For the update operation, Catalyst will update the rows that match the EmployeeID from the CSV file. For the upsert operation, Catalyst will update existing rows, and insert new rows if there are no records with matching EmployeeID values.
Update: This can be a column with unique values as configured in the Data Store, or the ROWID generated by Catalyst for the records.
Upsert: You can only specify a column with unique values as configured in the Data Store. You will not be able to specify the ROWID as the find_by value. This is because, upsert will insert new rows if matching ones are not found, and the ROWID values will not be available for rows not existing in the Data Store.
Insert: If you specify a find_by column for an insert operation, any records matching the column values from the CSV file will be skipped and not inserted into the Data Store table. This is because, insert cannot update if a given record already exists.
Foreign key mapping for the columns that are primary keys in other tables. This is specified if any columns imported from the CSV file should be configured as foreign keys.
You must map foreign keys in the following format:
{
“local_column” : “local_column_name_1”,
“reference_column” : “reference_column_name_1”
}
where local_column is the name of the column in the table where the bulk write operation is processed, and reference_column is the name of the column in the table in which it is a primary key.
Refer to the sample request code.
The section where you can define the properties of the callback URL, where automatic JSON responses of the job statuses will be sent to.
The URL where the automatic responses will be sent to using the HTTP POST method, each time the job status changes. Information about the job details will be contained in the body of the JSON response.
If you don’t configure a callback URL, you must manually check the status of the job processing when you need, using the Check Bulk Write Status API.
Headers that you require to be passed in the JSON response to the callback URL
You must specify the headers as:
{
"{header_name_1}" : “{header_value_1}”,
"{header_name_2}" : “{header_value_2}”
}
and so on. Refer to the sample request code.
Parameters that you require to be appended to the callback URL
You must specify the parameters as:
{
"{param_name_1}" : “{param_value_1}”,
"{param_name_2}" : “{param_value_2}”
}
and so on. Refer to the sample request code.
Response Details
There are three job processing states which will generate three different responses:
-
In-Progress: When you execute the API, Catalyst will immediately and automatically send you a response indicating that the job execution is in progress. If you have configured a callback URL in the request, the response will also be posted to the URL in the format you defined.
The response will contain a job_id parameter which you can use to check the status of the job execution for subsequent responses, if you have not configured a callback URL.
-
Success: The success state response will contain a download URL, where you can download a CSV report file that contains the details of the job execution. It will list all the records that were skipped from being written along with the reasons. If you have not configured a callback URL, this response will not be sent by Catalyst automatically.
-
Failed: The failed state response will contain a description of the error that occurred and the details. If you have not configured a callback URL, this response will not be sent by Catalyst automatically.
curl -X POST https://api.catalyst.zoho.com/baas/v1/project/4000000006007/bulk/write
-H “Authorization: Zoho-oauthtoken 1000.91016.2f*57”
-d ‘{
“table_identifier”: “3376000000165511”,
“operation”: “insert”,
“object_details”: {
“bucket_name”: “zylker-emp”,
“object_key”: “employees/emp_rec.csv”,
“version_id”: “3546576879809”
},
“callback”: {
“url”: “https://hr.zylker.com/en/EmpRecords/_callback.php",
“headers”: {
“src”: “ZCatalyst”,
“operation”: “bulkwriteAPI”
},
“params”: {
“project_name”: “EmployeeDatabase”
}
},
“find_by”: “EmpID”,
“fk_mapping”: [
{
“local_column”: “EmployeeID”,
“reference_column”: “EmpID”
},
{
“local_column”: “DepartmentID”,
“reference_column”: “DeptID”
}
]
}’
{
"status": "success",
"data": {
"job_id": 2000000110044,
"status": "In-Progress",
"operation": "insert",
"project_details": {
"project_name": "ShipmentTracking",
"id": 4000000006007
},
"query": [
{
"table_id": 3376000000165511,
"object_details": {
"bucket_name": "zylker-emp",
"object_key" : "employees/emp_rec.csv",
"version_id":"3546576879809"
}
}
],
"callback": {
"url": "https://hr.zylker.com/en/EmpRecords/_callback.php",
"headers": {
"src": "ZCatalyst",
"operation": "bulkwriteAPI"
},
"params": {
"project_name": "EmployeeDatabase"
}
},
"created_by": {
"zuid": 3000000006111,
"is_confirmed": true,
"email_id": "emma@zylker.com",
"first_name": "Amelia",
"last_name": "Burrows",
"user_id": 671930409
},
"created_time": "Oct 05, 2020 07:33 PM"
}
}
{
"status": "success",
"data": {
"job_id": 2000000110044,
"status": "Completed",
"operation": "insert",
"project_details": {
"project_name": "ShipmentTracking",
"id": 4000000006007
},
"query": [
{
"table_id": 3376000000165511,
"details": {
"object_url" : "https://zylker-emp-development.zohostratus.com/employees/emp_rec.csv"
}
}
],
"callback": {
"url": "https://hr.zylker.com/en/EmpRecords/_callback.php",
"headers": {
"src": "ZCatalyst",
"operation": "bulkwriteAPI"
},
"params": {
"project_name": "EmployeeDatabase"
}
},
"created_by": {
"zuid": 3000000006111,
"is_confirmed": true,
"email_id": "emma@zylker.com",
"first_name": "Amelia",
"last_name": "Burrows",
"user_id": 671930409
},
"created_time": "Oct 05, 2020 07:25 PM",
"results": {
"download_url": "https://api.catalyst.zoho.com/baas/v1/bulk/write/2000000110013/download",
"description": "Bulk WRITE job completed. ",
"details": [
{
"table_id": 3376000000165511,
"records_processed": 176
}
]
}
}
}
{
"status": "success",
"data": {
"job_id": 2000000110044,
"status": "Failed",
"operation": "insert",
"project_details": {
"project_name": "ShipmentTracking",
"id": 4000000006007
},
"query": [
{
"table_id": 3376000000165511,
"details": {
"object_url" : "https://zylker-emp-development.zohostratus.com/employees/emp_rec.csv"
}
}
],
"callback": {
"url": "https://hr.zylker.com/en/EmpRecords/_callback.php",
"headers": {
"src": "ZCatalyst",
"operation": "bulkwriteAPI"
},
"params": {
"project_name": "EmployeeDatabase"
}
}
"created_by": {
"zuid": 3000000006111,
"is_confirmed": true,
"email_id": "emma@zylker.com",
"first_name": "Amelia",
"last_name": "Burrows",
"user_id": 671930409
},
"created_time": "Oct 05, 2020 07:25 PM",
"results": {
"description": "The reference_column 'DeptID' in fk_mapping of table 3376000000165511 is not a unique column",
"details": [ ]
}
}
}
Yes
No
Thank you for your feedback!
Send your feedback to us