How to Setup Tableau S3 Connection in 2 Easy Methods

Imagine effortlessly connecting to your vast datasets stored in Amazon S3 and instantly transforming raw data into stunning visualizations. Amazon S3 is a cornerstone of AWS, offering robust storage and retrieval for massive datasets. Its high scalability, security, and performance make it a prime choice for building data pipelines. While S3 excels at storing data, tools like Tableau are essential for extracting meaningful insights.

This blog will guide you through the process of performing Tableau S3 connection, empowering you to explore trends, uncover hidden patterns, and make data-driven decisions with unprecedented speed and ease.

What is Amazon S3?

Amazon S3 is a cloud storage service offered by AWS that allows users to store and retrieve any amount of data at any time from anywhere on the web. Whether you are a business looking for secure file storage or an individual who needs reliable data backup, Amazon S3 does everything.

Key Features of S3

• Versioning: Maintain multiple versions of an object. Suppose you accidentally delete or modify a file; the versioning feature of S3 allows you to retrieve the older version.
• Cost-Efficient:  Flexible pricing allows you to hold huge amounts of data without ever having to break the bank. In addition, you can opt for multiple storage classes and hold it according to your budget.
• High Durability and Availability: S3 offers high durability(up to 99.999999999%) and availability. Your data is automatically replicated across multiple sources to avoid getting lost.
• Security: With built-in encryption options, access control policies, and integration with AWS Identity and Access Management (IAM) for precise security management, your data remains safe in Amazon S3.

What is Tableau?

Tableau is a powerful data visualization and business intelligence tool that helps users explore, analyze, and share data insights. It allows you to connect to various data sources, create interactive dashboards, and generate reports with a user-friendly drag-and-drop interface.

Key Features of Tableau

• Data Connectivity: Connects to various data sources, including databases (relational and NoSQL), spreadsheets, cloud platforms, and significant data sources.
• Drag-and-Drop Interface: An Intuitive interface allows users to easily create visualizations like charts, graphs, maps, and dashboards with simple drag-and-drop actions.
• Interactive Visualizations: Create dynamic and interactive visualizations that respond to user interactions like filtering, highlighting, and zooming.
• Data Blending: Combine data from multiple sources to gain a comprehensive view and perform complex analyses.

How to Connect Tableau and S3?

Connection Prerequisites 

• pre-installed Tableau Server, 
• pre-registered Amazon S3, 
• an active bucket on Amazon S3 space, 
• and a secret access key for the AWS IAM (Identity and Access Management). 

Since you are about to establish a Tableau S3 connection via Athena JDBC driver, make sure that you installed the latest version of 64-bit Java. A minimum of at least JDK 7.0 or Java 1.7 is required to make a proper Tableau S3 connection. 

Step 1: Downloading Athena JDBC Driver

• For downloading Athena JDBC drivers, visit the official website of Amazon Athena. On the website, you can find various Athena JDBC drivers in the form of jar files.
• From those, download the respective JDBC driver that suits your JDK (Java Development Kit) version. After downloading the JDBC jar file, move the respective driver file to the home path or location of Tableau based on your operating system. 

The appropriate JDBC storage locations according to the operating system are given below.

1. For Windows:  C:Program FilesTableauDrivers
2. For macOS: ~/Library/Tableau/Drivers location
3. For Linux: /opt/tableau/tableau_driver/jdbc

Step 2: Setting up Athena

Before setting up and configuring Athena, you have to create a “student” table in CSV format that points to a student-db.csv file in the Amazon S3 bucket.

You should also create a view named “student_view” on top of the student table created before. For creating a student table and student view easily, you can download the respective files from the GitHub repository.

• Now, open the Amazon S3 console and upload the student-db.csv in the bucket you created before.
• In the next step, create a “studentdb” database using the following DDL statement in your Athena Console. 

CREATE DATABASE studentdb;

• After creating the database, execute the below given DDL statement for creating a “student” table inside the “studentdb” database. You should also provide the name of your Amazon S3 bucket inside the location parameter, as shown above. 

CREATE EXTERNAL TABLE student(
  `school` string, 
  `country` string, 
  `sex` string, 
  `age` string, 
  `studytime` int, 
  `failures` int, 
  `preschool` string, 
  `higher` string, 
  `remotestudy` string, 
  `health` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<your_bucket_name>/'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1595149168')

• Now, you have to create a view named “student_view.” On creating the view, you can limit the number of fields or columns required to build dashboards in Tableau. 
• Execute the following command to create a View.

CREATE OR REPLACE VIEW student_view AS 
SELECT
 "school",
 "country",
 "sex",
 "age",
 "health",
 "studytime",
 "failures"
FROM
  student

• After creating the database, table, and views, you should now check whether the entities are created properly. You can check by executing SQL queries in the Athena console. 
• Execute the following command to check whether the student_view is created correctly.

SELECT * FROM “studentdb”.”student_view” limit=10;

• After executing the above command, you will get the following output. 

• From the above output, you can confirm that the view is successfully created since it only displays the columns that are mentioned inside the SQL view query.

Find a plan that’s right for you, Experience transparent pricing that ensures no billing surprises even as you scale. Get a 14 day free trial with 24×7 support. No credit card required. Get a custom quote tailored to your requirements

Step 3: Connecting Tableau S3 via Athena Connector 

Using the Amazon Athena connector, you can connect Tableau S3 data rapidly and effortlessly. After establishing the connection between both, you can seamlessly perform data visualization operations on data present in Amazon S3 with drag-and-drop flexibility. 

1. Open Tableau Desktop and navigate to Connect > More. Search for “Amazon Athena”.
2. In the connection dialog:
   • Enter your Athena endpoint (e.g., <region>.amazonaws.com).
   • Provide the port number.
   • Enter the S3 Staging Directory path from Athena Console’s Settings (Query result location).
   • Fill in your AWS IAM Access Key ID and Secret Access Key.
3. Click “Sign in”.
4. Drag and drop the desired table (e.g., “student_view”) from the data source pane to the Tableau workspace for analysis and visualization.

Step 4: Analyzing Amazon S3 data using Tableau

1. Creating and Sharing a Tableau Dashboard
   • Create worksheets: “country-wise” (based on country) and “age-wise” (using a bar chart).
   • Merge these worksheets into a single dashboard.
   • Publish the dashboard for sharing.
2. Data Refreshing
   • Live Connections: Real-time updates for dynamic data.
   • Data Extracts: Faster performance for complex visualizations.
   • Choose the refresh method based on your needs.
3. Viewing Generated SQL
   • In Tableau Desktop, view the raw SQL queries for your visualizations.
   • In the Athena Console History tab, find the auto-generated SQL queries.

Country-wise

SELECT "student_view"."age" AS "age",
  "student_view"."sex" AS "sex",
  SUM("student_view"."studytime") AS "sum:studytime:ok"
FROM "studentdb"."student_view" "student_view"
GROUP BY "student_view"."age",
  "student_view"."sex"

Age-wise

SELECT "student_view"."country" AS "country",
  SUM("student_view"."studytime") AS "sum:studytime:ok"
FROM "studentdb"."student_view" "student_view"
GROUP BY "student_view"."country"

On following the above-mentioned steps, you successfully established a Tableau S3 connection.

What are the Benefits of Connecting Tableau to S3?

• Centralized Data Access: Access all your data, regardless of source, within Tableau, simplifying analysis and exploration.
• Enhanced Data Agility: Quickly adapt to new data sources and changing business needs by easily connecting Tableau to new S3 buckets.
• Improved Data Governance: Leverage S3’s robust security features (encryption, access controls) to ensure data protection and compliance.
• Cost-Effectiveness: Reduce data storage costs by utilizing the cost-efficient S3 for data warehousing and then connecting Tableau for analysis.
• Streamlined Data Pipelines: Simplify data integration and ETL processes by directly connecting Tableau to S3, reducing manual effort and potential errors.

Conclusion

In this article, you learned about Amazon S3, Tableau, steps to establish a Tableau S3 connection, and how to analyze S3 data using Tableau Desktop.

This article mainly focused on integrating S3 and Tableau using the Athena JDBC connector or Driver.

However, you can also use third-party drivers or subscribe to online data pipelining platforms to seamlessly integrate Amazon S3 with Tableau, like Hevo. Sign up for a free trial and experience seamless data migration today.

Frequently Asked Questions