How can we improve Microsoft Azure SQL Data Warehouse ?

Polybase - fix file format type default for decimals

Currently it's not possible to load a file with empty decimal value and convert it to 0 when loading data.

This will load fine:
1;2.1;"zzzz"

This will fail:
1;;"zzzz"

According to the documentation( https://msdn.microsoft.com/en-us/library/dn935026.aspx) when USE_TYPE_DEFAULT = TRUE is set in the file format definition empty value should be converted to 0. While it works for Integers, it doesn't work for decimals. USE_TYPE_DEFAULT = FALSE will work fine for decimals (but of course the value will be loaded as NULL).

Code:
CREATE EXTERNAL FILE FORMAT text_file_format_raw
WITH
(
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS
(
FIELD_TERMINATOR =';',
STRING_DELIMITER = '0x22',
USE_TYPE_DEFAULT = TRUE
)
);

CREATE EXTERNAL TABLE dbo.test_external
(
col1 INT,
col2 DECIMAL(2,1),
col3 NVARCHAR(5)
)
WITH
(
LOCATION = 'test.txt',
DATA_SOURCE = azure_storage,
FILE_FORMAT = text_file_format_raw
);

CREATE TABLE dbo.test
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUND_ROBIN)
AS
SELECT * FROM dbo.test_external;

15 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Adam shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

3 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base