Bulk Read Rows

The Bulk Read operation can read thousands of records from a specific table in the Data Store and generate a CSV file containing the results of the read operation, if the job is successful. This data-intense operation can be performed in a single API execution.

Catalyst supports three APIs for the bulk read operation: a Create Bulk Read API, a Check Bulk Read Status API to check the status of the job execution manually, and a Download Bulk Read Result API to download the results. You can also configure a callback URL to enable the automatic posting of the job status responses in it.

Note: You can also perform Bulk Read operation using the Catalyst CLI. Refer to Data Store Import and Export CLI documentation.

Create Bulk Read Job

Description

This API triggers the bulk read operation to obtain the records from the table in the Data Store, that match the criteria you specify in the input JSON. The table is referred to using its unique table ID or the table name.

Request URL

https://api.catalyst.zoho.com/baas/v1/project/{projectId}/bulk/read

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.READ

Request JSON Properties

The input for the Bulk Read API request must be sent in a JSON format. The request parameters are explained below.

AttributesData TypeMandatoryDescription
table_identifierStringYesThe 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.
queryJSONNoThe section where you can define the conditions and criteria for the bulk read job
pageNumericalNoThe CSV file generated as the result of the bulk read process contains two hundred thousand/two lakh records in one page.

Page value '1' indicates that the first two lakh records matching your query will get exported. You can fetch subsequent records by increasing the page value.

For example, if you want to fetch records from the range 2,00,001 to 4,00,000, then you must specify the page value as '2'.

Default value: 1
select_columnsArrayNoSpecific columns in the records that need to be fetched from the table. If you do not specify the columns, all columns will be selected and fetched.

You can include multiple columns in an array. Refer to the sample request code window.
criteriaShow propertiesJSONNoA set of conditions based on which the records will be fetched

criteria Properties

AttributeData TypeMandatoryDescription
group_operatorStringYes, if you want to specify the criteriaThe operator that will bind the criteria groups together.

Supported Operators:AND, OR

If you define two or more criteria groups, you can use the AND or the OR operator in your request. For example, you can specify a criteria like "column1 equal value 1 AND column2 contains value 2" in the format specified in the previous section.

Note: You will not be able to use combinations of both the operators in a single API request. You can either use AND or OR in one request.
groupShow propertiesJSONYes, if you want to specify the criteriaYou can include upto 25 criteria sets in this section. The example shows 3 criteria sets.

Note: You can include only one overall group with one group operator in a single API request.

group Properties

AttributeData TypeMandatoryDescription
column_nameStringYes, if you want to specify the criteriaName of the specific column from the table that the criteria should be defined for
comparatorStringYes, if you want to specify the criteriaThe comparison operator that matches the column name with the criteria value

Supported Comparators:equal, not_equal, greater_than, greater_equal, less_than, less_equal, starts_with, ends_with, contains, not_contains, in, not_in, between, not_between

Note: 1. You can specify multiple values for in and not_in comparators, separated by commas.

2. Catalyst supports only the equal comparator for an encrypted column. You will not be able to use other comparators as the criteria for an encrypted column.
valueStringYes, if you want to specify the criteriaThe value for the specific column in the record that you require to be defined as a criteria
callbackJSONNoThe section where you can define the properties of the callback URL, where automatic JSON responses of the job statuses will be sent to
urlStringYes, if you want to specify the callback URLThe 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 Read Status API.
headersStringNoHeaders 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.
paramsStringNoParameters 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.
 

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 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 file that contains the records matching your query. 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/read  \
-H "Authorization: Zoho-oauthtoken 1000.910***************************16.2f****************************57" \
-d '{
   "table_identifier": "EmpDetails",
   "query" : {
	   "page" : "1",
           "select_columns" : [
       	            "EmpID", "EmpName", "Department"
                                          ],
           "criteria": {
     	           "group_operator": "or",
                   "group": [
      			            {
			          "column_name": "Department",
         		          "comparator": "equal",
        	                  "value": "Marketing"
        		             } ,
        		            {
        		          "column_name": "EmpID",
	                          "comparator": "greater_than",
	                          "value": "1000"
	       		            } ,
       			            {
	                          "column_name": "EmpName",
		       	          "comparator": "starts_with",
        	                  "value": "S"
		                    }
      			         ]
   	                   }
	            },
	"callback" : {
	          "url" : "https://hr.zylker.com/en/EmpRecords/_callback.php",
		  "headers" : {
				    "src" : "ZCatalyst",
				    "operation" : "bulkreadAPI"
				      },
                   "params" : {
				   "project_name" : "EmployeeDatabase"
				      }
                             }
}'			

Sample Response: Job In-Progress


				{
    "status": "success",
    "data": {
        "job_id": 2000000118004,
        "status": "In-Progress",
        "operation": "read",
        "project_details": {
             "project_name": "ShipmentTracking",
             "id": 4000000006007
         },
        "query": [
            {
                "table_id": 3376000000165541,
                "details": {
                    "page": "1"
                }
            }
        ],
        "callback": {
            "url": "https://hr.zylker.com/en/EmpRecords/_callback.php",
            "headers": {
                      "src" : "ZCatalyst",
                      "operation" : "bulkreadAPI"
                              },
            "params": {
                      "project_name" : "EmployeeDatabase"
                             }
        },
        "created_by": {
            "zuid": 1019540152,
            "is_confirmed": true,
            "email_id": "emma@zylker.com",
            "first_name": "Amelia",
            "last_name": "Burrows",
            "user_id": 671930409
        },
        "created_time": "Oct 06, 2020 07:06 PM"
    }
}			

Sample Response: Job Successful


				{
    "status": "success",
    "data": {
        "job_id": 2000000118004,
        "status": "Completed",
        "operation": "read",
        "project_details": {
             "project_name": "ShipmentTracking",
             "id": 4000000006007
         },
        "query": [
                        {
                "table_id": 3376000000165541,
                "details": {
                    "page": "1"
                                }
                          }
                       ],
        "callback": {
                "url": "https://hr.zylker.com/en/EmpRecords/_callback.php",
                "headers": {
                      "src" : "ZCatalyst",
                      "operation" : "bulkreadAPI"
                              },
                  "params": {
                       "project_name" : "EmployeeDatabase"
                             }
                           },
        "created_by": {
                "zuid": 1019540152,
                "is_confirmed": true,
                "email_id": "emma@zylker.com",
                "first_name": "Amelia",
                "last_name": "Burrows",
                "user_id": 671930409
                               },
        "created_time": "Oct 06, 2020 07:06 PM",
	"results": {
               "download_url": "https://api.catalyst.zoho.com/baas/v1/bulk/read/2000000117007/download",
               "description": "Bulk READ job 2000000117007 completed.",
                "details": [
                                 {
                       "table_id": 3376000000165541,
                       "records_processed": 21,
                       "more_records": false
                                 }
                               ]
                        }
               }
}			

Sample Response: Job Failed


				{
    "status": "success",
    "data": {
        "job_id": 2000000117007,
        "status": "Failed",
        "operation": "read",
        "project_details": {
             "project_name": "ShipmentTracking",
             "id": 4000000006007
         },
        "query": [
            {
                "table_id": 3376000000165541,
                "details": {
                    "page": "1"
                }
            }
        ],
        "callback": {
            "url": "https://hr.zylker.com/en/EmpRecords/_callback.php",
             "headers": {
                      "src" : "ZCatalyst",
                      "operation" : "bulkreadAPI"
                              },
            "params": {
                       "project_name" : "EmployeeDatabase"
                             }
        },
        "created_by": {
            "zuid": 1019540152,
            "is_confirmed": true,
            "email_id": "emma@zylker.com",
            "first_name": "Amelia",
            "last_name": "Burrows",
            "user_id": 671930409
        },
        "created_time": "Oct 06, 2020 07:00 PM",
        "results": {
            "description": "The column ID in select_columns is invalid",
        }
    }
}