Forum Discussion

Evogelpohl's avatar
Evogelpohl
Helper V
9 years ago
Solved

Need a Sleep or Wait step in my Query - hitting API rate limits

I have a working Power query function to pass ~12,000 tweets to MS cognitive sentiment API.  Working just fine.

 

I'm paying for the S1 (100,000 calls / 30 days) pricing teir.  Even in testing my script, i've not used anywhere near that many.

 

I'm starting my final analysis now and want it to run.  However, After 50 I get this: 

message=Rate limit is exceeded. Try again in 15 seconds. statusCode: 429.

 

Perhaps I'm going too fast and the problem isn't that i've breached my limit.

 

Is there a Power Query command similar to SLEEP 15 to wait or pause for 15 seconds and then do the next bunch?

 

Ideas?  Thanks.

 

 

? DataChant 

 

I'm hitting a rate limit from the Cognitive Text Analysis services.

  • DataChant's avatar
    DataChant
    9 years ago

    BTW - You may want to re-evaluate your approach. From the initial code above, you post a single message per API call. Instead you can send 1000 messages in a single API call, and repeat it on your entire dataset. You can learn how to do it in my blog here.

8 Replies

    • Evogelpohl's avatar
      Evogelpohl
      Helper V
      9 years ago

      GilbertQ Thanks.  I found that as well. 

       

       

      I'm having troubles getting it to work in my case.

       

      I have a query to get 10,000 items from a SharePoint list -> return to a list in PowerQuery.

       

      Next, I wrote a function, a second query called fxGetSentiment, that uses the API for: https://westus.api.cognitive.microsoft.com/text/analytics/v2.0.

       

      I then go back to the original query and choose ADD COLUMN - into which i put:  "try fxGetSentiment(id,tweet)".  It then runs for each of the 10K lines and pulls back the sentiment score from the second query, the Fx.

       

       

      It works great for 50-70 queries, then I get error:  "Exceeding API rate limit, try back in X seconds".

       

      I'm not sure where to place Chris Webb's code.  Here's my function below.  It would seem like it should go here, but I can't figure out where to put it.

       

      let

    TweetCognitive = (TweetID as text, TweetText as text) =>

let
    JsonRecords = Text.FromBinary(Json.FromValue([id=TweetID, text=TweetText])),
    JsonRequest = "{""documents"": [" & JsonRecords & "]}",
    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"="MYKEYHERE",
                           #"Content-Type"="application/json", Accept="application/json"],
                       Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse

in
    TweetCognitive
      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Evogelpohl I think that this might work

         

        let

    TweetCognitive = (TweetID as text, TweetText as text) =>

let
    JsonRecords = Text.FromBinary(Json.FromValue([id=TweetID, text=TweetText])),
    JsonRequest = "{""documents"": [" & JsonRecords & "]}",
    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"="MYKEYHERE",
                           #"Content-Type"="application/json", Accept="application/json"],
                       Content=JsonContent 
            ]),
    Delay = Function.InvokeAfter(Response, #duration(0,0,0,5)),
    JsonResponse = Json.Document(Delay ,1252)
in
    JsonResponse

in
    TweetCognitive