Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

benbassou
New Contributor III

Retrieve data from elasticsearch - Qlik Sense

Hello,

I have an issue when i try to load the data from elasticsearch REST API with the scan scroll method (QlikSense June 2017 Patch 1 - 11.11.2)

  2017-08-22 18:23:36 0379userRoles","
  2017-08-22 18:23:36 0380"viewportPixelHeight",
  2017-08-22 18:23:36 0381
"viewportPixelWidth",
  2017-08-22 18:23:36 0382"__FK__source"
  2017-08-22 18:23:36 0383FROM "_source" FK "__FK__source")
  2017-08-22 18:23:36 0384
FROM "hits" PK "__KEY_hits_u0" FK "__FK_hits_u0")
  2017-08-22 18:23:36 0385FROM "hits" PK "__KEY_hits" FK "__FK_hits")

  2017-08-22 18:23:36 0386 FROM JSON (wrap on) "root" PK "__KEY_root"

  2017-08-22 18:23:36 0387 WITH CONNECTION(Url "http://localhost:8090/_search/scroll?scroll=1m&size=100&scroll_id=cXVlcnlUaGVuRmV0Y2g7MzQ0OzQzOTA3MT.......................")

  2017-08-22 18:23:3665 fields found,

  2017-08-22 18:23:36      Error: QVX_UNEXPECTED_END_OF_DATA: Connection with server closed prematurely.

  2017-08-22 18:23:36      Execution Failed

  2017-08-22 18:23:36      Execution finished.

Note : This error is raised only when I try to retrieve data from a large index with multiple columns (>60) and thousands of lines.

Please let me know how can i fix this error ?

Thanks

9 Replies
mariozoske
New Contributor II

Re: Retrieve data from elasticsearch - Qlik Sense

Hi,

can you show the script how you load the scroll_id the first time and how you add it to the URL?

benbassou
New Contributor III

Re: Retrieve data from elasticsearch - Qlik Sense

Hi, thank you mario for the reply,

this is my code

let url = "http://localhost:8090/myindex/_search?size=100&search_type=scan&scroll=1m&pretty=true";

LIB CONNECT TO 'Index_open_web (win-58_lib)';

NoConcatenate

temp_total_web:

SQL SELECT

  "__KEY_root",

  (SELECT

  "total" AS "total_u0",

  "max_score",

        "_scroll_id",

  "__KEY_hits",

  "__FK_hits"

  FROM "hits" PK "__KEY_hits" FK "__FK_hits")

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

WITH CONNECTION(Url "$(url)");

/////////////////////////

LIB CONNECT TO 'Index_open_web (win-58_lib)'';

//get the total

total = lookup('total_u0', '__KEY_hits', 1 ,'temp_total_web');

totalfetched = 0;

startAt = 0;

pageSize = 1000;

Drop table temp_total_web;

for startAt = 0 to total step pageSize

RestConnectorMasterTable_web:

SQL SELECT

       "_scroll_id",

       "took",

       "timed_out",

       "__KEY_root",

            (SELECT

                 "total" AS "total_u0",

                 "max_score",

                 "__KEY_hits",

                 "__FK_hits",

                      (SELECT

                           "_index",

                           "_type",

                           "_id",

                           "_score",

                           "__KEY_hits_u0",

                           "__FK_hits_u0",

                                     (SELECT

                                          "@timestamp",

                                          "Alt",

                                         etc.....


                                          "viewportPixelWidth",

                                          "__FK__source"

  FROM "_source" FK "__FK__source")

  FROM "hits" PK "__KEY_hits_u0" FK "__FK_hits_u0")

  FROM "hits" PK "__KEY_hits" FK "__FK_hits")

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

WITH CONNECTION(Url "$(url)");

scrollid = lookup('_scroll_id', '__KEY_root', 1,'RestConnectorMasterTable_web');

Trace $(scrollid);

url = 'http://localhost:8090/_search/scroll?scroll=1m&size=100&scroll_id=$(scrollid)';

NEXT startAt;

Do you have any idea, what i m doing wrong ?

Note: Just for information, the same code work when i use a small index.

mariozoske
New Contributor II

Re: Retrieve data from elasticsearch - Qlik Sense

I would guess that this is an error on database side. Did you try to put the URL into the browser and the maximum size of 10000?

benbassou
New Contributor III

Re: Retrieve data from elasticsearch - Qlik Sense

i don't think the problem come from elasticsearch.

the scan scroll work very well when i retrieve data with javascript or with java n using the same index.

btw, in browser the size is limited to 10 000.

mariozoske
New Contributor II

Re: Retrieve data from elasticsearch - Qlik Sense

I tried to setup the same thing, but I am stucked at the beginning. When I modify and test your code, even my variable total stays empty. The URL and the response looks fine. Any Idea?

let url = ".../resultprocessing/resultData/_search?size=100&scroll=1m";

LIB CONNECT TO 'ConnectEB';

NoConcatenate

temp_total_web:

SQL SELECT

  "__KEY_root",

  (SELECT

  "total" AS "total_u0",

  "max_score",

  "_scroll_id",

  "__KEY_hits",

  "__FK_hits"

  FROM "hits" PK "__KEY_hits" FK "__FK_hits")

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

WITH CONNECTION(Url "$(url)");

LIB CONNECT TO 'ConnectEB';

//get the total

total = lookup('total_u0', '__KEY_hits', 1 ,'temp_total_web');

totalfetched = 0;

startAt = 0;

pageSize = 100;

//Drop table temp_total_web;

for startAt = 0 to total step pageSize     >>>>>>>>> Error, because totol is NULL

benbassou
New Contributor III

Re: Retrieve data from elasticsearch - Qlik Sense

try to execute only the first part of the code like

let url = ".../resultprocessing/resultData/_search?size=100&scroll=1m";

LIB CONNECT TO 'ConnectEB';

NoConcatenate

temp_total_web:

SQL SELECT

  "__KEY_root",

  (SELECT

  "total" AS "total_u0",

  "max_score",

  "_scroll_id",

  "__KEY_hits",

  "__FK_hits"

  FROM "hits" PK "__KEY_hits" FK "__FK_hits")

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

WITH CONNECTION(Url "$(url)");

let me know what data you have in temp_total_web table, just to verify that the total isnt really null and maybe the error is in the lookup.

mariozoske
New Contributor II

Re: Retrieve data from elasticsearch - Qlik Sense

Hi,

I've got the first part working .

Now I have an error, that scroll_id is not known. This should be an error on elastic search side:

When I try to open this in the browser:

http://.../_search?size=100&scroll=1m&scroll_id=qdkgn

I receive:

"request [/.../_search] contains unrecognized parameter: [scroll_id] -> did you mean [scroll]?"

In Qlik the error message looks like:

QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 400 (Bad Request): {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"request [/.../_search] contains unrecognized parameter: [scroll_id] -> did you mean [scroll]?"}],

benbassou
New Contributor III

Re: Retrieve data from elasticsearch - Qlik Sense

i think the problem come from your second URL, its should be like

...../_search/scroll?size=100&scroll=1m&scroll_id=qdkgn.........

and not

...../_search?size=100&scroll=1m&scroll_id=qdkgn......

mariozoske
New Contributor II

Re: Retrieve data from elasticsearch - Qlik Sense

That is what I tested in the first place, but when I try to request that in Qlik or browser, I receive:


QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 400 (Bad Request): No handler found for uri [/resultprocessing/resultData/_search/scroll?

Community Browser