topic Re: Pulling data from Elastic Search in QlikView

Pulling data from Elastic Search

Tue, 19 Jan 2016 12:10:05 GMT

Hi,

Is it possible to pull data from Elastic Search into the QlikView data model?

I saw an older thread mentioning the possibility to do it in QlikView Expressor, however I see that application has been decommissioned - is there possibility within QlikView to connect to Elastic Search?

I have a data source and I cannot connect via OLEDB so my only options are web services or Elastic Search, the latter being preferred as I can pull more data.

Thanks.

Re: Pulling data from Elastic Search

Wed, 20 Jan 2016 16:13:41 GMT

I'll answer my own question here, I was able to achieve this by using the QlikView REST Connector from the Qlik Market, I was able to connect to Elastic Search's Restful API, and using Custom pagination like documented here, I was able to load all records into my data source.

http://help.qlik.com/Connectors/en-US/connectors/#../Subsystems/REST_connector_QV_help/Content/1.0/Create-REST-connectio…

Re: Pulling data from Elastic Search

Wed, 20 Jan 2016 17:01:22 GMT

FYI, REST API connector available on QV 12 version only.

Re: Pulling data from Elastic Search

Anonymous — Mon, 18 Apr 2016 14:44:13 GMT

Hi Russell,

I am trying to make connection to Elastic Search as well. Is possible that you would share some of your load script, I am having some troubles making this work.

Re: Pulling data from Elastic Search

Wed, 20 Apr 2016 13:06:41 GMT

Hi,

Actually I got this working only sort of, but then I got pulled away from it and never finished it, now I've been away from Qlik for a long time I can barely remember how I did it - trying to refresh my memory here so i'll let you know some of my steps and where I was going with it and hopefully that will help you.

You're best off referring to the document link I sent in the previous note.

First you need to get the REST Connector first, download it and install it. Then in QlikView Script, on the Data tab select QvRestConnector from the Database dropdown.

Click Connect, enter your elastic search URL. The URL was like: https://hostname.com/search/nameofindex/_search

whatever Username/Password you have.

Then under Query Parameters, add 3 fields here

I did:

Name               Value

search_type     scan

scroll               25m

size                 1000

On the Pagination dropdown, set it to Custom.

Then press OK,

Your Connection string will look something like this

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://hostname.com/search/nameofindex/_search;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=search_type%2scan%1scroll%25m%1size%21000;PaginationType=Custom;XUserId=USERNAME;XPassword=PASSWORD";

Press Select.. button... select all your fields and press OK, it will create the script for you

Scroll down to this part and change it:

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "https://hostname.com/search/nameofindex/_search");

// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source.

// Please see the documentation for "Loading paged data."

I changed it to, like below (Note the URL to the _search/scroll url), also adding on additional parameters, and using the Scroll ID returned from the first call in the connect string

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (URL "https://hostname.com/search/_search/scroll",

QUERY "scroll" "5m",

QUERY "scroll_id" "$(scrollid)");

Notice I have variables there for scrollid.

I created this table at the top of my script, before the big RestConnectorMasterTable , containing this:

scrollTable:

SQL SELECT

"_scroll_id" AS "scrollid",

(SELECT

"total" AS "total_u1"

FROM "hits")

FROM JSON (wrap on) "root";

Let total = Peek('total_u1',0,'scrollTable');

Let scrollid = Peek('scrollid',1,'scrollTable');

Basically the idea here is, the first call to the elasticsearch with the Search string will return a Scroll ID, this will be stored in $(scrollid)

From here you're on your own, but the general idea was that there is to be a FOR loop before "RestConnectorMasterTable", so it would download the first 1000 records, have a new scroll id.

Store the new scroll id in a temp table, like below

[TotalFetched]:

LOAD

max(__KEY_hits_u0) as 'Hits',

scrollid as 'scrollid'

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

totalfetched = Peek('Hits',0,TotalFetched);

scrollid = Peek('scrollid',0,TotalFetched);

Then get the new scrollid assigned like above, and perform an Increment so the FOR loop would continue until you've retrieved all rows.

I had it working but I never finished the loop to increment properly and end properly.

I hope that at least helps, if even at least to be able to connect and pull something, then you can follow my principal of using a FOR loop to keep getting a new scroll id going through the LOAD multiple times.

There may be a cleaner way, I am new to Qlik so this was really trial and error for me, and I never finished it or else I'd provide you the full script of it working. Good luck

Re: Pulling data from Elastic Search

Anonymous — Sat, 23 Apr 2016 09:30:38 GMT

Hi Rusell,

Thank you so much for sharing this, I have managed to pull out a set of data and I am working on the scroll issue now.

I am currently battling this error, comes from addon in the url adds.

But I will share the document if I manage to get this working.

Regards

Birkir

The following error occurred:

Select statement parser error at (23,92): Multi-line literals are not supported.

Re: Pulling data from Elastic Search

Mon, 25 Apr 2016 09:34:06 GMT

when you say the error comes from the addon in the url adds

do you mean adding these "QUERY" parts in the URL connection? Strange because this worked fine for me as below, with a comma after the URL followed by the Query.. it all separated with Commas. Make sure you're using QlikView 12 as someone mentioned above this RESTful connector only works on QV12

WITH CONNECTION (URL "https://hostname.com/search/_search/scroll",

QUERY "scroll" "5m",

QUERY "scroll_id" "$(scrollid)");

Re: Pulling data from Elastic Search

Anonymous — Mon, 25 Apr 2016 22:46:39 GMT

Hi Russell,

Yes exactly this part. I am using Qlik Sense, but with the sames restful plugin. So I have managed to get a set of data but the scrolling part is still a problem.

I guess elastic search documentation is due for reading .

Regards

Birkir

Re: Pulling data from Elastic Search

Mon, 06 Jun 2016 18:21:26 GMT

Can we use 'Next Token' pagination for elasticsearch? I have been trying to use it but not sure how to specify the end of iteration.

Re: Pulling data from Elastic Search

Mon, 08 May 2017 07:20:19 GMT

Hi Russell,

Is it possible to create a elastic search instead of wildcard search . For example if I am searching for a description "I have 12 years of experience in Qlikview" the search results should give the matching records.

Regards,

Praveen

Re: Pulling data from Elastic Search

Thu, 25 May 2017 20:41:18 GMT

Birkir,

For Qlik Sense have you been able to do this with newer versions and is the scrolling getting better. The use case is that a customer has Elastic Search laying over a cancer registry and they would like to leverage Qlik alongside Elastic search. Let me know if you can.

Thanks,

Chris Sault

Re: Pulling data from Elastic Search

Anonymous — Thu, 25 May 2017 23:21:28 GMT

Hi Chris,

I have made no progress with this I have not worked on this project for a while.

Sorry for being of no help at all

Regards

Birkir

Re: Pulling data from Elastic Search

Fri, 26 May 2017 12:57:57 GMT

no problem thanks anyways

Re: Pulling data from Elastic Search

Anonymous — Wed, 16 Aug 2017 07:02:48 GMT

I do not think so. If you want to use the API "size and from" within elasticsearch, you will end up in the maximum limit of 10000 (default). The correct API for get all data from elasticsearch is the API "scroll".

And you cannot use the scroll_id in the URL because it is to long. The Qlik REST-Connector configuration is only for setting up paging within URLs.

It might be a way to send the scroll_id via body. But that would meen scripting. Maybe someone can help?

Re: Pulling data from Elastic Search

Anonymous — Wed, 16 Aug 2017 07:07:12 GMT

Hi Birkir,

did you work on this again? I am also stucked in getting all data out of my elastic search db.

Re: Pulling data from Elastic Search

adambrian — Mon, 10 Feb 2020 08:12:15 GMT

Hi Mario,

did you solved this one? I'm also stuck.