Qlik Sense JSON Connector

Happy 2020 Everyone! 2019 seemed to have gone flying by, it had been a very busy year for me – lots of really interesting Qlik projects, learnt a lot of new skills and of course, lot more about Qlik’s internal workings. With that in mind – I wanted to share an interesting concept today – how to build and deploy a “JSON” file connector using no additional tools other than what you already have installed in your Qlik Sense Enterprise on Windows environment.

Why?

Qlik already provides a REST connector out of the box – so you may be thinking why would you need a separate JSON connector as most of the JSON we consume usually comes from some type of REST API. However, there are times when requirement arises, may be to build a proof of concept or because of an issue with accessibility to the API, you are supplied with JSON files which you need to load into Qlik Sense or QlikView. You can use the flat file option to load a flat structured JSON file – however this method does not work when you have nested objects in your JSON file. You can build a pipeline using 3rd party tools or download and install Qlik web connectors but that brings its own set of administrative challenges. So I decided to demonstrate how to build and deploy a REST API to add this functionality and more in Qlik Sense for loading data without having to run any additional services. Idea is to use a simple and very light-weight node.js application that can be used by the Qlik’s native REST connector.

Show me how!

You can head to my GitHub repository if you had enough of me rambling away! The final solution is a node.js application that we will attach to “Qlik Sense Service Dispatcher” to run it alongside all the Qlik Sense services. This node.js application will provide a REST API endpoint where you can pass the JSON file location (local drive/network drive only, no internet location on this iteration) and it will provide you with the content of the file in response, which you can consume using Qlik’s native REST connector. This approach can be utilised to read not only JSON files, other file types (emails, web scraping etc.) and consume it straight into the Qlik platform.

Setup

Prerequisites

Please make sure you have backup of the environment/server which you wish to test this on.

Setting up the node.js application

Download and unzip this repo to the following location of your Qlik Sense server where you wish to run the API from.

Location of the Qlik Sense installation folder may vary as this is depending on where Qlik Sense has been installed.

%programfiles%\Qlik\Sense\ServiceDispatcher\Node

Configuring Qlik Sense Service Dispatcher

You will configure the Qlik Sense Service Dispatcher to make sure that this API is running as a service alongside with all the other Qlik services. This will also make sure the this API service restarts during a server reboot or when Qlik services reboot. To achieve this, you will need to make changes to the “services.conf” file (you can use editors such as notepad) at the following location –

Please make sure you backup the original file before making any changes to this. You will require admin privileges to make changes to this file.

%programfiles%\Qlik\Sense\ServiceDispatcher

This file should contain something similar to below –

[globals]
LogPath="${ALLUSERSPROFILE}\Qlik\Sense\Log"
MigrationPort=4545
DataPrepPort=4949
BrokerPort=4900
HubPort=9029
CapabilityPort=9031
AboutPort=9032
ConverterPort=3003
OdagPort=9098
WESPort=9080
DepgraphPort=9079
DownloadPrepPort=9090
HybridSetupConsolePort=5929
QrsPort=4242
PrecedentPort=4950
PrecedentEnginePort=4747
AdvanaPort=50057
MobilityRegistrarPort=9082
NotifierPort=9081
[migration-service]
Identity=Qlik.migration-service
DisplayName=App Migration
ExePath=Node\node.exe
Script=..\MigrationService\index.js
...... and lots more

Add the following to the very bottom of this file and save it. 

Please make sure you amend the script path below to reflect the folder name you have given during extracting the GitHub repository few steps above.

[jsonreader]
Identity=Qlik.jsonreader
Enabled=true
DisplayName=jsonreader
ExecType=nodejs
ExePath=Node\node.exe
Script=Node\json-reader\index.js

Now restart the “Qlik Sense Service Dispatcher” service by going to Windows Services. This should now start your API.

Using the API

You can use any REST client to test the API including Qlik’s own REST connector in Qlik Sense/QlikView.

By default – The API will be running in http and not https. It will be running on localhost and listening to port 3000. Default address/url should be as below –

http://localhost:3000/

You can try accessing the API root from a browser with-in the server. You should retrieve the following response –

To read a JSON file using this API – make sure you place the JSON file on the server or in a network drive where the Qlik Service account has access to. Then use the following endpoint as the example shown below –

http://localhost:3000/json?path=D:\jsonfile.json

or curl example –

curl --location --request GET 'http://localhost:3000/json?path=D:\jsonfile.json'

GET parameter details

The API call shown above can be broken down by –

Response

If you have a valid JSON file at the path provided in the query parameter, then this should return status of 200 with the JSON from the file. Example (provided this was the content of the file you are reading) –

{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters": {
        "batter": [
            {
                "id": "1001",
                "type": "Regular"
            },
            {
                "id": "1002",
                "type": "Chocolate"
            },
            {
                "id": "1003",
                "type": "Blueberry"
            },
            {
                "id": "1004",
                "type": "Devil's Food"
            }
        ],
    },
    "topping": [
        {
            "id": "5001",
            "type": "None"
        },
        {
            "id": "5002",
            "type": "Glazed"
        },
        {
            "id": "5005",
            "type": "Sugar"
        },
        {
            "id": "5007",
            "type": "Powdered Sugar"
        },
        {
            "id": "5006",
            "type": "Chocolate with Sprinkles"
        },
        {
            "id": "5003",
            "type": "Chocolate"
        },
        {
            "id": "5004",
            "type": "Maple"
        }
    ],
}

Response codes

Below table shows the response codes for this API and what they mean.

What’s next?

Next is to perhaps add an option to load files from web or built web scraping that can be triggered by GET request and retrieve the result (handling the transformations in node.js) in Qlik Sense. I welcome people to take this to the next stage and make contributions to the repository. It would be nice to see what other ideas people come up with using this approach. Please feel free to use the chat here or ping on twitter with your ideas.