CODEWITHSUNDEEP
azure-data-explorerazure-data-factory
vasyl_ms
vasyl_ms

Reputation: 11

Cannot ingest null values in Azure Data Explorer scalar columns

I use Azure Data Factory to ingest data into Azure Data Explorer and the ingestion keeps failing because of data conversion issue.

Kusto table definition: .create table Overview (..., Age: real)

Exception happens when I try to ingest null value into Age column.

Operation on target CopyOverviewFromBlobToADE failed: Failure happened on 'Sink' side. ErrorCode=UserErrorKustoWriteFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Write to Kusto failed with following error: An error occurred for source: 'DataReader'. Error: 'One or more errors occurred.'.,Source=Microsoft.DataTransfer.Runtime.KustoConnector,''Type=Kusto.Ingest.Exceptions.IngestClientException,Message=An error occurred for source: 'DataReader'. Error: 'One or more errors occurred.',Source=Kusto.Ingest,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'Age' contains an invalid value 'null'. Cannot convert 'null' to type 'Double'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'

It says here: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/ All non-string data types include a special "null" value, which represents the lack of data or a mismatch of data. For example, attempting to ingest the string "abc" into an int column results in this value.

How can null values be ingested then?

Upvotes: 0

Views: 861

Answers (2)

Vladik Branevich
Vladik Branevich

Reputation: 1175

On ingestion ADX does not recognize string literal 'null' as an indicator of a null value. An empty string or simply omitting the value/field will result in a null value being ingested.

Upvotes: 0

vasyl_ms
vasyl_ms

Reputation: 11

I fixed my issue by removing the Age property from a record that has it as 'null'.

So in My case the source is Blob with json files and in case the document has valid real type value the json would be { ... "Age": "2.5", ... }, in case the Age is missing I have to get rid of the Age property in the document altogether.

It turns out in ADF you can get rid of the property with the following derived column expression in the data flow: iif(equals(Age, 'None'), toString(null()), Age).

Upvotes: 1

Related Questions