为Athena查询优化S3用户活动数据的多级分区方案咨询

场景背景 #

当前需将用户活动日志存储在S3，供Athena查询。已定义包含大量遥测字段的事件类，由多个Lambda函数写入S3。

查询特点 #

• 核心关注6个字段：date、sessionId、userId、campaignId、companyId、eventType
• 查询常不限制日期，比如跨年度的活动全量事件、跨午夜的用户会话

问题解答 #

1. 单文件应存储什么内容？原设想文件名格式为YYYY-MM-DD---userIdX.json，存储单日单用户事件 #

• 不建议按单日单用户存储小文件。Athena查询小文件会显著降低性能，因为每个文件都需要单独处理，增加IO开销和查询时间。
• 推荐按时间窗口（比如1小时、4小时）批量合并同批次事件，生成压缩后大小在100MB-1GB之间的文件。文件名可包含时间戳和批次标识，比如2024-05-20-14-00-events.json.gz，无需绑定单个用户。若需按用户维度定位，保留文件内的userId字段，通过Athena的WHERE userId = 'X'筛选即可。

2. 是否应创建5个排除date的S3文件夹/分区，将文件重复存储5-6次？ #

• 绝对不建议重复存储文件。这会大幅增加S3存储成本，让Lambda写入逻辑复杂化，还会引发数据一致性问题（比如某文件更新后，其他副本同步困难）。
• Athena的查询优化依赖分区裁剪，但重复存储不是多维度查询的解决方案，正确做法是用合理的分区策略+列式存储格式（如Parquet、ORC）提升多维度查询性能。

3. 针对date分区，各方案及组合的优劣势 #

// A和B为其余5个分区的附加分区
// C和D为其余5个分区的重组形式
A: bucketName/YYYY/MM/DD/YYYY-MM-DD---userIdX.json
B: bucketName/YYYY-MM-DD/YYYY-MM-DD---userIdX.json
C: bucketName/[either sessionId, userId, campaignId, companyId or eventType]/YYYY/MM/DD/YYYY-MM-DD---userIdX.json
D: bucketName/[either sessionId, userId, campaignId, companyId or eventType]/YYYY-MM-DD/YYYY-MM-DD---userIdX.json
// 可组合(A和C)或(B和D)

• 方案A：按YYYY/MM/DD层级分区，优势是目录结构清晰，符合时间维度自然划分，Athena能很好识别层级分区进行裁剪；劣势是目录层级较深，Lambda写入时需创建多层目录，逻辑稍复杂。
• 方案B：按YYYY-MM-DD单层级分区，优势是目录结构简单，写入逻辑更简洁；劣势是和方案A相比，时间范围查询的性能差异不大，仅目录展示形式不同。
• 方案C/D：不建议采用。sessionId是高基数字段（每个会话一个ID），按它分区会生成海量目录，影响S3性能，Athena也无法有效利用这类分区；userId、campaignId若基数较高，同样会导致分区爆炸，存储和查询性能都会下降。即使是低基数的eventType，过多分区也会增加元数据管理负担。
• 组合方案（A+C/B+D）：完全不推荐，会导致分区数量指数级增长，S3目录管理成本飙升，Athena元数据同步和查询性能严重恶化，同时存储成本翻倍。

4. 使用Hive风格分区（如created_date=2019-03-07作为子文件夹）是否有优势？ #

• 有明显优势：
  1. 元数据自动识别：Athena原生支持Hive风格分区，创建表时通过PARTITIONED BY (created_date string)即可自动识别分区，无需手动添加（开启MSCK REPAIR TABLE或自动分区同步）。
  2. 查询语义清晰：查询时可直接用WHERE created_date = '2019-03-07'，比层级分区的筛选逻辑更直观，也更容易维护。
  3. 灵活扩展分区：后续若需添加其他低基数分区字段（比如region），可直接扩展为created_date=2019-03-07/region=us-east-1，兼容性更好。

5. 文件会自动压缩吗？还是需API处理？ #

• S3不会自动压缩文件，必须在写入前通过Lambda或其他处理逻辑完成压缩。
• 建议在Lambda中对批量事件压缩后再写入S3，这样能减少S3存储成本和数据传输带宽，同时Athena查询压缩文件时性能更好（需读取的数据量更小）。

6. 推荐哪种压缩算法？（听说可拆分压缩更优） #

• 推荐Snappy或Gzip，具体选择看场景：
  • Snappy：压缩速度快，解压速度极快，适合实时或准实时的数据写入场景（比如Lambda实时处理写入）。搭配Parquet等列式存储格式时，Snappy压缩的文件支持可拆分，Athena可并行读取文件不同部分，提升查询性能。
  • Gzip：压缩比高于Snappy，适合批量离线处理的数据，存储成本更低。但Gzip本身不可拆分，除非将文件分割成多块后再压缩，或使用支持可拆分Gzip的列式存储格式（Parquet/ORC会对数据分块后再用Gzip压缩，实现可拆分）。
• 注意：若使用JSON格式，Gzip压缩的文件不可拆分，Athena只能单线程读取，性能会受影响；而Parquet+Snappy/Gzip的组合既支持可拆分，又能利用列式存储优势，大幅提升多字段查询性能，是最优选择。

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