cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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?

Highlighted
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

Highlighted
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?

Highlighted
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
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.

Highlighted
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.

Highlighted
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!

Highlighted
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://Redacted 'URL edited by Admin':82/the_hive*/_search?scroll=1m",
    scrollURL = "http://Redacted 'URL edited by Admin':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?

 

Reply edited by Admin

Highlighted
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

 

 

Highlighted
Frequent Visitor

Re: Getting Data from Elastic Stack with REST with Scroll

Revisiting this old post. This works well on an unauthenticated cluster.

 

Anyone know how to add an API Key to perform this type of query?

 

I understand I can add: 

[Headers=[#"Authorization"="ApiKey redacted"]]
 
For web.content and use an anonymous login, but it doesn't seem to work when I do this:

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),[Headers=[#"Authorization"="ApiKey redacted"]]]))
        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
 
This is the error I get.

An error occurred in the ‘RecursiveElasticFetch’ query. DataSource.Error: Web.Contents failed to get contents from 'https://redacted:9200/index-*stats*/_search?scroll=1m' (406): Not Acceptable
Details:
DataSourceKind=Web
DataSourcePath=https://redacted:9200/filebeat-*stats*/_search
Url=https://redacted:9200/index-*stats*/_search?scroll=1m
 
 
Any ideas how to authenticate with this scroll function / elastic query?

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021