Amazon Athena - Column cannot be resolved on basic SQL WHERE query

Question

I am currently evaluating Amazon Athena and Amazon S3. I have created a database (testdb) with one table (awsevaluationtable). The table has two columns, x (bigint) and y (bigint).

When I run:

SELECT * 
FROM testdb."awsevaluationtable"

I get all of the test data:

However, when I try a basic WHERE query:

SELECT * 
FROM testdb."awsevaluationtable" 
WHERE x > 5

I get:

SYNTAX_ERROR: line 3:7: Column 'x' cannot be resolved

I have tried all sorts of variations:

SELECT * FROM testdb.awsevaluationtable WHERE x > 5
SELECT * FROM awsevaluationtable WHERE x > 5
SELECT * FROM testdb."awsevaluationtable" WHERE X > 5
SELECT * FROM testdb."awsevaluationtable" WHERE testdb."awsevaluationtable".x > 5
SELECT * FROM testdb.awsevaluationtable WHERE awsevaluationtable.x > 5

I have also confirmed that the x column exists with:

SHOW COLUMNS IN sctawsevaluation

This seems like an extremely simple query yet I can't figure out what is wrong. I don't see anything obvious in the documentation. Any suggestions would be appreciated.

Answer 1

I have edited my response to this issue based on my current findings and my contact with both the AWS Glue and Athena support teams.

We were having the same issue - an inability to query on the first column in our CSV files. The problem comes down to the encoding of the CSV file. In short, AWS Glue and Athena currently do not support CSV's encoded in UTF-8-BOM. If you open up a CSV encoded with a Byte Order Mark (BOM) in Excel or Notepad++, it looks like any comma-delimited text file. However, opening it up in a Hex editor reveals the underlying issue. There are a bunch of special characters at the start of the file:  i.e. the BOM.

When a UTF-8-BOM CSV file is processed in AWS Glue, it retains these special characters, and associates then with the first column name. When you try and query on the first column within Athena, you will generate an error.

There are ways around this on AWS:

• In AWS Glue, edit the table schema and delete the first column, then reinsert it back with the proper column name, OR

• In AWS Athena, execute the SHOW CREATE TABLE DDL to script out the problematic table, remove the special character in the generated script, then run the script to create a new table which you can query on.

To make your life simple, just make sure your CSV's are encoded as UTF-8.