AWS Athena Cheat sheet

Author: Ariel Yosef

In AWS Athena the application reads the data from S3 and all you need to do is define the schema and the location the data is stored in s3, i.e create tables.

AWS Athena also saves the results of the queries you make , So you will be asked to define the results bucket before you start working with AWS Athena. 

How to create a table over CSV in AWS Athena (read data from s3 csv)

CREATE EXTERNAL TABLE <table_name>(
  `col1` string, 
  `col2` int, 
  `col3` date (yyyy-mm-dd format), 
  `col4` timestamp (yyyy-mm-dd  hh:mm:ss format),
  `col5` boolean)
  ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ','
  LOCATION
  's3://bucket/folder'

NOTE: ROW FORMAT defines how Athena is going to read the file.

You can also define a SERDE to tell Athena how to parse the data.

You can watch all supported SERDE in Amazon Athena here

How to create a table  over json files – AWS Athena

CREATE EXTERNAL TABLE <table_name>(
  `col1` string, 
  `col2` int, 
  `col3` date (yyyy-mm-dd format), 
  `col4` timestamp (yyyy-mm-dd  hh:mm:ss format),
  `col5` boolean)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
  LOCATION
  's3://bucket/folder'

To delete table

DROP TABLE <table_name>

How to create json with nested values on AWS Athena

CREATE EXTERNAL TABLE <table_name>(
  `col1` struct<car1:string,car2:string,car3:string>, 
  `col2` int, 
  `col3` date (yyyy-mm-dd format), 
  `col4` timestamp (yyyy-mm-dd  hh:mm:ss format),
  `col5` boolean)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
  LOCATION
  's3://bucket/folder'

There are few more things Athena can do. It can transform your files into another hadoop storage format  such as  parquet, avro, and orc. We will use it for our queries, to reduce costs and improve performance.
Before we get into that, we need to understand CTAS.

CTAS – How to create a table from another table 

CREATE TABLE <table_name> as 
Select * from <database.table_name>

Now after we understand that we can use CTAS and apply the transformation and compression queries, we can ran the following example:

CREATE TABLE <table_name>
WITH (
      format = 'PARQUET',
      parquet_compression = 'SNAPPY',
      external_location = 's3://bucket/folder/')
AS SELECT * 
FROM <database.table_name>;

Notice:

  1. Available compressions are: 4mc, snappy, lzo, lz4, bzip2 and gzip
  2. Available storage formants: parquet, avro and orc
  3. PPT to compare the different file formats.
  4. CTAS examples of AWS Athena documentation.

  AVRO PRAQUET ORC
Schema Evlution Most efficient, as the schema is stores as JSON with the file
score: 10/10
Scheman evolution is expensive as it needs to be read and merged across all the parquet files
score:3/10

Schema evolution here is limited to adding new columns and a few cases of column type-widening

score:5/10

Compression less efficient
score:5/10
Best with Snappy
score:7/10
Best with ZLIB
score:5/10
Splitability Partially
score:7/10
Partially
score:7/10
Fully
score:10/10
ROW/Column Oriented Row Row Column
Optimied Processing Engines Kafka Spark Hive, Presto
Read/Write Write Read Read

Insert into

insert into  select * from <database.table_name>

Example for partitioning table done by Athena:

CREATE TABLE 
WITH (
      format = 'PARQUET',
      parquet_compression = 'SNAPPY',
      external_location = 's3://bucket/folder/'
      partitioned_by = ARRAY['col1']['col2']
)
AS SELECT * 
FROM <database.table_name>;

Watch our Post AWS Athena how to work with JSON

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with,

feel free to contact me via LinkedIn: