How to apply date filter while fetching data from Elastic search using Rest Connector

hsbc_m_chandan — Sat, 16 Nov 2024 01:12:32 GMT

I am using custom pagination while fetching data from Rest Connector, My script works fine and fetches all the data but because of less RAM on the source I was asked to do incremental. Can some one help me on how to apply date filter to load data from last uploaded date. Below is my Script

Total_Table:

SQL SELECT

"took",
"timed_out",
"__KEY_root",

(SELECT
"total" AS "total_u0",
"max_score",
"__KEY_hits",
"__FK_hits"

FROM "hits" PK "__KEY_hits" FK "__FK_hits")
FROM JSON (wrap on) "root" PK "__KEY_root";

Let total=Peek('total_u0',0,'Total_Table');

Drop Table Total_Table;

let current_scroll_id = '';

Let totalfetched = 0;

Let pageSize = 10000;

do while totalfetched < total

RestConnectorMasterTable:
SQL SELECT
"took",
"timed_out",
"__KEY_root",
(SELECT
"total",
"successful",
"failed",
"__FK__shards"
FROM "_shards" FK "__FK__shards"),
(SELECT
"total" AS "total_u0",
"max_score",
"__KEY_hits",
"__FK_hits",
(SELECT
"_index",
"_type",
"_id",
"_score",
"__KEY_hits_u0",
"__FK_hits_u0",
(SELECT
"input",
"questionId",
"dataId",
"response",
"solved",
"id",
"sessionId",
"time",
"userId",
"version",
"__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 "http:XYZsearch?scroll=25m&scroll_id=$(current_scroll_id)&size=$(pageSize)");
// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source.
// Please see the documentation for "Loading paged data."

Let current_scroll_id = Peek('_scroll_id');

hitsinThisIteration:

LOAD max([__KEY_hits_u0]) as 'hitscount'

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_hits_u0]);

let totalfetched = peek('hitscount');

drop Table hitsinThisIteration;

loop;

[source_]:
LOAD [input],
[questionId],
[dataId],
[response],
[solved],
[id],
[sessionId],
[time],
[userId],
[version],
[__FK__source] AS [__KEY_hits_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK__source]);

DROP TABLE RestConnectorMasterTable;

Tried below 2 methods but didn’t work

WITH CONNECTION(Url "http://XYZ _search ?scroll=25m&scroll_id=$(current_scroll_id)&size=$(pageSize) &time=$(time)");

WITH CONNECTION(Url "http://XYZ_search?scroll=25m&scroll_id=$(current_scroll_id)&size=$(pageSize)",QUERY "time" "$(time)");

topic How to apply date filter while fetching data from Elastic search using Rest Connector in QlikView

How to apply date filter while fetching data from Elastic search using Rest Connector