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:
This will fail:
According to the documentation( https://msdn.microsoft.com/en-us/library/dn935026.aspx) when USETYPEDEFAULT = 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. USETYPEDEFAULT = FALSE will work fine for decimals (but of course the value will be loaded as NULL).
CREATE EXTERNAL FILE FORMAT textfileformatraw
FORMATTYPE = DELIMITEDTEXT,
STRINGDELIMITER = '0x22',
USETYPE_DEFAULT = TRUE
CREATE EXTERNAL TABLE dbo.testexternal
LOCATION = 'test.txt',
DATASOURCE = azurestorage,
FILEFORMAT = textfileformat_raw
CREATE TABLE dbo.test
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUNDROBIN)
SELECT * FROM dbo.testexternal;
Thank you for reporting this issue. We have identified this as a product defect and plan to fix this behavior in an upcoming release. 7297383.
Jamey Johnston commented
still a problem. Any resolution?
Sai Nandam commented
Has this been resolved, I am still seeing this issue.
Any status on this ?