Always use UTF-8 collations to read UTF-8 text in serverless SQL pool

Published 11-13-2020 12:52 AM 6,547 Views
Microsoft

Synapse serverless SQL pool is a query engine that enables you to query a variety of files and formats that you store in Azure Data Lake and Azure Cosmos DB. One very common text encoding format is UTF-8 encoding where the most common characters used in Latin western languages are encoded with a single byte. Not very common western, Cyrillic, Turkish and other characters are encoded with 2 bytes, and the special characters are encoded with more than 2 bytes. The UTF-8 encoding is popular because it is optimal for majority of western languages, has the same the storage efficiency as the UTF-16 encodings in most of the character sets.

A serverless SQL pool in Azure Synapse Analytics enables you to read UTF-8 encoded text as VARCHAR columns and this is the most optimal approach for representing UTF-8 data. But you need to be careful to avoid conversion errors that might be caused by wrong collations on VARCHAR columns. 

 

At the time of writing this post, Synapse SQL forces conversion of UTF-8 characters to plain VARCHAR characters if UTF-8 collation is not assigned to VARCHAR type. This behavior might cause unexpected text conversion error.

 

This conversion issue might happen if you use OPENROWSET without WITH clause or OPENROWSET/External table that return VARCHAR column without UTF8 collation.

 

This issue is not applicable if you are using some NVARCHAR types to represent UTF-8 data. The NVARCHAR type is not dependent on a collation because it always represents characters as 2 or 4 byte sequences. However; with NVARCHAR type you have a performance issue because every UTF-8 character must be converted to NVARCHAR type.

 

In this article you will learn when this unexpected conversion can happen, how to avoid it, or how to fix the issue.

What is a collation?

A collation is a property of string types in SQL Server, Azure SQL, and Synapse SQL that defines how to compare and sort strings. In addition, it describes the encoding of string data. If a collation name in Synapse SQL ends with UTF8, it represents the strings encoded with the UTF-8 encoding schema. Otherwise you have something like UTF-16 encoded string.

In the following example is shown how to specify a collation associated to the string columns in an external table definition:

 

CREATE EXTERNAL TABLE population (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

 

 

This table references CSV file and the string columns don’t have UTF8 collation. Therefore, the CSV file should not be UTF-8 encoded if you want to read the data with this table. The mismatch between the encoding that is specified in the column collation and the encoding in the underlying files would probably cause a conversion error. In this case, if your population data contains some UTF-8 characters, they would be incorrectly converted once you read data. Therefore, you might need to use some UTF-8 collation instead of Latin1_General_BIN2 after the COLLATE clause.

What are the special UTF-8 encoded characters?

The UTF-8 encoding represents most of the characters using 1 byte, but there are some characters that are not common in western languages. One example might be characters ü and ö in German words Düsseldorf and Schönwald.

Let us imagine that we have a CSV file encoded with UTF-8 encoding scheme with the names of the towns containing these characters. If we preview the content of this file in Synapse Studio, we will get the following result:

JovanPop_0-1605256908984.png

Synapse Studio enables us to read the content of this file using the T-SQL queries with the  OPENROWSET function. Running a T-SQL query on database with default or any non-UTF8 collation might not return expected results:

JovanPop_1-1605256909001.png

You might see that the towns Düsseldorf and Schönwald are not same as in the preview on the previous picture.

In Synapse SQL, you must use some UTF-8 collation to return data from the UTF-8 files. Otherwise, the non-common characters would be suddenly converted.

At the time of writing this post, Synapse SQL silently forces conversion of UTF-8 characters to non-UTF-8 characters, and returns a warning message that this configuration might cause a conversion error.

The following functions and objects might be affected:

  1. The OPENROWSET function without WITH clause that returns VARCHAR columns.
  2. The OPENROWSET function with explicit WITH clause that returns VARCHAR columns without specified collation. This function is affected only if a database collation is set to some of the non-UTF8 collations.
  3. The OPENROWSET function with WITH clause that returns VARCHAR columns with explicitly set non-UTF8 collations.
  4. An external table that contains the VARCHAR columns without explicit collation. These external tables are affected only if a database collation at the time of creation of the table was some of the non-UTF8 collations.
  5. An external table that contains the VARCHAR columns with explicitly specified non-UTF8 collations.

It might be hard to exactly identify in what cases the issue might happen. There are two ways to avoid this issue or resolve it if it happens in some of the queries:

  • Setting a UTF-8 collation a database collation will resolve issues in scenarios 1 and 2. This solution will resolve issue in scenario 4 if you re-create the table.
  • Setting an UTF-8 collation on every string column that the OPENROWSET function returns will resolve issues in scenarios 3, 4, and 5

Setting database collation

If you are working with UTF-8 data, the best way to configure your database is to set the default collation on every database. You can set collation as part of the CREATE DATABASE T-SQL statement:

 

CREATE DATABASE mydb 
    COLLATE Latin1_General_100_BIN2_UTF8;

 

 

If you have an existing database, you can easily change the default collation:

 

ALTER DATABASE mydb 
    COLLATE Latin1_General_100_BIN2_UTF8;

 

 

From this point, every OPENROWSET function will return correctly converted data.

Note that you would need to drop and re-create external tables if you have not explicitly specified collation. New default database collation will be applied only when table is created.

Specifying explicit collations

Instead of defining default database collation, you can explicitly specify collation when you declare column type using WITH clause.

The OPENROWSET function enables you to explicitly specify columns and their types in the WITH clause:

 

SELECT TOP 10 *
FROM OPENROWSET(
        BULK 'latest/ecdc_cases.parquet',
        DATA_SOURCE = 'covid',
        FORMAT = 'parquet'
    ) WITH ( date_rep DATE, cases INT,
             geo_id VARCHAR(6) COLLATE Latin1_General_100_BIN2_UTF8 ) as rows

 

 

If you are reading parquet files, or UTF-8 encoded text files, you would need to add an UTF-8 collation in the type specification.

If you are defining tables, you can explicitly specify collation in column definition:

 

CREATE EXTERNAL TABLE population (
    [country_code] VARCHAR (5) COLLATE Latin1_General_100_BIN2_UTF8,
    [country_name] VARCHAR (100) COLLATE Latin1_General_100_BIN2_UTF8,
    [year] SMALLINT,
    [population] BIGINT
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

 

 

This way you can be sure that your table will return correct text.

NOTE: If you have existing tables that used default database collation, changing default database collation would not be applied on them. You would need to drop and re-create external tables so they can pickup new default.

Conclusion

Matching column collation of string columns and encoding in the files is important to avoid unexpected conversion errors. Currently, a serverless SQL pool silently converts UTF-8 data to non-UTF-8 data even if it knowns that there is a mismatch causing potential conversion error. This conversion causes a warning, so make sure that you are using proper collations in the OPENROWSET function and external tables to avoid errors.

To mitigate this issue, you need to alter database to use the default UTF8 collation or specify collation explicitly on every string column.

5 Comments
Frequent Visitor

Thanks for the advice.

 

When running the script on a database on the on-demand pool, the following error pops up

ALTER DATABASE XXXX COLLATE Latin1_General_100_CI_AS_SC_UTF8;
 

 

The database XXXX could not be exclusively locked to perform the operation.

 

Any workaround on this ? Standard resolution used on provisioned pool or SQL DB doesn't work

Occasional Visitor

@JovanPop: Having the same issue as @gh_lausanne : The database XXXX could not be exclusively locked to perform the operation. Cannot use the standard ways to list open connections either. 

 

Solved it by listing active sessions and killing them manually. However there are some connections that seem to auto-reconnect, but by executing the statements in one batch instead of command by command it worked for me. 

 

SELECT [session_id] 
FROM [sys].[dm_exec_connections]

-- Make kill statements for each session_id

kill 83
kill 82
kill 80
kill 79
ALTER DATABASE xxxx
COLLATE Latin1_General_100_CI_AS_SC_UTF8;

 

Senior Member

Thanks @RandomRichard  & @gh_lausanne for your feedback! Did run into the same error and you posted a working solution to it!

Regular Visitor

@JovanPop how should you handle csv files in UCS-2 LE BOM format with a Synapse Serverless SQL Pool?
When querying it gives an error:

Error handling external file: 'Incorrect file encoding. DATAFILETYPE = 'char' selected for querying UTF16 file.'. File/External table name:...

 

Converting the file to UTF-8 in Notepad++ resolves the issue BUT is there a way to query without converting?

 

Example file format (from Notepad++):

2021-03-04_16-54-47.jpg

Occasional Visitor

Hi @Stuart Lock,

 

Not sure whether you were able to solve this issue, but to help you and others facing a similar situation, here's what I did.

 

Basically, UCS-2 LE BOM and UTF-16 are very similar, so the trick is to define your source format as being encoded in UTF-16, e.g.: 

 

 

    CREATE EXTERNAL FILE FORMAT QuotedCsvWithHdrFormat
    WITH (  
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS ( PARSER_VERSION = '2.0', FIELD_TERMINATOR = '|', STRING_DELIMITER = '"', FIRST_ROW = 2, Encoding = 'UTF16' )
    );

And then align the corresponding format definition in your CREATE EXTERNAL TABLE statement.

 

 

From there, your SELECT query should work. I did not have to specify any COLLATION.

Hope this helps!

%3CLINGO-SUB%20id%3D%22lingo-sub-1917660%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20use%20UTF-8%20collations%20to%20read%20UTF-8%20text%20in%20serverless%20SQL%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1917660%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20advice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20running%20the%20script%20on%20a%20database%20on%20the%20on-demand%20pool%2C%20the%20following%20error%20pops%20up%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20database%20XXXX%20could%20not%20be%20exclusively%20locked%20to%20perform%20the%20operation.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20workaround%20on%20this%20%3F%20Standard%20resolution%20used%20on%20provisioned%26nbsp%3Bpool%20or%20SQL%20DB%20doesn't%20work%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1922947%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20use%20UTF-8%20collations%20to%20read%20UTF-8%20text%20in%20serverless%20SQL%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F296874%22%20target%3D%22_blank%22%3E%40JovanPop%3C%2FA%3E%3A%20Having%20the%20same%20issue%20as%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876102%22%20target%3D%22_blank%22%3E%40gh_lausanne%3C%2FA%3E%26nbsp%3B%3A%26nbsp%3B%3CSPAN%3EThe%20database%20XXXX%20could%20not%20be%20exclusively%20locked%20to%20perform%20the%20operation.%20Cannot%20use%20the%20standard%20ways%20to%20list%20open%20connections%20either.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESolved%20it%20by%20listing%20active%20sessions%20and%20killing%20them%20manually.%20However%20there%20are%20some%20connections%20that%20seem%20to%20auto-reconnect%2C%20but%20by%20executing%20the%20statements%20in%20one%20batch%20instead%20of%20command%20by%20command%20it%20worked%20for%20me.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20%5Bsession_id%5D%26nbsp%3B%3CBR%20%2F%3EFROM%20%5Bsys%5D.%5Bdm_exec_connections%5D%3C%2FP%3E%3CP%3E--%20Make%20kill%20statements%20for%20each%20session_id%3C%2FP%3E%3CP%3Ekill%2083%3CBR%20%2F%3Ekill%2082%3CBR%20%2F%3Ekill%2080%3CBR%20%2F%3Ekill%2079%3CBR%20%2F%3EALTER%20DATABASE%20xxxx%3CBR%20%2F%3ECOLLATE%20Latin1_General_100_CI_AS_SC_UTF8%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1976529%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20use%20UTF-8%20collations%20to%20read%20UTF-8%20text%20in%20serverless%20SQL%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1976529%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878553%22%20target%3D%22_blank%22%3E%40RandomRichard%3C%2FA%3E%26nbsp%3B%20%26amp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876102%22%20target%3D%22_blank%22%3E%40gh_lausanne%3C%2FA%3E%26nbsp%3Bfor%20your%20feedback!%20Did%20run%20into%20the%20same%20error%20and%20you%20posted%20a%20working%20solution%20to%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1883633%22%20slang%3D%22en-US%22%3EAlways%20use%20UTF-8%20collations%20to%20read%20UTF-8%20text%20in%20serverless%20SQL%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1883633%22%20slang%3D%22en-US%22%3E%3CP%3ESynapse%20serverless%20SQL%20pool%20is%20a%20query%20engine%20that%20enables%20you%20to%20query%20a%20variety%20of%20files%20and%20formats%20that%20you%20store%20in%20Azure%20Data%20Lake%20and%20Azure%20Cosmos%20DB.%20One%20very%20common%20text%20encoding%20format%20is%20UTF-8%20encoding%20where%20the%20most%20common%20characters%20used%20in%20Latin%20western%20languages%20are%20encoded%20with%20a%20single%20byte.%20Not%20very%20common%20western%2C%20Cyrillic%2C%20Turkish%20and%20other%20characters%20are%20encoded%20with%202%20bytes%2C%20and%20the%20special%20characters%20are%20encoded%20with%20more%20than%202%20bytes.%20The%20UTF-8%20encoding%20is%20popular%20because%20it%20is%20optimal%20for%20majority%20of%20western%20languages%2C%20has%20the%20same%20the%20storage%20efficiency%20as%20the%20UTF-16%20encodings%20in%20most%20of%20the%20character%20sets.%3C%2FP%3E%0A%3CP%3EA%20serverless%20SQL%20pool%20in%20Azure%20Synapse%20Analytics%20enables%20you%20to%20read%20UTF-8%20encoded%20text%20as%20VARCHAR%20columns%20and%20this%20is%20the%20most%20optimal%20approach%20for%20representing%20UTF-8%20data.%20But%20you%20need%20to%20be%20careful%20to%20avoid%20conversion%20errors%20that%20might%20be%20caused%20by%20wrong%20collations%20on%20VARCHAR%20columns.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAt%20the%20time%20of%20writing%20this%20post%2C%20Synapse%20SQL%20forces%20conversion%20of%20UTF-8%20characters%20to%20plain%20VARCHAR%20characters%20if%20UTF-8%20collation%20is%20not%20assigned%20to%20VARCHAR%20type.%20This%20behavior%20might%20cause%20unexpected%20text%20conversion%20error.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThis%20conversion%20issue%20might%20happen%20if%20you%20use%20OPENROWSET%20without%20WITH%20clause%20or%20OPENROWSET%2FExternal%20table%20that%20return%20VARCHAR%20column%20without%20UTF8%20collation.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThis%20issue%20is%20not%20applicable%20if%20you%20are%20using%20some%20NVARCHAR%20types%20to%20represent%20UTF-8%20data.%20The%20NVARCHAR%20type%20is%20not%20dependent%20on%20a%20collation%20because%20it%20always%20represents%20characters%20as%202%20or%204%20byte%20sequences.%20However%3B%20with%20NVARCHAR%20type%20you%20have%20a%20performance%20issue%20because%20every%20UTF-8%20character%20must%20be%20converted%20to%20NVARCHAR%20type.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20this%20article%20you%20will%20learn%20when%20this%20unexpected%20conversion%20can%20happen%2C%20how%20to%20avoid%20it%2C%20or%20how%20to%20fix%20the%20issue.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1208578921%22%20id%3D%22toc-hId--1208578921%22%20id%3D%22toc-hId--1208578921%22%20id%3D%22toc-hId--1208578921%22%20id%3D%22toc-hId--1208578921%22%20id%3D%22toc-hId--1208578924%22%3EWhat%20is%20a%20collation%3F%3C%2FH2%3E%0A%3CP%3EA%20collation%20is%20a%20property%20of%20string%20types%20in%20SQL%20Server%2C%20Azure%20SQL%2C%20and%20Synapse%20SQL%20that%20defines%20how%20to%20compare%20and%20sort%20strings.%20In%20addition%2C%20it%20describes%20the%20encoding%20of%20string%20data.%20If%20a%20collation%20name%20in%20Synapse%20SQL%20ends%20with%20UTF8%2C%20it%20represents%20the%20strings%20encoded%20with%20the%20UTF-8%20encoding%20schema.%20Otherwise%20you%20have%20something%20like%20UTF-16%20encoded%20string.%3C%2FP%3E%0A%3CP%3EIn%20the%20following%20example%20is%20shown%20how%20to%20specify%20a%20collation%20associated%20to%20the%20string%20columns%20in%20an%20external%20table%20definition%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20EXTERNAL%20TABLE%20population%20(%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bcountry_code%5D%20VARCHAR%20(5)%20COLLATE%20Latin1_General_BIN2%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bcountry_name%5D%20VARCHAR%20(100)%20COLLATE%20Latin1_General_BIN2%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Byear%5D%20smallint%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bpopulation%5D%20bigint%0A)%0AWITH%20(%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20LOCATION%20%3D%20'csv%2Fpopulation%2Fpopulation.csv'%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATA_SOURCE%20%3D%20sqlondemanddemo%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_FORMAT%20%3D%20QuotedCSVWithHeaderFormat%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20table%20references%20CSV%20file%20and%20the%20string%20columns%20don%E2%80%99t%20have%20UTF8%20collation.%20Therefore%2C%20the%20CSV%20file%20should%20not%20be%20UTF-8%20encoded%20if%20you%20want%20to%20read%20the%20data%20with%20this%20table.%20The%20mismatch%20between%20the%20encoding%20that%20is%20specified%20in%20the%20column%20collation%20and%20the%20encoding%20in%20the%20underlying%20files%20would%20probably%20cause%20a%20conversion%20error.%20In%20this%20case%2C%20if%20your%20population%20data%20contains%20some%20UTF-8%20characters%2C%20they%20would%20be%20incorrectly%20converted%20once%20you%20read%20data.%20Therefore%2C%20you%20might%20need%20to%20use%20some%20UTF-8%20collation%20instead%20of%20Latin1_General_BIN2%20after%20the%20COLLATE%20clause.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1278933912%22%20id%3D%22toc-hId-1278933912%22%20id%3D%22toc-hId-1278933912%22%20id%3D%22toc-hId-1278933912%22%20id%3D%22toc-hId-1278933912%22%20id%3D%22toc-hId-1278933909%22%3EWhat%20are%20the%20special%20UTF-8%20encoded%20characters%3F%3C%2FH2%3E%0A%3CP%3EThe%20UTF-8%20encoding%20represents%20most%20of%20the%20characters%20using%201%20byte%2C%20but%20there%20are%20some%20characters%20that%20are%20not%20common%20in%20western%20languages.%20One%20example%20might%20be%20characters%20%C3%BC%20and%20%C3%B6%20in%20German%20words%20D%C3%BCsseldorf%20and%20Sch%C3%B6nwald.%3C%2FP%3E%0A%3CP%3ELet%20us%20imagine%20that%20we%20have%20a%20CSV%20file%20encoded%20with%20UTF-8%20encoding%20scheme%20with%20the%20names%20of%20the%20towns%20containing%20these%20characters.%20If%20we%20preview%20the%20content%20of%20this%20file%20in%20Synapse%20Studio%2C%20we%20will%20get%20the%20following%20result%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_0-1605256908984.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233370i6C1276AAC64B4C64%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JovanPop_0-1605256908984.png%22%20alt%3D%22JovanPop_0-1605256908984.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESynapse%20Studio%20enables%20us%20to%20read%20the%20content%20of%20this%20file%20using%20the%20T-SQL%20queries%20with%20the%26nbsp%3B%20OPENROWSET%20function.%20Running%20a%20T-SQL%20query%20on%20database%20with%20default%20or%20any%20non-UTF8%20collation%20might%20not%20return%20expected%20results%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_1-1605256909001.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233371i2FB327CCC0834C69%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JovanPop_1-1605256909001.png%22%20alt%3D%22JovanPop_1-1605256909001.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20might%20see%20that%20the%20towns%20D%C3%BCsseldorf%20and%20Sch%C3%B6nwald%20are%20not%20same%20as%20in%20the%20preview%20on%20the%20previous%20picture.%3C%2FP%3E%0A%3CP%3EIn%20Synapse%20SQL%2C%20you%20must%20use%20some%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fcollations%2Fcollation-and-unicode-support%3Fview%3Dsql-server-ver15%23utf8%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EUTF-8%20collation%3C%2FA%3E%20to%20return%20data%20from%20the%20UTF-8%20files.%20Otherwise%2C%20the%20non-common%20characters%20would%20be%20suddenly%20converted.%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAt%20the%20time%20%3C%2FSPAN%3E%3CSPAN%3Eof%20writing%20this%20post%2C%20Synapse%20SQL%20silently%20forces%20conversion%20of%20UTF-8%20characters%20to%20non%3C%2FSPAN%3E%3CSPAN%3E-UTF-8%20characters%2C%20and%20returns%20a%20warning%20message%20that%20this%20configuration%26nbsp%3Bmight%20cause%20a%20conversion%20error.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThe%20following%20functions%20and%20objects%20might%20be%20affected%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3E%3CSPAN%3EThe%20OPENROWSET%20function%20without%20WITH%20clause%20that%20returns%20VARCHAR%20columns.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%3EThe%20OPENROWSET%20function%20with%20explicit%20WITH%20clause%20that%20returns%20VARCHAR%20columns%20without%20specified%20collation.%26nbsp%3BThis%20function%20is%20affected%20only%20if%20a%20database%20collation%20is%26nbsp%3Bset%20to%20some%20of%20the%20non-UTF8%20collations.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%3EThe%20OPENROWSET%20function%20with%20WITH%20clause%20that%20returns%20VARCHAR%20columns%20with%20explicitly%20set%20non-UTF8%20collations.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%3EAn%20external%20table%20that%20contains%20the%20VARCHAR%20columns%20without%20explicit%20collation.%20These%20external%20tables%20are%20affected%20only%20if%20a%20database%20collation%20at%20the%20time%20of%20creation%20of%20the%20table%20was%20some%20of%20the%20non-UTF8%20collations.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%3EAn%20external%20table%20that%20contains%20the%20VARCHAR%20columns%20with%20explicitly%20specified%20non-UTF8%20collations.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%3EIt%20might%20be%20hard%20to%20exactly%20identify%20in%20what%20cases%20the%20issue%20might%20happen.%26nbsp%3B%3C%2FSPAN%3EThere%20are%20two%20ways%20to%20avoid%20this%20issue%20or%20resolve%20it%20if%20it%20happens%20in%20some%20of%20the%20queries%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESetting%20a%20UTF-8%20collation%20a%20database%20collation%20will%20resolve%20issues%20in%20scenarios%201%20and%202.%20This%20solution%20will%20resolve%20issue%20in%20scenario%204%20if%20you%20re-create%20the%20table.%3C%2FLI%3E%0A%3CLI%3ESetting%20an%20UTF-8%20collation%20on%20every%20string%20column%20that%20the%20OPENROWSET%20function%20returns%20will%20resolve%20issues%20in%20scenarios%203%2C%204%2C%20and%205%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20id%3D%22toc-hId--528520551%22%20id%3D%22toc-hId--528520551%22%20id%3D%22toc-hId--528520551%22%20id%3D%22toc-hId--528520551%22%20id%3D%22toc-hId--528520551%22%20id%3D%22toc-hId--528520554%22%3ESetting%20database%20collation%3C%2FH2%3E%0A%3CP%3EIf%20you%20are%20working%20with%20UTF-8%20data%2C%20the%20best%20way%20to%20configure%20your%20database%20is%20to%20set%20the%20default%20collation%20on%20every%20database.%20You%20can%20set%20collation%20as%20part%20of%20the%20CREATE%20DATABASE%20T-SQL%20statement%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20DATABASE%26nbsp%3Bmydb%26nbsp%3B%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BCOLLATE%26nbsp%3BLatin1_General_100_BIN2_UTF8%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20an%20existing%20database%2C%20you%20can%20easily%20change%20the%20default%20collation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EALTER%20DATABASE%26nbsp%3Bmydb%26nbsp%3B%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BCOLLATE%20Latin1_General_100_BIN2_UTF8%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20this%20point%2C%20every%20OPENROWSET%20function%20will%20return%20correctly%20converted%20data.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENote%20that%20you%20would%20need%20to%20drop%20and%20re-create%20external%20tables%20if%20you%20have%20not%20explicitly%20specified%20collation.%20New%20default%20database%20collation%20will%20be%20applied%20only%20when%20table%20is%20created.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1958992282%22%20id%3D%22toc-hId-1958992282%22%20id%3D%22toc-hId-1958992282%22%20id%3D%22toc-hId-1958992282%22%20id%3D%22toc-hId-1958992282%22%20id%3D%22toc-hId-1958992279%22%3ESpecifying%20explicit%20collations%3C%2FH2%3E%0A%3CP%3EInstead%20of%20defining%20default%20database%20collation%2C%20you%20can%20explicitly%20specify%20collation%20when%20you%20declare%20column%20type%20using%20WITH%20clause.%3C%2FP%3E%0A%3CP%3EThe%20OPENROWSET%20function%20enables%20you%20to%20explicitly%20specify%20columns%20and%20their%20types%20in%20the%20WITH%20clause%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20TOP%2010%20*%0AFROM%20OPENROWSET(%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20BULK%20'latest%2Fecdc_cases.parquet'%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATA_SOURCE%20%3D%20'covid'%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FORMAT%20%3D%20'parquet'%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20)%20WITH%20(%20date_rep%20DATE%2C%20cases%20INT%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bgeo_id%20VARCHAR(6)%20COLLATE%20Latin1_General_100_BIN2_UTF8%20)%20as%20rows%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20reading%20parquet%20files%2C%20or%20UTF-8%20encoded%20text%20files%2C%20you%20would%20need%20to%20add%20an%20UTF-8%20collation%20in%20the%20type%20specification.%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20defining%20tables%2C%20you%20can%20explicitly%20specify%20collation%20in%20column%20definition%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20EXTERNAL%20TABLE%20population%20(%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bcountry_code%5D%20VARCHAR%20(5)%20COLLATE%20Latin1_General_100_BIN2_UTF8%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bcountry_name%5D%20VARCHAR%20(100)%20COLLATE%20Latin1_General_100_BIN2_UTF8%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Byear%5D%20SMALLINT%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bpopulation%5D%20BIGINT%0A)%0AWITH%20(%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20LOCATION%20%3D%20'csv%2Fpopulation%2Fpopulation.csv'%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATA_SOURCE%20%3D%20sqlondemanddemo%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_FORMAT%20%3D%20QuotedCSVWithHeaderFormat%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20way%20you%20can%20be%20sure%20that%20your%20table%20will%20return%20correct%20text.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENOTE%3A%20If%20you%20have%20existing%20tables%20that%20used%20default%20database%20collation%2C%20changing%20default%20database%20collation%20would%20not%20be%20applied%20on%20them.%20You%20would%20need%20to%20drop%20and%20re-create%20external%20tables%20so%20they%20can%20pickup%20new%20default.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-151537819%22%20id%3D%22toc-hId-151537819%22%20id%3D%22toc-hId-151537819%22%20id%3D%22toc-hId-151537819%22%20id%3D%22toc-hId-151537819%22%20id%3D%22toc-hId-151537816%22%3EConclusion%3C%2FH2%3E%0A%3CP%3EMatching%20column%20collation%20of%20string%20columns%20and%20encoding%20in%20the%20files%20is%20important%20to%20avoid%20unexpected%20conversion%20errors.%20Currently%2C%20a%20serverless%20SQL%20pool%20silently%20converts%20UTF-8%20data%20to%20non-UTF-8%20data%20even%20if%20it%20knowns%20that%20there%20is%20a%20mismatch%20causing%20potential%20conversion%20error.%20This%20conversion%20causes%20a%20warning%2C%20so%20make%20sure%20that%20you%20are%20using%20proper%20collations%20in%20the%20OPENROWSET%20function%20and%20external%20tables%20to%20avoid%20errors.%3C%2FP%3E%0A%3CP%3ETo%20mitigate%20this%20issue%2C%20you%20need%20to%20alter%20database%20to%20use%20the%20default%20UTF8%20collation%20or%20specify%20collation%20explicitly%20on%20every%20string%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1883633%22%20slang%3D%22en-US%22%3E%3CP%3EServerless%20SQL%20pool%20in%20Azure%20Synapse%20Analytics%20enables%20you%20to%20read%20UTF-8%20encoded%20text%20from%20CSV%20and%20PARQUET%20files.%20In%20this%20article%20you%20will%20learn%20how%20to%20properly%20configure%20a%20serverless%20SQL%20database%20to%20get%20the%20UTF-8%20data%20without%20unwanted%20conversions.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1883633%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EInternals%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2185783%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20use%20UTF-8%20collations%20to%20read%20UTF-8%20text%20in%20serverless%20SQL%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2185783%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F296874%22%20target%3D%22_blank%22%3E%40JovanPop%3C%2FA%3E%26nbsp%3Bhow%20should%20you%20handle%20csv%20files%20in%20UCS-2%20LE%20BOM%20format%20with%20a%20Synapse%20Serverless%20SQL%20Pool%3F%3CBR%20%2F%3EWhen%20querying%20it%20gives%20an%20error%3A%3C%2FP%3E%3CP%3E%3CEM%3EError%20handling%20external%20file%3A%20'Incorrect%20file%20encoding.%20DATAFILETYPE%20%3D%20'char'%20selected%20for%20querying%20UTF16%20file.'.%20File%2FExternal%20table%20name%3A...%3C%2FEM%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EConverting%20the%20file%20to%20UTF-8%20in%20Notepad%2B%2B%20resolves%20the%20issue%20BUT%20is%20there%20a%20way%20to%20query%20without%20converting%3F%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EExample%20file%20format%20(from%20Notepad%2B%2B)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021-03-04_16-54-47.jpg%22%20style%3D%22width%3A%20874px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F260969i4A1988B2BDDD4BF5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222021-03-04_16-54-47.jpg%22%20alt%3D%222021-03-04_16-54-47.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2404937%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20use%20UTF-8%20collations%20to%20read%20UTF-8%20text%20in%20serverless%20SQL%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404937%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F208405%22%20target%3D%22_blank%22%3E%40Stuart%20Lock%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20whether%20you%20were%20able%20to%20solve%20this%20issue%2C%20but%20to%20help%20you%20and%20others%20facing%20a%20similar%20situation%2C%20here's%20what%20I%20did.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20UCS-2%20LE%20BOM%20and%20UTF-16%20%3CA%20href%3D%22https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FUTF-16%23Byte-order_encoding_schemes%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Eare%20very%20similar%3C%2FA%3E%2C%20so%20the%20trick%20is%20to%20define%20your%20source%20format%20as%20being%20encoded%20in%20UTF-16%2C%20e.g.%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%20%20%20%20CREATE%20EXTERNAL%20FILE%20FORMAT%20QuotedCsvWithHdrFormat%0A%20%20%20%20WITH%20(%20%20%0A%20%20%20%20%20%20%20%20FORMAT_TYPE%20%3D%20DELIMITEDTEXT%2C%0A%20%20%20%20%20%20%20%20FORMAT_OPTIONS%20(%20PARSER_VERSION%20%3D%20'2.0'%2C%20FIELD_TERMINATOR%20%3D%20'%7C'%2C%20STRING_DELIMITER%20%3D%20'%22'%2C%20FIRST_ROW%20%3D%202%2C%20Encoding%20%3D%20'UTF16'%20)%0A%20%20%20%20)%3B%3C%2FPRE%3E%3CP%3EAnd%20then%20align%20the%20corresponding%20format%20definition%20in%20your%20CREATE%20EXTERNAL%20TABLE%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20there%2C%20your%20SELECT%20query%20should%20work.%20I%20did%20not%20have%20to%20specify%20any%20COLLATION.%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Dec 21 2020 01:41 AM
Updated by: