11

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.

dreamer
  • 1,039
  • 2
  • 16
  • 36
  • AWS doesn't provide this functionality. They only provide access to Redshift via the PostgreSQL API. If you want a REST API you would have to build one, or find a third-party package that is designed for Redshift or Postgres. – Mark B Jun 25 '16 at 00:54
  • 1
    You can use API-GW (https://aws.amazon.com/api-gateway/), that will call a Lambda function (https://aws.amazon.com/lambda/), that will query Redshift for the user. – Guy Jun 26 '16 at 21:14
  • if the idea is to just expose the data, you could think of creating an elasticsearch node with the redshift data in it. Then you can access the data through REST API which comes as part of elasticsearch – Sujay DSa Sep 04 '18 at 05:43
  • I know it's a little bit old thread, but Redshift has a new API called "Redshift Data API". With this API you could even retrieve data asynchronously. More information could be found in the official announce here: https://aws.amazon.com/about-aws/whats-new/2020/09/announcing-data-api-for-amazon-redshift/ and in the documentation https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html. Maybe it's not the best approach, in terms of flexibility and robustness, but it still valid. – Paulo Oct 26 '20 at 17:21

1 Answers1

24

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).

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470