Amazon Athena - User Guide

1 Amazon AthenaUser GuideAmazon Athena : user GuideCopyright 2018 Amazon Web Services, Inc. and/or its affiliates. All rights Athena user GuideAmazon's trademarks and trade dress may not be used in connection with any product or service that is not Amazon 's, in any mannerthat is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon . All other trademarks notowned by Amazon are the property of their respective owners, who may or may not be affiliated with, connected to, or sponsored Athena user GuideTable of ContentsWhat is Amazon Athena ?

2 1 When should I use Athena ?.. 1 Accessing Athena .. 1 Understanding Tables, Databases, and the Data Catalog .. 2 Release Notes .. 4 September 6, 5 August 23, 5 August 16, 6 August 7, 6 June 5, 6 Support for Views .. 7 Improvements and Updates to Error Messages .. 7 Bug Fixes .. 7 May 17, 2018 .. 7 April 19, 8 April 6, 8 March 15, 2018 .. 8 February 2, 2018 .. 8 January 19, 2018 .. 9 November 13, 2017 .. 9 November 1, 2017 .. 9 October 19, 2017 .. 10 October 3, 2017 .. 10 September 25, 10 August 14, 10 August 4, 10 June 22, 10 June 8, 11 May 19, 2017.

3 11 Improvements .. 11 Bug Fixes .. 12 April 4, 12 Features .. 12 Improvements .. 12 Bug Fixes .. 12 March 24, 2017 .. 13 Features .. 13 Improvements .. 13 Bug Fixes .. 13 February 20, 2017 .. 13 Features .. 13 Improvements .. 15 Setting Up .. 16 Sign Up for AWS .. 16To create an AWS account .. 16 Create an IAM user .. 16To create a group for administrators .. 16To create an IAM user for yourself, add the user to the administrators group, and create apassword for the user .. 17 Attach Managed Policies for Using Athena .

4 17 Getting Started .. 18 Prerequisites .. 18 Step 1: Create a Database .. 18 Step 2: Create a Table .. 19iiiAmazon Athena user GuideStep 3: Query Data .. 20 Accessing Amazon Athena .. 22 Using the 22 Using the 22 Using the 22 Integration with AWS Glue .. 23 Upgrading to the AWS Glue Data Catalog Step-by-Step .. 24 Step 1 - Allow a user to Perform the Upgrade .. 24 Step 2 - Update Customer-Managed/Inline Policies Associated with Athena Users .. 24 Step 3 - Choose Upgrade in the Athena 25 FAQ: Upgrading to the AWS Glue Data Catalog.

5 26 Why should I upgrade to the AWS Glue Data Catalog? .. 26 Are there separate charges for AWS Glue? .. 27 Upgrade process FAQ .. 27 Best Practices When Using Athena with AWS Glue .. 28 Database, Table, and Column Names .. 29 Using AWS Glue Crawlers .. 29 Working with CSV Files .. 33 Using AWS Glue Jobs for ETL with Athena .. 35 Connecting to Amazon Athena with ODBC and JDBC Drivers .. 38 Using Athena with the JDBC Driver .. 38 Links for Downloading the JDBC Driver .. 38 JDBC Driver Release Notes, License Agreement, and Notices.

6 38 Migration from Previous Version of the JDBC Driver .. 39 Connecting to Amazon Athena with ODBC .. 39 Amazon Athena ODBC Driver License Agreement .. 39 Windows .. 39 OSX .. 40 ODBC Driver Documentation .. 40 Migration from the Previous Version of the ODBC Driver .. 40 Previous Versions of the ODBC Driver .. 40 Security .. 41 Setting user and Amazon S3 Bucket Permissions .. 41 IAM Policies for user Access .. 41 AmazonAthenaFullAccess Managed Policy .. 42 AWSQ uicksightAthenaAccess Managed Policy .. 43 Access through JDBC and ODBC Connections.

7 44 Access to Amazon S3 .. 45 Access to Encrypted Metadata in the AWS Glue Data Catalog .. 45 Cross-account Access .. 45 Configuring Encryption Options .. 46 Amazon S3 Encryption Options Supported in Athena .. 46 Encrypting Query Results Stored in Amazon S3 .. 47 Permissions to Encrypted Data in Amazon S3 .. 48 Permissions to Encrypted Metadata in the AWS Glue Data Catalog .. 49 Creating Tables Based on Encrypted Datasets in Amazon S3 .. 49 Working with Source Data .. 51 Tables and Databases Creation Process in Athena .

8 51 Requirements for Tables in Athena and Data in Amazon S3 .. 52 Functions Supported .. 52 CREATE TABLE AS Type Statements Are Not Supported .. 52 Transactional Data Transformations Are Not Supported .. 52 Operations That Change Table States Are ACID .. 52 All Tables Are EXTERNAL .. 53 UDF and UDAF Are Not Supported .. 53To create a table using the AWS Glue Data Catalog .. 53ivAmazon Athena user GuideTo create a table using the wizard .. 53To create a database using Hive DDL .. 53To create a table using Hive DDL.

9 54 Names for Tables, Databases, and Columns .. 55 Table names and table column names in Athena must be lowercase .. 55 Athena table, database, and column names allow only underscore special characters .. 55 Names that begin with an underscore .. 55 Table names that include numbers .. 55 Reserved Keywords .. 56 List of Reserved Keywords in DDL Statements .. 56 List of Reserved Keywords in SQL SELECT Statements .. 56 Examples of Queries with Reserved Words .. 57 Table Location in Amazon S3 .. 57 Partitioning Data.

10 58 Scenario 1: Data already partitioned and stored on S3 in hive format .. 58 Scenario 2: Data is not partitioned .. 60 Converting to Columnar Formats .. 61 Overview .. 62 Before you begin .. 18 Example: Converting data to Parquet using an EMR cluster .. 64 Querying Data in Amazon Athena Tables .. 66 Query Results .. 66 Saving Query Results .. 67 Viewing Query History .. 67 Viewing Query History .. 68 When to Use Views?.. 68 Supported Actions for Views in Athena .. 69 Working with Views in the Console .. 69 Creating Views.