In this article, we will walk through the creation of a simple view in Amazon 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 out 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
TRIM(item) AS item,
SUM(retail_amount) as sales,
mytable_week_end as date
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 performs 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.
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:
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.
Edit Custom SQL pop up, simply enter the name of the Amazon Athena view (including the schema name). In the case you enter the name
openbridge_athena.pos_sales_by_day view name.
This will create a generic default name for the table/view and the connection…
Change them to something meaningful like
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 views in Tableau.