Polybase to treat \N as NULL (or allow custom null support)
I was trying to load external file into SQL DW table using polybase. This external file has \N as value for null columns. For example :
2,"C1008",1,\N,1,1,"2016-02-09 23:13:02","2014-02-21 20:11:36","2016-02-08 17:48:59","2008-07-16 14:17:40",947
I defined external data format as below:
CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
( FORMATTYPE = DELIMITEDTEXT
, FORMATOPTIONS ( FIELD_TERMINATOR = ','
, STRING_DELIMITER = ''
, USE_TYPE_DEFAULT = FALSE
)
);
when i try to load this record into DW table, I am getting below error:
Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.
(/mpadetlhistory/mpadetlhistory.csv.gz)Column ordinal: 1, Expected data type: INT, Offending value: \N (Column Conversion Error), Error: Error converting data type NVARCHAR to INT. [SQL State=S0001, DB Errorcode=107090]
Is there any way to define null values while loading data using polybase. If need, is this something can be implemented in future release?
Anonymous
shared this idea
Thank you for the request. This item is currently on our backlog and under review. We will update the item when the status changes.
3 comments
-
Daniel
commented
Azure data factory wants to write '\N' for nulls. You have to fight with it to get to treat nulls as nulls.
Not withstanding that, as user I want to define an external file format that can handle this scenario.
As a user, I want polybase to fault-tolerant.
I dont want a polybase failure because it can't convert '\N' to a datetime2 value.Polybase should be able to easily specify convert it to a custom default value or null.
-
Hugo PT
commented
Another must have feature for Polybase that is missing. With Hive files grow over columns without changes in order, additional new columns (so easy) are null over history, and do not appear while tables structure remains unchanged (ignored, also very important feature related to this point). With Polybase everytime a file changes data starts missing! Actually it only works retrieving data that matches the defined structures 1-1...
-
Shabbir
commented
I am stuck with the same problem. As anyone being able to resolve this issue or got a work around?