Amazon Athena: no viable alternative at input

Question

While creating a table in Athena; it gives me following exception:

no viable alternative at input

In my case, the table name couldn't start from the digit, it needs to be a letter. — kenorb, May 21 '19 at 14:55
Athena is such a pain; I wish AWS would put some more effort into it. Really hard to use but people still do because it's cheap. — wordsforthewise, Sep 26 '22 at 15:38

score 58 · Accepted Answer · answered Dec 01 '16 at 13:35

58

hyphens are not allowed in table name.. ( though wizard allows it ) .. Just remove hyphen and it works like a charm

answered Dec 01 '16 at 13:35

Deepak Singhal

10,568
11
59
98

2

In my case it was because I started the table's name with a number: `2017_04_03_some_data`. I changed to `some_data_20170403` and it worked. – Jonathan Morales Vélez May 02 '17 at 15:38
1

I was getting the same error when trying to create a table with column names with hyphens in it ```'-'``` – campeterson Aug 31 '17 at 22:46
1

in my case it was from having the COMMENT entry in the DDL. Worked after removing it. – tedder42 Apr 10 '18 at 00:48
3

In my case `)` was missing at the end. :) – ChikuMiku Apr 23 '18 at 18:29
Thanks, you saved me probably 30 minutes of my life. – Pawel Aug 23 '18 at 13:51
2

The database can't have hyphens in it either. – Alex Harvey Aug 24 '18 at 03:40
1

Choosing this as the answer is misleading. @esalgado's answer should be the selected answer. – Robert Penridge Dec 20 '18 at 15:57
this is overly specific to a rather generic error that can be caused by any syntax error in the query. @deepak-singhal, you should update for the general case (see other responses below). – ives Feb 16 '20 at 18:23

score 50 · Answer 2 · edited Aug 18 '20 at 09:14

50

Unfortunately, at the moment the syntax validation error messages are not very descriptive in Athena, this error may mean "almost" any possible syntax errors on the create table statement.

Although this is annoying at the moment you will need to check if the syntax follows the Create table documentation

Some examples are:

Backticks not in place (as already pointed out)
Missing/extra commas (remember that the last column doesn't need the comma after column definition
Missing spaces
More ..

edited Aug 18 '20 at 09:14

rahulroy9202

2,730
3
32
45

answered Mar 15 '17 at 13:22

esalgado

741
6
4

My "More .." was an extra `>`. – CorayThan Jun 06 '17 at 21:30
My DDL was missing a comma. Fortunately, the error gives out the line number to look at in the beginning. – Nirmal Dec 13 '17 at 06:19
I had provided a space in the attribute name by mistake. Basically the error thrown indicates anything wrong with the syntax of the query is how I see it. – Divyesh Kalbhor Sep 10 '18 at 08:10
Thanks! By the way, create table documentation is moved to https://docs.aws.amazon.com/athena/latest/ug/create-table.html – burkay Oct 29 '19 at 16:34
My "More.." was non breaking space \xa0 instead of regular space. @Nirmal the line number mentioned in the error is misleading! – shantanuo Aug 18 '20 at 06:52
- no comma after the last col_name data_type combination – 00schneider Feb 23 '21 at 09:02
My "More.." was the missing backticks from database name – Panagiotis Simakis Mar 16 '21 at 15:59
For me, I had to put backticks around the DB/table names because they had underscores. Double quotes did not work even though they do for querying (not creating) and inserting. Something like \`db_name\`.\`table_name\`. – wordsforthewise Sep 26 '22 at 15:36

score 11 · Answer 3 · answered Jun 18 '19 at 08:14

This error generally occurs when the syntax of DDL has some silly errors.There are several answers that explain different errors based on there state.The simple solution to this problem is to patiently look into DDL and verify following points line by line:-

Check for missing commas
Unbalanced `(backtick operator)
Incompatible datatype not supported by HIVE(HIVE DATA TYPES REFERENCE)
Unbalanced comma
Hypen in table name

score 4 · Answer 4 · answered Jan 11 '19 at 16:19

In my case, it was because of a trailing comma after the last column in the table. For example:

CREATE EXTERNAL TABLE IF NOT EXISTS my_table (
  one STRING,
  two STRING,
) LOCATION 's3://my-bucket/some/path';

After I removed the comma at the end of two STRING, it worked fine.

score 4 · Answer 5 · answered Oct 02 '19 at 23:10

My case: it was an external table and the location had a typo (hence didn't exist)

Couple of tips:

Click the "Format query" button so you can spot errors easily
Use the example at the bottom of the documentation - it works - and modify it with your parameters: https://docs.aws.amazon.com/athena/latest/ug/create-table.html

score 3 · Answer 6 · answered Feb 20 '20 at 20:18

3

Slashes. Mine was slashes. I had the DDL from Athena, saved as a python string.

WITH SERDEPROPERTIES ( 
  'escapeChar'='\\', 
  'quoteChar'='\"',
  'separatorChar'=',')

was changed to

WITH SERDEPROPERTIES ( 
  'escapeChar'='\', 
  'quoteChar'='"',
  'separatorChar'=',')

And everything fell apart.

Had to make it:

WITH SERDEPROPERTIES (
  'escapeChar'='\\\\', 
  'quoteChar'='\\\"',
  'separatorChar'=',')

answered Feb 20 '20 at 20:18

Jason Nichols

3,739
3
29
49

Please further clarify here: https://economics.stackexchange.com/a/6354/4483 – BCLC Aug 18 '20 at 06:33

Siva Budati · Answer 7 · 2018-11-28T18:46:10.027

2

In my case, it was an extra comma in PARTITIONED BY section,

edited Nov 28 '18 at 18:46

answered Nov 17 '18 at 03:59

Siva Budati

31
6

score 1 · Answer 8 · answered Dec 06 '18 at 10:25

1

In my case, I was missing the singlequotes for the S3 URL

answered Dec 06 '18 at 10:25

Akshay Kalbhor

151
1
5

score 1 · Answer 9 · answered Mar 19 '19 at 15:09

1

In my case, it was that one of the table column names was enclosed in single quotes, as per the AWS documentation :( ('bucket')

answered Mar 19 '19 at 15:09

Phil

909
1
10
17

drapkin11 · Answer 10 · 2020-08-13T00:32:55.430

As other users have noted, the standard syntax validation error message that Athena provides is not particularly helpful. Thoroughly checking the required DDL syntax (see HIVE data types reference) that other users have mentioned can be pretty tedious since it is fairly extensive.

So, an additional troubleshooting trick is to let AWS's own data parsing engine (AWS Glue) give you a hint about where your DDL may be off. The idea here is to let AWS Glue parse the data using its own internal rules and then show you where you may have made your mistake.

Specifically, here are the steps that worked for me to troubleshoot my DDL statement, which was giving me lots of trouble:

create a data crawler in AWS Glue; AWS and lots of other places go through the very detailed steps this requires so I won't repeat it here
point the crawler to the same data that you wanted (but failed) to upload into Athena
set the crawler output to a table (in an Athena database you've already created)
run the crawler and wait for the table with populated data to be created
find the newly-created table in the Athena Query Editor tab, click on the three vertical dots (...), and select "Generate Create Table DLL":
this will make Athena create the DLL for this table that is guaranteed to be valid (since the table was already created using that DLL)
take a look at this DLL and see if/where/how it differs from the DLL that you originally wrote. Naturally, this automatically-generated DLL will not have the exact choices for the data types that you may find useful, but at least you will know that it is 100% valid
finally, update your DLL based on this new Glue/Athena-generated-DLL, adjusting the column/field names and data types for your particular use case

score 1 · Answer 11 · answered Mar 17 '21 at 21:18

1

After searching and following all the good answers here. My issue was that working in Node.js i needed to remove the optional ESCAPED BY '\' used in the Row settings to get my query to work. Hope this helps others.

answered Mar 17 '21 at 21:18

Meccles

11
3

score 0 · Answer 12 · answered Mar 31 '20 at 17:45

Something that wasn't obvious for me the first time I used the UI is that if you get an error in the create table 'wizard', you can then cancel and there should be the query used that failed written in a new query window, for you to edit and fix.

My database had a hypen, so I added backticks in the query and rerun it.

score 0 · Answer 13 · answered Aug 12 '20 at 20:58

This happened to me due to having comments in the query.

I realized this was a possibility when I tried the "Format Query" button and it turned the entire thing into almost 1 line, mostly commented out. My guess is that the query parser runs this formatter before sending the query to Athena.

Removed the comments, ran the query, and an angel got its wings!

Amazon Athena: no viable alternative at input

13 Answers13

Linked