DynamoDB to Athena Bulk Export

Optimized bulk export tool for moving large DynamoDB tables (millions of records) to Amazon Athena. Uses parallel scanning with batching for maximum throughput and minimal API overhead.

Features

• Parallel Scanning: Splits table scan across multiple segments for high throughput
• Batch Processing: Groups records to reduce S3 API calls
• Type Conversion: Handles all DynamoDB data types (including Maps, Lists, Sets)
• Flexible Output: JSON or CSV format with optional gzip compression
• Athena Integration: Auto-generates DDL from sampled data
• Performance: Designed for tables with 4.5M+ records

DynamoDB Data Type Support

DynamoDB Type	Example	JSON/CSV Output
String (S)	{"name": {"S": "John"}}	"name": "John"
Number (N)	{"age": {"N": "30"}}	"age": 30
Binary (B)	{"data": {"B": "base64..."}}	"data": "base64..."
String Set (SS)	{"tags": {"SS": ["a", "b"]}}	"tags": ["a", "b"]
Number Set (NS)	{"nums": {"NS": ["1", "2"]}}	"nums": [1, 2]
Binary Set (BS)	{"files": {"BS": [...]}}	"files": [...]
Map (M)	{"attrs": {"M": {"key": {"S": "val"}}}}	"attrs": {"key": "val"}
List (L)	{"items": {"L": [{"S": "a"}]}}	"items": ["a"]
Boolean (BOOL)	{"active": {"BOOL": true}}	"active": true
Null (NULL)	{"deleted": {"NULL": true}}	"deleted": true

Installation

pip install boto3

Configuration

Copy config.yaml and update with your settings:

dynamodb:
  table_name: "your-table-name"
  region: "us-east-1"

s3:
  bucket: "your-s3-bucket"
  prefix: "dynamodb-export"

export:
  format: "json" # or "csv"
  batch_size: 500
  segment_workers: 20
  compression: true

athena:
  database: "default"
  table_name: "your-table-name"
  sample_size: 1000

Usage

Step 1: Export DynamoDB to S3

# Edit the main() function in export_dynamodb.py with your config
python export_dynamodb.py

Or import and use programmatically:

from export_dynamodb import ExportConfig, export_dynamodb_to_s3

config = ExportConfig(
    table_name="your-table-name",
    s3_bucket="your-s3-bucket",
    s3_prefix="dynamodb-export",
    output_format="json",
    region="us-east-1",
    batch_size=500,
    segment_workers=20,
    compression=True
)

export_dynamodb_to_s3(config)

Step 2: Generate Athena DDL

# Update config in generate_athena_ddl.py main() function
python generate_athena_ddl.py

This will:

1. Sample records from S3
2. Infer schema from data
3. Generate CREATE TABLE DDL
4. Save to athena_ddl.sql

Step 3: Create Athena Table

Execute the generated DDL in Athena console or using CLI:

aws athena start-query-execution \
  --query-string "$(cat athena_ddl.sql)" \
  --query-execution-context Database=default \
  --result-configuration OutputLocation=s3://your-bucket/query-results/

Performance Tuning

For Large Tables (4.5M+ records)

Key Parameters:

1. segment_workers: Number of parallel scan segments

   • Start with 10-20
   • Maximum: table's provisioned RCUs / 5
   • More segments = faster but higher RCU cost

2. batch_size: Records per batch

   • Range: 100-1000
   • Larger batches reduce S3 API calls
   • Too large = memory pressure

3. Compression: gzip compression

   • Always enable for production
   • Reduces S3 storage by ~60-80%
   • Athena handles gzip natively

Performance Estimates

Based on 4.5M records:

segment_workers	batch_size	Est. Time	RCU/s
10	500	~30 min	50
20	500	~15 min	100
40	500	~8 min	200

Format Considerations

JSON Format

Pros:

• Preserves nested structures (Maps, Lists)
• Schema flexibility
• Better for complex data

Cons:

• Larger file size
• Slower to parse

Recommended: Tables with Maps, Lists, or variable schemas

CSV Format

Pros:

• Smaller file size
• Faster to parse
• Better for flat data

Cons:

• Nested structures serialized as JSON strings
• Requires consistent columns

Recommended: Tables with simple, flat schemas

Athena Query Examples

After creating the table:

-- Basic query
SELECT * FROM your_table LIMIT 100;

-- Filter on nested fields (for JSON exports)
SELECT * FROM your_table
WHERE nested_field->>'key' = 'value';

-- Aggregate
SELECT COUNT(*) FROM your_table;

Troubleshooting

Scan Throttling

Error: ProvisionedThroughputExceededException

Solutions:

1. Reduce segment_workers
2. Increase table's RCUs temporarily
3. Use on-demand capacity mode

Memory Issues

Error: MemoryError

Solutions:

1. Reduce batch_size
2. Reduce segment_workers

Schema Inference Issues

Problem: Athena DDL doesn't match expected schema

Solutions:

1. Increase sample_size in config
2. Manually edit generated DDL
3. Run export with smaller sample to inspect data

Cost Considerations

DynamoDB

• Read Units: ~0.5 RCU per record scanned
• 4.5M records: ~2.25M RCUs
• On-demand: ~$0.56 per million RCUs
• Estimated: ~$1.25

S3 Storage

• Uncompressed: ~5-10 GB for 4.5M records
• Compressed (gzip): ~1-2 GB
• Cost: ~$0.023/GB/month
• Estimated: ~$0.05/month

Athena Queries

• Data scanned: ~$5.00 per TB
• Typical queries: Minimal cost with filtering

Requirements

• Python 3.7+
• boto3
• AWS credentials with permissions:
  • dynamodb:Scan
  • s3:PutObject
  • s3:GetObject
  • s3:ListBucket

License

MIT