Creating an Azure Stream Analytics Output with Postgresql datasource using az cli in powershell fails

Question

When I try to create an Azure Stream Analytics Output with a Postgresql datasource using az cli in Powershell on an existing stream analytics job and a postgresql database, it fails returning

(BadRequest) The JSON provided in the request body is invalid. The required property 'datasource type' is missing from the request.
Code: BadRequest

Here is the testing powershell code:

$dataSourceJson = @"
{
    "type": "Microsoft.DBforPostgreSQL/servers/databases",
    "properties": {
        "server": "<YourHostFullName>",
        "database": "<YourDbName>",
        "table": "<YourTableName>",
        "user": "<user>",
        "password": "<password>"
    }
}
"@

# Output to verify the content
Write-Host "Datasource JSON String:"
Write-Host $dataSourceJson

$dataSource = ($dataSourceJson -replace "\s+", "") -replace '"', '\"'

Write-Host "Datasource JSON String on a single line, no spaces and double-quote escaped:"
Write-Host $dataSource

# Create the Azure Stream Analytics output using the datasource
az stream-analytics output create `
    --job-name "<YourJobName>" `
    --name "<YourOutputName>" `
    --resource-group "<YourResourceGroupName>" `
    --datasource "$dataSource" `
    --verbose

You could think that the Postgres output is not supported, but you can create it however into the Azure Portal. It also works using Terraform, eventhough the type of the datasource is missing in the output description afterwards (using az stream-analytics output show).

The datasourceJson string seems to have the right format as when you replace the postgres type "Microsoft.DBforPostgreSQL/servers/databases" by an Azure SQL type "Microsoft.Sql/Server/Database", it succeeds.

Any clue ?

Answer 1

Thank you for your response, @Smaran Thoomu . However, in my initial question, I mentioned that I am already using the type "Microsoft.DBforPostgreSQL/servers/databases" in my datasource JSON.

$dataSourceJson = @" 
{ 
	"type": "Microsoft.DBforPostgreSQL/servers/databases", 
	"properties": { 
		"server": "<YourHostFullName>", 
		"database": "<YourDbName>", 
		"table": "<YourTableName>", 
		"user": "<user>", 
		"password": "<password>" 
	} 
} "@

Despite this, I still encounter an error indicating that the required property "datasource type" is missing.

Additionally, when I use the az stream-analytics output list command to list the outputs of my Stream Analytics job (postgresql output), I receive a null value for the type:

{ 
	"properties": {
		"authenticationMode": "ConnectionString",
		"database": "xxx",
		"maxWriterCount": 0,
		"server": "xxx",
		"table": "xxx",
 		"user": "xxx"       
	},       
	"type": null     
}

Unlike what I see when inspecting the data through the Azure portal (DevTool), where the type is clearly defined as "Microsoft.DBForPostgreSQL/servers/databases."

{
	"type": "Microsoft.DBForPostgreSQL/servers/databases",
	"properties": {
		"maxWriterCount": 0,
		"tabxxx", 		
		"server": "xxx", 		
		"database": "xxx", 		
		"user": "xxx", 		
		"authenticationMode": "ConnectionString" 	
	} 
}

Do you have any further recommendations on how to resolve this JSON validation issue or ensure that the datasource type is correctly recognized in all CLI operations? I have, of course, tried your version, but it doesn't work either... This strongly resembles a bug in the az stream-analytics output, as if it were unable to interpret the type "Microsoft.DBForPostgreSQL/servers/databases."