Cosmos Synapse Link - SQL Serverless Query

Question

Please describe your issue below

I have a Cosmos database in Well-Defined schema mode. I have a Synapse Serverless SQL instance. I have documents in Cosmos with objects that contain fields with null values. When I create a View for the Container in SQL the null object values are appearing as 0, why would it do this?

Answer 1

@David Baker

Thanks for reaching out to Microsoft Q&A.

It looks like you’re encountering an issue where null values in your Cosmos DB documents are being interpreted as 0 in your Synapse Serverless SQL instance. This can happen due to the way SQL handles null values and data types.

In SQL, when you create a view or query data, null values can sometimes be converted to a default value, such as 0, especially if the field is expected to be a numeric type. This behavior can be influenced by the schema definition and the way the data is being cast or interpreted in the SQL query.

To handle this, you can explicitly check for null values in your SQL view and handle them accordingly. For example, you can use the CASE statement to replace null values with a desired default value or keep them as null.

CREATE VIEW YourView AS
SELECT 
    id,
    customerId,
    orderDate,
    orderTotal,
    CASE 
        WHEN shippingAddress.state IS NULL THEN 'Unknown' 
        ELSE shippingAddress.state 
    END AS State,
    shippingAddress.zip
FROM YourContainer;

In this example, if the state field in the shippingAddress object is null, it will be replaced with ‘Unknown’. You can adjust this logic to fit your specific needs.

Hope this helps. Do let us know if you any further queries.

Answer 2

Hi,

I have the same problem as David and still no solution for it

Here is a minimal example:

avro schema:

{
                              "name": "data",
                              "type": [
                                "null",
                                {
                                  "type": "bytes",
                                  "scale": 9,
                                  "precision": 30,
                                  "connect.version": 1,
                                  "connect.parameters": {
                                    "scale": "9",
                                    "connect.decimal.precision": "30"
                                  },
                                  "connect.name": "org.apache.kafka.connect.data.Decimal",
                                  
                                }
                              ],
                              "default": null
                            }

Document in CosmosDB:

{
    "data": null  } 

Select Statement in Azure Synapse:

SELECT
"data"
FROM   OPENROWSET(
    PROVIDER = 'CosmosDB',     
	CONNECTION = 'XXX',
    OBJECT = 'XXX',
    SERVER_CREDENTIAL = 'XXX'   ) 
with (
	"data" numeric(30,9) '$.data'
   ) AS cosmosdb_select
where
  1 = 1

When executing the SQL, Azure Synapse does not display null but 0.000000000

1. Casting does not help, because for Synapse the data is 0.
2. when trying to change the data type in the with-block to varchar it only changes the output to 0