cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Re: Getting Data from Elastic Stack with REST with Scroll

Thanks for you comment, I getting only a set of record not the whole dataset , Do you suggest me something?

nicpenning Frequent Visitor
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

Hello,

 

I believe I am having the cached result issue. Adding this to the function ends up with an error since scrollBody and scrollEND are not defined anywhere in the code.

 

Where would I add these variables?

 

Please help!

 

Thanks!

 

Nic

KenvM Frequent Visitor
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

Just want to confirm: Are you using the code snippet that I posted in this forum or that wendt_1 posted in the Elastic forum?

Anonymous
Not applicable

Re: Getting Data from Elastic Stack with REST with Scroll

Hi yes I used your code posted in this blog the problem is the data, I have millions of records and power bi crashes and it can not to finish to get all information. Imy thinking probably is the computer's hardware I mean ram , core etc.

do you have another suggestion?
thanks
Highlighted
KenvM Frequent Visitor
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

Sorry emorquecho, that question was directed at nicpenning.

 

In your case, I'm not sure how to help or what the issue might be. Your suggestion sounds reasonable, but I haven't done a lot with Elastic data in Power BI since I originally posted on this.

nicpenning Frequent Visitor
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

I didn't have the right code! I had a different code block I was using.

 

The following is the function I used that worked:

let
    RecursiveElasticFetch = (url, scrollURL, urlBody, scrollBody, scrollID, counter, scrollEND) =>

    let
        Counter = if (counter = null) then 0 else counter,
        Results = if (scrollID = null) then
            Json.Document(Web.Contents(url,[Content = Text.ToBinary(urlBody)]))
        else
            Json.Document(Web.Contents(scrollURL,[Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])),
        ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        Return = if (Counter < 10) then
            ParsedResults & RecursiveElasticFetch(url, scrollURL, urlBody, scrollBody, Results[_scroll_id], Counter+1, scrollEND)
        else
            ParsedResults
    in
        Return
in
    RecursiveElasticFetch

 

And this is a snippet of the query:

let
    url = "http://[elasticinstance]:9200/index*/_search?scroll=1m",
    scrollURL = "http://[elasticinstance]:9200/_search/scroll",
    urlBody = "{""size"":10000,""query"":{""match_all"":{}}}",
    scrollBody = "{""scroll"":""10m"",""scroll_id"":""",
    scrollID = null,
    counter = null,
    scrollEND = """}",
    Source = RecursiveElasticFetch(url, scrollURL, urlBody, scrollBody, scrollID, counter scrollEND)
in*
    Source*

* I chopped out all of my steps but I believe this should work.

 

I can query all the data, but it is a bit slow. It takes a few minutes for about 120K rows.

nicpenning Frequent Visitor
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

This might be a long shot, but would there be any reason why this query doesn't quite pull absolutely everything? I see that I am missing some of the most recent indexed data but I haven't figured how much yet. If anybody else is seeing this as well please let me know.

 

Also, is there a good method to only query anything newer than what has already been queried? It doesn't seem efficient to query everything every time there is a change in the data.

 

Thanks in advance!

nahayo New Member
New Member

Re: Getting Data from Elastic Stack with REST with Scroll

I have the following code but for some reasons it's not working
can anyone help me correct it? i'm getting: Token comma expected error. and cant seem to solve it

let
    url = "http://dev.thehive.sd.gov:82/the_hive*/_search?scroll=1m",
    scrollURL = "http://dev.thehive.sd.gov:82/_search/scroll",
    urlBody = "{""size"":10000,""query"":{""match_all"":{}}}",
    scrollBody = "{""scroll"":""10m"",""scroll_id"":""",
    scrollID = null,
    counter = null,
    scrollEND = """}",
    Source = RecursiveElasticFetch(url, scrollURL, urlBody, scrollBody, scrollID, counter, scrollEND)
    
    let
        RecursiveElasticFetch = (url, scrollURL, urlBody, scrollBody, scrollID, counter, scrollEND) =>

        let
            Counter = if (counter = null) then 0 else counter,
            Results = if (scrollID = null) then
                Json.Document(Web.Contents(url,[Content = Text.ToBinary(urlBody)]))
            else
                Json.Document(Web.Contents(scrollURL,[Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])),
            ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

            Return = if (Counter < 10) then
                ParsedResults & RecursiveElasticFetch(url, scrollURL, urlBody, scrollBody, Results[_scroll_id], Counter+1, scrollEND)
            else
                ParsedResults
        in
            Return
    in
        RecursiveElasticFetch
in
    Source

@nicpenning  how do you actually combine the 2 queries to get the elastic scroll efect?

 

nicpenning Frequent Visitor
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

@nahayo 

 

Create your query with this code:

let
    url = "http://[elasticinstance]:9200/index*/_search?scroll=1m",
    scrollURL = "http://[elasticinstance]:9200/_search/scroll",
    urlBody = "{""size"":10000,""query"":{""match_all"":{}}}",
    scrollBody = "{""scroll"":""10m"",""scroll_id"":""",
    scrollID = null,
    counter = null,
    scrollEND = """}",
    Source = RecursiveElasticFetch(url, scrollURL, urlBody, scrollBody, scrollID, counter scrollEND)
in
    Source

 

You should create a function (Right-Click your Query and click new function) with this code:

 

let
    RecursiveElasticFetch = (url, scrollURL, urlBody, scrollBody, scrollID, counter, scrollEND) =>

    let
        Counter = if (counter = null) then 0 else counter,
        Results = if (scrollID = null) then
            Json.Document(Web.Contents(url,[Content = Text.ToBinary(urlBody)]))
        else
            Json.Document(Web.Contents(scrollURL,[Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])),
        ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        Return = if (Counter < 10) then
            ParsedResults & RecursiveElasticFetch(url, scrollURL, urlBody, scrollBody, Results[_scroll_id], Counter+1, scrollEND)
        else
            ParsedResults
    in
        Return
in
    RecursiveElasticFetch

 

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 311 members 3,046 guests
Please welcome our newest community members: