Summary: I demonstrate how to use a simple Python REST server to wrap any data source for loading by the Qlik REST connector.
In my years of Qlik development I have many times needed to load data from a source not supported by available connectors. Sometimes the data resides on a factory floor controller and can only be accessed using something like telnet. One approach is to create a batch process that extracts the data to a flat file accessible to Qlik. This works, but you have to deal with issues of timing and failure monitoring.
If you want real time direct loading from Qlik script, you can write a custom connector for your data source. Writing a custom connector is a programming chore of medium complexity and there are limited languages you can work in.
As an alternative to a custom connector or batch process, I’m going to show how you can use the out-of-the-box Qlik REST connector to access your special data, wherever it lives!
REST is a popular way of exchanging data between systems. Data is requested from a REST Endpoint using a URL like:
http://localhost:3000/customers
Data is returned in JSON format like:
[{"Name": "Abc corp", "City": "New York"},
{"Name": "GeoFarm": "City": "Boston"}]
The Qlik REST connector translates JSON data into the rows and fields of a Qlik table. This URL request + JSON output makes it very easy to test REST endpoints using a browser.
Due to REST’s popularity, the tools we need to create REST endpoints are available in just about all programming languages, platforms and lowcode services. Use whatever language you are familiar with or that runs in your environment. I’m going to show a quick example using Python. If you want to create this example yourself, make sure you have Python 3+ installed along with the packages Flask and Pandas.
For my “special data source”, I’m going to use output from the Windows “Tasklist” command. The output looks like below.
Our REST Endpoint must provide three components:
- A web server that will handle the incoming URL.
- Data from our special/custom data source.
- A conversion of data from our native format to JSON.
Here is a complete Python program that satisfies all three requirements.
from flask import Flask, json
from io import StringIO
import pandas as pd
api = Flask(__name__)
@api.route('/tasklist', methods=['GET'])
def get_tasklist():
import subprocess
subprocess = subprocess.Popen("tasklist /FO CSV", shell=True, stdout=subprocess.PIPE, text=True)
csvStringIO = StringIO(subprocess.stdout.read())
df = pd.read_csv(csvStringIO);
return Response(df.to_json(orient='records'), content_type='application/json')
if __name__ == '__main__':
api.run(port=3000, host='0.0.0.0') Save this code in file “SimpleRest.py” and start the python task with the command:
python SimpleRest.py
In a Qlik App, create a new REST connection. The URL will be our new REST endpoint. If your Python task is running on other than “localhost”, change the host in the URL.
Give it a Name. Test and Save.
Use the “Select data” button on the connection and select the “root” table. The Qlik wizard will build the required script.
Reload and you should have data!
Let’s break down the pieces of the python program to understand what you would change and what you would re-use in a new scenario.
The first four lines import required libraries and initialize Flask, the REST library we will use. Here is the remaining code.
@api.route('/tasklist', methods=['GET'])
def get_tasklist():
import subprocess
subprocess = subprocess.Popen("tasklist /FO CSV", shell=True, stdout=subprocess.PIPE, text=True)
csvStringIO = StringIO(subprocess.stdout.read())
df = pd.read_csv(csvStringIO);
return Response(df.to_json(orient='records'), content_type='application/json')
if __name__ == '__main__':
api.run(port=3000, host='0.0.0.0') The bolded lines above start a webserver listening on port 3000 and declare what code will run when the URL path is “/tasklist”.
@api.route('/tasklist', methods=['GET'])
def get_tasklist():
import subprocess
subprocess = subprocess.Popen("tasklist /FO CSV", shell=True, stdout=subprocess.PIPE, text=True)
csvStringIO = StringIO(subprocess.stdout.read())
df = pd.read_csv(csvStringIO);
return Response(df.to_json(orient='records'), content_type='application/json')
if __name__ == '__main__':
api.run(port=3000, host='0.0.0.0') These two lines above run the tasklist /FO CSV command and capture the output. To make wrangling easier I added tasklist options “/FO CSV” which generates output in CSV format.
@api.route('/tasklist', methods=['GET'])
def get_tasklist():
import subprocess
subprocess = subprocess.Popen("tasklist /FO CSV", shell=True, stdout=subprocess.PIPE, text=True)
csvStringIO = StringIO(subprocess.stdout.read())
df = pd.read_csv(csvStringIO);
return Response(df.to_json(orient='records'), content_type='application/json')
if __name__ == '__main__':
api.run(port=3000, host='0.0.0.0') Lastly we wrangle the output from CSV to JSON format and send the data to the requestor.
I hope you can see from this example how easy it can be to expose your custom data to Qlik and have real time loading. I used Python for this example, but you could implement this same example in many languages with little effort.
if you’ve created my example in your own environment, you should now be able to create a treemap chart of Memory Usage. Tip: parse the [Mem Usage] field in the script with
Num#(SubField([Mem Usage], ' ', 1), '#,##0') * 1024 as [Mem Usage]
Want to talk more data loading techniques? Join me at the Masters Summit for Qlik in Fall 2022 where in addition to heads down learning, we’ll be kicking the tires on things like Server Side Extensions and Qlik Application Automation.
-Rob
