Pagination tutorial

Processing large amounts of data at once can lead to errors. Learn how pagination lets users split data querying processes and retrieve a limited number of records.

When you build an integration flow, you may need to process large amounts of data. Processing all of that data at once can lead to errors such as a timeout error or an Out of Memory (OOM) error. One solution to this problem is pagination. Using pagination, you can split the data querying process into batches and retrieve only a limited number of records at a time.

In the following articles, we will guide you on how to implement pagination in a flow that makes a total migration of an HR database every day at 1 AM.

When you copy components on the Digibee Integration Platform, they are stored on your clipboard as JSON objects. The JSON object that refers to this pagination pipeline is available on the expandable block at the end of this article. You can copy and paste it into the Canvas and modify it as needed for your integration flow.

Before implementing pagination, you need to check if the API, database or web service you are querying supports pagination. If not, you can still improve the performance of your integration flow by increasing the deployment size of your pipelines.

In this example, the pagination flow is triggered every five minutes. It starts with a sequence of initial steps. After these steps, it splits into three paths, as displayed below:

Pagination pipeline

• The flow follows the “FINISHED” path if the data migration process has already ended for the day or if it is not yet time to start it.

• The flow follows the “EXTRACTING_DATA” path when it is time to start the data migration process and this process is not yet completed.

• The flow follows the error path if an error occurs during the initial steps.

In the following articles, we will give you step-by-step instructions on how to build each of these paths.

{ "meta": { "trigger": { "position": { "x": 41, "y": 173 } }, "b5ff08e4-4be2-4363-8315-94f2f8e330e1": { "position": { "x": 173, "y": 173 } }, "43f2edbb-bfb4-401d-9268-b249a9647a7c": { "position": { "x": 305, "y": 173 } }, "94d7c8aa-b933-4215-90da-9e444694cc7a": { "position": { "x": 437, "y": 173 } }, "4603fa33-63ff-4a21-8b9e-f36d292bd856": { "position": { "x": 574, "y": 173 } }, "c956b8a4-11b1-43e6-9071-cc75e6406f6c": { "position": { "x": 861, "y": 173 } }, "512fae5f-9eb5-47fa-ab4b-afccafd04271": { "position": { "x": 998, "y": 173 } }, "2bcd9f6a-a9ef-456a-824d-120b33d9b180": { "position": { "x": 1135, "y": 173 } }, "68cb6d2e-8c4c-4955-9043-bd9a19ddfb78": { "position": { "x": 1267, "y": 173 } }, "399e154c-2f04-4089-adba-54505537a5d8": { "position": { "x": 1399, "y": 173 } }, "190ff285-eed4-4284-80fa-67eaf50a9567": { "position": { "x": 1536, "y": 173 } }, "10cf847e-1841-4f51-9070-61e0c90ea9f5": { "position": { "x": 1915, "y": 239 } }, "6ecdab96-f805-40da-9042-43213767fcdc": { "position": { "x": 2047, "y": 239 } }, "a7ead047-b435-4c93-a11c-d5b43a8ca36a": { "position": { "x": 2179, "y": 239 } }, "c8335fac-e519-46f2-84cb-aa5e9c24ac85": { "position": { "x": 2311, "y": 239 } }, "64f5b700-8f85-4bbd-b13f-6dd417dcb71a": { "position": { "x": 1915, "y": 107 } }, "75fc8b48-27d7-4a98-95d8-444c87ac2834": { "position": { "x": 2047, "y": 107 } }, "9ef51996-056b-42a7-ae84-f0f2764fb356": { "position": { "x": 2179, "y": 107 } }, "04871318-a796-44b2-8862-b2265a1b5a48": { "position": { "x": 2311, "y": 107 } }, "bfe65bda-ad52-469b-807b-71163faba44c": { "position": { "x": 763.0364291711267, "y": 421.53565929452884 } }, "5d0c17ca-4d0d-4e58-9c86-d50e8d8c97e4": { "position": { "x": 998, "y": 414 } }, "b77a096e-558d-44bc-bb05-e0906f2e943a": { "position": { "x": 1536, "y": 482.5 } }, "57f8478b-1675-4d3a-83d0-a5c8d19b60a1": { "position": { "x": 1678, "y": 482.5 } }, "3695db00-d82e-4f8c-818e-10883acdf1b8": { "position": { "x": 2047, "y": 411.5 } }, "5387162d-d9a9-473d-8adf-14adabadcd35": { "position": { "x": 2179, "y": 411.5 } }, "d5213b0b-a44d-4c88-a046-913e82d7bcc6": { "position": { "x": 2311, "y": 410.5 } }, "fc73381f-54e8-44f3-b830-3f2a541a183b": { "position": { "x": 2047, "y": 548.5 } }, "f354b82b-8518-4509-b703-04133ed767d3": { "position": { "x": 2179, "y": 548.5 } }, "c3b39a8c-3bbf-4f4e-aad8-f2851e26d34a": { "position": { "x": 1536, "y": 345.5 } }, "f23200ca-8203-4adf-a07d-8bc779088bc5": { "position": { "x": 1678, "y": 345.5 } }, "d25738e8-bd93-4f53-9d1e-ccd9a255e852": { "position": { "x": 861, "y": 41 } }, "f7a84b35-d8ff-4750-adf7-f71a97e05acd": { "position": { "x": 998, "y": 41 } } }, "flowSpec": { "disconnected-root:a7cb376f-7012-46f8-85de-64547c319707": [ { "stepName": "get pagination parameters", "accountLabel": "dgb-internal-object-store-account", "params": { "operation": "FIND", "objectStore": "pagination-OS-example", "objectId": "{{ CONCAT(metadata.pipeline.name, "_v" , metadata.pipeline.versionMajor) }}", "limit": "0", "skip": "0", "sort": "{}", "unique": true, "isolated": false, "upsert": false, "failOnError": false }, "id": "b5ff08e4-4be2-4363-8315-94f2f8e330e1", "type": "connector", "name": "object-store-connector" }, { "stepName": "create default values", "params": { "json": "{\n "control":{\n "startTimestamp": {{ metadata.execution.startTimestamp }},\n "limit": {{ DEFAULT( message.data[0].limit, 500 ) }},\n "start": {{ DEFAULT( message.data[0].start, 0 ) }},\n "end": {{ DEFAULT( message.data[0].end, 500 ) }},\n "step": {{ DEFAULT( message.data[0].step, "FINISHED" ) }},\n "nextExecutionTimestamp": {{ message.data[0].nextExecutionTimestamp }}\n }\n}", "failOnError": false }, "id": "43f2edbb-bfb4-401d-9268-b249a9647a7c", "type": "connector", "name": "json-generator-connector" }, { "type": "session-management", "stepName": "put control", "operation": "PUT", "sessionType": "LOCAL", "scoped": false, "fields": [ "control" ], "id": "94d7c8aa-b933-4215-90da-9e444694cc7a" }, { "stepName": "check step", "id": "4603fa33-63ff-4a21-8b9e-f36d292bd856", "type": "choice", "when": [ { "jsonPath": "$.control.[?(@.step == 'EXTRACTING_DATA')]", "target": "EXTRACTING_DATA (1)" }, { "jsonPath": "$.control.[?(@.step == 'FINISHED')]", "target": "FINISHED (1)" } ], "otherwise": "error - unknown step" } ], "EXTRACTING_DATA (1)": [ { "type": "connector", "name": "log-connector", "stepName": "Log - EXTRACTING_DATA", "params": { "logLevel": "INFO", "message": " EXTRACTING_DATA" }, "id": "c956b8a4-11b1-43e6-9071-cc75e6406f6c" }, { "stepName": "select enb_person", "accountLabel": "mysql-2", "params": { "url": "{{global.enablement-db-url}}", "sql": "select * from enb_person limit {{ message.control.start }}, {{ message.control.limit }}", "itemIdentifier": "codigo", "parallel": false, "blobAsFile": false, "clobAsFile": false, "failOnError": false, "connectionProperties": "{}", "keepConnection": true, "advanced": false, "dbPoolByActualConsumers": false, "exclusiveDbPool": false, "columnFromLabel": false, "connectionTestQuery": "", "onProcess": "512fae5f-9eb5-47fa-ab4b-afccafd04271-onProcessTrack", "onException": "512fae5f-9eb5-47fa-ab4b-afccafd04271-onExceptionTrack" }, "id": "512fae5f-9eb5-47fa-ab4b-afccafd04271", "type": "connector", "name": "stream-db-connector-v3" }, { "stepName": "group summary", "params": { "json": "{\n "summary": {{ message.$ }}\n}", "failOnError": false }, "id": "2bcd9f6a-a9ef-456a-824d-120b33d9b180", "type": "connector", "name": "json-generator-connector" }, { "stepName": "put summary", "operation": "PUT", "sessionType": "LOCAL", "fields": [ "summary" ], "scoped": false, "id": "68cb6d2e-8c4c-4955-9043-bd9a19ddfb78", "type": "session-management" }, { "type": "session-management", "stepName": "get control", "operation": "GET", "sessionType": "LOCAL", "scoped": false, "fields": [ "control" ], "id": "399e154c-2f04-4089-adba-54505537a5d8" }, { "type": "choice", "stepName": "Choice", "when": [ { "jsonPath": "$.[?(@.summary.total < @.control.limit)]", "target": "pagination is over" } ], "otherwise": "next page", "id": "190ff285-eed4-4284-80fa-67eaf50a9567" } ], "512fae5f-9eb5-47fa-ab4b-afccafd04271-onProcessTrack": [ { "stepName": "publish processing event", "params": { "eventName": "evt-proc-handson-architecture-danilo-loose-t33", "body": "{{ message.$ }}", "showSendEventLog": false, "stopOnError": false }, "id": "d872bc41-d300-4d08-840e-d05cbbb46420", "type": "connector", "name": "event-publisher-connector" }, { "stepName": "success message", "params": { "json": "{\n "success": true\n}", "failOnError": false }, "id": "2cb6cf22-c9d1-4c3b-8afc-c0eef532ec2e", "type": "connector", "name": "json-generator-connector" } ], "512fae5f-9eb5-47fa-ab4b-afccafd04271-onExceptionTrack": [ { "stepName": "Log - onException Stream DB", "params": { "logLevel": "ERROR", "message": "onException stream DB" }, "id": "f3c5ba58-35a1-4830-b1cf-570668f86bcc", "type": "connector", "name": "log-connector" }, { "stepName": "Publish error event", "params": { "eventName": "error-event-pagination", "body": "{{ message.$ }}", "showSendEventLog": false, "stopOnError": false }, "id": "46061315-89ca-46a7-ba84-3ead41a50ab2", "type": "connector", "name": "event-publisher-connector" }, { "type": "connector", "name": "throw-error-connector", "stepName": "Throw Error", "params": { "errorMessage": "Error occurred.", "errorCode": 500, "customErrorEnabled": false, "failOnError": true }, "id": "70abcbbf-5048-4145-8ed8-646230c29542" } ], "pagination is over": [ { "stepName": "Log - pagination is over", "params": { "logLevel": "WARN", "message": "acabou a paginação" }, "id": "10cf847e-1841-4f51-9070-61e0c90ea9f5", "type": "connector", "name": "log-connector" }, { "stepName": "update pagination parameters", "accountLabel": "dgb-internal-object-store-account", "params": { "operation": "UPDATE", "objectStore": "pagination-OS-example", "objectId": "{{ CONCAT(metadata.pipeline.name, "_v" , metadata.pipeline.versionMajor) }}", "document": "{\n $set:{\n "start": 0,\n "end": null,\n "step": "FINISHED",\n "nextExecutionTimestamp": {{ FORMATDATE(FORMATDATE(SUMDATE( NOW() , "DAY", 1), "timestamp", "dd/MM/yyyy 01:00:00"), "dd/MM/yyyy HH:mm:ss", "timestamp") }}\n }\n}", "unique": true, "isolated": false, "upsert": true, "failOnError": false }, "id": "6ecdab96-f805-40da-9042-43213767fcdc", "type": "connector", "name": "object-store-connector" }, { "operation": "GET", "scoped": false, "sessionType": "LOCAL", "stepName": "get control", "fields": [ "control" ], "type": "session-management", "id": "a7ead047-b435-4c93-a11c-d5b43a8ca36a" }, { "stepName": "set output", "params": { "json": "{\r\n "message": {{ CONCAT("Migration will start at ", FORMATDATE( message.control.nextExecutionTimestamp, "timestamp", "dd/MM/yyyy HH:mm:ss")) }}\r\n}\r\n", "failOnError": false }, "id": "c8335fac-e519-46f2-84cb-aa5e9c24ac85", "type": "connector", "name": "json-generator-connector" } ], "next page": [ { "stepName": "Log - next page", "params": { "logLevel": "INFO", "message": "proxima pagina" }, "id": "64f5b700-8f85-4bbd-b13f-6dd417dcb71a", "type": "connector", "name": "log-connector" }, { "stepName": "update pagination parameters", "accountLabel": "dgb-internal-object-store-account", "params": { "operation": "UPDATE", "objectStore": "pagination-OS-example", "objectId": "{{ CONCAT(metadata.pipeline.name, "_v" , metadata.pipeline.versionMajor) }}", "document": "{\n $set:{\n "start": {{ message.control.end }},\n "end": {{ TOINT(SUM(message.control.end, message.control.limit)) }},\n "step": "EXTRACTING_DATA"\n }\n}", "unique": true, "isolated": false, "upsert": true, "failOnError": false }, "id": "75fc8b48-27d7-4a98-95d8-444c87ac2834", "type": "connector", "name": "object-store-connector" }, { "stepName": "update pagination parameters", "accountLabel": "dgb-internal-object-store-account", "params": { "operation": "FIND", "objectStore": "pagination-OS-example", "objectId": "{{ CONCAT(metadata.pipeline.name, "_v" , metadata.pipeline.versionMajor) }}", "limit": "0", "skip": "0", "sort": "{}", "unique": true, "isolated": false, "upsert": false, "failOnError": false }, "id": "9ef51996-056b-42a7-ae84-f0f2764fb356", "type": "connector", "name": "object-store-connector" }, { "stepName": "set output", "params": { "json": "{\n "message": "The data is being migrated.",\n "next_execution": {{ message.data[0] }}\n}", "failOnError": false }, "id": "04871318-a796-44b2-8862-b2265a1b5a48", "type": "connector", "name": "json-generator-connector" } ], "FINISHED (1)": [ { "stepName": "Log - FINISHED", "params": { "logLevel": "INFO", "message": "FINISHED" }, "id": "bfe65bda-ad52-469b-807b-71163faba44c", "type": "connector", "name": "log-connector" }, { "type": "choice", "stepName": "Choice", "when": [ { "jsonPath": "$.[?(@.control.nextExecutionTimestamp != null)]", "target": "nextExecutionTimestamp is not null" } ], "otherwise": "nextExecutionTimestamp is null", "id": "5d0c17ca-4d0d-4e58-9c86-d50e8d8c97e4" } ], "nextExecutionTimestamp is not null": [ { "stepName": "Log - nextExecutionTimestamp is not null", "params": { "logLevel": "INFO", "message": "nextExecution preenchido" }, "id": "b77a096e-558d-44bc-bb05-e0906f2e943a", "type": "connector", "name": "log-connector" }, { "type": "choice", "stepName": "Choice", "when": [ { "jsonPath": "$.control.[?(@.startTimestamp>= @.nextExecutionTimestamp)]", "target": "restart migration" } ], "otherwise": "wait until next migration", "id": "57f8478b-1675-4d3a-83d0-a5c8d19b60a1" } ], "restart migration": [ { "stepName": "Log - restart migration", "params": { "logLevel": "INFO", "message": "recomeçar a migração" }, "id": "3695db00-d82e-4f8c-818e-10883acdf1b8", "type": "connector", "name": "log-connector" }, { "stepName": "update pagination parameters", "accountLabel": "dgb-internal-object-store-account", "params": { "operation": "UPDATE", "objectStore": "pagination-OS-example", "objectId": "{{ CONCAT(metadata.pipeline.name, "_v" , metadata.pipeline.versionMajor) }}", "document": "{\n $set:{\n "step": "EXTRACTING_DATA"\n }\n}", "unique": true, "isolated": false, "upsert": true, "failOnError": false }, "id": "5387162d-d9a9-473d-8adf-14adabadcd35", "type": "connector", "name": "object-store-connector" }, { "stepName": "set output", "params": { "json": "{\n "message": "Migration will start at the next execution."\n}", "failOnError": false }, "id": "d5213b0b-a44d-4c88-a046-913e82d7bcc6", "type": "connector", "name": "json-generator-connector" } ], "wait until next migration": [ { "stepName": "Log - wait until next migration", "params": { "logLevel": "INFO", "message": "esperar" }, "id": "fc73381f-54e8-44f3-b830-3f2a541a183b", "type": "connector", "name": "log-connector" }, { "stepName": "set output", "params": { "json": "{\n "mensagem": {{ CONCAT("Migration will begin at ", FORMATDATE( message.control.nextExecutionTimestamp, "timestamp", "dd/MM/yyyy HH:mm:ss")) }}\n}", "failOnError": false }, "id": "f354b82b-8518-4509-b703-04133ed767d3", "type": "connector", "name": "json-generator-connector" } ], "nextExecutionTimestamp is null": [ { "stepName": "Log - nextExecutionTimestamp is null", "params": { "logLevel": "WARN", "message": "nextExecution nulo" }, "id": "c3b39a8c-3bbf-4f4e-aad8-f2851e26d34a", "type": "connector", "name": "log-connector" }, { "stepName": "update pagination parameters", "accountLabel": "dgb-internal-object-store-account", "params": { "operation": "UPDATE", "objectStore": "pagination-OS-example", "objectId": "{{ CONCAT(metadata.pipeline.name, "_v" , metadata.pipeline.versionMajor) }}", "document": "{\n $set:{\n "nextExecutionTimestamp": {{ FORMATDATE(FORMATDATE(SUMDATE( NOW() , "DAY", 1), "timestamp", "dd/MM/yyyy 01:00:00"), "dd/MM/yyyy HH:mm:ss", "timestamp") }}\n }\n}", "unique": true, "isolated": false, "upsert": true, "failOnError": false }, "id": "f23200ca-8203-4adf-a07d-8bc779088bc5", "type": "connector", "name": "object-store-connector" } ], "error - unknown step": [ { "stepName": "Log - error unkown step", "params": { "logLevel": "ERROR", "message": "Error processing message {{ message.$.myField }}" }, "id": "d25738e8-bd93-4f53-9d1e-ccd9a255e852", "type": "connector", "name": "log-connector" }, { "type": "connector", "name": "throw-error-connector", "stepName": "Throw Error", "params": { "errorMessage": "Error occurred.", "errorCode": 500, "customErrorEnabled": false, "failOnError": true }, "id": "f7a84b35-d8ff-4750-adf7-f71a97e05acd" } ] } }