Redshift Spectrum能否按列名匹配加载列顺序不一致、部分列缺失的CSV外部表？

你遇到的问题确实是Redshift Spectrum默认行为导致的：默认使用的OpenCSVSerDe是按列的位置顺序映射数据，而非列名，所以当文件列顺序不同、列有缺失时，就会出现值错位、类型不匹配导致NULL的情况。不过不用担心，Redshift Spectrum是支持按列名匹配加载这类CSV的，下面给你具体的解决方案和原理说明：

解决方案1：配置OpenCSVSerDe实现按列名匹配 #

通过调整外部表的SerDe属性和表属性，让OpenCSVSerDe读取文件表头并按列名匹配表字段，不需要修改原始CSV文件：

CREATE EXTERNAL TABLE test_schema.test_table (
  "id" VARCHAR,
  "name" VARCHAR,
  "type" SMALLINT
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES (
  "separatorChar" = ",",    -- 指定CSV的分隔符，根据你的文件实际情况调整
  "quoteChar" = "\"",       -- 指定CSV的引号字符
  "escapeChar" = "\\"       -- 指定转义字符
)
STORED AS TEXTFILE 
LOCATION 's3://test_path/' 
TABLE PROPERTIES (
  'skip.header.line.count' = '1',  -- 跳过每个CSV的表头行
  'csv.column.names' = 'id,name,type'  -- 定义所有可能出现的列名（覆盖三个文件的所有列）
);

原理解释： #

• skip.header.line.count='1'告诉SerDe忽略每个文件的第一行（表头），避免把表头当成数据加载。
• csv.column.names指定了所有可能存在的列名，SerDe会自动读取每个文件的表头，将对应列名的值映射到外部表中同名的字段，完全不关心列的顺序。
• 对于缺失列的文件（比如c.csv没有type列），对应的表字段会自动填充NULL，正好符合你的期望结果。

执行这个创建语句后，再查询SELECT * FROM test_schema.test_table，就能得到你想要的正确结果：

id  name    type
a1  apple   1
a2  banana  2
b1  orange  1
b2  lemon   2
c1  kiwi    NULL

解决方案2：预处理CSV为结构化格式（长期推荐） #

如果你的数据量较大，或者需要更好的查询性能，建议把CSV转换为Parquet或JSON这类自带元数据的结构化格式：

• Parquet是列存储格式，压缩率高，查询速度远快于CSV，并且自带列名信息，Redshift Spectrum会自动按列名匹配，缺失列自动填充NULL。
• 你可以用Python（pandas）、Apache Spark等工具批量转换文件，比如用pandas的示例代码：

  import pandas as pd
  import os
  import pyarrow as pa
  import pyarrow.parquet as pq
  
  # 假设本地下载了S3上的CSV文件，处理后再上传回S3
  input_dir = "./local_csv_files"
  output_dir = "./local_parquet_files"
  os.makedirs(output_dir, exist_ok=True)
  
  for filename in os.listdir(input_dir):
      if filename.endswith(".csv"):
          df = pd.read_csv(os.path.join(input_dir, filename))
          # 确保列包含所有需要的字段，缺失的自动填充NULL
          df = df.reindex(columns=["id", "name", "type"])
          # 转换为Parquet格式
          table = pa.Table.from_pandas(df)
          pq.write_table(table, os.path.join(output_dir, filename.replace(".csv", ".parquet")))
  # 把转换后的Parquet文件上传到S3的新路径，比如s3://test_path_parquet/
  

  然后创建指向Parquet文件的外部表：

  CREATE EXTERNAL TABLE test_schema.test_table_parquet (
    "id" VARCHAR,
    "name" VARCHAR,
    "type" SMALLINT
  ) 
  STORED AS PARQUET 
  LOCATION 's3://test_path_parquet/' 
  TABLE PROPERTIES ('parquet.compress'='SNAPPY');
  

为什么默认创建表会出现错位？ #

你最初的创建语句没有配置列名匹配的属性，OpenCSVSerDe默认按列的位置顺序映射数据：

• a.csv的列顺序和表定义一致，所以数据正确。
• b.csv的第一列type被映射到表的第一列id，第二列id映射到表的第二列name，第三列name因为是字符串类型，无法转换为表第三列的SMALLINT类型，所以变成NULL。
• c.csv的第一列name映射到表的id，第二列id映射到表的name，第三列缺失所以是NULL，这就是你看到错位结果的原因。

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