Learn how to leverage views with serverless Amazon Athena and Tableau

Thomas Spicer

·

Follow

Published in

Openbridge

·

5 min read

·

Jul 12, 2018

78

Listen

Share

Amazon Athena now supports the creation of views which is a very powerful tool to manipulate data prior to use in data visualization tools like Tableau. This a welcome enhancement for teams that have built serverless analytics stacks with Athena, S3, and Tableau!

In this post, we will walk through the creation of a simple view in AWS Athena and then describe a method of connecting to that view in Tableau.

Step 1: Create Views In Amazon Athena

First, let’s create a simple view using data from one of our Amazon Athena tables using your favorite SQL tools like SQL Workbench, TeamSQL or any others you are comfortable with, including the Amazon UI:

CREATE VIEW openbridge_athena.pos_sales_by_day_view AS
SELECT 
TRIM(item) AS item, 
SUM(retail_sales) as sales,
mytable_week_end as date
FROM openbridge_athena.my_data
GROUP BY mytable_week_end, item;

In our example, the simple view we are creating aggregates sales data (retail_amount) by date and item and also perform a TRIM function on one of the fields (item) to clean up any leading and trailing spaces which have will be problematic for joins in Tableau. When you run your SQL a view named openbridge_athena.pos_sales_by_day_view has been created. Be sure to include the schema name when referencing the view name and the table name (e.g. <schema_name>.<table/view_name>). In this case openbridge_athena is the schema name and pos_sales_by_day_view is the table name.

Step 2: Using AWS Athena Views In Tableau

Now let’s use that view in one of our Tableau data visualizations…

First, we create a new data source within the Tableau workbook by selecting New Data Source from the Data menu.

Select Amazon Athena from the list of server types, enter the connection details and click Sign In. You should have credentials and location info provided by your AWS admin:

• Tableau connection for the Athena server: athena.us-east-1.amazonaws.com
• S3 staging directory: s3://yourbucketname/results/

Once you select the appropriate Catalog and Database from the respective drop-down menus, you will see the list of Athena tables available for use in Tableau.

But wait, where is the view named pos_sales_by_day_view that we just created!?

Step 3: Initialize Your Amazon Athena View In Tableau

Tableau does not currently display Amazon Athena views in the tables list. Fear not, the view is available with a simple workaround. What is this workaround? Rather than dragging the desired view onto the workspace, drag the New Custom SQL box instead.

In the Edit Custom SQL pop up, simply enter the name of the Amazon Athena view (including the schema name). In this case, you enter the name openbridge_athena.pos_sales_by_day view name.

Click OK.

This will create a generic default name for the table/view and the connection…

Change them to something meaningful like pos_sales_by_day_view…

Now when you open a new or existing sheet in Tableau, you will see the pos_sales_by_day_view in the Data sidebar and if you select it, you will see the expected fields available for use in your Tableau visualization…

Huzzah! You can now leverage Amazon Athena's views in Tableau.

Looking To Get Started With Amazon Athena?

Our zero administration AWS Athena data pipeline service automates Athena for you. It has never been easier to get your data into AWS Athena for use with Tableau. Our service optimizes and automates the configuration, processing, and loading of data to AWS Athena unlocking how users can return query results. With our new zero administration, AWS Athena service you simply push data from supported data sources and our service will automatically load it into your AWS Athena database.

With fully-managed Amazon Athena in place, you can leverage our rich catalog of social media, advertising, support, e-commerce, analytics, and other marketing technology categories. Send data to Athena from 600+ data sources like Google Analytics 360, DoubleClick, Instagram, YouTube, Adobe Analytics, Facebook, Salesforce, Marketo, Zendesk, HubSpot, and many more and start querying!

We have launched a code-free, zero-admin, fully automated data pipeline that automates database, table creation, Parquet file conversion, Snappy compression, partitioning, and more.

Get started with Amazon Athena for free!

DWant to discuss how to use Tableau and Athena within your organization? Need a platform and team of experts to kickstart your data and analytic efforts? We can help! Getting traction adopting new technologies, especially if it means your team is working in different and unfamiliar ways, can be a roadblock for success. This is especially true in a self-service only world. If you want to discuss a proof-of-concept, pilot, project, or any other effort, the Openbridge platform and team of data experts are ready to help.

Reach out to us at hello@openbridge.com. Prefer to talk to someone? Set up a call with our team of data experts.

Visit us at www.openbridge.com to learn how we are helping other companies with their data efforts.

References