Calling An API In Batches

Often the need arises to call an API in batches, loading for example 1000 objects at a time. For example, the DV360 advertisers.lineItems.bulkListAssignedTargetingOptions can take list of lineItemIds[] but if you pass all the ids in, an error indicates:

Number of line item IDs in the request exceeds the maximum allowed limit of "50".

BQFlow allows you to batch call the API. Under the hood it will make multiple API calls and serialize the data into the same BigQuery table specified in the workflow. Since every call has the same schema, it all just works. To accomplish batched calls, the BQFlow task below performs several steps:

1. The inner query numbers each row uniquely within each advertiser api batch parameter.
2. The outer query uses ARRAY_AGG to batch several ids into the lineItemIds[] parameter.
3. The outer call also uses the PARTITION OVER windowing feature to get the current row plus 49 following rows.
4. The outer rows are QUALIFIED to grab only the 50th row so our batches don't overlap.
5. The MOD has to be 1 because ROW_NUMBER starts at 1.

{ "google_api": {
  "description":"Pull lineitem targeting",
  "api": "displayvideo",
  "version": "v3",
  "auth": "user",
  "function": "advertisers.lineItems.bulkListAssignedTargetingOptions",
  "iterate": true,
  "kwargs_remote": {
    "bigquery": {
      "auth": "user",
      "dataset": "example_dv360",
      "query": "SELECT
        CAST(advertiserId AS STRING) AS advertiserId,
        ARRAY_AGG(lineItemId) OVER(PARTITION BY advertiserId ORDER BY num ASC RANGE BETWEEN CURRENT ROW AND 49 FOLLOWING) AS lineItemIds,
        'targetingType=TARGETING_TYPE_GEO_REGION OR targetingType=TARGETING_TYPE_PROXIMITY_LOCATION_LIST OR targetingType=TARGETING_TYPE_REGIONAL_LOCATION_LIST' AS filter,
        5000 AS pageSize
      FROM ( 
        SELECT
          advertiserId,
          lineItemId,
          ROW_NUMBER() OVER(PARTITION BY advertiserId ORDER BY lineItemId ASC) AS num,
        FROM `DV360_LineItems`
      )   
      QUALIFY MOD(num, 50) = 1"
    }   
  },  
  "results": {
    "bigquery": {
      "auth": "user",
      "dataset": "example_dv360",
      "table": "DV360_LineItems_Targeting"
    }   
  }   
}}