Azure流分析-在CSV文件上加入将返回0行 [英] Azure stream analytics - Joining on a csv file returns 0 rows

问题描述

我有以下查询:

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

其中iot-eventHub是我的事件中心，而VanList是已上传到Azure存储的参考列表(csv文件).

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

我尝试上传示例数据以测试查询，但是它始终返回0行.

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

下面是我的事件中心输入捕获的JSON示例

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"
   } ]

下面是我的CSV参考数据的示例.

Below is a sample of my CSV reference data.

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

两个列表的设备ID均为1，因此我希望我的查询能够将两者结合在一起.

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

我尝试在查询语法中同时使用内部联接"和联接"，但均未成功完成联接.我的Stream Analytics查询有什么问题?

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?

推荐答案

尝试在联接中添加CAST函数.我不确定为什么行得通，为VanList参考数据输入添加CREATE TABLE子句不会完成相同的事情.但是我认为这可行.

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)

这篇关于Azure流分析-在CSV文件上加入将返回0行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！