Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
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