Building stream analytics project with reference data join requires time bound

[jeremy-ellis-tech]

I have a stream analytics project I'm trying to build using azure-streamanalytics-cicd@2.2.5 The project has 2 inputs:

{
    "Name": "StreamInput",
    "Type": "Data Stream",
    "DataSourceType": "Event Hub",
    "EventHubProperties": {
    ...

and

{
    "Name": "ReferenceInput",
    "Type": "Reference data",
    "DataSourceType": "SQL Database",
    "SqlReferenceProperties": {

The query is:

SELECT S.DeviceKey, R.DeviceDbId
INTO MyOutput
FROM StreamInput S
JOIN ReferenceInput R
ON S.DeviceKey = R.DeviceKey

Because the join on ReferenceInput is a reference data join the time bound is not necessary according to the docs. (I also get an error trying to add an optional one)

However, trying to build the project using npx azure-streamanalytics-cicd build -project project.asaproj gives me the following error:

[Error] 4/20/2022 1:45:08 PM : The join predicate is not time bounded. JOIN operation between data streams requires specifying max time distances between matching events. Please add DATEDIFF to the JOIN condition.
Example:
SELECT input1.a, input2.b FROM input1 JOIN input2 ON DATEDIFF(minute, input1, input2) BETWEEN 0 AND 10

Alternatively, when I preview the job in vscode with Run Locally -> Use Live Input and Local Output it seems to be able to preview the project.

[ms-guizha]

if the previous version still doesn't work well, it may exist here for quite long time, anyway, we will have a hot fix as soon as possible to unblock you

[pengyuli-ms]

Hi @jeremy-ellis-tech , I noticed you're running the CICD command using a Visual Studio project (because the project file is project.asaproj, rather than asaproj.json), I tried the same configuration with you and observed the same error. We will figure out why and try to fix it. And you're already using our VSCode extension, as a workaround (also a recommendation), you could try to create the same project in VSCode, and running the CICD command on that new project, this issue should be gone.

[jeremy-ellis-tech]

@pengyuli-ms Thanks. Using a asaproj.json project format instead of project.asaproj has resolved the issue. Is the json project file format the newer and preferred project format?

[pengyuli-ms]

@jeremy-ellis-tech yes! VSCode extension is our top priority tool for ASA, it supports more features, and the VS extension is out of date actually.

[jeremy-ellis-tech]

Thanks for the clarification. I didn't know the two project types (vscode and visual studio) were so different considering the vs extension also contained the asaproj.json file I just assumed they used the same format.

[AndreasHassing]

@pengyuli-ms I get this same issue with (local) reference data even with asaproj.json configuration (created from latest version of the VS Code extension (v1.2.0)).

I created a repository to repro the issue: https://github.com/AndreasHassing/ASAVsCodeReferenceDataIssueRepro