Access Apache Superset APIs from Invantive PSQL

Wiki (en)
,
1

This topic explains how to access the Apache Superset APIs from Invantive PSQL. Using Invantive PSQL the rows of a data set can be retrieved.

The Superset user interface uses the Superset APIs (see API | Superset). By using the Chrome Devtools or similar it is quite easy to determine the API calls needed.

Execute query

Use the following call to execute a query. First an access token is acquired, then the CSRF-token and session cookie. With these, a random query can be executed:

declare
  --
  -- Superset server: protocol, host and port.
  --
  g_server_url   varchar2 := 'https://superset.acme.com:8088'
  --
  -- Superset user name.
  --
  g_username     varchar2 := 'john.doe@acme.com';
  --
  -- Superset user password.
  --
  g_password     varchar2 := 'secret';
  --
  -- Query to run.
  --
  g_query        varchar2 := 'SELECT * FROM `myschema`.tablename LIMIT 5';
  --
  l_access_token varchar2;
  l_csrf_token   varchar2;
  l_query        varchar2;
  l_xml          varchar2;
  l_download_uid varchar2;
  l_cookie_value varchar2;
begin
  select jte.access_token
  into   l_access_token
  from   HttpDownload@DataDictionary
         ( url => g_server_url || '/api/v1/security/login'
         , method => 'POST'
         , acceptMimeType => 'application/json'
         , contentType => 'application/json'
         , textPayload 
           => '{' 
           || jsonelement('username', g_username, false) 
           || ',' || jsonelement('provider', 'db') 
           || ',' || jsonelement('refresh', true) 
           || ',' || jsonelement('password', g_password, false) 
           || '}'
         ) htp
  join   jsontable
         ( ''
           passing htp.contents_char
           columns access_token varchar2 path 'access_token'
           ,       refresh_token varchar2 path 'refresh_token'
        ) jte
  ;
  --
  -- Get CSRF token.
  --
  -- The cookie with name 'session' is also needed.
  --
  select jte.result
  ,      htp.uid
  into   l_csrf_token
  ,      l_download_uid
  from   HttpDownload@DataDictionary
         ( url => g_server_url || '/api/v1/security/csrf_token/'
         , method => 'GET'
         , acceptMimeType => 'application/json'
         , contentType => 'application/json'
         , headers => 'Authorization=Bearer ' || l_access_token
         ) htp
  join   jsontable
         ( ''
           passing htp.contents_char
           columns result varchar2 path 'result'
        ) jte
  ;
  --
  -- Get the session cookie.
  --
  select value
  into   l_cookie_value
  from   HttpDownloadCookiesHistory@datadictionary
  where  download_uid = l_download_uid
  and    name = 'session'
  ;
  --
  -- Construct query.
  --
  l_query :=
    '{'
    || jsonelement('client_id', null, false)
    || ','
    || jsonelement('ctas_method', null, false)
    || ','
    || jsonelement('database_id', 1, false)
    || ','
    || jsonelement('expand_data', true, false)
    || ','
    || jsonelement('json', true, false)
    || ','
    || jsonelement('queryLimit', 0, false)
    || ','
    || jsonelement('runAsync', false, false)
    || ','
    || jsonelement('schema', null, false)
    || ','
    || jsonelement('select_as_cta', false, false)
    || ','
    || jsonelement('sql', g_query, false)
    || ','
    || jsonelement('sql_editor_id', null, false)
    || ','
    || jsonelement('tab', null, false)
    || ','
    || jsonelement('templateParams', null, false)
    || ','
    || jsonelement('tmp_table_name', null, false)
    || '}'
  ;
  dbms_output.put_line('*** Credentials ***');
  dbms_output.put_line('Access token: ' || l_access_token);
  dbms_output.put_line('CSRF token: ' || l_csrf_token);
  dbms_output.put_line('Cookie session: ' || l_cookie_value);
  dbms_output.put_line('Query: ' || l_query);
  --
  select *
  into   l_xml
  from   HttpDownload@DataDictionary
         ( url => g_server_url ||'/api/v1/sqllab/execute/'
         , method => 'POST'
         , acceptMimeType => 'application/json'
         , contentType => 'application/json'
         , headers
           => 'Authorization=Bearer ' || l_access_token
           || '&'
           || 'X-CSRF-Token=' || l_csrf_token
         , cookies => 'session=' || l_cookie_value
         , textpayload => l_query
         ) htp
  join   jsontable
         ( 'data[*]'
           passing htp.contents_char
           columns id           varchar2 path 'id'
           ,       name         varchar2 path 'name'
           ,       registerDate datetime path 'registerDate'
        ) jte
  for xml auto
  ;
  dbms_output.put_line('Results for example: ' || l_xml);
end;

Please note, that as of release 24.0 PSQL does not have a yamltable to parse or for yaml to export YAML.