CODEWITHSUNDEEP
amazon-web-servicesamazon-athena
jari4107
jari4107

Reputation: 1

The following query returns line 1:8: mismatched input 'EXTERNAL'. Expecting: 'OR', 'SCHEMA', 'TABLE', 'VIEW'

The following query returns:

line 1:8: mismatched input 'EXTERNAL'. Expecting: 'OR', 'SCHEMA', 'TABLE', 'VIEW'

CREATE EXTERNAL TABLE IF NOT EXISTS adult_data_clean(
    age bigint,
    workclass string,
    education string,
    relationship string,
    occupation string,
    country string,
    income_cat string
)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
STORED AS OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 
    's3://census-income-example/clean.data'
TBLPROPERTIES (
    'classification'='csv'
    'skip.header.line.count'='1')

Upvotes: 0

Views: 6246

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269360

There are two errors:

  • The TBLPROPERTIES is missing a comma between the parameters
  • OUTPUTFORMAT should not have STORED AS before it (since it simply continues from INPUTFORMAT):
CREATE EXTERNAL TABLE IF NOT EXISTS adult_data_clean(
    age bigint,
    workclass string,
    education string,
    relationship string,
    occupation string,
    country string,
    income_cat string
)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 
    's3://census-income-example/clean.data'
TBLPROPERTIES (
    'classification'='csv',
    'skip.header.line.count'='1')

Yes, those error messages were confusing and misleading.

Upvotes: 2

Related Questions