Construct a REST API to allow knowledge consumption from Amazon Redshift

API (Software Programming Interface) is a design sample used to reveal a platform or utility to a different social gathering. APIs allow applications and functions to speak with platforms and companies, and will be designed to make use of REST (REpresentational State Switch) as a software program structure fashion.

APIs in OLTP (on-line transaction processing) are known as regularly (tens to lots of of occasions per second), delivering small payloads (output) within the order of some bytes to kilobytes. Nonetheless, OLAP (on-line analytical processing) has the ratio flipped. OLAP APIs have a low name quantity however massive payload (100 MB to a number of GBs). This sample provides new challenges, like asynchronous processing, managing compute capability, and scaling.

On this publish, we stroll by organising an utility API utilizing the Amazon Redshift Knowledge API, AWS Lambda, and Amazon API Gateway. The API performs asynchronous processing of consumer requests, sends consumer notifications, saves processed knowledge in Amazon Easy Storage Service (Amazon S3), and returns a presigned URL for the consumer or utility to obtain the dataset over HTTPS. We additionally present an AWS CloudFormation template to assist arrange sources, accessible on the GitHub repo.

Answer overview

In our use case, Acme sells flowers on its web site acmeflowers.com and collects opinions from prospects. The web site maintains a self-service stock, permitting completely different producers to ship flowers and different supplies to acmeflowers.com when their provides are working low.

Acme makes use of Amazon Redshift as their knowledge warehouse. Close to-real-time adjustments and updates to their stock circulate to Amazon Redshift, displaying correct availability of inventory. The desk PRODUCT_INVENTORY accommodates up to date knowledge. Acme desires to reveal stock info to companions in a cheap, safe approach for stock administration course of. If Acme’s companions are utilizing Amazon Redshift, cross-account knowledge sharing might be a possible choice. If companions aren’t utilizing Amazon Redshift, they may use the answer described on this publish.

The next diagram illustrates our answer structure:

The workflow accommodates the next steps:

1. The consumer utility sends a request to API Gateway and will get a request ID as a response.
2. API Gateway calls the request receiver Lambda operate.
3. The request receiver operate performs the next actions:
   1. Writes the standing to an Amazon DynamoDB management desk.
   2. Writes a request to Amazon Easy Queue Service (Amazon SQS).
4. A second Lambda operate, the request processor, performs following actions:
   1. Polls Amazon SQS.
   2. Writes the standing again to the DynamoDB desk.
   3. Runs a SQL question on Amazon Redshift.
5. Amazon Redshift exports the info to an S3 bucket.
6. A 3rd Lambda operate, the poller, checks the standing of the leads to the DynamoDB desk.
7. The poller operate fetches outcomes from Amazon S3.
8. The poller operate sends a presigned URL to obtain the file from the S3 bucket to the requestor by way of Amazon Easy Electronic mail Service (Amazon SES).
9. The requestor downloads the file utilizing the URL.

The workflow additionally accommodates the next steps to test the standing of the request at numerous phases:

1. The consumer utility or consumer sends a request ID to API Gateway that’s generated in Step 1.
2. API Gateway calls the standing test Lambda operate.
3. The operate reads the standing from the DynamoDB management desk.
4. The standing is returned to the requestor by API Gateway.

Conditions

You want the next stipulations to deploy the instance utility:

Full the next prerequisite steps earlier than deploying the pattern utility:

1. Run the next DDL on the Amazon Redshift cluster utilizing the question editor to create the schema and desk:

   create schema rsdataapi;
   
   create desk rsdataapi.product_detail(
    sku varchar(20)
   ,product_id int 
   ,product_name varchar(50)
   ,product_description varchar(50)
   );
   
   Insert into rsdataapi.product_detail values ('FLOWER12',12345,'Flowers - Rose','Flowers-Rose');
   Insert into rsdataapi.product_detail values ('FLOWER13',12346,'Flowers - Jasmine','Flowers-Jasmine');
   Insert into rsdataapi.product_detail values ('FLOWER14',12347,'Flowers - Different','Flowers-Different');

2. Configure AWS Secrets and techniques Supervisor to retailer the Amazon Redshift credentials.
   
3. Configure Amazon SES with an e-mail deal with or distribution record to ship and obtain standing updates.

Deploy the applying

To deploy the applying, full the next steps:

1. Clone the repository and obtain the pattern supply code to your setting the place AWS SAM is put in:

   git clone https://github.com/aws-samples/redshift-application-api

2. Turn into the challenge listing containing the template.yaml file:

   cd aws-samples/redshift-application-api/property
   export PATH=$PATH:/usr/native/decide/python@3.8/bin

3. Change the API .yaml file to replace your AWS account quantity and the Area the place you’re deploying this answer:

   sed -i ‘’ “s/<input_region>/us-east-1/g” *API.yaml
   sed -i ‘’ “s/<input_accountid>/<present your AWS account id with out dashes>/g” *API.yaml

4. Construct the applying utilizing AWS SAM:

   sam construct

   

5. Deploy the applying to your account utilizing AWS SAM. Remember to comply with correct Amazon S3 naming conventions, offering globally distinctive names for S3 buckets:

   

SAM deploy requires you to offer the next parameters for configuration:

This template is designed solely to indicate how one can arrange an utility API utilizing the Amazon Redshift Knowledge API, Lambda, and API Gateway. This setup isn’t meant for manufacturing use with out modification.

Take a look at the applying

You should use Postman or every other utility to hook up with API Gateway and go the request to entry the dataset from Amazon Redshift. The APIs are approved by way of IAM customers. Earlier than sending a request, select your authorization kind as AWS SigV4 and enter the values for AccessKey and SecretKey for the IAM consumer.

The next screenshot exhibits a pattern request.

The next screenshot exhibits the e-mail response.

The next screenshot exhibits pattern response with the standing of a request. That you must go the request ID and enter all for standing historical past or newest for contemporary standing.

Clear up

While you’re completed testing this answer, bear in mind to scrub up all of the AWS sources that you just created utilizing AWS SAM.

Delete the add and obtain S3 buckets by way of the Amazon S3 console after which run the next on SAM CLI:

For extra info, see sam delete.

Abstract

On this publish, we confirmed you how one can arrange an utility API that makes use of the Amazon Redshift Knowledge API, Lambda, and API Gateway. The API performs asynchronous processing of consumer requests, sends consumer notifications, saves processed knowledge in Amazon S3, and returns a presigned URL for the consumer or utility to obtain the dataset over HTTPs.

Give this answer a attempt to share your expertise with us!

Concerning the Authors

Jeetesh Srivastva is a Sr. Supervisor Specialist Options Architect at AWS. He makes a speciality of Amazon Redshift and works with prospects to implement scalable options utilizing Amazon Redshift and different AWS Analytic companies. He has labored to ship on-premises and cloud-based analytic options for purchasers in banking and finance and hospitality trade verticals.

Ripunjaya Pattnaik is an Enterprise Options Architect at AWS. He enjoys problem-solving together with his prospects and being their advisor. In his free time, he likes to strive new sports activities, play ping pong, and watch motion pictures.