Calling Qliksense Repository API from Apache Drill via sql
2022-02-23
Abstract
I’ll show how to connect to Qliksense Repository API via sql using Apache Drill.
In this example Qliksense engine service runs at https://qlik.redaelli.org:4242/
Download
Download and unzip Apache Drill from https://drill.apache.org/download/
Configure
Create or edit the file conf/storage-plugins-override.conf
"storage": {
"qliksense" : {
"type" : "http",
"cacheResults" : true,
"connections" : {
"p4242" : {
"url" : "http://nginx.redaelli.org/qlik/",
"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_pass https://qlik.redaelli.org:4242/;
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
./bin/drill-embedded
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)
Queries
Extract a stream with custom properties values (nested json)
select
t1.name as streamName,
t1.owner as streamOwner,
t1.cps.definition.name as cpName,
t1.cps.value as cpValue
from (
select
s.name,
s.owner.name owner,
flatten(s.customProperties) as cps
from
qliksense.p4242.`qrs/stream/full` s
where
filter='name eq ''Merlot'''
) t1;
The output is
+------------+---------------+---------------+-----------------+
| streamName | streamOwner | cpName | cpValue |
+------------+---------------+---------------+-----------------+
| Merlot | sa.vino | CustomBalance | SelfService |
| Merlot | sa.vino | GroupAccess | Qliksense_Merlot|
+------------+---------------+---------------+-----------------+