REST API for Redshift

I'm currently brainstorming an idea and trying to figure out whether it's feasible or a better way to handle this approach.

Assume I have a Redshift table and I want to expose this table through a REST API. For example, there are several customer who needs some kind of meta data from this table. They will call a REST service and it will execute on the Redshift to get the data and will response to the client in JSON format.

I'm fairly new in Redshift/AWS area so not sure whether AWS already have something for that? I know S3 supports REST API.

Does it sound feasible? I can definitely write typical RESTful service using Java while reading data from Redshift using JDBC. But wanted to know if there is a better way to handle this in AWS world.

The Amazon API Gateway can expose a public API and will then call a Lambda function upon invocation. The Lambda function can do whatever you wish!

For some AWS services, API Gateway can also act as a proxy to the normal API calls (eg Create an AWS Service Proxy for Amazon SNS. However, making SQL calls to Amazon Redshift involves connecting to the database as a client, rather than making API calls to AWS.

Therefore, you would need to:

Write an AWS Lambda function (in either Node.js, Java or Python)
Have the function connect to the Amazon Redshift database and perform an SQL call
Define an API Gateway API that receives the REST request and forwards it to the Lambda function

It may seem complex, but if should be straight-forward if you break it down into components and get each one working in turn.

API Gateway also has the ability to cache responses, which can provide faster performance by not always connecting to Amazon Redshift (eg for recently accessed or slow-changing data).