Calling Qliksense Repository API from Apache Drill via sql



I’ll show how to connect to Qliksense Repository API via sql using Apache Drill.


In this example Qliksense engine service runs at


Download and unzip Apache Drill from


Create or edit the file conf/storage-plugins-override.conf

"storage": {
  "qliksense" : {
    "type" : "http",
    "cacheResults" : true,
    "connections" : {
      "p4242" : {
	"url" : "",
	"method" : "GET",
	"authType" : "none",
	"userName" : null,
	"password" : null,
	"postBody" : null,
	"params" : ["filter"],
	"dataPath" : null,
	"requireTail" : true,
	"inputType" : "json",
	"xmlDataLevel" : 1
    "proxyType" : "direct",
    "enabled" : true

At the moment Apache drill cannot query external rest services with client certificates DRILL-8052 and so we need a reverse proxy like Nginx.

Inside nginx configure a reverse proxy like

    location /qlik/ {
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header x-qlik-xrfkey 9003456789Zbcdez;
        proxy_set_header X-Qlik-User "UserDirectory=internal;UserId=sa_repository";
        set $args $args&xrfkey=9003456789Zbcdez;
        proxy_ssl_certificate     /certificates/qlik/client.pem;
        proxy_ssl_certificate_key /certificates/qlik/client_key.pem;
        proxy_ssl_verify       off;

Start Drill

Run drill with


Now the system is started and you have a command line sql interface. There is also a web ui at http://localhost:8047/ where you can run queries or add/change storages/connections instead of editing the previous file (http://localhost:8047/storage)


Extract a stream with custom properties values (nested json)

select as streamName,
  t1.owner as streamOwner, as cpName,
  t1.cps.value as cpValue
from (
  select, owner,
    flatten(s.customProperties) as cps
    qliksense.p4242.`qrs/stream/full` s
    filter='name eq ''Merlot'''
) t1;

The output is

| streamName |  streamOwner  |    cpName     |     cpValue     |
| Merlot     | sa.vino       | CustomBalance | SelfService     |
| Merlot     | sa.vino       | GroupAccess   | Qliksense_Merlot|

Enter your instance's address

More posts like this