locked
Copy Activity every 15minutes between 7am-5pm RRS feed

  • Question

  • Evening,

    I've got some live data feeding through from a business and I'd like to be able to have it live updating my powerbi reports. Someone has reccomended stream analytics/event hubs but I'm not really sure.

    I pull the data from API's and put the data in my azure blob. I then use the copy function to move it to an azure sql table. After some decent reading, the best I can come up with is using an offset to start at a certain time, but not way to stop the copy activity pipeline running but 5pm. I'd like to be able to do this automatically without having to manually pause the pipeline.

    Thoughts?

    Wednesday, May 24, 2017 12:04 PM

All replies

  • You could use Stream Analytics to read data from Blob storage, and both write data to Azure SQL DB.. and  live stream data to Power BI dashboards directly. Event ordering and offsets is possible with simple configurations. Some pointers to get you started.

    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-define-inputs

    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-power-bi-dashboard

    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-out-of-order-and-late-events

    Wednesday, May 24, 2017 4:03 PM
  • Elliot in Stream Analytics query , you can put a condition on System.Timestamp to emit events or not based on its hour value. Something like this --

    e.g. SELECT * FROM Input
    WHERE DATEPART (hour, System.Timestamp)  > 9 and DATEPART (hour, System.Timestamp)  < 17


    [Disclaimer] This posting is provided "AS IS" with no warranties, and confers no rights. User assumes all.

    Wednesday, May 24, 2017 7:16 PM
  • Morning,

    Stream Analytics seems like the best way to move the data from my blob to my sql storage. Thank you so much for the advice.

    I haven't tried the time stamp constraint but I feel that's ideal.

    I spent a lot of time yesterday working to get data factory to come together so I feel a little bit silly given how easy it is with stream analytics.

    My understanding, a simple query in stream analytics:

    SELECT *

    FROM INPUT [blob]

    WHERE DATEPART (hour, System.Timestamp) > 9 and DATEPART (hour, System.Timestamp) < 17

    TO OUTPUT [sqltable]

    ----

    My only question is around billing. Am I able to leave the stream analytics job running 24/7 and I will only be billed when it copies from the blob to sql table (data only coming in between 7am and 5pm).

    Will the bill be 10hours * 30 days? or a lot less as there is roughly a transaction every minute to minute.5?

    Wednesday, May 24, 2017 10:11 PM
  • Unfortunately currently Stream Analytics does not support dynamic billing or auto scaling, so billing happens as long as the job is running. Sounds like your job is more of a batch job, so ADF might be more suitable for you in that case.

    [Disclaimer] This posting is provided "AS IS" with no warranties, and confers no rights. User assumes all.

    Thursday, May 25, 2017 5:20 PM