Qlik Community

Qlik Sense Data Connectivity

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

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Partner
Partner

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

10 Replies
Highlighted
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?

Highlighted
Partner
Partner

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.

Highlighted
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?

Highlighted
Partner
Partner

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.

Highlighted
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

Highlighted
Partner
Partner

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.

Highlighted
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]?"}],

Highlighted
Partner
Partner

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......

Highlighted
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?