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 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_amount) as sales,
mytable_week_end as date
FROM openbridge_athena.erp_pos_v1
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.
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:
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 views in Tableau.