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

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

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

Attribute	Data Type	Mandatory	Description
url	String	Yes, if you want to specify the callback URL	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	String	No	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.
params	String	No	Parameters that you require to be 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.

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

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.

Curl

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": [ ]
                        }
                   }
}