创建Athena表查询CloudTrail日志时遇HIVE_CURSOR_ERROR求助

我参考AWS官方文档创建了用于查询CloudTrail日志的Athena表，表DDL如下：

CREATE EXTERNAL TABLE `cloudtrail_logs_pp`(
  `eventversion` string COMMENT 'from deserializer', 
  `useridentity` struct<type:string,principalid:string,arn:string,accountid:string,invokedby:string,accesskeyid:string,username:string,sessioncontext:struct<attributes:struct<mfaauthenticated:string,creationdate:string>,sessionissuer:struct<type:string,principalid:string,arn:string,accountid:string,username:string>,ec2roledelivery:string,webidfederationdata:map<string,string>>> COMMENT 'from deserializer', 
  `eventtime` string COMMENT 'from deserializer', 
  `eventsource` string COMMENT 'from deserializer', 
  `eventname` string COMMENT 'from deserializer', 
  `awsregion` string COMMENT 'from deserializer', 
  `sourceipaddress` string COMMENT 'from deserializer', 
  `useragent` string COMMENT 'from deserializer', 
  `errorcode` string COMMENT 'from deserializer', 
  `errormessage` string COMMENT 'from deserializer', 
  `requestparameters` string COMMENT 'from deserializer', 
  `responseelements` string COMMENT 'from deserializer', 
  `additionaleventdata` string COMMENT 'from deserializer', 
  `requestid` string COMMENT 'from deserializer', 
  `eventid` string COMMENT 'from deserializer', 
  `readonly` string COMMENT 'from deserializer', 
  `resources` array<struct<arn:string,accountid:string,type:string>> COMMENT 'from deserializer', 
  `eventtype` string COMMENT 'from deserializer', 
  `apiversion` string COMMENT 'from deserializer', 
  `recipientaccountid` string COMMENT 'from deserializer', 
  `serviceeventdetails` struct<eventrequestdetails:struct<dashboardid:string>,eventresponsedetails:struct<dashboarddetails:struct<dashboardname:string,dashboardid:string,analysisidlist:string,datasetidlist:string>>> COMMENT 'from deserializer', 
  `sharedeventid` string COMMENT 'from deserializer', 
  `vpcendpointid` string COMMENT 'from deserializer', 
  `tlsdetails` struct<tlsversion:string,ciphersuite:string,clientprovidedhostheader:string> COMMENT 'from deserializer')
PARTITIONED BY ( 
  `timestamp` string)
ROW FORMAT SERDE 
  'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS INPUTFORMAT 
  'com.amazon.emr.cloudtrail.CloudTrailInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://xxxxxxxxxxxx-cloudtrail-logs/AWSLogs/xxxxxxxxxxxx/CloudTrail'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2021/01/01,NOW', 
  'projection.timestamp.type'='date', 
  'storage.location.template'='s3://xxxxxxxxxxxx-cloudtrail-logs/AWSLogs/xxxxxxxxxxxx/CloudTrail/yy-yyyy-y/${timestamp}', 
  'transient_lastDdlTime'='1668349800')

执行查询时触发错误：

HIVE_CURSOR_ERROR: java.io.IOException: Start of Object expected

技术建议 #

• 修正存储路径模板：你的storage.location.template中包含无效的yy-yyyy-y路径段，不符合CloudTrail默认的CloudTrail/<region>/<year>/<month>/<day>路径结构。这会导致Athena读取到错误路径下的非日志文件，引发JSON解析失败。调整为匹配实际S3存储结构的模板，例如：

  'storage.location.template'='s3://xxxxxxxxxxxx-cloudtrail-logs/AWSLogs/xxxxxxxxxxxx/CloudTrail/${region}/${timestamp}'
  

  同时需添加region的投影配置（若日志跨区域存储），或直接写死区域路径（单区域场景）。

• 替换为CloudTrail专用SerDe：当前使用的org.apache.hive.hcatalog.data.JsonSerDe无法兼容CloudTrail日志的特殊嵌套格式，建议替换为AWS官方推荐的com.amazon.emr.hive.serde.CloudTrailSerDe，搭配原有的CloudTrailInputFormat使用，确保日志结构正确解析。

• 验证S3日志文件完整性：检查S3目标路径下是否存在损坏、空值或非JSON格式的文件。可通过Athena查询SELECT $path FROM cloudtrail_logs_pp LIMIT 10定位可疑文件，手动下载验证内容是否符合CloudTrail的单行JSON格式要求。

• 对齐分区格式与实际路径：确认projection.timestamp.format的yyyy/MM/dd与S3路径中的日期段格式完全一致，同时检查LOCATION路径是否正确指向包含所有分区子目录的CloudTrail日志根目录。

• 刷新表分区：修改表结构或路径后，执行MSCK REPAIR TABLE cloudtrail_logs_pp;让Athena同步最新的分区信息，避免因分区缺失导致的路径解析错误。

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