Amazon Athena创建分区表后查询返回零条记录问题求助

问题根因 #

1. Athena的分区表不读取CSV文件内容生成分区字段值，分区字段的取值完全来自S3对象的路径分层命名，要求路径必须符合${分区键名}=${分区值}的格式，且分区键的顺序和建表时声明的分区顺序一致。
   你当前所有CSV文件都直接存放在表根路径s3://projectzzzz2/0001_aaaa_delme/下，路径中没有符合规则的分区层级，MSCK REPAIR TABLE执行后不会识别到任何有效分区，查询时自然不会扫描任何文件，返回零条记录。
2. 你当前的分区字段声明顺序是emaildomain string, emailusername string，即使后续添加了分区，CSV文件中前两列是emailusername、emaildomain，而你建表时保留的列是name、details，会导致列映射错位，就算能读到数据也会出现值不匹配的问题。

解决方法 #

场景1：不修改现有S3文件路径，仅需要按对应字段做查询过滤 #

这种场景不需要用原生分区功能，直接用原有非分区表结构，给表加上skip.header.line.count属性跳过表头，查询时直接过滤对应字段即可，5GB数据量的查询性能完全满足需求：

CREATE EXTERNAL TABLE IF NOT EXISTS db1.tablea (
  `emailusername`  string,
  `emaildomain`  string,
  `name` string,
  `details` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\", "quoteChar" = "\"")
LOCATION 's3://projectzzzz2/0001_aaaa_delme/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1'
);

如果需要提升过滤查询速度，可以给该表创建分区投影，不需要移动文件也不需要Glue爬网：

CREATE EXTERNAL TABLE IF NOT EXISTS db1.tablea (
  `name` string,
  `details` string 
)
PARTITIONED BY (emaildomain string, emailusername string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\", "quoteChar" = "\"")
LOCATION 's3://projectzzzz2/0001_aaaa_delme/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1',
  'projection.enabled' = 'true',
  'projection.emaildomain.type' = 'injected',
  'projection.emailusername.type' = 'injected'
);

查询时在WHERE子句中指定两个分区字段的取值，即可正常返回结果。

场景2：需要使用原生分区功能提升大文件扫描性能 #

你需要先对现有CSV文件和S3路径做两处调整：

• 把每个CSV文件中的前两列emailusername、emaildomain删除，仅保留name、details两列内容（原生分区字段从路径取值，不会读取文件内的对应列，不删除会导致列映射错位）
• 按照分区规则重新组织S3路径，路径层级顺序和建表时声明的分区键顺序保持一致，格式要求如下：

  s3://projectzzzz2/0001_aaaa_delme/emaildomain=website1.com/emailusername=myname1/xxx.csv
  s3://projectzzzz2/0001_aaaa_delme/emaildomain=website2.com/emailusername=myname2/yyy.csv
  

路径和文件调整完成后执行建表+修复分区语句即可：

CREATE EXTERNAL TABLE IF NOT EXISTS db1.tablea (
  `name` string,
  `details` string 
)
PARTITIONED BY (emaildomain string, emailusername string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\", "quoteChar" = "\"")
LOCATION 's3://projectzzzz2/0001_aaaa_delme/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1'
);

MSCK REPAIR TABLE tablea;

执行后即可正常查询数据。

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