Bulk Write Rows
The Bulk Write operation can fetch thousands of records from a CSV file uploaded in the File Store, 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.
Note: You can also perform Bulk Write operation using the Catalyst CLI. Refer to Data Store Import and Export CLI documentation.
Create Bulk Write Job
Description
This API triggers the bulk write operation to obtain the records from a CSV file in the File Store and export 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 File ID in the File Store, and the table is referred to using its unique table ID or the table name.
Note:
1. You must upload the CSV file containing the data in the File Store, before you begin executing this operation.
2. The CSV file's first row must be the header row i.e., the column names must be present as the header row.
3. 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.
4. You can insert 5000 records in each table per project in the development environment using this API. If the CSV file contains records more than that, the first 5000 records will be written to the table. The others will be skipped in the development environment. There are no table limits for insertion in the production environment. However, the Bulk Write API can insert upto 100000 records in a single API call from a CSV file even in the production environment. If you require more records to be written, you can do so in batches of 100000 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 URL
https://api.catalyst.zoho.com/baas/v1/project/{projectId}/bulk/write
project_id - The unique ID of the project
Request Header
Authorization: Zoho-oauthtoken 1000.910***************************16.2f****************************57
Request Method
POST
Scope
scope=ZohoCatalyst.tables.bulk.CREATE
Request JSON Properties
The input for the Bulk Read API request must be sent in a JSON format. The request parameters are explained below.
Attributes | Data Type | Mandatory | Description | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
table_identifier | String | Yes | The unique ID of the table or the table name. You can obtain the table ID from Data Store or from the URL when the table is opened in the console. | |||||||||||||||||
operation | String | No | Specifies the operation to be executed using the API. Allowed values:insert, update, upsert Default operation:insert | |||||||||||||||||
file_id | Numerical | Yes | The unique ID of the CSV file in the File Store which contains the records to be written. | |||||||||||||||||
callbackShow properties | JSON | No | The section where you can define the properties of the callback URL, where automatic JSON responses of the job statuses will be sent to | |||||||||||||||||
callback Properties
| ||||||||||||||||||||
find_by | String | Mandatory for update and upsert operations Not mandatory for insert operation | 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. | |||||||||||||||||
fk_mapping | JSON | No | 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. |
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 in progress. If you have configured a callback URL in the request, the response will also be posted to the URL in the format defined by you.
The response will contain a job_id parameter which you can use to check the status of the job execution manually 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 occurred and the details. If you have not configured a callback URL, this response will not be sent by Catalyst automatically.
Sample Request
curl -X POST \
https://api.catalyst.zoho.com/baas/v1/project/4000000006007/bulk/write \
-H "Authorization: Zoho-oauthtoken 1000.910***************************16.2f****************************57" \
-d '{
"table_identifier": "3376000000165511",
"operation" : "insert",
"file_id" : 3376000000363098,
"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"
}
]
}'
Sample Response: Job In-Progress
{
"status": "success",
"data": {
"job_id": 2000000110044,
"status": "In-Progress",
"operation": "insert",
"project_details": {
"project_name": "ShipmentTracking",
"id": 4000000006007
},
"query": [
{
"table_id": 3376000000165511,
"details": {
"file_id": 3376000000363098
}
}
],
"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"
}
}
Sample Response: Job Successful
{
"status": "success",
"data": {
"job_id": 2000000110044,
"status": "Completed",
"operation": "insert",
"project_details": {
"project_name": "ShipmentTracking",
"id": 4000000006007
},
"query": [
{
"table_id": 3376000000165511,
"details": {
"file_id": 3376000000363098
}
}
],
"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
}
]
}
}
}
Sample Response: Job Failed
{
"status": "success",
"data": {
"job_id": 2000000110044,
"status": "Failed",
"operation": "insert",
"project_details": {
"project_name": "ShipmentTracking",
"id": 4000000006007
},
"query": [
{
"table_id": 3376000000165511,
"details": {
"file_id": 3376000000363098
}
}
],
"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": [ ]
}
}
}