Every business that analyzes their operational (or transactional) data needs to build a custom data pipeline involving several batch or streaming jobs to extract transactional data from relational databases, transform it, and load it into the data warehouse. In this post, we show how you can leverage Amazon Aurora zero-ETL integration with Amazon Redshift and ThoughtSpot for GenAI driven near real-time operational analytics.
Solution overview
The use case for this solution is near real-time analytics on transaction data from Amazon Aurora. Zero-ETL integration is set up between Amazon Aurora and Amazon Redshift. Once transaction data is available in your Amazon Redshift cluster, you can then run AI powered search on the data to get your insights using ThoughtSpot.
The following diagram illustrates the solution architecture at a high level.
Below are the required steps to implement this solution:
Create zero-ETL integration under Amazon RDS.
Create a connection in ThoughtSpot to Amazon Redshift.
Create AI powered analytics in ThoughtSpot.
Prerequisites
Create Amazon Aurora zero-ETL integration with Amazon Redshift
To create an Amazon Aurora zero-ETL integration with Amazon Redshift with sample TICKIT database, please follow all the steps in the getting started guide until your Amazon Aurora transaction data is replicated to your Amazon Redshift serverless aurora_zeroetl database.
Analyzing near-real-time transactional data with ThoughtSpot
Validate source data in your Amazon Redshift data warehouse
Navigate to Redshift Serverless, open query editor v2, pick the cluster/workgroup and database created from integration from the drop-down list, and run the following SQLs to see integration in action, seconds later:
select * from aurora_zeroetl.demodb.sales;
Create connections in ThoughtSpot
To create a connection from ThoughtSpot to Amazon Aurora zero-ETL tables in Amazon Redshift, complete the following steps:
1. Login to your ThoughtSpot portal.
2. Create a new data connection to Amazon Redshift.
3. Enter the connection name and description. Click Continue.
4. Enter the following parameters:
Host: your Amazon Redshift serverless workgroup (refer to this blog for more detail).
Port: 5439
User: your Amazon Redshift serverless user name
Password: password
Database: aurora_zeroetl
5. Click Continue.
6. Select all tables and all columns except column name start with padb*.
7. Click Create Connections.
8. Click Create.
9. Your new connection to Amazon Aurora zero-ETL tables in Amazon Redshift is created.
Create AI-Powered Analytics in ThoughtSpot
To create high performance AI-powered analytics using ThoughtSpot, complete the following steps:
1. Click Create new from your Data workspace and select Custom SQL view.
2. Enter SQL statement below into the SQL Editor and click Run.
SELECT qtysold, pricepaid, saletime, eventname, venuename, venuecity, venuestate
FROM aurora_zeroetl.demodb.sales AS s
INNER JOIN aurora_zeroetl.demodb.event AS e ON e.eventid = s.eventid
INNER JOIN aurora_zeroetl.demodb.venue AS v ON v.venueid= e.venueid3. You should see Results returned.
4. Enter a name for the SQL View and click Save.
5. Click Exit.
6. Click Search data.
7. Click Select a Data source.
8. Select the SQL View created and click Select.
9. Select saletime, venuecity, pricepaid, and qtysold and click Go.
10. Click the 3 dots and select Save.
11. Click Save answer.
12. Click Search answers and Liveboards from the top right corner.
13. You can now leverage natural language processing and generative AI to search insights from your transaction data from Amazon Aurora. For example, “what is the qtysold over period of time?”
Turn your insights into action
It’s simple to set up an Amazon Aurora zero-ETL integration from Amazon Aurora MySQL to Amazon Redshift. This integration eliminates complex data pipelines and enables near real-time analytics on transactional/operational data. You can then connect ThoughtSpot to Amazon Aurora zero-ETL integration tables in Amazon Redshift to run AI-Powered Analytics.
Try it for yourself! We look forward to hearing from you about your experience. If you have questions or suggestions, please leave a comment. Otherwise, see how ThoughtSpot is working with AWS to deliver AI-Powered Analytics and faster insight-to-action.
