Batch Processing using Gateway

[Subhash.chandra]

Hello,

How to achieve batch processing in API Gateway.
I have some questions regarding our requirements:-

1. How to execute batches with 50 records at a time. (we have 500 records)
2. How to update 50 records in the database.

Thanks,

------------------------------
[SE]
[SM]
[India]
------------------------------

[Joe Dascole]

Hi Subhash,

We have the "Run All Assertions Concurrently Assertion" that may help here. I have not personally tried it with DB updates, but used with the "Perform JDBC query Assertion" may get the results you need. Please read and carefully consider the technical notes listed in this document.

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/layer7-api-management/api-gateway/9-4/policy-assertions/assertion-palette/policy-logic-assertions/run-all-assertions-concurrently-assertion.html

Regards,
Joe

[Jay MacDonald]

Can you please describe the use case in more detail and be more specific about what you mean by "batch processing". What is the source of the information being updated? This will probably involve looping through the list to be processed, but without understanding your requirements, it's hard to help.

------------------------------
Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
------------------------------

[Subhash.chandra]

Hi Jay,

Thanks for the update!!!

We want to process a request in batches suppose we have 500 records and we want to execute only 100 records with a single request and after this request, another request will be executed with the same records.
By looping we can achieve this scenario so how to implement looping in service.


Thanks,

------------------------------
[SE]
[SM]
[India]
------------------------------

[Jay MacDonald]

The "Run Assertion for Each Item" assertion in the Policy Logic assertions category is used for looping. It loops through all of the values in a multivalued context variable, so you need to get a multivalued variable to loop through first.

I think an example best suits this explanation. I will use the following schema for the demo:

DROP DATABASE IF EXISTS demo;
CREATE DATABASE IF NOT EXISTS demo;
CREATE USER demo@localhost IDENTIFIED BY '7layer';
GRANT ALL ON demo.* TO demo@localhost;
use demo;
DROP TABLE IF EXISTS food;
CREATE TABLE IF NOT EXISTS food (
        id int NOT NULL,
        name varchar(255) NOT NULL,
        price varchar(255) NOT NULL,
        PRIMARY KEY (id)
);

Say we have the following XML document that contains information for the DB call:

<items>
  <item id="1">
    <name>apple</name>
    <price>1.40</price>
  </item>
  <item id="2">
    <name>carrot</name>
    <price>0.80</price>
  </item>
  <item id="3">
    <name>peach</name>
    <price>1.25</price>
  </item>
</items>

You would first use the "Evaluate Request XPath" assertion to isolate each <item> element, then loop through each one to extract the details. In this case I will extract all of the @id attributes with the XPath /items/item/@id then loop through them to extract the name (/items/item[@id=${id.current}/name) and price ​(/items/item[@id=${id.current}/price) from the document to build the SQL statement then call the DB.

Note that if you are only ever doing INSERT statements you could build a comma delimited list and use the "Insert JDBC in Bulk" assertion, but if there are any primary key collisions it will fail.

Attached is a policy to illustrate several approaches. To use it, create a DB using the schema above (I used the mysql database in my dev Gateway - DON'T DO THIS IN PRODUCTION. THIS IS FOR DEMO ONLY) and configure a JDBC connection to it, then publish a Web API at /batchDemo and Import the policy into it using the Policy Manager. It should ask you to select the database connection on loading. Note that this demo uses a mode parameter in the url to illustrate the different approaches and has hardcoded XML requests in it. Then simply hit the service with your browser with the mode set: http://<gateway>:8080/batchDemo?mode=attribute (or field or bulk or flush).

I'm using an XML document in this example, but this could just as easily be done with JSON or any other format that can be parsed by the policy language.

I hope this helps.

------------------------------
Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
------------------------------

Attachment(s)

BatchDemo.xml   47 KB 1 version