Azure Synapse Analytics

We would love to hear your ideas for new features for Azure Synapse Analytics. Below, enter a new idea or upvote an existing one. The Synapse engineering team pays attention to all requests.

If instead you need a technical question answered or help, try the these options: DocumentationMSDN forum, and StackOverflow. If you need support, please open a support ticket.

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. [In preview] PolyBase: allow escaping string delimiters within string fields

    This feedback item is currently in preview here:

    https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

    If you want to quote the string columns, and if one of the quoted columns has a quote character in it, the proper way to encode this is to double the quote character. (Build a CSV file in Excel and you will see that's how it saves it. I would say Excel is the authority on CSV formats.)

    it appears it's impossible for Polybase to properly load that file. For example the following format is not interpreted right with STRING_DELIMITER = '"' since it divides the value 2.5" into two columns

    240 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    started  ·  28 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  2. [In preview] Polybase: allow field/row terminators within string fields

    This feedback item is currently in preview here:

    https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

    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 textfileformat_raw
    WITH
    (

    FORMAT_TYPE = DELIMITEDTEXT,
    
    FORMAT_OPTIONS
    (
    FIELD_TERMINATOR =';',
    STRING_DELIMITER = '0x22',
    USE_TYPE_DEFAULT = TRUE
    )

    );

    CREATE EXTERNAL TABLE dbo.testexternal
    (
    col1 INT,
    col2 DECIMAL(2,1),
    col3 NVARCHAR(5)
    )
    WITH
    (
    LOCATION = 'test
    failing.txt',
    DATASOURCE = azurestorage,
    FILEFORMAT = textfileformatraw

    );

    CREATE TABLE dbo.test
    WITH…

    230 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    started  ·  26 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  3. Expose File Properties as additional columns to load in SQL DW

    It would be helpful if some of the properties of a BLOB file could be exposed in the external table such as: Last Modified, Filename, Size. It should be optional and could be defined in the EXTERNAL FILE FORMAT definition. Each Property should be appended to the end of the result set of an external table in the order given in the definition.

    For instance, if we are loading a directory of several files we can read the filename and derive a new column. For example, we might have a directory with 20160504.csv and 20160505.csv and the contents of the…

    123 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    9 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  4. Provide File Format to read and load JSON and XML data from Storage

    Able to read the JSON and XML file as part of external system OR provide functionality like Extractor in Data Lake analytics so we can write our own Extractor to read respective data format.

    98 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  4 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  5. [In preview] More flexible date formats in Polybase

    This feedback item is currently in preview here:

    https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

    Polybase currently has very limited date format support. All of the acceptable date formats have dashes or slashes. For example, I can't define a date format of yyyyMMdd since it doesn't have slashes or dashes. Please allow any date format I can express.

    Also, please allow more than one date format per external table. For example, what if I have a date column as yyyyMMdd and then a different column as yyyyMMddHHmm. With only being able to define one date format in the CREATE EXTERNAL FILE FORMAT statement I think I…

    79 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    planned  ·  8 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  6. 58 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    started  ·  2 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  7. 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
    , FORMAT
    OPTIONS ( 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…

    45 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  8. [In preview] Import files via single COPY command from blob store.

    This feedback item is currently in preview here:

    https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

    It shouldn't require 5+ unintelligible queries just to import a file from blob store. Redshift lets me import files (including zipped files) from S3 with a single COPY command.

    44 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    started  ·  0 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  9. Support for Large Objects in External Tables

    While you can read and write to blob storage with large objects like varchar(max) now you can't read them via external tables.

    E.g. If you define a field with varchar(max) in an external table you can't use it you need to use BCP or SSIS to get it into a physical table that is a heap or supported indexed table.

    Msg 102048, Level 16, State 1, Line 56
    Columns with large object types are not supported in external tables.

    38 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    17 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  10. Polybase - Give the ability to specify a file name when outputting external tables and consolidate into a single file

    Creating an external table will result in multiple files being generated in blob storage. It would be great if the multiple files could be consolidated into a single file and be able to configure the file name.

    There are multiple files that contain 0 bytes. It would be better to have a single file and be able to specify the file name.
    This would help with idempotency to delete the file if I wanted to re-create the external table.

    30 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  11. support for fixed width flat files in Polybase

    Polybase currently only supports delimited flat files. It should support fixed width flat files.

    26 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  12. 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 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).

    Code:
    CREATE EXTERNAL FILE FORMAT textfileformatraw
    WITH
    (
    FORMAT
    TYPE = DELIMITEDTEXT, …

    18 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  13. Ability set resultset caching on or off by user

    Add the ability to turn off resultset caching by user. So we can turn it off for our ETL user which is moving large amounts of data throughout the day. Allowing it to stay on for the report user account.

    7 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  14. "COPY INTO" - Support for Parameterized Source Location

    In an incremental load scenarios, data files always land in a new folder in ADLS each time.

    Currently the “COPY INTO” command does not support “Source Location” to be parameterized , it uses a hardcoded source folder location. Please add the ability to use a SQL string variable for the source folder location.

    3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  15. Allow for the option for polybase to truncate columns

    Allow for the option for polybase to truncate columns

    3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  16. Azure Synapse - Query on demand/reservation slot.

    Add another SKU similar to OnDemand query to reserve query compute units, acts like query on demand but no need to load the file.
    Would be beneficial for predictable data - similar to BQ reserved slots

    1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

Feedback and Knowledge Base