I need to get all of the records from hubspot's api but pagination is where i am stuck.
As per their api documentation vidOffset and has-more parameters are the ones that we need to use to create pagination.
Now when i run my rest connection i get only 20 records by default and 100 max with a offset value that tells me where i am in the contacts records.So in order to get next set of records i need to start from the last returned offset value untill "has-more" becomes false which tells me that there are no more records i can pull out from the portal.
I need to do a DoWhile loop to get this done until "has-more" becomes false to get all of the contacts in hubspot.
here's my script.
LIB CONNECT TO 'HubSpot (basware_pankajkaun)';
// Action required: Implement the logic to retrieve the total records from the REST source and assign to the 'total' local variable.
Let total = 35000;
Let vidOffset = 0;
Let startAt = 0;
Let pageSize = 100;
for startAt = 0 to total step pageSize
RestConnectorMasterTable:
SQL SELECT
"has-more",
"vid-offset",
"__KEY_root",
(SELECT
"addedAt",
"vid" AS "vid_u0",
"canonical-vid",
"portal-id",
"is-contact",
"profile-token",
"profile-url",
"__KEY_contacts",
"__FK_contacts",
(SELECT
"@Value",
"__FK_merged-vids"
FROM "merged-vids" FK "__FK_merged-vids" ArrayValueAlias "@Value"),
(SELECT
"__KEY_properties",
"__FK_properties",
(SELECT
"value",
"__FK_firstname"
FROM "firstname" FK "__FK_firstname"),
(SELECT
"value" AS "value_u0",
"__FK_lastmodifieddate"
FROM "lastmodifieddate" FK "__FK_lastmodifieddate")
FROM "properties" PK "__KEY_properties" FK "__FK_properties"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_form-submissions"
FROM "form-submissions" FK "__FK_form-submissions" ArrayValueAlias "@Value_u0"),
(SELECT
"vid",
"saved-at-timestamp",
"deleted-changed-timestamp",
"__KEY_identity-profiles",
"__FK_identity-profiles",
(SELECT
"type",
"value" AS "value_u1",
"timestamp",
"is-primary",
"__FK_identities"
FROM "identities" FK "__FK_identities")
FROM "identity-profiles" PK "__KEY_identity-profiles" FK "__FK_identity-profiles"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_merge-audits"
FROM "merge-audits" FK "__FK_merge-audits" ArrayValueAlias "@Value_u1")
FROM "contacts" PK "__KEY_contacts" FK "__FK_contacts")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://api.hubapi.com/contacts/v1/lists/all/contacts/all?property=firstname");
// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source.
// Please see the documentation for "Loading paged data."
NEXT startAt;
[merged-vids]:
LOAD [@Value] AS [@Value],
[__FK_merged-vids] AS [__KEY_contacts]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_merged-vids]);
[firstname]:
LOAD [value] AS [value],
[__FK_firstname] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_firstname]);
[lastmodifieddate]:
LOAD [value_u0] AS [value_u0],
[__FK_lastmodifieddate] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_lastmodifieddate]);
[properties]:
LOAD [__KEY_properties] AS [__KEY_properties],
[__FK_properties] AS [__KEY_contacts]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_properties]);
[form-submissions]:
LOAD [@Value_u0] AS [@Value_u0],
[__FK_form-submissions] AS [__KEY_contacts]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_form-submissions]);
[identities]:
LOAD [type] AS [type],
[value_u1] AS [value_u1],
[timestamp] AS [timestamp],
[is-primary] AS [is-primary],
[__FK_identities] AS [__KEY_identity-profiles]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_identities]);
[identity-profiles]:
LOAD [vid] AS [vid],
[saved-at-timestamp] AS [saved-at-timestamp],
[deleted-changed-timestamp] AS [deleted-changed-timestamp],
[__KEY_identity-profiles] AS [__KEY_identity-profiles],
[__FK_identity-profiles] AS [__KEY_contacts]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_identity-profiles]);
[merge-audits]:
LOAD [@Value_u1] AS [@Value_u1],
[__FK_merge-audits] AS [__KEY_contacts]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_merge-audits]);
[contacts]:
LOAD [addedAt] AS [addedAt],
[vid_u0] AS [vid_u0],
[canonical-vid] AS [canonical-vid],
[portal-id] AS [portal-id],
[is-contact] AS [is-contact],
[profile-token] AS [profile-token],
[profile-url] AS [profile-url],
[__KEY_contacts] AS [__KEY_contacts],
[__FK_contacts] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_contacts]);
[root]:
LOAD [has-more] AS [has-more],
[vid-offset] AS [vid-offset],
[__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
DROP TABLE RestConnectorMasterTable;
Basically, I need to get all of the records from API.
