Amazon Athena: New Release Supports Views Within The AWS Interactive Query Service
Amazon quietly released an Athena update that includes one of the most requested features; views!
A welcome enhancement to AWS Athena is that it now supports views! This has been one of the most request features since Athena launched. Here is the announcement from AWS:
You can now create Views and run queries against those Views. Views simplify how you query data by abstracting away the underlying tables and columns. Views are not materialized and the query that defines the View is run every time the View is referenced. Click here to learn more about Views.”
We have launched a code-free, zero-admin, fully automated data pipeline that automates database, table creation, Parquet file conversion, partitioning and more.
Get started with Amazon Athena for free!
With the new ability to leverage views, this will open new opportunities for teams to get value from their Athena data efforts. One caveat is that tools like Tableau might not recognize the views visually. However, we have confirmed that tools like Tableau and SQL Workbench can query the views even if they don’t list/display them.
When to Use Amazon Athena Views?
- Query a subset of data. For example, you can create a table with a subset of columns from the original table to simplify querying data.
- Combine multiple tables in one query. When you have multiple tables and want to combine them with
UNION ALL, you can create a view with that expression to simplify queries against the combined tables. - Hide the complexity of existing base queries and simplify queries run by users. Base queries often include joins between tables, expressions in the column list, and SQL syntax that make it difficult to understand and debug them. You might create a view that hides the complexity and simplifies queries.
- Experiment with optimization techniques and create optimized queries. For example, if you find a combination of
WHEREconditions,JOINorder, or other expressions that demonstrate the best performance, you can create a view with these clauses and expressions. Applications can then make relatively simple queries against this view. If you later find a better way to optimize the original query, when you recreate the view, all the applications immediately take advantage of the optimized base query. - Hide the underlying table and column names, and minimize maintenance problems if those names change. In that case, you recreate the view using the new names. All queries that use the view rather than the underlying tables keep running with no changes.
Supported Actions for Views in Athena
Athena supports the following actions for views.
- CREATE VIEW: Creates a new view from a specified
SELECTquery. For more information, see Creating Views. The optionalOR REPLACEclause lets you update the existing view by replacing it. - DESCRIBE VIEW: Shows the list of columns for the named view. This allows you to examine the attributes of a complex view.
- DROP VIEW: Deletes an existing view. The optional
IF EXISTSclause suppresses the error if the view does not exist. For more information, see Deleting Views. - SHOW CREATE VIEW: Shows the SQL statement that creates the specified view.
- SHOW VIEWS: Lists the views in the specified database, or in the current database if you omit the database name. Use the optional
LIKEclause with a regular expression to restrict the list of view names. You can also see the list of views in the left panel in the console.
Considerations for Views
- In Athena, you can preview and work with views created in the Athena Console, in the AWS Glue Data Catalog, if you have migrated to using it, or with Presto running on the Amazon EMR cluster connected to the same catalog. You cannot preview or add to Athena views that were created in other ways.
- Athena prevents you from running recursive views and displays an error message in such cases. A recursive view is a view query that references itself.
- Athena detects stale views and displays an error message in such cases. A stale view is a view query that references tables or databases that do not exist.
- You can create and run nested views as long as the query behind the nested view is valid and the tables and databases exist.
- You cannot use views to manage access control on data in Amazon S3. To query a view, you need permissions to access the data stored in Amazon S3.
AWS Athena Automated — 60 Second Setup, Zero Administration And Automatic Optimization
Our zero administration AWS Athena data pipeline service automates Athena for you. It has never been easier to get your data into AWS Athena. 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!
With data in AWS Athena, you can use your favorite tools for data analysis, visualization, reporting, and analysis like Tableau Software, Looker, AWS QuickSight, and many others.
If you wanted to get started with Amazon’s query service AWS Athena but did not have the time or expertise, this is the solution for you!
We have launched a code-free, zero-admin, fully automated data pipeline that automates database, table creation, Parquet file conversion, partitioning, and more.
Get started with AWS Athena for free!
DWant to discuss how to leverage Amazon Athena for 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.
