Specify compression type with Athena

Question

I have S3 data which is has GZIP compression. I'm trying to create a table in Athena using this file, and my CREATE TABLE statement succeeds - but when I query the table all rows are empty.

create external table mydatabase.table1 (
   date date,
   week_begin_date date,
   week_end_date date,
   value float
)
row format delimited fields terminated by ','
stored as inputformat 'org.apache.hadoop.mapred.TextInputFormat'     
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location 's3://my-bucket/some/path/'

How can I insist that Athena read my files as GZIP?

Answer 1

While Athena supports TBLPROPERTIES metadata (we can set properties within a CREATE TABLE, ALTER TABLE to set these properties, and SHOW TBLPROPERTIES to display properties of any table), it does not respect the TBLPROPERTIES ('compressionType'='gzip') option.

There's no apparent way to force compression / decompression algorithm. Athena attempts to identify compression based on file extension. A GZIP file with a .gz suffix will be readable; a GZIP file without that suffix will not.

Similarly, an uncompressed file with a .gz suffix will fail. The reported error is

HIVE_CURSOR_ERROR: incorrect header check

Some investigation revealed the following:

• The only known way to have Athena recognize a file as a GZIP is to name it with a .gz suffix.
• Other similar suffixes that do not work include .gzip, .zip, [^.]gz
• GZIP and uncompressed files can live happily side by side in an Athena table or partition - the compression detection is done at the file level, not at the table level.