Data Lake Analytics and Nullable DateTime fields

My first solo foray into Data Lake Analytics on Azure had me stumped for a while so I thought I’d share in case anyone came across the same problem.

I had created a CSV file from some live data extracted from SQL Server which contained several nullable DateTime fields. The upload to Azure Data Lake storage was fine, but when I ran my U-SQL job it failed.

It turns out, although I had declared the field as nullable in the U-SQL script:-

DECLARE @in string = @"data/MyFile.csv";
// Define schema of file
@absences =
	EXTRACT OrganisationId int,
			StartDate DateTime,
			EndDate DateTime?,
			CompletionDate DateTime?
	FROM @in
	USING Extractors.Csv();

It did not like the fact that the CSV file contained ‘NULL’ in the field:-

1,2017-07-11 00:00:00.000,NULL,NULL
1,2017-07-10 00:00:00.000,2017-07-11 00:00:00.000,NULL
1,2017-07-09 00:00:00.000,2017-07-11 00:00:00.000,2017-07-11 00:00:00.000

I had to replace the ‘NULL’ with an empty field:-

1,2017-07-11 00:00:00.000,,
1,2017-07-10 00:00:00.000,2017-07-11 00:00:00.000,
1,2017-07-09 00:00:00.000,2017-07-11 00:00:00.000,2017-07-11 00:00:00.000

It’s always the stupid little things that get you 🙂