Amazon Athena CREATE EXTERNAL TABLE mismatched input 'external' invalidrequestexception

Question

I am trying to create an external table in Amazon Athena. My query is the following:

CREATE EXTERNAL TABLE priceTable (
  WeekDay STRING,
  MonthDay INT,
  price00 FLOAT,
  price01 FLOAT,
  price02 FLOAT,
  price03 FLOAT,
  price04 FLOAT,
  price05 FLOAT,
  price06 FLOAT,
  price07 FLOAT,
  price08 FLOAT,
  price09 FLOAT,
  price10 FLOAT,
  price11 FLOAT,
  price12 FLOAT,
  price13 FLOAT,
  price14 FLOAT,
  price15 FLOAT,
  price16 FLOAT,
  price17 FLOAT,
  price18 FLOAT,
  price19 FLOAT,
  price20 FLOAT,
  price21 FLOAT,
  price22 FLOAT,
  price23 FLOAT,
  )

  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ';'
  LINES TERMINATED BY '\n'
  LOCATION 's3://myquicksighttestbucket/C1_SphdemDD_CANARIAS_20190501_20190531_v2'

Where the file in S3 is just a csv deliminted by semicolons. However, I get the following error:

line 1:8: mismatched input 'external'. expecting: 'or', 'schema', 'table', 'view' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: e524f7e6-39ca-4af7-9e39-f86a4d0a36c8; proxy: null)

Can anybody tell what I am doing wrong? Any help is much appreciated.

For me it didn't like I put `PARTITIONED BY` at the end instead of before `ROW FORMAT` [as per here](https://docs.aws.amazon.com/athena/latest/ug/partitions.html) — citynorman, Aug 14 '21 at 01:23
It seems this is some sort of default error message for any odd error. Misplaced quotes caused the same error. — citynorman, Dec 04 '21 at 18:13

MrMuppet · Accepted Answer · 2021-05-12T09:28:54.940

18

Oooh! I am sorry, the error was the comma after the last field!!

And, also, instead of:

FIELDS TERMINATED BY ';'

I should have used the delimiter's OCT code (073) like this:

FIELDS TERMINATED BY '073'

edited May 12 '21 at 09:28

answered May 12 '21 at 09:16

MrMuppet

547
1
4
12

score 5 · Answer 2 · answered May 20 '22 at 14:20

5

Make sure table name does not have "-", spaces, or any other character not allowed in table names.

answered May 20 '22 at 14:20

Ghaith Matalkah

51
1
1

This was exactly my issue. I had - in my table name. Thanks – node_saini Sep 22 '22 at 12:15
This was my issue, and it was caused by copying an example from the AWS docs that used hyphens in the name! Presumably it was a placeholder that they intended for you to replace. – Stuart Bertram Nov 30 '22 at 10:35

score 2 · Answer 3 · answered Oct 28 '21 at 06:09

2

I had invalid field names which included - chars. A rather easy mistake when copying names like flow-direction directly from flow logs definitions.

answered Oct 28 '21 at 06:09

Federico

1,636
2
22
24

score 2 · Answer 4 · answered Mar 29 '22 at 15:13

I had the same error today, and unlike others, I had a partitioned by clause where I didn't submit the type for the column:

CREATE EXTERNAL TABLE IF NOT EXISTS table_name(
  creationtime string,
  anumber bigint,
  somearray array<struct<...>>,
  somestring string)
  PARTITIONED BY (creation_date string)
                                ^^^^^^ <--- 'string' was missing
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
LOCATION
's3://location/';

Once I added the type, the error vanished and the query was successful.

score 1 · Answer 5 · answered Jun 14 '22 at 13:47

1

Lots of answers here already, but I just wanted to summarize and say it seems like any syntax error in the statement can cause this error.

In my case I had a trailing comma after the last item of my TBLPROPERTIES

answered Jun 14 '22 at 13:47

Jason Capriotti

1,836
2
17
33

score 1 · Answer 6 · answered May 31 '23 at 09:44

Like others have said, this is a common error for a variety of problems. For me, I managed to use a query like

CREATE TABLE IF NOT EXISTS example_table(
  id INT NOT NULL,
  name STRING,
  address STRING
)
;

without the EXTERNAL qualifier which meant I actually returned helpful errors like (in this case)

line 2:10: no viable alternative at input 'CREATE TABLE IF NOT EXISTS example_table(\n id INT NOT'

Athena won't build the table without a target location, so once you get the error No location was specified for table. An S3 location must be specified your query should hopefully work.

score 0 · Answer 7 · answered Jan 21 '23 at 02:51

I got same error,changing column datatype INTEGER to INT resolved this error for me.

https://docs.aws.amazon.com/athena/latest/ug/data-types.html int and integer – Athena uses different expressions for integer depending on the type of query. int – In Data Definition Language (DDL) queries like CREATE TABLE, use the int data type. integer – In DML queries like SELECT * FROM, use the integer data type. integer is represented as a 32-bit signed value in two's complement format, with a minimum value of -231 and a maximum value of 231-1.

Amazon Athena CREATE EXTERNAL TABLE mismatched input 'external' invalidrequestexception

7 Answers7

Linked