How to Build a Real-Time Analytics Platform Using Amazon Aurora and ThoughtSpot

By BP Yau, Sr. Partner Solutions Architect – AWS 
By Darien Hong, Solutions Architect – AWS
By Ali Alladin, Sr. Partner Solutions Architect – ThoughtSpot

ThoughtSpot

Amazon Aurora is a fully managed modern relational database service offering performance and high availability at scale, along with fully open-source MySQL and PostgreSQL-compatible editions.

ThoughtSpot is a next-generation analytics platform that delivers live analytics to your modern data stack. ThoughtSpot, an AWS Specialization Partner and AWS Marketplace Seller, makes it possible for anyone to get granular insights instantly from billions of rows of data using search and generative artificial intelligence (AI).

In this post, we discuss how you can connect ThoughtSpot to Amazon Aurora MySQL for real-time operational analytics.

Solution Overview

The use case for this solution is real-time analytics on transaction data from Amazon Aurora MySQL where you perform generative AI-powered search on the data to get insights using ThoughtSpot.

Figure 1 – Analytics architecture.

To implement this solution, you complete the following steps:

• Create an Amazon Aurora MySQL instance.
• Create a connection in ThoughtSpot to Amazon Aurora MySQL.
• Create AI-powered analytics in ThoughtSpot.

Prerequisites

• Amazon Aurora MySQL instance; you can follow the steps in this quick start guide to create an instance.
• ThoughtSpot account.

Populate Source Amazon Aurora MySQL Data

To populate the source data, complete the following steps:

• Connect to your Aurora MySQL cluster and create a database/schema for the TICKIT data model, and initiate the load process:

mysql -h <amazon_aurora_mysql_writer_endpoint> -u admin -p

You can use the script from the following HTML file to create the sample database demodb (using the tickit.db model) in the Amazon Aurora MySQL-compatible edition.

• Run the script to create the tickit.db model tables in the demodb database/schema.
• Load data from Amazon Simple Storage Service (Amazon S3).

Create Connections in ThoughtSpot

To create connection from ThoughtSpot to Amazon Aurora, complete the following steps:

• Log in to your ThoughtSpot portal.
• Create a new data connection to Amazon Aurora MySQL.
• Enter the connection name and description, and then click Continue.
• Enter the following parameters:
  • Host: Your Amazon Aurora MySQL endpoint
  • Port: 3306
  • User: Your Amazon Aurora MySQL user name
  • Password: Your Amazon Aurora MySQL password
  • Database: demodb

Figure 2 – Create ThoughtSpot to Aurora connection.

• Click Continue.
• Select all tables and all columns.
• Click Create Connections.
• Click Create.
• New connection to Amazon Aurora MySQL is created.

Figure 3 – View new table connections created.

Create AI-Powered Analytics in ThoughtSpot

To create high-performance AI-powered analytics using ThoughtSpot, complete the following steps:

• Click Create new from your Data workspace and select Custom SQL view.
• Enter SQL statement below into the SQL Editor and click Run:

  SELECT qtysold, pricepaid, saletime, eventname, venuename, venuecity, venuestate 
  FROM demodb.sales AS s 
  INNER JOIN demodb.event AS e ON e.eventid = s.eventid 
  INNER JOIN demodb.venue AS v ON v.venueid= e.venueid

• You should see results returned:

Figure 4 – Run SQL query.

• Enter a name for the SQL view and click Save.
• Click Exit.
• Click Search data.
• Click Select a Data source.
• Select the SQL view created and click Select.

Figure 5 – Select the SQL view previously created.

• Select saletime, venuecity, pricepaid, qtysold, and then click Go.

Figure 6 – Select the appropriate attributes on the left panel.

• Click the 3 dots and select Save.
• Click Save answer.
• Click Search answers and Liveboards from the top right corner.
• You can now leverage natural language processing and generative AI to search insights from your transaction data from Amazon Aurora; for example, enter “what is the qtysold over period of time” and click Go.
• Trend chart below should be returned.

Figure 7 – AI-generated trend chart.

Summary

You have successfully connected ThoughtSpot to your Amazon Aurora data source and can now run generative AI-powered analytics. With the power of ThoughtSpot, you’re able to create live visualizations and search through your data with natural language.

Check out AWS Marketplace and sign up for a free trial with ThoughtSpot.

.

.

ThoughtSpot – AWS Partner Spotlight

ThoughtSpot is an AWS Specialization Partner and next-generation analytics platform that delivers live analytics to your modern data stack. ThoughtSpot makes it possible for anyone to get granular insights instantly from billions of rows of data using search and generative AI.

Contact ThoughtSpot | Partner Overview | AWS Marketplace