How can we improve Microsoft Azure SQL Data Warehouse ?

Polybase: allow field/row terminators within string fields

If a line end is present within a text field (qualified) importing data using Polybase will fail.

Loading this line will work fine:
1;2.1;"zzzz"

Loading this will fail:
1;2.1;"zz
zz"

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_failing.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;

142 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    11 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base