Trying to connect RDS to Athena

To connect your RDS PostgreSQL database with Amazon Athena, you can use the Amazon Athena PostgreSQL connector. This connector enables Athena to access your PostgreSQL databases directly without the need for intermediary storage or Glue catalog tables.

Here's how you can set it up:

1. Deploy the Athena PostgreSQL connector to your AWS account using either the Athena console or AWS Serverless Application Repository.

2. Once deployed, you can use this connector to create a data source in Athena that connects to your RDS PostgreSQL instance.

3. When setting up the connection, you'll need to provide the necessary connection details, including the JDBC connection string for your RDS instance.

4. For authentication, you can either specify the username and password directly in the JDBC connection string or use AWS Secrets Manager for enhanced security.

5. After the connection is established, you'll be able to query your RDS PostgreSQL data directly from Athena without the need for data replication or intermediate storage.

This approach addresses your concerns about the Glue catalog table limitations and the issues with writing large datasets to CSV. By querying the RDS database directly, you can handle large datasets more efficiently and ensure you're always working with the most up-to-date data.

Keep in mind that while this solution allows for direct querying, it may have performance implications for your RDS instance, especially with very large queries. You might want to consider implementing some query optimization techniques or possibly using read replicas for your RDS instance to offload query processing.

If you're still experiencing issues with very large result sets, you might want to explore options for streaming results or implementing pagination in your queries to handle the data in manageable chunks.

Sources
Amazon Athena PostgreSQL connector - Amazon Athena
Amazon Athena Microsoft SQL Server connector - Amazon Athena