topic Re: Integrate QLIK with Elastic Search in QlikView

Integrate QLIK with Elastic Search

Anonymous — Wed, 30 May 2018 10:07:15 GMT

Is it possible to connect QLik View with Elastic Search. That is to have the QLik view as the visualization system at the front-end which connects and directly executes queries against big data hosted at elastic.

Best Regards,

Reem

Re: Integrate QLIK with Elastic Search

Gysbert_Wassenaar — Wed, 06 Feb 2019 16:55:26 GMT

Yes, certainly. ElasticSearch has a REST interface that you can use with the Qlik Sense Rest Connector.

Re: Integrate QLIK with Elastic Search

yuvalos3 — Mon, 08 Apr 2019 07:03:18 GMT

Hi, can you give an example of how to achieve it?

I was trying to use the pagination but elasticsearch has a limit of 10K docs.

Is there a known way to fetch a large index from es using qlik rest connector?

Re: Integrate QLIK with Elastic Search

Gysbert_Wassenaar — Mon, 08 Apr 2019 07:46:03 GMT

You can use the scroll api to get all the data from an index. I use two subroutines to do this. The first gets the first batch and is used to calculate the number of iterations needed to scroll through all the data. The second subroutine is called iteratively in a loop.

CALL GetData_FirstBatch;

For i = 2 To $(vIterations)
CALL GetData_NextBatch(i);
Next

SUB GetData_FirstBatch

    SET vTotal=;
    SET vCalls=;
    SET vScroll_ID=;
    SET vBody =;

    LET vSize = 5000;

	LIB CONNECT TO 'elasticsearch_rest_connection'; // just a template, we overrule the url in WITH CONNECTION ...

    RestConnectorMasterTable:
    LOAD
    	*,
        '$(vNow)|1' as BatchNo
        ;
    SQL SELECT 
		...loads of stuff...
    FROM JSON (wrap on) "root" PK "__KEY_root"
   
    WITH CONNECTION (
    URL "https://myelasticsearchsite/myindex-*/_search",
    HTTPHEADER "Authorization" "Bearer $(vSessionToken)",
    QUERY "scroll" "10m",
    QUERY "size" "$(vSize)"
    );


	[hits_u0]:
    LOAD	
    	[total_u0]
    RESIDENT
    	RestConnectorMasterTable
    WHERE
    	NOT IsNull([__FK_hits])
        ;

    LET vTotal = peek ('total_u0',-1,'hits_u0');
    LET vIterations = ceil($(vTotal)/$(vSize));

	DROP TABLE [hits_u0];
   

END SUB


SUB GetData_NextBatch(Iteration)

    [root]:
    LOAD	
    	[_scroll_id]
    RESIDENT
    	RestConnectorMasterTable
    WHERE
    	NOT IsNull([__KEY_root])
        ;

    LET vScroll_ID = peek('_scroll_id',-1,'root');
    LET vBody = '{ "scroll" : "10m", "scroll_id" : "$(vScroll_ID)" }';
    LET vBody = Replace('$(vBody)','"',chr(34)&chr(34));

    DROP TABLE [root]; 
        
    
    LIB CONNECT TO 'elasticsearch_rest_connection';

    CONCATENATE (RestConnectorMasterTable)
    
    LOAD
    	*,
        '$(vNow)|$(Iteration)' as BatchNo
        ;
    SQL SELECT 
		...lots of stuff...
    FROM JSON (wrap on) "root" PK "__KEY_root"

    WITH CONNECTION (
    URL "https://myelasticsearchsite/_search/scroll",    
    HTTPHEADER "Authorization" "Bearer $(vSessionToken)",
    BODY "$(vBody)"
    );
   
    
    
END SUB

Re: Integrate QLIK with Elastic Search

yuvalos3 — Mon, 08 Apr 2019 18:58:37 GMT

Thanks a lot, it was very helpful!

Re: Integrate QLIK with Elastic Search

yuvalos3 — Wed, 10 Apr 2019 10:54:02 GMT

Hi, I'm having problem with this script for large amounts of data,

I was trying to retrieve from an index with 500K docs, and it was working fine, took about a minute.

But then I tried to use it for an index with 8.5 million records, and it takes too long and Qlik also stuck on "Loading table" when I go to the Data manager. the 'Data load progress' is getting slower as the time passes by,

I think it has something to do with the concatenation in each loop.

But even if I didn't write it explicitly, Qlik will do the concatenation implicitly because the columns are the same.

I do know that our architect once achieved it using the regular pagination of elasticsearch and just used the:
"Offset" pagination with: "Count": size, "from": 0, "Total": "root/hits/total".

I'm not sure what I'm doing differently, he told me that he did the very same thing.

Though I can't blame Qlik for this, the pagination limitation is indeed in the elasticsearch.

So, can you think of something else to load such large amounts of data?

Thanks

Re: Integrate QLIK with Elastic Search

Gysbert_Wassenaar — Wed, 10 Apr 2019 13:35:13 GMT

The RestConnectorMasterTable table can get very big. Make sure you're not running out of ram.

If your ES index contains a field that allows you to partition the data into blocks perhaps you can retrieve all the data is smaller sets, like for example 1 million at a time. You retrieve the 1 million records, store them into qvd's, retrieve the next million, concatenate them to the qvd's, store the qvd's with now 2 million records, etc...., till you've got all the records.
Or perhaps you can partition your ES index into several indices and load from each separate index one at a time and store intermediate results into qvd's again.

Re: Integrate QLIK with Elastic Search

yuvalos3 — Mon, 15 Apr 2019 12:20:23 GMT

I just want to update how we managed to solve the issue.

We've increased the index parameter index.max_result_window to 10M.

After that, we used a regular pagination (no scroll)

In the rest connector we put the following:

URL: our index url

Pagination: Offset

'Start' param name: from

'Start' initial value: 0

'Count' parameter name: size

'Count' initial value: 10000

'Total records' path: root/hits/total

This took about 100 minutes to load 8.5M records from es.

Afterwards we had another issue, we couldn't load the 'Data manager' window.

We've bypassed it by adding at the end of the script a store command to a qvd file:

STORE [TableName] into [lib://[qvd_folder_connector]/[FileName].qvd];

And then we've created an app and loaded the data from the qvd file instead.

Re: Integrate QLIK with Elastic Search

Gysbert_Wassenaar — Mon, 15 Apr 2019 15:19:21 GMT

Thanks for the feedback!

Re: Integrate QLIK with Elastic Search

Hanan — Thu, 24 Oct 2019 13:12:06 GMT

Hello,
I want make a REST connection for ES.

I have the same problem, number of lines (10000) I implement your code but I have the following error message:

"HTTP protocol error 401 (Unauthorized): 'Bearer' authentication schema provided by the web-service is not supported or your credentials are not valid."

The credential its OK(the connection work)

Maybe you know why?

Best regard (sorry for my english)

Hanan

Re: Integrate QLIK with Elastic Search

sanjoybarai — Tue, 29 Aug 2023 09:03:59 GMT

Dear Yuvalos3, @yuvalos3 @Michael_Tarallo

I have tried your way in Qlik Sense , screen below :

But while loading the data it gave me error as "Total filed path" is not correct.

In "data manager" I do see like below way :

Note: 1.as total became "total_u0" , I also tried with both "root/hits/total_u0" and "root/hits/total" as total record path param in Pagination-offset. But no luck.

2. If I un-check the 'Look in header' option , the load-data works but I dont get all records ...only 10000 approx.

If JSON output from POSTman with simple POST qry , I see total like below way . So not sure where exactly , I missing to identify the correct path .

Thanks

Sanjoy

Re: Integrate QLIK with Elastic Search

sanjoybarai — Thu, 31 Aug 2023 09:49:23 GMT

Hi @yuvalos3 @Michael_Tarallo -- Need your help !