关于Azure Synapse中FIELDQUOTE工作机制的理解是否正确？

初始测试场景 #

我用Azure Synapse查询以下CSV文件：

1,"A","G"
2,"B","H"
3,"C","I"
4,"D","J"
5,"E,F","K"

对应的T-SQL查询语句：

SELECT *
FROM
    OPENROWSET(
        BULK 'taxi/raw/Sample.csv',
        DATA_SOURCE = 'nyc_taxidata',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW=2,
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        FIELDQUOTE = '"'
    )
    AS [result]

查询输出：

C1   C2   C3
1    A    G
2    B    H
3    C    I
4    D    J
5    E,F  K

修改CSV后的测试 #

随后修改CSV文件，在字母G前添加冒号：

1,"A",:"G"
2,"B","H"
3,"C","I"
4,"D","J"
5,"E,F","K"

得到的输出：

C1   C2   C3
1    A    :"G"
2    B    H
3    C    I
4    D    J
5    E,F  K

关于FIELDQUOTE的结论 #

基于上述测试，我得出以下结论：

• 查询会逐字符扫描每行，寻找FIELDQUOTE或FIELDTERMINATOR；若先找到FIELDQUOTE，则会忽略后续的FIELDTERMINATOR，直到找到下一个FIELDQUOTE（基于最后一行得出）。
• 若要FIELDQUOTE正常工作，其前必须紧跟FIELDTERMINATOR（基于第二个示例的第一行得出）。

不确定这些结论是否正确，希望得到反馈。

后续测试异常情况 #

测试1 #

使用以下CSV文件执行上述查询：

1,"A"":",G
2,"B","H"
3,"C","I"
4,"D","J"
5,"E,F","K"

返回结果：

C1   C2  C3
1    A":  G
2    B    H
3    C    I
4    D    J
5    E,F  K

测试2：带表头的CSV错误场景 #

尝试使用带表头的CSV文件：

Number,Letter
1,"A""":",G
2,"B","H"
3,"C","I"
4,"D","J"
5,"E,F","K"

遇到错误：

Error handling external file: 'Unexpected token ':' at [byte: 22]. Expecting tokens ',', ' ', or '"'. '. File/External table name: 'taxi/raw/Sample.csv'.

测试3：带表头的另一错误场景 #

尝试该CSV文件：

Number,Letter
1,"A":",G
2,"B","H"
3,"C","I"
4,"D","J"
5,"E,F","K"

同样得到错误：

Error handling external file: 'Unexpected token ':' at [byte: 20]. Expecting tokens ',', ' ', or '"'. '. File/External table name: 'taxi/raw/Sample.csv'.

测试4：带表头的异常解析场景 #

尝试该CSV文件：

Number,Letter
1,"A""",G
2,"B","H"
3,"C","I"
4,"D","J"
5,"E,F","K"

得到的结果：

C1   C2        C3
1    A"",G2,B"  H
3    C          I
4    D          J
5    E,F        K

请专业人士对我的结论及上述测试异常情况给予反馈。

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