Athena lets you use SQL to query arbitrary data stored in S3.
You can find your Redshift cluster details via the AWS console. The Endpoint section gives you the hostname and port all in one. You'll need to get the database name and a username/password from whoever set up the Redshift cluster. You'll also need to whitelist the Retool IP address:
AWS Console > Redshift > Clusters
Create a new resource in Retool, and select "Amazon Redshift" as the type. Enter your cluster's hostname and port, the database's name, and a username/password combination and press "Save".
Resources > Add > Amazon Redshift
You can now select your newly-created Redshift resource from the Resource dropdown when creating queries in your Retool apps.
You can display the results of Redshift queries as with any other query in Retool: