Athena创建外部表ParseException

Question

嗨，我正在尝试在Athena 中运行以下命令

CREATE EXTERNAL TABLE transport_evaluator_prod(
messageId STRING,
type STRING,
causationId STRING,
correlationId STRING,
traceparent STRING,
`data` struct < 
evaluationOccurred: STRING,
eta struct < distance: INT,
timeToDestination: INT,
eta: STRING,
destination struct < latitude: DOUBLE,
longitude: DOUBLE,
altitude: DOUBLE >,
destinationEventId: STRING,
origin struct < latitude: DOUBLE,
longitude: DOUBLE,
altitude: DOUBLE >, originEventId: STRING,
plannedArrival: STRING,
locationActionReference: STRING,
resourceUrn: STRING,
eventProvider: STRING,
occured: STRING,
position struct < latitude: DOUBLE,
longitude: DOUBLE,
altitude: DOUBLE >, 
equipmentNumber: STRING,
received: STRING > >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'ignore.malformed.json' = 'true'
)
LOCATION 'changed-for-security'
TBLPROPERTIES ('has_encrypted_data' = 'false')

不幸的是，当我尝试运行此程序时，会出现以下错误：失败：缺少ParseException第1:189行：在"附近的"struct"处缺少第1:262行：在"附近的"struct"处缺少第1:363行：在靠近"的"struct'处缺少第1:579行：在附近的"struct"处

有人能帮忙吗？

Answer 1

在阅读使用JSONSerDe|AWS大数据博客的嵌套JSON和映射在Amazon Athena中创建表时，我注意到STRUCT内的任何字段都应该被引用为field_name:type。

这也适用于struct。

因此，这种类型的线路(位于struct内部(：

destination struct < latitude: DOUBLE,

应该是：

destination:struct < latitude: DOUBLE,

因此，这似乎奏效了：

CREATE EXTERNAL TABLE transport_evaluator_prod(
messageId STRING,
type STRING,
causationId STRING,
correlationId STRING,
traceparent STRING,
`data` struct < 
evaluationOccurred: STRING,
eta:struct < distance: INT,
timeToDestination: INT,
eta: STRING,
destination:struct < latitude: DOUBLE,
longitude: DOUBLE,
altitude: DOUBLE >,
destinationEventId: STRING,
origin:struct < latitude: DOUBLE,
longitude: DOUBLE,
altitude: DOUBLE >,
originEventId: STRING,
plannedArrival: STRING,
locationActionReference: STRING,
resourceUrn: STRING,
eventProvider: STRING,
occured: STRING,
position:struct < latitude: DOUBLE,
longitude: DOUBLE,
altitude: DOUBLE >, 
equipmentNumber: STRING,
received: STRING > >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'ignore.malformed.json' = 'true'
)
LOCATION 'changed-for-security'
TBLPROPERTIES ('has_encrypted_data' = 'false')