Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
hsbc_m_chandan
Contributor
Contributor

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

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)");

Labels (1)
0 Replies