CODEWITHSUNDEEP
amazon-web-servicesamazon-athena
Kelly Norton
Kelly Norton

Reputation: 497

Amazon Athena table creation fails with "no viable alternative at input 'create external'"

This is my first attempt to craft my own table in Athena so please be gentle :) I have several other tables based off AWS examples running in this database with no troubles, so I'm confident the DB is setup correctly.

Also, I'm sure my regex is atrocious, please try to ignore it for now!

I have some vpc flow logs stored on S3, the format of the log files is:

2 123456654321 eni-ae343be4 100.75.233.178 172.31.26.162 49178 80 6 6 270 1488872163 1488872180 ACCEPT OK
2 123456654321 eni-ae343be4 100.75.233.178 172.31.26.162 64013 80 6 2 84 1488872163 1488872180 ACCEPT OK
2 123456654321 eni-ae343be4 206.97.36.202 172.31.26.162 33164 445 6 2 96 1488872163 1488872180 REJECT OK

I have based my table on the AWS example published here. The query I have created is:

CREATE EXTERNAL TABLE IF NOT EXISTS flow_logs_raw_native (
         version int,
         account-id bigint,
         interface-id string,
         srcaddr string,
         dstaddr string,
         srcport int,
         dstport int,
         protocol int,
         packets int,
         bytes int,
         Tstart bigint,
         Tend bigint,
         action string,
         log-status string 
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)' ) LOCATION 's3://vpcflowlogstos3-bucket-1ns5peqxgc31n/flowlogs/';

Every time I run the query I receive an error:

line 1:8: no viable alternative at input 'create external' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: b0cfde99-031c-11e7-b4de-4139fae95147)

I've been looking at this for a couple of hours now, trying to find a spelling mistake, or missing element, but I'm stuck! Can anyone see what's wrong here?

Thank you,

Kelly.

Upvotes: 4

Views: 10729

Answers (1)

Kelly Norton
Kelly Norton

Reputation: 497

Oh, I got it, I GOT IT!

I was using hyphens for some of the field names, this is NOT supported by Athena. Changed them to underscore and away it went!

Upvotes: 6

Related Questions