HubSpot Pagination pattern not supported by Azure Data Factory?

[JBorn]

Hi HubSpot!  I'm trying to use a Azure Data Factory to incrementally pull data into our SQL database.  I found that I can use the V3 Search to find all the contact modified after my last pull which will give me my incremental pull, and I can easily specify the contact properties that I'd like to sync.  Now the only problem is when there are over 100 contacts (max limit is 100) I need to page through the result set and ADFv2 doesn't support the pagination pattern on you API call as far as I can tell.  Here is the ADFv2 documentation about this: https://docs.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#pagination-supp... 

Right now this is what the documentation says:

This generic REST connector supports the following pagination patterns:

• Next request’s absolute or relative URL = property value in current response body
• Next request’s absolute or relative URL = header value in current response headers
• Next request’s query parameter = property value in current response body
• Next request’s query parameter = header value in current response headers
• Next request’s header = property value in current response body
• Next request’s header = header value in current response headers

And from your site, after is a body parameter.

My question is there any way this could be expanded to be either a query parameter or header to allow integrations with ADFv2?

[KyleParker · Accepted solution]

@JBorn I'm currently working on a similar implementation and here's how I tackled that problem:

Define initial variables:

* "After" - 0

* "PagingFound" - true

* "TempPageCount" - 1

* "CurrentPageCount" - 1

* "TotalPageCount" - 0

Until Activity

* Settings -> Expression > @equals(variables('PagingFound'), 'false')

Inside Until Activity

* Copy Data Activity > HubSpot Search API to DataLake

Request method: POST

Request body: 

@{concat('{
"limit": ', variables('Limit'), ',
"after": ',variables('After'),',
    "filterGroups": [
        {
            "filters": [
                {
                    "operator": "GTE",
                    "propertyName": "lastmodifieddate",
                    "value": "',variables('LastModifiedDate'),'"
                }
            ]
        }
    ],
  "properties":[',variables('HubSpotContactProperties'),'],
    "sorts": [
        "string"
    ]
}')} 

Sink: JSON file with FileName appending the CurrentPageCount to create a new file for each batch from HubSpot

On Success for the Copy Data Activity > Lookup Activity to get the First Row only of that file - need to determine if it contains the "paging" object in the JSON.

On Failure for the Copy Data Activity > Set Variable - "PagingFound" = false

On Success for the Lookup Activity > If Condition Activity

* @if(contains(activity('Result count').output.firstRow, 'paging'), true, false)

On Failure for the Lookup Activity > Set Variable - "PagingFound" = false

If Condition Activity > True

* Set PagingFound = true

* Set After = @{activity('Result count').output.firstrow.paging.next.after}

* Set TempPageCount = @string(add(int(variables('CurrentPageCount')), 1))

* Set CurrentPageCount = TempPageCount

If Condition Activity > False > Set Variable - PagingFound = false

Main Pipeline

Until Activity

After the Until Activity completes, I reset the page counts,. then iterate over the DataLake files and insert those in my Azure SQL tables. It's a bit of a hack and additional work around, but it was the only way to pass the "After" to the Request Body for the Search API call.

Let me know if you have any questions. So far this implementation has worked well for our needs (small delta sets - large datasets of 100K+ records would be pretty brutal using the API, regardless of the implementation).

[JBoon6]

This has been super useful, Thanks Kyle 🙂

[dennisedson]

@johnelmer ,

Once again, mind helping out 🙏

[johnelmer]

@dennisedson Sounds like a question for the HubSpot API devs.