Looking for Best Practise regarding loading data from an GraphQL-api via REST connector
Hi all. I'm looking for some suggestions/advise on how to extract data from an GraphQL api where the data needs to be explored and adapted during development.
The flexibility of the api is making it extremely difficult to iterate since the script most likely will be generated differently each time the query is altered. I'd like to stay away from attempting to write everything by hand.
My strategy, when working with regular REST api:s, is to keep each script untouched, save all tables with the names the script generated, and do the joins and renaming in the transform script.
This works but once the iterations start, I have a hard time aligning the old and the new names and structures. This is a huge api and I'm tackling the most complex part of it with paging and "cost" constraints for the queries.
So, the strength of GraphQL when it comes to frontend development and flexibility make it a nightmare to work with in Qlik.
Working with the current rather dx-unfriendly REST connector can be a challenge at times, but this is at a completely different level.
Any suggestion to keep me sane are appreciated! /lars
One approach worth considering, depending on your environment, is to stop trying to handle GraphQL in the Qlik Cloud REST Data Connector altogether and let a scripting layer deal with it instead.
The idea is simple: a Python script (or whatever language fits your stack) talks to the GraphQL API. It handles the query, the pagination, the cost tracking, and any retry logic. The output is just flat CSV files dropped into Cloud Storage Service - AWS, Azure, Dropbox, etc - or even in a on-prem server with Qlik Data Gateway installed to expose the files to Qlik Cloud.
So, when your GraphQL query changes, you fix the Python script. Qlik doesn't care. The contract between the two sides is just the CSV column names, and you control those.
It also buys you proper tooling for the hard parts: cost budgeting, pagination loops, logging, and debugging in an actual IDE instead of inside the script editor.
Where you run it depends on what you have available. AWS Lambda, Azure Functions, even a scheduled task somewhere. If you're already inside a cloud environment there's usually an obvious place to hook it in.
Might be overkill depending on how locked-in you are to the REST connector approach, but for a large API with cost constraints and changing queries, the separation of concerns tends to be worth the setup. Happy to go deeper if this direction makes sense for your setup.
I briefly considered this option so thank you for confirming that it is a way forward.
If this wasn't for a customer who wants to minimize dependencies and they want the solution to be self-contained within Qlik.
The current solution "works", but since the API is so huge, each query isn't finalized and inspiration change and evolves the scope. The business logic is in the original client, and I/we have to try to "discover" many of the rules as we go.
I think the only way forward is to minimize the queries. Instead of trying to fetch nested structures, I will do many shallower fetches instead. So instead of manipulating, as in recreate, a working query another query with the id and the new field/structures are added. That means no changing and if more fields or structures are needed, that will be its own isolated handling. Never change anything, add, and eventually disable.
The total load time will increase but perhaps that enables me to make incremental loads once the queries stabilize.
I will definitely build a POC to evaluate...
The world is moving to APIs of varying quality, and the DX and features for the REST connector is more or less unchanged. It is quirky to work with for anything beyond demos or simple plain table extracts.
You have hit on a classic data engineering pain point: GraphQL is a dream for front-end developers because they can request exactly what they need, but it is a total nightmare for relational, schema-first BI tools like Qlik because the schema changes dynamically based on the query payload.
Combine that with paging, nesting, and complexity/cost-limiting budgets, and the native Qlik REST Connector will quickly drive you crazy trying to auto-generate load scripts.
Here are a few strategies to keep you sane, ranked from architectural changes to pure Qlik script workflows.
Strategy 1: The "Middleman" Approach (Highly Recommended)
If you can step outside of Qlik for the extraction phase, do it. Trying to handle complex GraphQL pagination, query cost tracking, and dynamic schema parsing directly inside Qlik's native script is like using a hammer to turn a screw.
Instead, build a lightweight Python script (or use an ETL tool like Pentaho/Talend) to act as a buffer:
The Middleman Script handles the complex POST requests, manages the GraphQL edge/node pagination, and respects the API's cost limits.
It flattens the deeply nested JSON response into a consistent, relational format.
It dumps the data into flat files (CSV or JSON lines) or a staging database.
Qlik simply reads those static staging files.
Why this saves your sanity: When the GraphQL query inevitably changes during development, you only change the query string in Python. Python handles the dynamic JSON structure flawlessly, and you can output a predictable file format for Qlik.
Strategy 2: If You Must Stay in Qlik, Use a "Manifest/Mapping Table"
If you are strictly limited to the native Qlik REST Connector and cannot use an external tool, you need to decouple the API script generation from your Transform layer.
Instead of letting Qlik auto-generate a giant, unreadable nested block of SELECT statements that breaks every time you add a field, map your fields explicitly using a control table.
1. The Raw Dump (Extractor QVD)
Keep your API connection script as generic as possible. Pull the raw JSON using the lib:// connection without heavily renaming things in the wizard. Store this raw, unmapped data directly into a staging QVD (e.g., stg_GraphQL_Raw.qvd).
2. The Mapping Table (Transform Layer)
Create an inline mapping table in your transform script that translates the highly volatile GraphQL paths into your stable business names.
Instead of hardcoding field names in your LOAD statement, read the metadata of your raw table, compare it against your mapping table, and rename fields programmatically.
If a new field is added to the GraphQL query, your script won't crash—the new field will just sit in the raw data safely until you explicitly add it to your FieldMapping table.
Strategy 3: Hardcode the JSON Body via Variables
The native REST connector forces you to generate scripts based on the response, but the actual control of a GraphQL API happens in the request body (the variables and query definitions).
To make iterating easier within Qlik, store your GraphQL query text as a clean string variable at the very top of your script.
Code snippet
SET vGraphQLQuery = '{"query": "{ orders(first: 100) { edges { node { id totalCost items { name quantity } } } } }"}';
LIB CONNECT TO 'Your_GraphQL_REST_Connection';
LOAD *
FROM WITH CONNECTION (
BODY "$(vGraphQLQuery)"
);
By isolating the query payload into a single variable, you can tweak the fields without clicking through the "Select Data" wizard over and over again. You can also manually add pagination variables (after: "cursor") into this string loop.
Summary Recommendation
If this is a huge API with tight cost constraints and deep pagination, do not use Qlik as the extractor. Use Python to land the data as flat files or into a database, then let Qlik do what it does best: model and visualize.
If you are forced to stay in Qlik, dump the JSON as raw as possible to a QVD, and use an INLINE mapping table to control the shifting names during development.
Yes, GraphQL is probably great at solving another problem but for extracts it is on the far right of the REST-extract nightmare-scale.
I've even created transformers where I can grab the structure from Postman and get well suited text snippets that can be used in the REST-connector, the actual body and other places. But every time the query is changed the whole script for that query, and all handling of that data, will have to be re-created from scratch.
I will definitely look into your suggestions and hopefully keep my sanity 😉
I've already built a middle-man node-js extractor that create different ndjson files for different queries. There's so much data to load that I had to stream it to a file. The files are passed along to a ndjson to json converter so it can be consumed by Qlik.
Finally I created a schema-creator, since each object in the array will have different properties populated, to ensure that the property structure is complete.
The ndjson to json solves the most annoying objects-in-objects issue by creating dot-notaded fields and all I have to focus on are the arrays.
This will give me a better template to generate the script to load the json files.
The problem is the "Qlik Analytics only" situation, but I'm negotiating 🙂
