如何每小时将Amazon Redshift表导出至S3外部表？

Great question! You don’t have to limit yourself to Amazon EMR—there are several straightforward and scalable options in AWS to achieve this. Let’s walk through each approach:

1. UNLOAD Command + Partition Management #

The UNLOAD command can absolutely write to S3 paths that map to partitions of your external table. Here’s how to make it work:

• First, define your external table (e.g., in Redshift Spectrum or Athena) with partition keys (like year, month, day, hour).
• Use UNLOAD to export data directly to the corresponding partition path in S3. For example:

  UNLOAD ('SELECT * FROM your_source_table WHERE event_time >= DATEADD(hour, -1, CURRENT_TIMESTAMP) AND event_time < CURRENT_TIMESTAMP')
  TO 's3://your-bucket/your-table/year=2024/month=05/day=20/hour=12/'
  IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftUnloadRole'
  FORMAT PARQUET
  PARTITION BY (year, month, day, hour);
  

• After unloading, refresh the external table’s partition metadata so it recognizes the new data. You can do this with:

  ALTER TABLE your_external_table ADD PARTITION (year=2024, month=05, day=20, hour=12) LOCATION 's3://your-bucket/your-table/year=2024/month=05/day=20/hour=12/';
  

• Automation: Use Redshift Scheduled Queries or CloudWatch Events triggering an AWS Lambda function to run these commands hourly.

2. Redshift Spectrum INSERT INTO External Table #

If you already have a partitioned Spectrum external table, you can directly use INSERT INTO to populate partitions—Redshift handles writing to the correct S3 paths automatically:

INSERT INTO spectrum.your_partitioned_table
SELECT * FROM your_source_table
WHERE event_time >= DATEADD(hour, -1, CURRENT_TIMESTAMP) AND event_time < CURRENT_TIMESTAMP;

• Redshift will automatically create the partition in S3 if it doesn’t exist, and update the table’s metadata.
• Automation: Same as above—schedule this query via Redshift’s built-in scheduler or Lambda + CloudWatch.

3. AWS Glue ETL Jobs #

For scenarios where you need data transformation alongside export, AWS Glue is a great serverless option:

• Create a Glue Crawler to discover your Redshift source table and the target partitioned S3 table (or define the target schema manually).
• Build an ETL job (using PySpark, Scala, or Glue’s visual editor) that reads from Redshift, applies any needed transformations, and writes to the partitioned S3 table. Glue automatically manages partition creation based on your specified keys.
• Automation: Use Glue’s built-in job scheduling feature to run the job hourly, or trigger it via CloudWatch Events.

4. Amazon EMR (Optional, Not Required) #

EMR is certainly an option, especially if you’re dealing with extremely large datasets or need complex distributed processing (like advanced analytics or joins across multiple sources):

• Use Spark or Hive on EMR to connect to Redshift (via JDBC or the Redshift Spark connector), read the hourly data, and write it to partitioned S3 storage.
• However, this is overkill for simple hourly exports—stick to the lighter options above unless you need EMR’s processing capabilities.

Which to Choose? #

• For simple, no-transform exports: Go with UNLOAD + partition management or Redshift Spectrum INSERT—they’re the most efficient and cost-effective.
• For data transformation needs: AWS Glue is the best serverless choice.
• For large-scale complex processing: EMR makes sense, but it’s not the only solution.

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