1/ Everyone understands the power of APIs. But what happens when a Database gets an API? #AWS #Redshift got one recently and it opens up plenty of new use cases. A thread 👇
2/ Until September 2020, Redshift required clients to use JDBC/ODBC based connections through the drivers it provided. But that changed when the Data API was introduced. With Data API, you can now fire SQL queries through a simple HTTPS endpoint docs.aws.amazon.com/redshift/lates…
3/ You can use the familiar IAM based auth and this API is integrated in AWS SDKs. Which means you can now programmatically run SQL queries just like invoking any AWS API. You simply invoke the “execute-statement” API by providing the SQL query and get an execution id in response
4/ The Data API also works in async mode where query results are automatically stored for a period of 24 hours. You can poll if your query finished execution and then invoke the “get-statement-result” API to get the query results
5/ Now you can start imagining various use cases that can be easily solved. A file arrives in S3 -> a Lambda function gets called -> load data into Redshift by invoking copy command through Data API. How about running a scheduled sales report every day?
6/ With the “Scheduled Queries” feature,Redshift now integrates with AWS #EventBridge where you can configure a query to be run periodically through a cron expression. This can be configured right within the Redshift mgmt console. docs.aws.amazon.com/redshift/lates…
7/ Here’s a pipeline to send a daily sales report: EventBridge triggers query execution->query runs in Redshift->get notification in a SNS topic on query completion->run Lambda to generate a report from results->send report in an email
8/ A lot of use cases can be automated using the Data API and Scheduled Queries. Executing long running queries, periodically unloading data from Redshift to Data Lake, Refreshing external schema for Spectrum
• • •
Missing some Tweet in this thread? You can try to
force a refresh
