AWS Athena创建外部表报错：'EXTERNAL'输入不匹配，求语法排查

错误原因排查 #

你遇到的错误提示AWS Athena - mismatched input 'EXTERNAL'. Expecting: 'MATERIALIZED', 'MULTI', 'OR', 'PROTECTED', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'，本质是建表语句里存在语法错误导致解析器错位，误判了EXTERNAL关键字。

具体错误出现在attributes结构体的execution_type字段定义中：

execution_type: struct < s.string >

这里把结构体键值的分隔符写成了.，正确写法应该是:，也就是struct < s: string >。

修正后的建表语句 #

CREATE EXTERNAL TABLE IF NOT EXISTS exportedfroms3.timtams (
  Item struct < tams_id: struct < s: string >,
  last_updated: struct < s: string >,
  id: struct < s: string >,
  rig: struct < m: struct < summary: struct < s: string >,
                            future_programme: struct < s: string >,
                            technologies: struct < l: array < struct < s: string > > >,
                            owner: struct < s: string >,
                            lead_programme: struct < s: string >,
                            level: struct < s: string >,
                            domain: struct < s: string >,
                            location: struct < s: string >,
                            attributes: struct < m: struct < execution_type: struct < s: string >,
                                                             gitlab_runner: struct < bool: string >,
                                                             schedulable: struct < bool: string >,
                                                             is_safe: struct < bool: string > > >,
                            primary_contact: struct < s: string >,
                            status: struct < s: string >,
                            commissioned: struct < s: string > > >,
  type: struct < s: string > >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'FALSE',
  'dots.in.keys' = 'FALSE',
  'case.insensitive' = 'TRUE',
  'mapping' = 'TRUE'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://dynamodb-athena-test/AWSDynamoDB/01727258263124-ea55e337/data/test/'
TBLPROPERTIES ('classification' = 'json');

额外说明 #

这类错位报错具有迷惑性，看似是关键字不合法，实际是前面的语法错误导致解析器无法正确识别后续语句结构。排查时可重点检查结构体、数组等复杂类型的定义语法，确保键和类型之间用:分隔，而非其他符号。

内容的提问来源于stack exchange，提问作者Master