Call GraphQL API (Shopify Example) using ODBC Driver for JSON / REST - Power BI, Informatica and Other Apps

In our previous post we saw how to Call GraphQL API in SSIS. Now lets look at how to call GraphQL API in any ODBC / JAVA Apps.

Check this link to odbc apps for BI / ETL / Reporting / Programming.

Once you configure JSON DSN using these steps you can run the following style query.

Below is a Shopify GraphQL API example but feel free to change as per your need for any other API.

  1. Open ODBC DSN. Create a new DSN using ZappySys JSON Driver
  2. Loading below connection string using these steps. Change UI values as needed e.g. URL, Table Names Authentication etc
DRIVER={ZappySys JSON Driver};DataPath='';DataConnectionType='HTTP';Url='';TokenAuthHeader='X-Shopify-Access-Token';CredentialType='Token';Password='shpat_296e0ebbxxxxxxxxx';AuthScheme='{none}';Filter='$.data.products.nodes';IncludeParentColumns=0;RequestContentTypeCode='ApplicationJson';RequestMethod='POST';NextUrlAttributeOrExpr='$.data.products.pageInfo.endCursor';NextUrlEndIndicator='false';StopIndicatorAttributeOrExpr='$.data.products.pageInfo.hasNextPage';NextPageBodyPart='after: \"[$pagetoken$]\"';HasDifferentNextPageInfo=1;EnablePageTokenForBody=1;RequestHeaders='cache-control: no-cache || Accept: */*'
  1. Run below Query
  --OR Use full URL
  , IncludeParentColumns=0
  , Method='POST'
  , Filter='$.data.products.nodes' --change table name here e.g. products
  , Header='Content-Type: application/json || X-Shopify-Access-Token: shpat_296e0xxxxxxxxx'
  --change table name and columns below here e.g. products... and id, title etc
  -- change pagesize if needed (i.e. max 250)
  , Body='{
 "query" : "<<{
 products(first: 250 [$tag$])
    nodes {
    pageInfo {
	, NextUrlAttributeOrExpr='$.data.products.pageInfo.endCursor' --change table name 
	, NextUrlEndIndicator='false'
	, StopIndicatorAttributeOrExpr='$.data.products.pageInfo.hasNextPage' --change table name 
	, EnablePageTokenForBody='True'
	, HasDifferentNextPageInfo='True'
	, NextPageBodyPart='after: \"[$pagetoken$]\"'
	--Use metadata to speed up execution. To get Metadata Run query without Meta clause. 
	-- Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
	--, Meta='id:String(255); title:String(255); createdAt:DateTime; '

Load GraphQL API data in Power BI

Now lets look at how to load GraphQL data in Power BI. For detailed steps check this article