topic Re: Getting Data from Elastic Stack with REST with Scroll in Power Query

Getting Data from Elastic Stack with REST with Scroll

KenvM — Fri, 11 Aug 2017 20:48:17 GMT

Hello,

I'm pulling data into Power BI using the Elastic Stack REST API, but I'm running into a problem when trying to pull more than 10k records.

In order to do so, I need to use the scroll function: https://www.elastic.co/guide/en/elasticsearch/reference/master/search-request-scroll.html

The first call:

GET /_search?scroll=1m 
{ "query": ... }

Will return the first set of results plus a scroll id:

DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAD4...

Which is used for subsequent calls:

GET /_search {
  "scroll_id": "DXF1Z..."
}

Each call returns a set of records plus a new scroll id. The scroll id is then passed onto the next record, and so on.

For performance reasons, I don't want to increase the number of records that can be returned by the REST API.

Is it possible to solve this using Power BI?

Thanks,

Ken

Re: Getting Data from Elastic Stack with REST with Scroll

Greg_Deckler — Fri, 11 Aug 2017 23:25:32 GMT

There have been a few different threads like this but I believe the solution lies in creating a Power Query function that you call recursively. Here is an example of functions and recursion on a completely unrelated topic but might get you there:

https://community.powerbi.com/t5/Community-Blog/Using-Recursion-to-Solve-Hex-to-Decimal-Conversion/ba-p/34153

Re: Getting Data from Elastic Stack with REST with Scroll

KenvM — Wed, 16 Aug 2017 15:51:03 GMT

Thanks for pointing me in the right direction.

For anyone else that runs into this problem, here's the first iteration of a working function:

let
    RecursiveElasticFetch = (queryUrl, scrollUrl, scrollId, counter) =>

    let
        Counter = if (counter = null) then 0 else counter,

        Results = if (scrollId = null) then            
            Json.Document(Web.Contents(queryUrl))
        else
            Json.Document(Web.Contents(scrollUrl&scrollId, [Headers=[MyHeader=Text.From(Counter)]])),

        ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        Return = if (Counter < 10) then
            ParsedResults & RecursiveElasticFetch(queryUrl, scrollUrl, Results[_scroll_id], Counter+1)
        else
            ParsedResults 
    in
        Return 
in
    RecursiveElasticFetch

For the parameters:

queryUrl - The query URL that kicks off the search: http://elasticsrch-dev:9200/intranet*/_search?scroll=1m&source={....}

scrollUrl - The URL used for subsequent searches (the scroll id is appended at the end): http://elasticsrch-dev:9200/_search/scroll?scroll=1m&scroll_id=

scrollId - Used to pass the scroll_id to subsequent calls. Leave it blank for the first call

counter - Used to limit the number of iterations. Leave it blank for the first call.

Re: Getting Data from Elastic Stack with REST with Scroll

wendt_1 — Tue, 12 Dec 2017 21:24:05 GMT

Hey KenvM,

Thanks for sharing this iteration! Have you made any new changes? I'm having an issue after the second loop of this script. First call returns one set of records, second call returns a new set, third and all subsequent calls returns a repeat set of data (not sure if from first or second call, but I could verify if needed).

Have you encountered this yourself, and if so, have you found a solution?

Link to a more detailed description on the Elastic forums here: Elastic Forums

Thanks!

Re: Getting Data from Elastic Stack with REST with Scroll

KenvM — Tue, 12 Dec 2017 21:36:19 GMT

I looked at your example posted on the Elastic Search site, and I think the problem may be in this section:

        ## If function is called w/o a scrollID, then make the initial query, otherwise, query with a scrolling query
        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)])),

I believe you need to pass the scrollId as part of the query on subsequent calls. Otherwise, you get a new request each time.

Try:

        ## If function is called w/o a scrollID, then make the initial query, otherwise, query with a scrolling query
        Results = if (scrollID = null) then
            Json.Document(Web.Contents(url,[Content = Text.ToBinary(urlBody)]))
        else
            Json.Document(Web.Contents(scrollURL&scrollId,[Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])),

Re: Getting Data from Elastic Stack with REST with Scroll

wendt_1 — Wed, 13 Dec 2017 13:37:19 GMT

Thanks for pointing that out! I actually am passing the scrollID with that calll, but I'm passing it as part of the body as opposed to the URL. Because the _scroll_id value passed back by Elasticsearch grows with the number of shards you have (if my understanding is correct), it can get crazy long. The one I'm returning is 13,873 characters long. Too long to send back as part of the URL. One forum post on that here.

The bolded part below is where I'm passing it.

        ## If function is called w/o a scrollID, then make the initial query, otherwise, query with a scrolling query
        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)])),

Re: Getting Data from Elastic Stack with REST with Scroll

KenvM — Thu, 14 Dec 2017 01:53:51 GMT

Good to know, thanks!

I replicated the problem in our environment and I believe I have a fix.

Power BI caches results from repeat calls to the same URL - this is why I added in the customer header. Adding the same snippet to your call seems to fix the problem for me.

Json.Document(Web.Contents(scrollURL,[Content = Text.ToBinary(scrollBody&scrollID&scrollEND), Headers=[MyHeader=Text.From(Counter)]])),

Let me know if this works for you.

Re: Getting Data from Elastic Stack with REST with Scroll

wendt_1 — Thu, 14 Dec 2017 03:36:12 GMT

You. Are. A lifesaver. I had no idea that Power cached results, and your solution worked like a charm. Thank you for the time you put in to help me with this issue!

Re: Getting Data from Elastic Stack with REST with Scroll

Anonymous — Mon, 10 Sep 2018 15:25:36 GMT

Hi Dear all, Im currently working with this function but I need to connect at ElasticSearch server using credentials (Basic username:password) , Do you know if this is possible?

I could not get a good result :(

thanks a lot for your help.

Re: Getting Data from Elastic Stack with REST with Scroll

KenvM — Mon, 10 Sep 2018 15:38:24 GMT

I haven't needed to do so yet.

I believe that the solutions on this page may help you though. They should how to pass headers through when making a web.contents call.

https://community.powerbi.com/t5/Desktop/Issue-with-getting-data-via-API-with-bearer-token/td-p/126232

Re: Getting Data from Elastic Stack with REST with Scroll

Anonymous — Mon, 10 Sep 2018 20:55:26 GMT

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

Re: Getting Data from Elastic Stack with REST with Scroll

nicpenning — Wed, 06 Mar 2019 23:13:49 GMT

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

Re: Getting Data from Elastic Stack with REST with Scroll

KenvM — Thu, 07 Mar 2019 01:23:17 GMT

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?

Re: Getting Data from Elastic Stack with REST with Scroll

Anonymous — Thu, 07 Mar 2019 01:47:00 GMT

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

Re: Getting Data from Elastic Stack with REST with Scroll

KenvM — Fri, 08 Mar 2019 16:07:43 GMT

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.

Re: Getting Data from Elastic Stack with REST with Scroll

nicpenning — Thu, 02 May 2019 18:06:57 GMT

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.

Re: Getting Data from Elastic Stack with REST with Scroll

nicpenning — Fri, 12 Apr 2019 16:16:01 GMT

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!

Re: Getting Data from Elastic Stack with REST with Scroll

nahayo — Fri, 12 Jun 2020 20:01:36 GMT

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

Re: Getting Data from Elastic Stack with REST with Scroll

nicpenning — Thu, 18 Jun 2020 22:46:43 GMT

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

Re: Getting Data from Elastic Stack with REST with Scroll

nicpenning — Fri, 12 Jun 2020 22:44:35 GMT

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?