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 ? .. 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.

2 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 .. 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 .. 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.

3 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 .. 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 .. 38 Migration from Previous Version of the JDBC Driver .. 39 Connecting to Amazon Athena with ODBC .. 39 Amazon Athena ODBC Driver License Agreement.

4 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 .. 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 .. 51 Requirements for Tables in Athena and Data in Amazon S3.

5 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 .. 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.

6 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 .. 70 Examples of 71 Updating 72 Deleting 72 Querying Arrays .. 72 Creating Arrays .. 72 Concatenating Arrays .. 74 Converting Array Data Types .. 75 Finding 75 Accessing Array Elements .. 75 Flattening Nested Arrays .. 76 Creating Arrays from Subqueries .. 79 Filtering Arrays .. 79 Sorting Arrays .. 80 Using Aggregation Functions with Arrays .. 80 Converting Arrays to Strings .. 81 Querying Arrays with Complex Types and Nested Structures.

7 81 Creating a 82 Changing Field Names in Arrays Using 82 Filtering Arrays Using the . 83 Filtering Arrays with Nested Values .. 83 Filtering Arrays Using 84 Finding Keywords in Arrays Using 85 Querying Arrays with Maps .. 72 Querying JSON .. 88 Best Practices for Reading JSON Data .. 88 Extracting Data from JSON .. 89vAmazon Athena user GuideSearching for Values .. 91 Obtaining Length and Size of JSON Arrays .. 93 Querying Geospatial Data .. 95 What is a Geospatial Query? .. 95 Input Data Formats and Geometry Data Types .. 95 Input Data Formats .. 95 Geometry Data Types .. 96 List of Supported Geospatial Functions .. 96 Before You Begin .. 96 Constructor Functions .. 97 Geospatial Relationship Functions .. 98 Operation Functions .. 100 Accessor Functions .. 101 Examples: Geospatial 104 Querying AWS Service Logs .. 106 Querying AWS CloudTrail Logs .. 106 Understanding CloudTrail Logs and Athena Tables .. 107 Creating a Table for CloudTrail Logs in the CloudTrail Console.

8 107 Manually Creating the Table for CloudTrail Logs in Athena .. 108 Tips for Querying CloudTrail Logs .. 109 Querying Amazon CloudFront Logs .. 110 Creating the Table for CloudFront Logs .. 110 Example Query for CloudFront logs .. 111 Querying Classic Load Balancer Logs .. 111 Creating the Table for Elastic Load Balancing Logs .. 111 Example Queries for Elastic Load Balancing 112 Querying Application Load Balancer Logs .. 112 Creating the Table for ALB Logs .. 113 Example Queries for ALB 114 Querying Amazon VPC Flow Logs .. 114 Creating the Table for VPC Flow Logs .. 114 Example Queries for Amazon VPC Flow Logs .. 115 Handling Schema 117 Summary: Updates and Data Formats in Athena .. 117 Index Access in ORC and Parquet .. 118 ORC: Read by Index .. 119 Parquet: Read by Name .. 119 Types of Updates .. 120 Adding Columns at the Beginning or in the Middle of the Table .. 121 Adding Columns at the End of the Table .. 121 Removing Columns .. 122 Renaming Columns.

9 122 Reordering Columns .. 123 Changing a Column's Data Type .. 124 Updates in Tables with Partitions .. 125 Avoiding Schema Mismatch Errors for Tables with Partitions .. 125 Monitoring Logs and Troubleshooting .. 127 Logging Amazon Athena API Calls with AWS CloudTrail .. 127 Athena Information in CloudTrail .. 127 Understanding Athena Log File 128 Troubleshooting .. 130 SerDe Reference .. 131 Using a 131To Use a SerDe in Queries .. 131 Supported SerDes and Data Formats .. 132 Avro SerDe .. 133 RegexSerDe for Processing Apache Web Server Logs .. 135viAmazon Athena user GuideCloudTrail SerDe .. 136 OpenCSVSerDe for Processing CSV .. 138 Grok SerDe .. 140 JSON SerDe Libraries .. 143 LazySimpleSerDe for CSV, TSV, and Custom-Delimited Files .. 145 ORC SerDe .. 150 Parquet SerDe .. 153 Compression Formats .. 155 DDL and SQL Reference .. 157 Data Types .. 157 List of Supported Data Types in Athena .. 157 DDL 158 ALTER DATABASE SET DBPROPERTIES.

10 159 ALTER TABLE ADD PARTITION .. 159 ALTER TABLE DROP PARTITION .. 160 ALTER TABLE RENAME PARTITION .. 161 ALTER TABLE SET LOCATION .. 161 ALTER TABLE SET TBLPROPERTIES .. 162 CREATE DATABASE .. 162 CREATE TABLE .. 163 CREATE VIEW .. 166 DESCRIBE TABLE .. 167 DESCRIBE VIEW .. 167 DROP DATABASE .. 168 DROP TABLE .. 168 DROP VIEW .. 169 MSCK REPAIR TABLE .. 169 SHOW COLUMNS .. 169 SHOW CREATE TABLE .. 170 SHOW CREATE VIEW .. 170 SHOW DATABASES .. 170 SHOW PARTITIONS .. 171 SHOW TABLES .. 171 SHOW TBLPROPERTIES .. 172 SHOW VIEWS .. 172 SQL Queries, Functions, and Operators .. 173 SELECT .. 173 Unsupported DDL .. 178 Code Samples, Service Limits, and Previous JDBC Driver .. 179 Code 179 Create a Client to Access Athena .. 179 Start Query Execution .. 180 Stop Query Execution .. 183 List Query Executions .. 184 Create a Named Query .. 185 Delete a Named Query .. 185 List Named 186 Using the Previous Version of the JDBC Driver .. 187 Using the Previous Version of the JDBC Driver.