Can SELECT content from S3 Object be used with API Gateway?

Question

My aim is to use S3 in AWS to store csv files and API Gateway to query those objects and ideally select rows and columns from within the csv files and return them in my web app.

In AWS, there is a method for selecting content from S3 objects. It acts as a filter on a csv file for example to only return certain columns. It can be written in SQL see here: https://docs.aws.amazon.com/AmazonS3/latest/API/RESTObjectSELECTContent.html

There is also a way to use API Gateway as a proxy for S3 to create an API into the bucket, see here: https://docs.aws.amazon.com/apigateway/latest/developerguide/integrating-api-with-aws-services-s3.html

Can these methods be combined so that I can map API Gateway requests directly to a SQL SELECT content from S3 Object query or do I need to use a Lambda function in the middle or some other technique?

user_3936644 · Answer

To request a specific file part you can either do it yourself or use one of the AWS managed services S3 Select or Athena. The difference between both is simple: S3 Select over one file, Athena can execute a request over a whole bucket.

Depending on your situation you may use one or the other, you will have to think up the performance needed and admissible costs.

In any case you cannot just plug API Gateway directly to one of this service, you need a middleware processing the requests.

Still i need to mention that it is possible to directly use S3 Select or Athena by-passing API Gateway. If you do so you will have to be really careful on the rights related to the access keys used. You can create in IAM a specific access (very narrow) to S3 and then use an sdk to directly process your queries from the client side. You have more security issues to handle but you avoid the use of both API Gateway and Lambda.

user_10798636 · Answer

The S3 proxy only let you access the files as documented.

For your purpose you need a entity in the middle which will execute additional business logic for you.

I would recommend lambda.

So you do:

api-gateway->lambda->s3

user_10487508 · Answer

I was able to perform S3 Select query on 100GB of data under 30 seconds in serverless architecture using API Gateway and Lambda. Here is the solution in case you still interested in it. https://github.com/sandyghai/Query-100GB-Data-With-AWS-S3-Select-Under-30-Seconds

Can SELECT content from S3 Object be used with API Gateway?

3 Answers