How to consume tabular data from Amazon S3 Tables for insights and business reporting

When was the last time you found yourself trying to look at rows and rows of data in a spreadsheet struggling to interpret and draw conclusions? Many analysts and engineers experience the same challenge every day. Whether it’s analyzing sales trends, monitoring operational metrics, or understanding customer behavior, the challenge lies not just in interpreting it, but also storing, transforming, and accessing it quickly and efficiently.

Cloud storage solutions revolutionized how we store and manage data, offering scalability and cost-effectiveness that traditional solutions cannot match. Amazon S3 Tables build on these advantages by providing a managed service that combines the reliability of object storage with the querying capabilities typically associated with databases.

This advancement enables organizations to interact with their data through familiar table-based concepts while retaining the economic and flexible advantages of object storage. The integration between S3 Tables and AWS analytics services opens new possibilities for data analysis and visualization. Organizations can now query, analyze, and visualize their tabular data using your preferred choice of visualization tool, allowing you to uncover meaningful insights without complicated data transformation processes.

In this post, we explore the journey of visualizing data stored in S3 Tables using different tools. We walk through the process of using Amazon Managed Grafana for operational monitoring and Amazon QuickSight for business intelligence, demonstrating how these tools work together with Amazon Athena to create powerful data insights to drive decision making. We showcase how each tool serves different visualization needs—from real-time operational dashboards to interactive business intelligence reports—and how to choose the right tool for your specific use case.

The scenario: RetailCo analytics

Meet RetailCo, a growing retail chain with stores across multiple locations. They maintain detailed records of their store operations, such as store performance metrics, employee staffing levels, operational hours, and management data. This valuable information is stored in S3 Tables, providing a comprehensive view of their retail operations over time.

Their data includes crucial metrics such as:

• Store performance across different locations
• Staffing levels and floor space usage
• Operating hours and scheduling information
• Management assignments and market segmentation

Although RetailCo successfully captures this data in S3 Tables, they face several challenges in turning this information into actionable insights:

• Store performance analysis: The operations team needs real-time dashboards to monitor store performance metrics, compare different locations, and track historical trends to optimize operations.
• Resource optimization: HR and operations managers need detailed analytics to understand staffing patterns, floor space usage, and operating hours effectiveness across different store locations.
• Management insights: Regional directors need interactive reports to analyze store management performance and market-specific trends to make informed decisions about resource allocation and operational strategies.
• Business intelligence: Executive leadership needs high-level dashboards that provide quick insights into overall chain performance, market penetration, and growth opportunities.

Throughout this post, we reference RetailCo’s scenario to demonstrate how visualization tools can transform their store operations data into insights.

Prerequisites

In this solution, we use the instructions and dataset from the blog post, Build a managed transactional data lake with Amazon S3 Tables. Please follow instructions through Part 2 and stop when you reach Part 3. In the post, the authors introduce S3 Tables constructs and provide instructions to load data into a table bucket using Amazon EMR. To complete this solution, you need the following prerequisites:

• AWS Account with access to Amazon Athena, Amazon QuickSight, and AWS Lake Formation
• Active Grafana workspace
• QuickSight user

Enabling integration with AWS analytics services

To connect your S3 Tables to your visualization tool, you need to enable Integration with AWS analytics services, which allows AWS Glue Data Catalog and Lake Formation access to your table buckets. You can enable this integration through the AWS Management Console or programmatically.

Go to the Amazon S3 console and choose Table buckets from the pane on the left-hand side. You should see the blog-s3tables-data bucket created from the prerequisite. Choose Enable integration at the top of the page and again on the next page. This creates the following:

• AWS Identity and Access Management (IAM) role: S3TablesRoleForLakeFormation
• AWS Glue Data Catalog: s3tablescatalog

You can read more about how this integration works in our documentation.

Integrate with your choice of visualization tool

The following sections outline how to integrate with different visualization tool options.

Grafana

To add your S3 Tables to Grafana workspace, you need to add Athena as a data source by using the AWS data source configuration option in the Grafana workspace. Please follow the instructions to add Athena as a data source.

Permissions: To configure the data source in Amazon Managed Grafana, you need to provide access to Grafana Service role to the table in Lake Formation. From the Amazon Managed Grafana console, note the IAM role as shown in the following image.

Go to the Lake Formation console and choose Databases underneath the Data Catalog section on the left. From the Choose Catalog dropdown menu, choose the table you created: <your AWS account number>:S3tablescatalog/blog-s3tables-data. This would list the namespace we created as a database, choose the database and from actions menu, choose Grant, as shown in the following figure.

For Principals, choose IAM users and roles and search for the Grafana Service role, which we noted previously.

Choose the Named Data Catalog resources and choose the databases and tables from the dropdown menu as shown in the following image.

You only need read only access, so check the Select and Describe boxes under Table permissions and then choose Grant, as shown in the following figure.

Updating IAM role

Browse to the IAM console and search for the Grafana Service role that you noted in the previous step.

Add an inline policy to provide access to the glue catalog so that it can list the s3tablescatalog and get permissions from Lake Formation. Make sure you update the AWS Region and account ID in the following IAM policy.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "glue",
            "Effect": "Allow",
            "Action": [
                "glue:GetCatalogs",
                "glue:GetCatalog"
            ],
            "Resource": [
                "",
                "arn:aws:glue:<REGION>:<ACCOUNT-ID>:catalog"
            ]
        },
        {
            "Sid": "lakeformation",
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess"
            ],
            "Resource": "*"
        }
    ]
}

Configure data source in Grafana

Navigate to the Amazon Managed Grafana console, and open the Grafana workspace URL of your active workspace. Make sure you provide admin rights to your Grafana user. You can configure permissions underneath the Administration section within your Grafana workspace.

In the Grafana workspace, choose Apps, AWS Data Source, and choose Data sources, as shown in the preceding figure.

From the dropdown menu for Service, choose Athena and the Default Region where you created your S3 Tables (in this case, US East (N. Virginia)). Choose the workgroup that you tagged with GrafanaDataSource: true. Choose Add 1 data source, as shown in the following figure.

Build dashboard

When the data source has been added, browse to Dashboard and navigate to Create Dashboard, as shown in the following figure.

Choose Add visualization. This prompts you to choose a data source.

Choose the Athena data source that we just added, as shown in the following figure.

To query your table, you need to provide the name of the catalog, and namespace, followed by the table name. When you integrate S3 Tables with AWS Analytics services, an AWS Glue Data Catalog named s3tablescatalog/<s3-table-name> is automatically created. Therefore, for our demo, the format becomes "s3tablescatalog/blog-s3tables-data".blognamespace.blogstoresale.

For the sample visualization, we create a bar chart visualization of the number of stores in states.

The sample query used is:

select count(s_store_id) as "Number of Stores", s_state as "State" from "s3tablescatalog/blog-s3tables-data".blognamespace.blogstoresales group by s_state

When you first run this query, you may see a message saying “Data is missing a time field.” This is because Grafana’s default visualization expects time-series data. To view the results, please change the Visual type to a bar chart in the visualization settings, as shown in the following figure.

QuickSight

To continue addressing RetailCo’s business intelligence needs, we can use QuickSight to build interactive reports and dashboards from their S3 Tables data. QuickSight provides a range of capabilities that allow RetailCo’s executive leadership to gain quick insights into overall chain performance, market penetration, and growth opportunities.

The steps to integrate S3 Tables with QuickSight are as follows:

You need a QuickSight Admin user Amazon Resource Name (ARN) so you can grant permissions in Lake Formation for your QuickSight user to your table. You can find your QuickSight Admin user ARN through the CLI or console:

CLI:

aws quicksight list-users —aws-account-id <accountid> —namespace default —region <aws region>

Console: Go to the QuickSight console, choose the user in the top right corner, note the QuickSight username, and fill in the information accordingly:

arn:aws:quicksight:<current AWS Region>:<your AWS Account Number>:user/default/<your Amazon QuickSight Username>

Grant permissions from the Lake Formation console. Choose Grant in the Data Permissions section and make the following selections:

Principals: Choose SAML users and groups and enter your QuickSight Admin user ARN from the previous step, as shown in the following figure.

Lake Formation Tags (LF-Tags) or catalog resources

• Choose Named Data Catalog resources.
  • Catalog: <your AWS Account Number>:s3tablescatalog/blogs-s3tables-data
  • Databases: blognamespace
  • Tables: blogstoresales
• You can use the Data Filters section for Row-Level Security (RLS) and Column-Level Security (CLS) purposes.

Table permissions

• For Catalog permissions, check Select.
• For Grantable permissions, leave all unchecked, as shown in the following figure.

Finally, choose Grant.

Adding your table as a data source in QuickSight

Now that you have the permissions and connections established to your data source, you can add it to QuickSight. From the QuickSight console, choose Datasets and then choose New dataset in the right-hand corner, as shown in the following figure.

Choose Athena. Enter a Data source name, then choose Create data source, as shown in the following figure.

Choose Use custom SQL. You can’t choose your table from the Choose your table pane unless you added it as a data source in Athena, as shown in the following figure.

Enter an Athena SQL query that captures the columns you want to visualize. For the entire table, use the following query, then choose Confirm query.

SELECT * FROM "s3tablescatalog/blog-s3tables-data".blognamespace.blogstoresales

Choose Visualize to analyze data and start building dashboards.

Generative BI with Amazon Q in QuickSight

Amazon Q in QuickSight, powered by Amazon Bedrock, can save authors time by quickly transforming, visualizing, and summarizing your table data.

To get started, choose Topics from the home page of QuickSight and choose New topic, as shown in the following figure.

Choose a Topic name and keep the new generative Q&A experience checked. Choose Continue, as shown in the following figure.

The generative Q&A experience suggests AI-generated questions for your dataset and creates summaries and visualizations. It takes a couple minutes to analyze the dataset. When it’s complete, you can start a review of your dataset. In the review process, you can edit field names, give more names, add calculated fields, and more. In our example, we enable the field with the S City column by selecting the include toggle. We also add “city” as a synonym, as shown in the following figure.

When you continue through the review process, you can ask Amazon Q questions about your data in natural language and review questions asked by other users in the Monitor activity tab. You can also analyze topic performance and update topic details based on usage and feedback. Finally, you are asked to review questions. These could be user asked questions or suggested AI-generated questions. In our example, we asked which state has the most stores, and Amazon Q generated several options to meaningfully display your data, as shown in the following figure.

Cleaning up

You can follow these steps to remove all resources you’ve set up while following along with this post.

Grafana

1. Open the Amazon Managed Grafana console.
2. In the left navigation pane, choose the menu icon.
3. Choose All workspaces.
4. Choose the name of the workspace that you want to delete.
5. Choose Delete.
6. To confirm the deletion, enter the name of the workspace and choose Delete.
7. After deleting the workspace, clean up the associated IAM roles:

7.1. Go to the IAM console.
7.2. Delete the inline policy you created for the Grafana Service role.
7.3. Delete the Grafana service role if no other workspaces are using it.
7.4. Navigate to IAM, then Roles and search for “AWSServiceRoleForAmazonManagedGrafana”.
7.5. Delete the service-linked role if you no longer have any Grafana workspaces in your account. (The service-linked role can only be deleted if you have no remaining Grafana workspaces in your account.)

QuickSight

1. To terminate your account by using the QuickSight UI:

1.1. Choose your profile on the application bar, and then choose Manage QuickSight.
1.2. Use one of the following methods to open the Account termination screen.

1.2.1. Open the Amazon QuickSight account termination screen.
1.2.2. Choose Account settings, then Manage.

1.3. On the Account termination page, confirm that you are viewing the correct QuickSight account by checking the name listed for account name, as shown in the following figure.

1.4. Toggle off Account termination protection is on. Doing this enables the Delete account section.
1.5. Under Type “confirm” to delete this account, enter the word confirmation word shown on your screen.

2. Permissions necessary to terminate a QuickSight account:

2.1. You’re a QuickSight administrator and have an admin role in QuickSight.
2.2. You need permissions to run the following (except if you’re the root admin user (IAM) who added QuickSight).

2.2.1. quicksight:Unsubscribe
2.2.2. ds:UnauthorizeApplication
2.2.3. ds:DeleteDirectory
2.2.4. ds:DescribeDirectories
2.2.5. quicksight:UpdateAccountSettings

2.3. To remove custom namespaces, you need permission to run the following API operations:

2.3.1. quicksight:ListNamespaces
2.3.2. quicksight:DeleteNamespace

2.4. You don’t need extra permissions to delete the default namespace.

Resources from prerequisites

1. Run the CLEANUP steps provided at the end of the notebook.
2. Delete the S3 table bucket after deleting all tables and namespaces within the table bucket. To do this, you can run the following command in the CLI.

2.1. aws s3tables delete-table-bucket --table-bucket-arn <table bucket ARN>

3. Navigate to AWS CloudFormation in the AWS Management Console, locate the stack you created for your setup and choose the Delete button. This removes all the resources, except the security groups.
4. To delete the security groups, go to the Amazon Elastic Compute Cloud (Amazon EC2) console and find the Security Groups section under Network & Security in the left sidebar. For each security group associated with your deleted stack, remove all inbound rules. When you’ve cleared the inbound rules, you can delete the security groups.

Conclusion

In this post, you learned how to unlock business insights with Amazon S3 Tables integration with AWS analytics services and how to pair that with your choice of visualization tools, such as Amazon Managed Grafana and Amazon QuickSight. Through practical examples using RetailCo’s dataset, we showed how organizations can transform tabular data into actionable dashboards and reports without complex data transformations. Adding Amazon Q in QuickSight further accelerates insight discovery through natural language querying and AI-generated visualizations.

To get started with your own implementation, explore the Amazon S3 Tables documentation for detailed setup instructions and best practices. You can also use the sample queries provided in this post to create your first visualizations. To learn more about Amazon S3 Tables, visit the product overview page and AWS News Blog.