topic Re: Retrieve data from elasticsearch - Qlik Sense in Connectivity & Data Prep

Retrieve data from elasticsearch - Qlik Sense

Benbassou — Wed, 23 Aug 2017 09:51:21 GMT

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 0379

userRoles",

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 0383

FROM "_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 0385

FROM "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=cXVlcnlUaGVuRmV0Y2g7MzQ0OzQzOTA3MTM1OnZhdjJJMHJwUU5XZ29FQ25rZWNvRXc7NDM3Mjc1ODc6b3lpczBwdjJUV2lqaVVlMnc2RFgyZzs2NjY0Njg4OkZORXhURktjUVRXTThHTnZoR1pUOEE7NDM5MDcxMzY6dmF2MkkwcnBRTldnb0VDbmtlY29Fdzs0MzkwNzEzNzp2YXYySTBycFFOV2dvRUNua2Vjb0V3OzQyMDYzNzU5OmJreElTYnU4UUpDUUtXbFVSOUF....................")

2017-08-22 18:23:36

65 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

Re: Retrieve data from elasticsearch - Qlik Sense

Anonymous — Tue, 29 Aug 2017 14:58:01 GMT

Hi,

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

Re: Retrieve data from elasticsearch - Qlik Sense

Benbassou — Tue, 29 Aug 2017 15:20:19 GMT

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.

Re: Retrieve data from elasticsearch - Qlik Sense

Anonymous — Tue, 29 Aug 2017 15:28:14 GMT

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?

Re: Retrieve data from elasticsearch - Qlik Sense

Benbassou — Tue, 29 Aug 2017 15:42:28 GMT

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.

Re: Retrieve data from elasticsearch - Qlik Sense

Anonymous — Wed, 30 Aug 2017 06:26:27 GMT

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

Re: Retrieve data from elasticsearch - Qlik Sense

Benbassou — Wed, 30 Aug 2017 07:26:08 GMT

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.

Re: Retrieve data from elasticsearch - Qlik Sense

Anonymous — Wed, 30 Aug 2017 13:07:20 GMT

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

Re: Retrieve data from elasticsearch - Qlik Sense

Benbassou — Wed, 30 Aug 2017 15:43:17 GMT

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

Re: Retrieve data from elasticsearch - Qlik Sense

Anonymous — Thu, 31 Aug 2017 14:25:43 GMT

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?

Re: Retrieve data from elasticsearch - Qlik Sense

fashid — Fri, 15 Nov 2019 12:10:06 GMT

Does anybody have a proper solution for extracting data from elastic search