CODEWITHSUNDEEP
azureazure-rm-templateazure-stream-analytics
nmca70
nmca70

Reputation: 313

Azure Stream Analytics Job - Transformation Query - correct formatting in ARM template

When editing a Stream Analytics transformation query in the Portal, you can format it for readability across multiple lines...e.g.

SELECT 
INTO [Output1]
FROM [Input1]
PARTITION BY PartitionId
WHERE etc etc etc

When putting this into an ARM template for CI/CD, this is entered as one massive long string and would end up displaying in the portal as...

SELECT * INTO [Output1] FROM [Input1] PARTITION BY PartitionId WHERE etc etc etc to infinity....

The official documentation is pretty useless and doesn't give any clues for the query part of the template, just that it is a "string"...

https://learn.microsoft.com/en-us/azure/templates/microsoft.streamanalytics/2016-03-01/streamingjobs/transformations

There is a Microsoft sample template that is the only example I could find with a transform query specified... https://github.com/Azure/azure-quickstart-templates/blob/master/101-streamanalytics-create/azuredeploy.json ...and it looks like it is trying to do spacing...

"query": "SELECT\r\n    *\r\nINTO\r\n    [YourOutputAlias]\r\nFROM\r\n    [YourInputAlias]"

...but failing badly - see screenshot

Has anyone managed to do this?

Also does anyone know why you can see the transformation query in the Azure Resource Explorer (https://resources.azure.com/)? Or that it cannot be exported from the portal with the rest of the Stream Job? (done at Resource Group level)

Thanks in advance

enter image description here

Upvotes: 1

Views: 487

Answers (1)

user3246601
user3246601

Reputation: 26

I know it is a full year later and perhaps you've figured this out already, however, this is what I did:

In my Parameters file, I used an array of strings, for example:

"StreamAnalyticsJobQueryMultiline": {
    "value": [
    "WITH allData AS ( ",
    "    SELECT ",
    "        *, ",
    "        GetMetadataPropertyValue([%%INPUTSTREAMNAME%%], '[User].[EventNamespace]') AS EventNamespace ",
    "    FROM [%%INPUTSTREAMNAME%%] Partition By PartitionId ",
    "SELECT ",
    "    *, ",
    "    'EventHubWriterv1' AS EventType ",
    "INTO ",
    "    [%%OUTPUTSTREAMNAME%%] ",
    "FROM ",
    "    allData Partition By PartitionId "
    ]

When the array is concatenated, and output as a string, it produces something like this, where each item in that array is still enclosed by the quotation marks and the entire thing is contained within square braces (see: https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/template-functions-string#concat)

["Foo","Bar","Blah"]

So additional transformation in order to turn it into something readable in the Stream Analytics output is required.
Also note here the %%INPUTSTREAMNAME%% and %%OUTPUTSTREAMNAME%%, as both my input and output streams are also parameters, and using the typical inline [parameter('ParamName')] did not work nicely with the rest of the transformation needed.

In my Template file, I take the StreamAnalyticsJobQueryMultiline parameter and use the variables field to do this transformation:

"QueryStringRaw":  "[string(concat(parameters('StreamAnalyticsJobQueryMultiline')))]",
    
// Update the end-of-lines by removing the doublequote and comma, and replacing it with a newline character 
"QueryStringIter1": "[replace(variables('QueryStringRaw'), '\",', '\n')]",

// Update the beginning-of-lines by removing the doublequote
"QueryStringIter2": "[replace(variables('QueryStringIter1'), '\"', '')]",

// Update the InputStreamName and OutputStreamName values
"QueryStringIter3": "[replace(variables('QueryStringIter2'), '%%INPUTSTREAMNAME%%', parameters('InputStreamName'))]",
"QueryStringIter4": "[replace(variables('QueryStringIter3'), '%%OUTPUTSTREAMNAME%%', parameters('OutputStreamName'))]",

// Produce the final output for the query string by trimming the leading and trailing square brackets
"QueryStringFinal": "[substring(variables('QueryStringIter4'), 1, sub(length(variables('QueryStringIter4')), 2))]"

Then I reference that in the transformation portion of the Microsoft.StreamAnalytics/streamingjobs properties:

"transformation": {
    "name": "Transformation",
    "properties": {
        "streamingUnits": "[parameters('StreamAnalyticsStreamingUnitsScale')]",
        "query": "[variables('QueryStringFinal')]"
    }
}

Upvotes: 1

Related Questions