Amamzon ATHENA: no viable alternative at input 'create external'

0
votes

I am using following DDL query to create table

CREATE EXTERNAL TABLE IF NOT EXISTS poi_test1(  
  'taxonomy_level_1' string,
  'taxonomy_level_2' string,
  'taxonomy_level_3' string,
  'taxonomy_level_4' string,
  'poi_name' string,
  'mw_segment_name' string,
  'latitude' double,
  'longitude' double,
  'city' string,
  'state' string,
  'country_code' string,
  'default_radius' 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://mw.test/jishan1/qa1/poi1';

Error: line 1:8: no viable alternative at input 'create external' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: 5dbd0eb8-6842-45ca-8f60-9f17fd2e4c04)

1
amazon-web-servicesamazon-s3amazon-athena

1 Answers

1
votes

You should remove single quotes around columns or enclose them in backticks if reserved keywords present and in double quotes if column starts with digit. Read this for naming conventions to be used with Athena.

I ran your query as shown below by removing single quotes and it created table successfully

CREATE EXTERNAL TABLE poi_test1(  
  taxonomy_level_1 string,
  taxonomy_level_2 string,
  taxonomy_level_3 string,
  taxonomy_level_4 string,
  poi_name string,
  mw_segment_name string,
  latitude double,
  longitude double,
  city string,
  state string,
  country_code string,
  default_radius 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://mw.test/jishan1/qa1/poi1';