Azure Synapse Analytics读取CSV文件时数据转换错误的修复方法

我正在学习Azure分析相关内容，尝试把CSV文件导入Azure Analytics Workspace，执行SQL查询时出现编码转换错误。

执行的SQL代码：

-- This is auto-generated code
SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://daveslakestore.dfs.core.windows.net/daves-fs/products.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS [result];

报错提示：

potential conversion error while reading VARCHAR column 'ProductName' from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns [etc]...

我用下面的语句查到当前数据库排序规则是SQL_Latin1_General_CP1_CI_AS：

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS Collation;

我知道需要改成UTF8排序规则，但不知道具体怎么操作——试了下面的SQL没成功，因为不清楚目标数据库名称，也不确定这个方法对不对：

USE master;  
GO

ALTER DATABASE MyOptionsTest  
    COLLATE Latin1_General_100_BIN2_UTF8 ;   --?
GO  
  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  —what should this really be?
GO  

方案1：修改当前数据库的UTF8排序规则 #

1. 先查清楚当前连接的数据库名称：

   SELECT DB_NAME() AS CurrentDatabase;
   

2. 执行修改排序规则的语句（注意：执行前要确保数据库没有活跃连接，这个操作会影响现有对象）：

   ALTER DATABASE [你的数据库名称]
   COLLATE Latin1_General_100_CI_AS_SC_UTF8;
   

   推荐用Latin1_General_100_CI_AS_SC_UTF8这个排序规则，它支持补充字符，兼容性较好。
3. 验证修改是否成功：

   SELECT name, collation_name
   FROM sys.databases
   WHERE name = N'你的数据库名称';
   

方案2：在OPENROWSET里指定带UTF8排序的列架构（不用改整个库） #

如果不想修改数据库全局的排序规则，直接在OPENROWSET的WITH子句里定义列结构，给VARCHAR类型的列指定UTF8排序规则就行，示例如下：

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://daveslakestore.dfs.core.windows.net/daves-fs/products.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    ProductID INT,
    ProductName VARCHAR(255) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
    -- 其他列按照CSV实际结构定义，注意匹配数据类型和长度
    Category VARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
    Price DECIMAL(10,2)
) AS [result];

这种方式更灵活，只针对当前查询的CSV文件处理编码问题，不会影响数据库里的其他对象。

内容的提问来源于stack exchange，提问作者user19526110