Skip to main content

Connectivity & Data Prep

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Benbassou
Partner - Contributor III
Partner - 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

10 Replies
mariozoske
Contributor II
Contributor II

Hi,

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

Benbassou
Partner - Contributor III
Partner - Contributor III
Author

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
Contributor II
Contributor II

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
Partner - Contributor III
Partner - Contributor III
Author

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
Contributor II
Contributor II

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
Partner - Contributor III
Partner - Contributor III
Author

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
Contributor II
Contributor II

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
Partner - Contributor III
Partner - Contributor III
Author

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
Contributor II
Contributor II

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?