Run queries on Shopify API

Wiki (en)
,
1

At this moment, there is no integrated SQL or Power BI driver for Shopify with Invantive Cloud.

However, the Shopify API is so uncomplicated that it is easy to retrieve data. The basics is to retrieve the JSON payload as documented in the API, using a user/password combination:

select *
from   HTTPDOWNLOAD
       ( 'https://USER:SECRET@SUBDOMAIN.myshopify.com/admin/api/2020-01/orders/123123123.json'
       , 'application/json'
       , 'TEST'
       , null
       , null
       , 'GET'
       , null
       , false
       , null
       )@DataDictionary htp

The resulting JSON can then easily be fed into jsontable:

select ord.*
from   HTTPDOWNLOAD(...)@DataDictionary htp
join   jsontable
       ( 'order'
         passing src.contents_char
         columns email varchar2 path 'email'
         ,       shipping_first_name varchar2 path 'shipping_address.first_name'
         ,       shipping_last_name  varchar2 path 'shipping_address.last_name'
         ,       shipping_address1   varchar2 path 'shipping_address.address1'
         ,       shipping_phone varchar2 path 'shipping_address.phone'
         ,       shipping_city varchar2 path 'shipping_address.city'
         ,       shipping_zip varchar2 path 'shipping_address.zip'
         ,       shipping_province varchar2 path 'shipping_address.province'
         ,       shipping_country varchar2 path 'shipping_address.country'
         ,       shipping_address2 varchar2 path 'shipping_address.address2'
         ,       shipping_company varchar2 path 'shipping_address.copmpany'
         ,       shipping_country_code varchar2 path 'shipping_address.country_code'
         ,       shipping_province_code varchar2 path 'shipping_address.province_code'
       )
       ord