Amazon S3/Athena大数据存储最佳实践：数据类型选型问询

Hey there, let's dive into your question about storing all your S3/Athena dataset as strings, especially since you're dealing with hundreds of GB and frequent arithmetic/grouping operations.

Short answer: It's technically feasible, but it's a terrible fit for your use case. Let's break down the pros and cons clearly.

Pros of All-String Storage #

• Simplified ETL/Ingestion: You skip the hassle of validating and converting data types during ingestion. No more fighting with inconsistent date formats, numeric precision mismatches, or weird edge cases from source systems—just dump everything as strings and worry about it later (if at all).
• Flexibility for Schema Changes: If you later need to adjust a field's intended type (e.g., a numeric field that suddenly needs to hold larger values), you can just update your Athena table's DDL to cast it appropriately without rewriting the entire dataset.
• Preserve Raw Data: String storage keeps the exact original format of your data, which can be useful if you ever need to audit or reprocess source data without losing context (e.g., numeric values with thousand separators).

Cons (Critical for Your Scenario) #

This is where the approach falls apart for your use case of frequent arithmetic and grouping:

• Catastrophic Query Performance: Athena (built on Presto) can't optimize operations on string-encoded numbers or dates. Every arithmetic calculation (like CAST(sales AS DOUBLE) * 1.1) or grouping operation requires runtime type conversion, which eats up CPU and slows queries to a crawl—especially on hundreds of GB of data. You'll likely see query times 2-10x longer than with native types.
• Higher Storage & Query Costs: Strings are far less storage-efficient than native types. For example:
  • A 4-byte INT becomes 6+ bytes as a string (e.g., "123456")
  • A 4-byte DATE becomes 10+ bytes as "2024-05-20"
    This can increase your S3 storage size by 30-100%, and since Athena charges by the amount of data scanned, you'll pay more for every query too.
• Error-Prone Grouping & Aggregation: Grouping on string dates (e.g., GROUP BY order_date) is slower and risky—if your data has inconsistent formats (like "2024/05/20" vs "2024-05-20"), you'll get incorrect groups without extra cleaning steps. Native date types avoid this entirely.
• No Built-In Type Validation: With all strings, Athena can't catch bad data (like non-numeric values in a field meant for calculations) until you run a query. This leads to frustrating debugging sessions when queries fail unexpectedly.
• Limited Function Support: Many optimized Athena functions (e.g., DATE_DIFF for dates, PERCENTILE for numbers) require native types to work efficiently (or at all). Using strings means you'll have to wrap every function call in CAST, adding complexity and overhead.

Recommendations for Your Use Case #

Given your need for frequent arithmetic and grouping, stick to native data types for every field:

• Use INT, BIGINT, DOUBLE, or DECIMAL for numeric fields (pick DECIMAL if you need precise decimal arithmetic).
• Use DATE or TIMESTAMP for date/time fields.
• Reserve STRING for actual text data (like product names, descriptions).
• Pair this with a columnar storage format like Parquet or ORC—these formats compress native types extremely well, reducing storage costs and speeding up queries by only scanning the columns you need.

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