Databricks COPY INTO导入CSV遇Schema不匹配问题，请求解决

问题场景 #

执行以下COPY INTO命令导入CSV到Delta表：

COPY INTO dev_stg.mtip.table1
FROM '/Volumes/prd_stg/ext-raw/25714_Table1.CSV'
FILEFORMAT = csv
COPY_OPTIONS ('inferSchema' = 'true','header'='true')

目标Delta表结构：

CREATE TABLE dev_stg.mtip.Table1 (
    PlayerName STRING,
    SiteNumber STRING
)

出现Schema不匹配错误：

schema mismatch was detected while copying into the Delta table (Table: dev_stg.mtip.table1).
This may indicate an issue with the incoming data, or the Delta table schema can be evolved automatically according to the incoming data by setting:
COPY_OPTIONS ('mergeSchema' = 'true')

Schema difference:
Incoming schema is missing field(s): PlayerName, SiteNumber ,
Incoming schema has additional field(s): _c0, _c2, _c3,

添加mergeSchema=true后，表结构被修改为包含_c0字段，不符合预期。

解决方案 #

1. 验证CSV实际内容与表头 #

先确认CSV文件的表头是否与目标表字段匹配，是否存在拼写错误、大小写差异或多余列。用以下命令查看CSV数据：

SELECT * FROM csv.`/Volumes/prd_stg/ext-raw/25714_Table1.CSV` LIMIT 5

2. 手动指定列映射，禁用自动Schema推断 #

放弃inferSchema=true，通过子查询明确选择需要的列，将CSV列映射到目标表字段，忽略多余列。修改后的COPY INTO命令如下：

COPY INTO dev_stg.mtip.table1 (PlayerName, SiteNumber)
FROM (SELECT PlayerName, SiteNumber FROM '/Volumes/prd_stg/ext-raw/25714_Table1.CSV')
FILEFORMAT = csv
COPY_OPTIONS ('header'='true')

如果CSV列名与目标表不一致（比如CSV用小写playername），在子查询中做别名转换：

COPY INTO dev_stg.mtip.table1 (PlayerName, SiteNumber)
FROM (SELECT playername AS PlayerName, sitenumber AS SiteNumber FROM '/Volumes/prd_stg/ext-raw/25714_Table1.CSV')
FILEFORMAT = csv
COPY_OPTIONS ('header'='true')

3. 验证导入结果 #

执行命令后，查询目标表确认数据是否正确导入：

SELECT * FROM dev_stg.mtip.table1 LIMIT 10

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