How to retrieve more than 10k records through rest epi connection in Qlik sense

JQlik5 — Tue, 05 Sep 2023 08:37:11 GMT

Hi All, below mentioned the generated script in the load editor. I have 25434 records in source but Rest api connection fetching only minimum 1o records, if mention size =10000 in elastic search query then only 10k reocrds are getting. I need a logic to retrieve my whole records through API connection.

Sample URL:https://apiplato/.com/v1/datalake/searchEql

LIB CONNECT TO 'Rest';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

"total" AS "total_u0",

"max_score",

"__KEY_hits",

"__FK_hits",

(SELECT

"__FK_hits_u0",

"__KEY_hits_u0",

(SELECT

"filePath",

"fileId",

"__KEY__source",

"__FK__source",

(SELECT

"project_name",

"__KEY_data",

"__FK_data",

(SELECT

"sequence",

"action",

"details",

"misc",

"__KEY_log_entries",

"__FK_log_entries",

(SELECT

"created" AS "created_u0",

"__KEY_time",

"__FK_time_u0",

(SELECT

"created",

"__FK_raw"

FROM "raw" FK "__FK_raw")

FROM "time" PK "__KEY_time" FK "__FK_time_u0"),

(SELECT

"name" AS "name_u0",

"type",

"__FK_user"

FROM "user" FK "__FK_user")

FROM "log_entries" PK "__KEY_log_entries" FK "__FK_log_entries")

FROM "data" PK "__KEY_data" FK "__FK_data")

FROM "_source" PK "__KEY__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";

[raw]:

LOAD [created],

[__FK_raw] AS [__KEY_time]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_raw]);

[time_u0]:

LOAD [created_u0] AS [created_u0],

[__KEY_time],

[__FK_time_u0] AS [__KEY_log_entries]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_time_u0]);

[user]:

LOAD [name_u0] AS [name_u0],

[type],

[__FK_user] AS [__KEY_log_entries]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_user]);

[log_entries]:

LOAD [sequence],

[action],

[details],

[misc],

[__KEY_log_entries],

[__FK_log_entries] AS [__KEY_data]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_log_entries]);

[data]:

LOAD [project_name],

[__KEY_data],

[__FK_data] AS [__KEY__source]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data]);

[_source]:

LOAD [filePath],

[fileId],

[__KEY__source],

[__FK__source] AS [__KEY_hits_u0]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK__source]);

[hits_u0]:

LOAD [total_u0] AS [total_u0],

[max_score],

[__KEY_hits],

[__FK_hits] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_hits]);

DROP TABLE RestConnectorMasterTable;

/// Number of hits

"hits": {
"total": 25434,
"max_score": 1.3647444,

{

Re: How to retrieve more than 10k records through rest epi connection in Qlik sense

jprdonnelly — Tue, 05 Sep 2023 13:02:44 GMT

@JQlik5 - this should be a combination of this information from Elastic's API manual, and either Offset or Custom Pagination to retrieve the next set of results.

Re: How to retrieve more than 10k records through rest epi connection in Qlik sense

JQlik5 — Tue, 05 Sep 2023 13:23:28 GMT

Hi @jprdonnelly ,

I have tried pagination with offset but got error for total field path. below is screenshot. can you please let me know how to identify or mention total field path?

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.

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

Re: How to retrieve more than 10k records through rest epi connection in Qlik sense

JQlik5 — Mon, 11 Sep 2023 06:58:59 GMT

Hi,

I tried with Withconnection but only 1o records are coming and if i make chnages in script we get that 10 records in number of times.

topic How to retrieve more than 10k records through rest epi connection in Qlik sense in Integration, Extension & APIs

How to retrieve more than 10k records through rest epi connection in Qlik sense

Re: How to retrieve more than 10k records through rest epi connection in Qlik sense

Re: How to retrieve more than 10k records through rest epi connection in Qlik sense

Re: How to retrieve more than 10k records through rest epi connection in Qlik sense