locked
Compiling query failed RRS feed

  • Question

  • I am having problems with getting my query compiled on the web editor. It says Compiling query failed and nothing else.

    Here is my query in two parts

    First I define a temporary query to reuse later. Here SensorData part of JSON is an array of records.

    WITH carsensorvalues AS (
    select CAST(inputeventhub.starttime as datetime) as starttime,
           CAST(inputeventhub.eventtime as datetime) as eventtime,
           inputeventhub.deviceid as deviceid,
           inputeventhub.customerid as customerid,
           GetArrayElement(inputeventhub.SensorData, 0) as latitude,
           GetArrayElement(inputeventhub.SensorData, 1) as longitude,
           GetArrayElement(inputeventhub.SensorData, 2) as fuel_system_stat,
           GetArrayElement(inputeventhub.SensorData, 3) as calc_load_value,
           GetArrayElement(inputeventhub.SensorData, 4) as coolant_temp,
           GetArrayElement(inputeventhub.SensorData, 5) as engine_rpm,
           GetArrayElement(inputeventhub.SensorData, 6) as vehicle_speed,
           GetArrayElement(inputeventhub.SensorData, 7) as intake_air_temp
    from inputeventhub
    )

    Than I define output to a service bus query

    And it happens whenever I add the line that is commented out here below. The one where I use LAST function.

    I am trying to get the last value in the group. TopOne is returning a record but I want to query individual fields.


    select starttime as starttime, 
           MAX(eventtime) as eventtime_last,
           TopOne() OVER (ORDER BY eventtime DESC) as last_values,
           --LAST(deviceid) OVER (PARTITION BY starttime LIMIT DURATION(hour, 1)) as s,
           AVG(latitude.sensorvalue) as latitude_avg,
           AVG(longitude.sensorvalue) as longitude_avg,
           AVG(vehicle_speed.sensorvalue) as vehicle_speed_avg,
           AVG(engine_rpm.sensorvalue) as engine_rpm_avg,
           AVG(intake_air_temp.sensorvalue) as intake_air_temp_avg,
           deviceid,
           customerid 
    into outputifsapplications
    from carsensorvalues
    group by starttime, deviceid, customerid, TumblingWindow(hour, 1)


    Monday, August 24, 2015 9:05 AM

Answers

  • LAST is not an aggregate function in Azure Stream Analytics today. You can use it in SELECT query and get most recent value matching WHEN condition up to specified timeout. We are considering exposing it as an aggregate as well.

    Lacking details on compilation error is a bug. Thank you for reporting that!

    Can you just add another step to the query to read deviceID field from the record produced by TopOne as a workaround?

    WITH step1 AS
    (
        select starttime as starttime,
            MAX(eventtime) as eventtime_last,
            TopOne() OVER (ORDER BY eventtime DESC) as last_values,
            AVG(latitude.sensorvalue) as latitude_avg,
            AVG(longitude.sensorvalue) as longitude_avg,
            AVG(vehicle_speed.sensorvalue) as vehicle_speed_avg,
            AVG(engine_rpm.sensorvalue) as engine_rpm_avg,
            AVG(intake_air_temp.sensorvalue) as intake_air_temp_avg,
            deviceid,
            customerid
     from carsensorvalues
     group by starttime, deviceid, customerid, TumblingWindow(hour, 1)
    )

    select
        starttime,
        last_values.deviceid,
        latitude_avg,
        longitude_avg,
        vehicle_speed_avg,
        engine_rpm_avg,
        intake_air_temp_avg    
    into outputifsapplications
    from step1

    • Marked as answer by Gökhan Kurt Tuesday, August 25, 2015 7:41 AM
    Monday, August 24, 2015 11:24 PM

All replies

  • LAST is not an aggregate function in Azure Stream Analytics today. You can use it in SELECT query and get most recent value matching WHEN condition up to specified timeout. We are considering exposing it as an aggregate as well.

    Lacking details on compilation error is a bug. Thank you for reporting that!

    Can you just add another step to the query to read deviceID field from the record produced by TopOne as a workaround?

    WITH step1 AS
    (
        select starttime as starttime,
            MAX(eventtime) as eventtime_last,
            TopOne() OVER (ORDER BY eventtime DESC) as last_values,
            AVG(latitude.sensorvalue) as latitude_avg,
            AVG(longitude.sensorvalue) as longitude_avg,
            AVG(vehicle_speed.sensorvalue) as vehicle_speed_avg,
            AVG(engine_rpm.sensorvalue) as engine_rpm_avg,
            AVG(intake_air_temp.sensorvalue) as intake_air_temp_avg,
            deviceid,
            customerid
     from carsensorvalues
     group by starttime, deviceid, customerid, TumblingWindow(hour, 1)
    )

    select
        starttime,
        last_values.deviceid,
        latitude_avg,
        longitude_avg,
        vehicle_speed_avg,
        engine_rpm_avg,
        intake_air_temp_avg    
    into outputifsapplications
    from step1

    • Marked as answer by Gökhan Kurt Tuesday, August 25, 2015 7:41 AM
    Monday, August 24, 2015 11:24 PM
  • Yes sure I can do that

    Thx

    Tuesday, August 25, 2015 7:41 AM