Amazon Athena と Amazon QuickSight を活用した Amazon SageMaker 上のエージェント型 AI アナリティクスの実現

Modern enterprises face mounting challenges in extracting actionable insights from vast data lakes and lakehouses spanning petabytes of structured and unstructured data. Traditional analytics require specialized technical expertise in SQL, data modeling, and business intelligence tools, creating bottlenecks that slow decision-making across retail, financial services, healthcare, Travel & Hospitality, manufacturing and many more industries. This architecture demonstrates how agentic AI assistant from Amazon Quick transform data analytics into a self-service capability. It showcases enabling business users to query complex structured datasets and mix with unstructured data to find the valuable insights to improve their business outcomes through intuitive natural language interfaces.

To demonstrate the functionality, we built a lakehouse using the TPC-H datasets as our foundation. This integrated architecture leverages Amazon Simple Storage Service (Amazon S3) as a storage, Amazon SageMaker and AWS Glue for lakehouse, Amazon Athena for serverless SQL querying across multiple storage formats (S3 Table, Iceberg, and Parquet), and multiple features from Quick to build dashboard and conversational AI agents that provide natural language access to data insights. Through integrated knowledge bases using Amazon Quick spaces, this solution democratizes lakehouse data access for business users while preserving enterprise-grade security, governance frameworks, and the scalability required for modern data-driven decision-making across the organization.

Solution Overview

The following diagram shows the overall design and corresponding dataflow that we implemented as part of this blog post.

Figure 1: Overall design diagram Reference following steps for the detailed end to end data flow and user interaction capabilities.

• Data Source Ingestion: Structured Data TPC-H serves as the primary data source, containing benchmark datasets stored in relational database format. AWS hosted the TPC-H data in the publicly available s3 bucket (s3://redshift-downloads/TPC-H/2.18/100GB)

• Data Load: Amazon Athena performs the first query layer, executing serverless SQL queries against the TPC-H structured data to extract, prepare data for processing, load data in S3, and create corresponding catalog in Glue.

• Multi-Format Storage Layer: To illustrate the versatility of Data lake and Lakehouse we saved the data into three optimized storage formats:

Amazon S3 -CSV: Use external table to create Athena table based on existing CSV files.

• Amazon S3 (Apache Iceberg-parquet): ACID-compatible table format enabling time-travel and schema evolution

• Amazon S3 Table: Amazon S3 Tables deliver the first cloud object store with built-in Apache Iceberg support and streamline storing tabular data at scale.

• Metadata Cataloging: AWS Glue Catalog indexes all three storage formats, creating a unified metadata layer that enables seamless querying across different data formats.

• Lakehouse Query Layer: We used the Amazon Athena SQL queries across storage formats (S3 Table, Iceberg, and Parquet) using the Glue Catalog metadata, providing a unified query interface.

• Business Intelligence Pipeline: Structured TPC-H data flows into Amazon Quick, which integrates with Quick Sight to create:

Dataset – We utilized Amazon Athena connection from Amazon Quick to extract structured data to load in Quick SPICE (Super-fast, Parallel, In-memory Calculation Engine) dataset

• Topic – Organized data domains for business context

• Dashboard Using Q – Interactive visualizations with natural language query capabilities to build the dashboard and publish it

• AI Knowledge Enhancement: Parallel to the structured data flow, a Web Crawler for TPC-H specifications ingests unstructured data (documentation, specifications) and feeds it into Knowledge Bases to provide contextual understanding.

• Conversational Agentic AI Layer: Knowledge Bases power Amazon Quick spaces (collaborative environments), which in turn enable the Amazon Quick chat agents with contextual awareness and domain knowledge for natural language interactions.

• End User Access: Users interact with the system through two primary interfaces:

Dashboard Using Q – Visual analytics and self-service Business Intelligence

• Chat Agent – Conversational AI for natural language data exploration

Pre-requisite

Before you get started, make sure you have the following prerequisites:

• An AWS account and Amazon Quick account

• A basic understanding of Amazon Simple Storage Service, Amazon SageMaker, AWS Lake Formation, and Amazon Athena

• Console role with permissions to create the dataset in S3, run Athena queries, create Glue catalog, Lake Formation admin privileges, and access Quick features. To decide the relevant policy/policies reference policy document.

Data Preparation for lakehouse / data Lake

In this section, we will mimic many of the data lake features by working with external tables, which allow querying data stored in Amazon S3 without loading it into a managed storage layer. We will explore Open Table Format (OTF) tables using Apache Iceberg to consider possible ACID transactions supported tables. Amazon managed S3 Tables will be leveraged to showcase how Amazon natively supports Iceberg-compatible table management directly within S3, simplifying lakehouse architecture at scale. Throughout these exercises, we will use the industry-standard TPC-H dataset, a benchmark workload representing a realistic business data model with orders, customers, and line items to make sure our examples are both meaningful and reproducible.

We will leverage Amazon Athena for data preparation. If this is your first time using Amazon Athena, you’ll need to create an Amazon S3 bucket to store your query results. Athena uses S3 as its output location before you can run queries. Follow the official AWS getting started guide to complete this one-time setup: Getting Started with Amazon Athena. Alternately, you can use Managed query results feature.

Tip: Choose an S3 bucket in the same AWS Region as your data sources to avoid cross-region data transfer costs and latency.

Once your S3 output location is configured, you’re ready to proceed.

Create the Glue Database

Start by creating a Glue database that will serve as the metadata catalog for all your tables using Athena. Run the following SQL in the Athena query editor:

CREATE DATABASE IF NOT EXISTS blog_qs_athena_tpc_h_db_sql COMMENT 'TPC-H database'; 

Figure 2: Database creation blog_qs_athena_tpc_h_db_sql

What this does: This registers a logical database in the AWS Glue Data Catalog, which Athena uses to organize and discover your tables. Tables created in subsequent steps will live under this database.

Create an External Table on S3

Next, create an external table pointing to the TPC-H “customer” dataset stored in a public S3 bucket ('s3://redshift-downloads/TPC-H/2.18/100GB/customer/'). External tables in Athena don’t move or copy data — they query it directly from S3, making this a fast and cost-effective way to explore raw data.

CREATE EXTERNAL TABLE IF NOT EXISTS blog_qs_athena_tpc_h_db_sql.customer_csv 
( 
	C_CUSTKEY INT, 
	C_NAME STRING, 
	C_ADDRESS STRING, 
	C_NATIONKEY INT, 
	C_PHONE STRING, 
	C_ACCTBAL DOUBLE, 
	C_MKTSEGMENT STRING, 
	C_COMMENT STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 's3://redshift-downloads/TPC-H/2.18/100GB/customer/'
TBLPROPERTIES ('classification' = 'csv'); 

Verify the table by previewing a few rows:

SELECT * FROM blog_qs_athena_tpc_h_db_sql.customer_csv LIMIT 10; 

Figure 3: verify blog_qs_athena_tpc_h_db_sql.customer_csv

Create an Apache Iceberg Table

Next, we will mimic the table using Apache Iceberg, which is an open table format that brings ACID transactions, time travel, and partition evolution to your data lake — making it ideal for production-grade workloads. This is a three-step process.

Step1: Create the S3 Bucket – Before writing SQL queries, set up your storage layer. You can create an S3 bucket using the AWS Management Console or AWS CLI.

For this blog, I’m using the S3 bucket: amzn-s3-demo-bucket

Note: Your bucket name will be different, as S3 bucket names must be globally unique across all AWS accounts.

Step2: Create an External CSV Table for Orders – First, register the raw orders data as an external table in its original format, in our case it’s CSV.

CREATE EXTERNAL TABLE IF NOT EXISTS blog_qs_athena_tpc_h_db_sql.orders_csv 
( 
	O_ORDERKEY BIGINT, 
	O_CUSTKEY BIGINT, 
	O_ORDERSTATUS STRING, 
	O_TOTALPRICE DOUBLE, 
	O_ORDERDATE STRING, 
	O_ORDERPRIORITY STRING, 
	O_CLERK STRING, 
	O_SHIPPRIORITY INT, 
	O_COMMENT STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('field.delim' = '|')
LOCATION 's3://redshift-downloads/TPC-H/2.18/100GB/orders/'
TBLPROPERTIES ('classification' = 'csv');

Let’s verify the dataset.

SELECT * FROM blog_qs_athena_tpc_h_db_sql.orders_csv LIMIT 10;

Figure 4: verify blog_qs_athena_tpc_h_db_sql.orders_csv

Step3: Create the Iceberg Table Using CREATE TABLE AS SELECT (CTAS) – Use CREATE TABLE AS SELECT (CTAS) to create a self-managed Iceberg table in Parquet format, partitioned by order date. We’ll load a sample date range O_ORDERDATE BETWEEN ‘1998-06-01’ AND ‘1998-12-31’.

CREATE TABLE blog_qs_athena_tpc_h_db_sql.orders_iceberg
WITH ( 
	table_type = 'ICEBERG', 
	format = 'PARQUET', 
	is_external = false, 
	partitioning = ARRAY['o_orderdate'], 
	location = 's3://amzn-s3-demo-bucket/tpch_iceberg/orders/')
AS
SELECT * FROM blog_qs_athena_tpc_h_db_sql.orders_csv
WHERE O_ORDERDATE BETWEEN '1998-06-01' AND '1998-12-31';

Verify the Iceberg table data:

SELECT * FROM blog_qs_athena_tpc_h_db_sql.orders_iceberg LIMIT 10; 

Figure 5: verify blog_qs_athena_tpc_h_db_sql.orders_iceberg

Create an Amazon S3 Table

Amazon S3 Tables are purpose-built, fully managed tables with built-in Apache Iceberg support. It delivers high-performance query throughput without the overhead of managing maintenance operations, such as compaction, snapshot management, and unreferenced file removal. This is a three-step process.

Step1: Create the S3 Table Bucket and Namespace – Navigate to S3 → Table Buckets in the AWS Console to create the bucket blog-qs-athena-tpc-h-db-sql-s3-table-mar-3 and namespace. Alternatively, use the AWS CLI for scripted setup.

Note : You can ignore these steps if you already have an S3 table bucket and namespace available.

Figure 6: Create S3 Table bucket blog-qs-athena-tpc-h-db-sql-s3-table-mar-3

Now let’s create a namespace blog_qs_athena_tpc_h_namespace associated with above S3 table bucket by clicking on the blog-qs-athena-tpc-h-db-sql-s3-table-mar-3.

Figure 7: Create S3 table Namespace blog_qs_athena_tpc_h_namespace

Step2: Create an External CSV Table for Line Items – Use Athena to register the TPC-H line items dataset as an external table:

CREATE EXTERNAL TABLE IF NOT EXISTS blog_qs_athena_tpc_h_db_sql.lineitem_csv 
( 
	L_ORDERKEY BIGINT, 
	L_PARTKEY BIGINT, 
	L_SUPPKEY BIGINT, 
	L_LINENUMBER INT, 
	L_QUANTITY DECIMAL(15,2), 
	L_EXTENDEDPRICE DECIMAL(15,2), 
	L_DISCOUNT DECIMAL(15,2), 
	L_TAX DECIMAL(15,2), 
	L_RETURNFLAG STRING, 
	L_LINESTATUS STRING, 
	L_SHIPDATE STRING, 
	L_COMMITDATE STRING, 
	L_RECEIPTDATE STRING, 
	L_SHIPINSTRUCT STRING, 
	L_SHIPMODE STRING, 
	L_COMMENT STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 's3://redshift-downloads/TPC-H/2.18/100GB/lineitem/'
TBLPROPERTIES ('skip.header.line.count' = '0');

Preview the data:

SELECT * FROM blog_qs_athena_tpc_h_db_sql.lineitem_csv LIMIT 10;

Figure 8: verify data blog_qs_athena_tpc_h_db_sql.lineitem_csv

Step3: Create the S3 Tables Table Using CTAS – Finally, create a Parquet-formatted S3 Tables in your new catalog using CTAS. We filter a sample date range to limit the initial data load based on CAST(L_SHIPDATE AS DATE) BETWEEN DATE(‘1998-06-01’) AND DATE(‘1998-12-31’).

Note: Make sure to use s3tablescatalog to run the following queries as shown in the following screenshot.

CREATE TABLE lineitem_csv_s3_table

WITH ( format = 'PARQUET')

AS

SELECT * FROM AwsDataCatalog.blog_qs_athena_tpc_h_db_sql.linei