Blogs

Data on AWS: RDS, Redshift, and Athena

Overview

Operational checklists and copy-paste patterns for three common data workflows on AWS:

SectionUse when
Amazon RDSConnecting to managed databases, backups, SSL, and performance triage
Redshift COPY from S3Loading warehouse data from S3 with IAM roles
Athena forensicsQuerying VPC Flow Logs stored in S3

Amazon RDS

Day-2 checklist for connectivity, backups, SSL, and performance triage on Amazon RDS.

Connectivity checklist

  • Publicly accessible alone is not enough — verify subnet routes, security groups, and NACL return traffic.
  • Prefer private subnets with inbound rules from the application security group only.
  • Confirm SSL requirements and engine-specific parameters such as rds.force_ssl when enforced.

Operations checklist

  • Set backup retention, deletion protection, and final snapshot behavior deliberately.
  • Track parameter group changes that enter pending-reboot.
  • Enable CloudWatch metrics plus Database Insights or Performance Insights depending on engine support.

Export data with the MySQL client

mysql -h <endpoint> -P 3306 -u <user> -p --ssl-ca=global-bundle.pem <db> \
  -e "SELECT * FROM products" > products.csv

References

Redshift COPY from S3

Load data from Amazon S3 into Amazon Redshift using the COPY command and an IAM role (no long-lived access keys).

COPY target_table
FROM 's3://bucket/prefix/'
IAM_ROLE 'arn:aws:iam::<account-id>:role/<redshift-copy-role>'
FORMAT AS JSON 'auto'
REGION '<bucket-region>';

Replace FORMAT AS JSON 'auto' with CSV, PARQUET, or other formats as needed.

Pre-flight checklist

CheckWhy it matters
Use IAM_ROLEAvoids embedding access keys in SQL or secrets
Grant s3:GetObject and s3:ListBucketRole must read the source prefix
Include KMS permissionsRequired when objects use SSE-KMS
Use manifest filesControlled, repeatable multi-file loads
Load via staging tablesValidate rows before merging to production tables
Set REGIONRequired when the S3 bucket is in a different Region than the cluster

References

Athena forensics

Query VPC Flow Logs (and similar log data) in S3 with Amazon Athena. For full OrderFlow query samples, see OrderFlow snippets.

VPC Flow Logs external table

Point the table LOCATION at your log prefix. Partition by date to limit scan cost.

CREATE EXTERNAL TABLE IF NOT EXISTS vpc_flow_logs (
  version int,
  account_id string,
  interface_id string,
  srcaddr string,
  dstaddr string,
  srcport int,
  dstport int,
  protocol bigint,
  packets bigint,
  bytes bigint,
  start bigint,
  `end` bigint,
  action string,
  log_status string
)
PARTITIONED BY (`date` date)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/{account_id}/vpcflowlogs/{region_code}/'
TBLPROPERTIES ("skip.header.line.count"="1");

After creating the table, add partitions (or use partition projection) before running queries:

ALTER TABLE vpc_flow_logs ADD IF NOT EXISTS
  PARTITION (`date`='2026-06-01')
  LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/123456789012/vpcflowlogs/us-east-1/2026/06/01/';

Example query — top talkers by bytes

SELECT srcaddr, dstaddr, SUM(bytes) AS total_bytes
FROM vpc_flow_logs
WHERE `date` = DATE '2026-06-01'
  AND action = 'ACCEPT'
GROUP BY srcaddr, dstaddr
ORDER BY total_bytes DESC
LIMIT 20;

Related Posts