Azure stream analytics - Joining on a csv file returns 0 rows

Question

I have the following query:

SELECT
[VanList].deviceId
,[VanList].[VanName]
events.[timestamp]
,events.externaltemp
,events.internaltemp
,events.humidity
,events.latitude    
,events.longitude

INTO
    [iot-powerBI]
FROM
    [iot-EventHub] as events timestamp by [timestamp]
    join [VanList] on events.DeviceId = [VanList].deviceId

where iot-eventHub is my event hub and VanList is a reference list (csv file) that has been uploaded to azure storage.

I have tried uploading sample data to test the query, but it always returns 0 rows.

Below is a sample of the JSON captured by my Event Hub Input

    [
   {
      "DeviceId":1,
      "Timestamp":"2015-06-29T12:15:18.0000000",
      "ExternalTemp":9,
      "InternalTemp":8,
      "Humidity":43,
      "Latitude":51.3854942,
      "Longitude":-1.12774682,
      "EventProcessedUtcTime":"2015-06-29T12:25:46.0932317Z",
      "PartitionId":1,
      "EventEnqueuedUtcTime":"2015-06-29T12:15:18.5990000Z"
   } ]

Below is a sample of my CSV reference data.

deviceId,VanName
1,VAN 1
2,VAN 2
3,Standby Van

Both lists contain a device id of 1, so I am expecting my query to be able to join the two together.

I have tried using both "inner join" and "join" in my query syntax, but neither result in a successful join. What is wrong with my Stream Analytics query?

Answer 1

Try adding a CAST function in the join. I'm not sure why that works and adding a CREATE TABLE clause for the VanList reference data input doesn't accomplish the same thing. But I think this works.

SELECT
[VanList].deviceId
,[VanList].[VanName]
,events.[timestamp]
,events.externaltemp
,events.internaltemp
,events.humidity
,events.latitude    
,events.longitude

INTO
    [iot-powerBI]
FROM
    [iot-EventHub] as events timestamp by [Timestamp]
    join [VanList] on events.DeviceId = cast([VanList].deviceId as bigint)