If you're using Facebook API, it's important to remember that by default, most responses to API requests are paginated by default. In this article, we describe how to work with paged results.

Facebook will return a next and previous element in every request, depending on if there are pages left to fetch:

• next: the Graph API endpoint that will return the next page of data. If not included, this is the last page of data. Due to how pagination works with visibility and privacy it is possible that a page may be empty but contain a 'next' paging link - you should stop paging when the 'next' link no longer appears.
• previous: the Graph API endpoint that will return the previous page of data. If not included, this is the first page of data.

To handle this in Data Virtuality, you can write a simple stored procedure using the initial endpoint, e.g. https://graph.facebook.com/v2.5/<fb_ads_number>/insights?limit=100:

create VIRTUAL PROCEDURE views.my_proc_facebook_insights( IN "endpoint" string )
RETURNS(
  "date_start" string,
  "date_stop" string,
  "account_id" string,
   "account_name" string,
  "ad_id" string,
  "ad_name" string,
  "campaign_id" string,
  "campaign_name" string,
  "adset_id" string,
  "adset_name" string,
   "country" string
)
AS
BEGIN
CREATE local temporary table tmpdata(
  chunkid long,
  "date_start" string,
  "date_stop" string,
  "account_id" string,
  "account_name" string,
  "ad_id" string,
  "ad_name" string,
  "campaign_id" string,
  "campaign_name" string,
  "adset_id" string,
  "adset_name" string,
  "country" string,
  "next" string
);
DECLARE string "current_endpoint" = NVL( "endpoint", 'https://graph.facebook.com/v2.5/<fb_ads_number>/insights?limit=1000' );
DECLARE long chunkid = 0;
WHILE( "current_endpoint" IS NOT NULL AND "current_endpoint" != '' )
BEGIN
 INSERT INTO tmpdata
 SELECT
   chunkid as "chunkid",
   "xml_table.date_start",
   "xml_table.date_stop",
   "xml_table.account_id",
   "xml_table.account_name",
   "xml_table.ad_id",
   "xml_table.ad_name",
   "xml_table.campaign_id",
   "xml_table.campaign_name",
   "xml_table.adset_id",
   "xml_table.adset_name",
   "xml_table.country",
   "xml_table.next"
 FROM
 (exec "facebook_src".invokeHTTP(action=>'GET',
requestContentType=>'application/xml',endpoint=>current_endpoint)) w,
 XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as 
"xsi" ),'/root/data' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
 COLUMNS
   "date_start" STRING PATH 'date_start',
   "date_stop" STRING PATH 'date_stop',
   "account_id" STRING PATH 'account_id',
   "account_name" STRING PATH 'account_name',
   "ad_id" STRING PATH 'ad_id',
   "ad_name" STRING PATH 'ad_name',
   "campaign_id" STRING PATH 'campaign_id',
   "campaign_name" STRING PATH 'campaign_name',
   "adset_id" STRING PATH 'adset_id',
   "adset_name" STRING PATH 'adset_name',
   "country" STRING PATH 'country',
   "next" STRING PATH '../paging/next'
 ) "xml_table" ;
 IF( ( select max("chunkid") from tmpdata ) < "chunkid" )
   "current_endpoint" = NULL;
 ELSE
   "current_endpoint" = select next from tmpdata order by chunkid desc limit 1;
 chunkid = chunkid + 1;
END
select
  "date_start",
  "date_stop",
  "account_id",
  "account_name",
  "ad_id",
  "ad_name",
  "campaign_id",
  "campaign_name",
  "adset_id",
  "adset_name",
  "country"
from tmpdata;