跨AWS账号实现DynamoDB（账号A）到Athena（账号B）的每日数据同步与查询方案咨询

Hey there! Let's break down a couple of solid, production-ready solutions for your cross-account DynamoDB sync and Athena query needs. I’ll cover two approaches—one that keeps data in DynamoDB for direct Athena access, and a more optimized path using S3 (since Athena works way better with columnar storage like Parquet).

Option 1: Daily Batch Sync to Account B's DynamoDB + Athena Query Directly on DynamoDB #

This is straightforward if you need the replicated table to live in Account B's DynamoDB for other use cases, plus Athena querying.

Step-by-Step Setup #

• Cross-Account IAM Permissions First:
  • In Account A, create an IAM role (or use an existing one) with dynamodb:Scan and dynamodb:Query permissions on your source table.
  • In Account B, create an IAM role for your sync tool (Lambda/Glue) with dynamodb:BatchWriteItem and dynamodb:PutItem permissions on the target table. Also, grant Account A's role permission to assume Account B's role (or vice versa, depending on which side runs the sync).
• Choose Your Sync Tool:
  • AWS Glue ETL Job (Recommended for Medium/Large Data):
    Glue handles pagination, batch operations, and retries out of the box. You can:
    1. Create a Glue connection to Account A's DynamoDB table.
    2. Write a simple Python/Scala ETL script to read from the source, transform if needed, and write to Account B's DynamoDB.
    3. Schedule the job daily via CloudWatch Events (e.g., run at 2 AM during low traffic).
       For incremental sync, add logic to filter items by updated_at timestamp (if your table has one) or use DynamoDB's LastEvaluatedKey to pick up where you left off.
  • Lambda + CloudWatch Events (Good for Small Data):
    Write a Python Lambda function using boto3 that:
    1. Scans Account A's DynamoDB table (handle pagination with ExclusiveStartKey).
    2. Uses BatchWriteItem to bulk insert/update items in Account B's table.
    3. Set up a CloudWatch Event rule to trigger the Lambda daily.
       Pro tip: Store the last sync timestamp in Parameter Store to enable incremental scans instead of full table scans every time.
• Athena Query Setup:
  In Account B, create an Athena external table mapped to your replicated DynamoDB table. Use this DDL template (adjust for your schema):

  CREATE EXTERNAL TABLE IF NOT EXISTS my_dynamo_table (
    id string,
    data string,
    updated_at timestamp
  )
  STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
  TBLPROPERTIES (
    "dynamodb.table.name" = "my-target-table",
    "dynamodb.region" = "us-east-1",
    "dynamodb.throughput.read.percent" = "0.5"
  );
  

Pros & Cons #

• ✅ Pros: No extra storage needed; keeps data in DynamoDB for other applications; simple setup for small datasets.
• ❌ Cons: Athena query performance on DynamoDB is limited (it uses DynamoDB's read capacity, so complex aggregations can be slow/costly); full scans may hit RCU limits on your production table.

Option 2: Sync to Account B's S3 (Parquet Format) + Athena Query S3 (Highly Recommended for Athena) #

This is the better choice if Athena query performance and cost efficiency are top priorities. Athena shines with columnar, compressed formats like Parquet stored in S3.

Step-by-Step Setup #

• Account A: Export DynamoDB Data to S3
  • Full Daily Export:
    Use DynamoDB's built-in Export to S3 feature. Trigger it daily via Lambda + CloudWatch Events:
    1. Call the ExportTableToPointInTime API to export your table to Account A's S3 bucket (choose Parquet as the format for optimal Athena performance).
    2. Configure your S3 bucket policy to allow Account B's IAM roles to read the exported files. Alternatively, export directly to Account B's S3 bucket (pre-configure the bucket policy to allow Account A's DynamoDB service role to write to it).
  • Incremental Sync (For Large Datasets):
    If full exports are too slow, use DynamoDB Streams + Lambda:
    1. Enable DynamoDB Streams on your source table to capture all create/update/delete events.
    2. Write a Lambda function that triggers on stream events, converts records to Parquet, and writes them to Account B's S3 bucket (partitioned by date for easy querying).
    3. Use AWS Glue ETL to merge daily incremental data into a master Parquet table in S3.
• Account B: Prepare Data for Athena
  • Glue Crawler: Run a Glue Crawler on your S3 Parquet files to auto-generate a table in the Glue Data Catalog. This saves you from writing manual DDL.
  • Manual Athena Table (If Preferred): Create an external table pointing to your S3 Parquet path. Example DDL:

    CREATE EXTERNAL TABLE IF NOT EXISTS my_synced_parquet_table (
      id string,
      data string,
      updated_at timestamp
    )
    PARTITIONED BY (date string)
    STORED AS PARQUET
    LOCATION 's3://my-bucket/dynamo-exports/';
    

    Run MSCK REPAIR TABLE my_synced_parquet_table; to load partitions.
• Cross-Account Permissions:
  • Ensure Account A's S3 bucket allows Account B's Glue/Athena roles to s3:GetObject and s3:ListBucket.
  • Account B's IAM role needs permissions for s3:Read, glue:Crawl, glue:CreateTable, and athena:QueryExecution.

Pros & Cons #

• ✅ Pros: Athena queries are fast and cheap (Parquet reduces scan volume by 70-90%); scalable for large datasets; supports partitioning and compression for further optimization.
• ❌ Cons: Requires S3 storage (but it's very low-cost); extra steps to set up Glue/partitioning, but the long-term benefits are worth it.

Key Tips for Success #

• Incremental vs Full Sync: Use incremental sync if your daily data changes are small; full sync is simpler for smaller tables.
• Error Handling: Add retries in your Lambda/Glue jobs, and set up CloudWatch Alarms to alert you on sync failures.
• Data Consistency: For full exports, run them during off-peak hours to avoid capturing partial writes. For incremental sync, handle delete events from DynamoDB Streams to keep the target data in sync.
• Least Privilege Permissions: Follow AWS best practices—only grant the minimum permissions needed for each role (e.g., Account A's role only reads the specific DynamoDB table, no other resources).

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