WeFact Data extracts to SQL Server

We want to extract WeFact (WeFact API webservice - WeFact) data into a SQL Database. What would be the easiest way to do this?

they provide PHP on-the-shelve routines.
I see they use an API Key logic.

so if your are good at sql programming you can build something using httpdownload@DataDictionary with POST or GET

or submit improvement for native support … SQL/Power BI driver WeFact

2 Likes

The WeFact API has rate limits:

  • Minutely: 200
  • Hourly: 3600

Also, the WeFact API returns HTTP status 200 in violation of the W3C specifications when an error occurs. It is necessary to inspect the payload.

A sample on settings is:

declare
  g_api_key             varchar2 := 'SECRETKEY';
  --
  l_retrieval_succesful boolean;
  l_http_status_code    int;
  l_contents            varchar2;
  l_error_cnt           pls_integer;
begin
  select RETRIEVAL_SUCCESSFUL
  ,      HTTP_STATUS_CODE
  ,      CONTENTS_CHAR
  into   l_retrieval_succesful
  ,      l_http_status_code
  ,      l_contents
  from   HTTPDOWNLOAD@DataDictionary
         ( url => 'https://api.mijnwefact.nl/v2/'
         , method => 'POST'
         , contentType => 'application/x-www-form-urlencoded'
         , textPayload => 'api_key=' || urlencode(g_api_key)
                          || '&controller=' || urlencode('settings')
                          || '&action=' || urlencode('list')
         , minimumDurationMs => 1000 * 3600 / 3600 /* 3600 calls per hour. */
         )
  ;
  --
  -- Any error returned?
  --
  select count(*)
  into   l_error_cnt
  from   jsontable
         ( 'errors[*]'
           passing l_contents
           columns error_text varchar2 path '::self'
         )
  ;
  if not l_retrieval_succesful 
     or l_http_status_code != 200 
     or l_error_cnt != 0
  then
    wefact_print_generic_payload(l_contents);
  else
    for r
    in
    ( select *
      from   jsontable
             ( 'data[*]'
               passing l_contents -- Such as '{"controller":..., "data": [{"identifier": "1"}, {"identifier": "2"}]}'
               columns identifier varchar2 path 'identifier'
             )
    )
    loop
      dbms_output.put_line('ID: ' || r.identifier);
    end loop;
  end if;
end;

The PSQL-procedure for printing generic information is:

create or replace procedure wefact_print_generic_payload
( l_contents varchar2
)
is
  l_controller           varchar2;
  l_action               varchar2;
  l_status               varchar2;
  l_date                 date;
  l_totalresults         int;
  l_currentresults       int;
  l_offset               int;
  l_error_header_printed boolean;
begin
  select t.controller
  ,      t.action
  ,      t.status
  ,      t.date
  ,      t.totalresults
  ,      t.currentresults
  ,      t.offset
  into   l_controller
  ,      l_action
  ,      l_status
  ,      l_date
  ,      l_totalresults
  ,      l_currentresults
  ,      l_offset
  from   jsontable
         ( ''
           passing l_contents
           columns controller     varchar2 path 'controller'
           ,       action         varchar2 path 'action'
           ,       status         varchar2 path 'status'
           ,       date           datetime path 'date'
           ,       totalresults   int      path 'totalresults'
           ,       currentresults int      path 'currentresults'
           ,       offset         int      path 'offset'
         ) t
  ;
  dbms_output.put_line('*** Generic Payload ***');
  if l_controller is not null
  then
    dbms_output.put_line('Controller: ' || l_controller);
  end if;
  if l_action is not null
  then
    dbms_output.put_line('Action: ' || l_action);
  end if;
  if l_status is not null
  then
    dbms_output.put_line('Status: ' || l_status);
  end if;
  if l_date is not null
  then
    dbms_output.put_line('Date: ' || l_date);
  end if;
  if l_totalresults is not null
  then
    dbms_output.put_line('Total Results: ' || l_totalresults);
  end if;
  if l_currentresults is not null
  then
    dbms_output.put_line('Current Results: ' || l_currentresults);
  end if;
  if l_offset is not null
  then
    dbms_output.put_line('Offset: ' || l_offset);
  end if;
  --
  -- Print errors (if any).
  --
  l_error_header_printed := false;
  for r
  in 
  ( select error_text
    from   jsontable
           ( 'errors[*]'
             passing l_contents
             columns error_text varchar2 path '::self'
           )
  )
  loop
    if not l_error_header_printed
    then
      l_error_header_printed := true;
      dbms_output.put_line('Errors:');
    end if;
    dbms_output.put_line('* ' || r.error_text);
  end loop;
end;

A sample retrieving all debtors (with paging) is:

declare
  g_api_key             varchar2    := 'SECRET';
  --
  -- Choose page size between 1 and 10.000.
  --
  g_page_size           pls_integer := 10000;
  --
  l_offset              pls_integer;
  l_totalresults        pls_integer;
  l_done                boolean;
  l_retrieval_succesful boolean;
  l_http_status_code    int;
  l_contents            varchar2;
  l_error_cnt           pls_integer;
begin
  l_done         := false;
  l_totalresults := null;
  l_offset       := 0;
  --
  -- Create an empty table to load the data into.
  --
  -- Replace @inmemorystorage by the data container alias of for instance
  -- a SQL Server database.
  -- 
  create or replace table we_fact_debtors@inmemorystorage
  ( identifier   varchar2
  , debtor_code  varchar2
  , company_name varchar2
  , first_name   varchar2
  , last_name    varchar2
  );
  --
  while not l_done
  loop
    dbms_output.put_line('Retrieve ' || to_char(g_page_size) || ' rows starting at offset ' || to_char(l_offset) || '.');
    select RETRIEVAL_SUCCESSFUL
    ,      HTTP_STATUS_CODE
    ,      CONTENTS_CHAR
    into   l_retrieval_succesful
    ,      l_http_status_code
    ,      l_contents
    from   HTTPDOWNLOAD@DataDictionary
           ( url => 'https://api.mijnwefact.nl/v2/'
           , method => 'POST'
           , contentType => 'application/x-www-form-urlencoded'
           , textPayload => 'api_key=' || urlencode(g_api_key)
                            || '&controller=' || urlencode('debtor')
                            || '&action=' || urlencode('list')
                            || '&limit=' || urlencode(g_page_size)
                            || '&offset=' || urlencode(l_offset)
           , minimumDurationMs => 1000 * 3600 / 3600 /* 3600 calls per hour. */
           )
    ;
    --
    -- Any error returned?
    --
    select count(*)
    into   l_error_cnt
    from   jsontable
           ( 'errors[*]'
             passing l_contents
             columns error_text varchar2 path '::self'
           )
    ;
    if not l_retrieval_succesful 
       or l_http_status_code != 200 
       or l_error_cnt != 0
    then
      wefact_print_generic_payload(l_contents);
      exit;
    else
      --
      -- Sample 1: print the data row-by-row.
      --
      for r
      in
      ( select *
        from   jsontable
               ( 'debtors[*]'
                 passing l_contents
                 columns identifier    varchar2 path 'Identifier'
                 ,       debtor_code   varchar2 path 'DebtorCode'
                 ,       company_name  varchar2 path 'CompanyName'
                 ,       gender        char     path 'Sex'
                 ,       first_name    varchar2 path 'Initials'
                 ,       last_name     varchar2 path 'SurName'
                 ,       email_address varchar2 path 'EmailAddress'
                 ,       modified_date datetime path 'Modified'
               )
      )
      loop
        dbms_output.put_line(r.identifier || ' - ' || r.debtor_code || ': ' || r.company_name || ' (' || r.first_name || ' ' || r.last_name || ')');
      end loop;
      --
      -- Sample 2: bulk load into table in-memory, SQL Server, Oracle, PostgreSQL or MySQL:
      --
      bulk insert into we_fact_debtors@inmemorystorage
      ( identifier
      , debtor_code
      , company_name
      , first_name
      , last_name
      )
      select identifier
      ,      debtor_code
      ,      company_name
      ,      first_name
      ,      last_name
      from   jsontable
             ( 'debtors[*]'
               passing l_contents
               columns identifier    varchar2 path 'Identifier'
               ,       debtor_code   varchar2 path 'DebtorCode'
               ,       company_name  varchar2 path 'CompanyName'
               ,       gender        char     path 'Sex'
               ,       first_name    varchar2 path 'Initials'
               ,       last_name     varchar2 path 'SurName'
               ,       email_address varchar2 path 'EmailAddress'
               ,       modified_date datetime path 'Modified'
             )
      ;
    end if;
    --
    -- Request next page when there is one.
    -- Number of rows in result set doesn't change after initial retrieval.
    --
    if l_totalresults is null
    then
      select t.totalresults
      into   l_totalresults
      from   jsontable
             ( ''
               passing l_contents
               columns totalresults   int      path 'totalresults'
             ) t
      ;    
    end if;
    --
    l_offset := l_offset + g_page_size;
    l_done := l_offset > l_totalresults;
  end loop;
end;

Output is printed to the DBMS output in the first sample application.

The second sample application is to load the database into a database table:

image

WeFact API Performance

Retrieving approximately 7500 debtors is a nice number to assess the performance of the WeFact API across various page sizes, including loading into the in-memory database table.

The following table lists runtime depending on page size:

Page Size Runtime (sec) Throughput (rows/sec)
100 82 91
1.000 12.0 624
7.500 5.9 1.271
10.000 5.5 1.363

Please note that also the rate limiters are not on rows but on the number of API calls. The best approach with WeFact is to use the largest page size available.

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.