如何通过Athena读取S3中bz2/lz4压缩的SQL Dump文件？

你遇到的HIVE_CANNOT_OPEN_SPLIT: Not an Avro data file错误本质是格式不匹配：你创建的是Avro格式的外部表，要求存储路径下的文件必须是Avro容器格式，但S3里实际是bz2/lz4压缩的SQL Dump文件（文本类型的SQL语句集合），两者完全不兼容，因此Athena无法识别解析。

根据你的需求，提供两种可行方案：

方案一：直接解析压缩后的SQL Dump文件（快速验证） #

Athena支持自动解压bz2和lz4格式的文件（只要文件后缀为.bz2/.lz4），可以通过正则SerDe直接解析SQL Dump中的数据：

示例建表语句（适配INSERT格式的SQL Dump） #

假设你的SQL Dump是类似INSERT INTO table VALUES (1, 'type1'), (2, 'type2');的格式，用正则提取括号内的字段：

CREATE EXTERNAL TABLE IF NOT EXISTS `rds-database-backup-archive`.`testing_loop` (
  `id` int,
  `type` varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "\\((\\d+), '(.*?)'\\)"
)
STORED AS TEXTFILE
LOCATION 's3://rds-database-backup-archive/testing-athena/'
TBLPROPERTIES (
  'skip.header.line.count' = '2' -- 根据实际情况调整，比如跳过开头的CREATE TABLE、USE语句等行数
);

• 注意：input.regex需要完全匹配你的SQL记录格式，比如字段带双引号、有NULL值时，要修改正则表达式。
• 如果SQL Dump中存在非INSERT行（如COMMIT、注释），可以通过skip.header.line.count或正则过滤掉无关行。

方案二：转换为列存格式（高效分析推荐） #

如果数据量较大，直接解析文本SQL Dump的查询效率较低，建议先将数据转换为Parquet/Avro这类列存格式：

1. 用Glue ETL或Lambda处理数据：
   • 读取S3上的压缩SQL Dump文件，逐行解析SQL语句，提取字段值
   • 将解析后的数据写入S3的新路径，保存为Parquet格式
2. 创建Parquet格式的外部表：

CREATE EXTERNAL TABLE IF NOT EXISTS `rds-database-backup-archive`.`testing_loop_parquet` (
  `id` int,
  `type` varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://rds-database-backup-archive/testing-athena-parquet/'
TBLPROPERTIES (
  'classification' = 'parquet',
  'compressionType' = 'snappy'
);

这种方式适合数据分析团队长期查询，性能远高于文本解析。

• 确保S3上的压缩文件后缀正确：bz2压缩文件后缀为.bz2，lz4为.lz4，Athena会自动识别解压
• 正则解析时，务必先拿单条SQL记录测试正则表达式的匹配准确性

内容的提问来源于stack exchange，提问作者devops ka sardar