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.
- Open ODBC DSN. Create a new DSN using ZappySys JSON Driver
- Loading below connection string using these steps. Change UI values as needed e.g. URL, Table Names Authentication etc
DRIVER={ZappySys JSON Driver};DataPath='https://YOUR-SHOP.myshopify.com/admin/api/2023-10/graphql.json';DataConnectionType='HTTP';Url='https://YOUR-SHOP.myshopify.com/admin/api/2023-10/graphql.json';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: */*'
- Run below Query
SELECT * FROM $
WITH (
--URL='/graphql.json'
--OR Use full URL
Src='https://YOUR-SHOP.myshopify.com/admin/api/2023-10/graphql.json'
, 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 {
id
title
createdAt
}
pageInfo {
hasNextPage
endCursor
}
}
},FUN_JSONENC>>"
}'
, 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