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
