如何正确创建基于S3中JSON格式CloudTrail日志的Athena表？

问题描述 #

需从CloudTrail JSON日志提取信息做分析，此前用Excel手动处理效率低，改用Athena创建外部表后，表中始终只有单列，无法正常解析日志内容。

CloudTrail日志样例 #

{"Records":[{"eventVersion":"1.05","userIdentity":{"type":"AssumedRole","principalId":"ARXXXXXXXXXXXXXXXXXFVEC:AWSConfig-Describe","arn":"arn:aws:sts::2CCCCCCCCC8:assumed-role/AWSServiceRoleForConfig/AWSConfig-Describe","accountId":"2CCCCCCCCC8","accessKeyId":"ASXXXXXXXXXXXXXXXQVL","sessionContext":{"sessionIssuer":{"type":"Role","principalId":"ARXXXXXXXXXXXXXXXXXFVEC","arn":"arn:aws:iam::2CCCCCCCCC8:role/aws-service-role/config.amazonaws.com/AWSServiceRoleForConfig","accountId":"2CCCCCCCCC8","userName":"AWSServiceRoleForConfig"},"attributes":{"creationDate":"2019-09-03T07:40:00Z","mfaAuthenticated":"false"}},"invokedBy":"AWS Internal"},"eventTime":"2019-09-03T07:40:00Z","eventSource":"s3.amazonaws.com","eventName":"HeadBucket","awsRegion":"us-west-2","sourceIPAddress":"172.18.87.252","userAgent":"[AWSConfig]","requestParameters":{"bucketName":"service_logs_10l51wolgib72","Host":"s3.us-west-2.amazonaws.com"},"responseElements":null,"additionalEventData":{"SignatureVersion":"SigV4","CipherSuite":"ECDHE-RSA-AES128-SHA","bytesTransferredIn":0.0,"AuthenticationMethod":"AuthHeader","x-amz-id-2":"JYEwSk6jEv2rB/MjwluNXcnKxRSo72GCOz8WP9OYXDFI2FxS1T81K7excoDuo36rJIQz9MWYKEE=","bytesTransferredOut":0.0},"requestID":"E224F90BD7370007","eventID":"77d7ea03-b8a2-4b50-8f81-b8217eacf008","readOnly":true,"resources":[{"type":"AWS::S3::Object","ARNPrefix":"arn:aws:s3:::service_logs_10l51wolgib72/"},{"accountId":"2CCCCCCCCC8","type":"AWS::S3::Bucket","ARN":"arn:aws:s3:::service_logs_10l51wolgib72"}],"eventType":"AwsApiCall","recipientAccountId":"2CCCCCCCCC8","vpcEndpointId":"vpce-3c0ee766"},{"eventVersion":"1.05","userIdentity":{"type":"AssumedRole","principalId":"ARXXXXXXXXXXXXXXXXXFVEC:AWSConfig-Describe","arn":"arn:aws:sts::2CCCCCCCCC8:assumed-role/AWSServiceRoleForConfig/AWSConfig-Describe","accountId":"2CCCCCCCCC8","accessKeyId":"ASXXXXXXXXXXXXXXXQVL","sessionContext":{"sessionIssuer":{"type":"Role","principalId":"ARXXXXXXXXXXXXXXXXXFVEC","arn":"arn:aws:iam::2CCCCCCCCC8:role/aws-service-role/config.amazonaws.com/AWSServiceRoleForConfig","accountId":"2CCCCCCCCC8","userName":"AWSServiceRoleForConfig"},"attributes":{"creationDate":"2019-09-03T07:40:00Z","mfaAuthenticated":"false"}},"invokedBy":"AWS Internal"},"eventTime":"2019-09-03T07:40:00Z","eventSource":"s3.amazonaws.com","eventName":"HeadBucket","awsRegion":"us-west-2","sourceIPAddress":"172.18.87.252","userAgent":"[AWSConfig]","requestParameters":{"bucketName":"service_logs_10l51wolgib72","Host":"s3.us-west-2.amazonaws.com"},"responseElements":null,"additionalEventData":{"SignatureVersion":"SigV4","CipherSuite":"ECDHE-RSA-AES128-SHA","bytesTransferredIn":0.0,"AuthenticationMethod":"AuthHeader","x-amz-id-2":"JYEwSk6jEv2rB/MjwluNXcnKxRSo72GCOz8WP9OYXDFI2FxS1T81K7excoDuo36rJIQz9MWYKEE=","bytesTransferredOut":0.0},"requestID":"E224F90BD7370020","eventID":"77d7ea03-b8a2-4b50-8f81-b8217eacf021","readOnly":true,"resources":[{"type":"AWS::S3::Object","ARNPrefix":"arn:aws:s3:::service_logs_10l51wolgib72/"},{"accountId":"2CCCCCCCCC8","type":"AWS::S3::Bucket","ARN":"arn:aws:s3:::service_logs_10l51wolgib72"}],"eventType":"AwsApiCall","recipientAccountId":"2CCCCCCCCC8","vpcEndpointId":"vpce-3c0ee766"},{"eventVersion":"1.05","userIdentity":{"type":"AssumedRole","principalId":"ARXXXXXXXXXXXXXXXXXFVEC:AWSConfig-Describe","arn":"arn:aws:sts::2CCCCCCCCC8:assumed-role/AWSServiceRoleForConfig/AWSConfig-Describe","accountId":"2CCCCCCCCC8","accessKeyId":"ASXXXXXXXXXXXXXXXQVL","sessionContext":{"sessionIssuer":{"type":"Role","principalId":"ARXXXXXXXXXXXXXXXXXFVEC","arn":"arn:aws:iam::2CCCCCCCCC8:role/aws-service-role/config.amazonaws.com/AWSServiceRoleForConfig","accountId":"2CCCCCCCCC8","userName":"AWSServiceRoleForConfig"},"attributes":{"creationDate":"2019-09-03T07:40:00Z","mfaAuthenticated":"false"}},"invokedBy":"AWS Internal"},"eventTime":"2019-09-03T07:40:00Z","eventSource":"s3.amazonaws.com","eventName":"HeadBucket","awsRegion":"us-west-2","sourceIPAddress":"172.18.87.252","userAgent":"[AWSConfig]","requestParameters":{"bucketName":"service_logs_10l51wolgib72","Host":"s3.us-west-2.amazonaws.com"},"responseElements":null,"additionalEventData":{"SignatureVersion":"SigV4","CipherSuite":"ECDHE-RSA-AES128-SHA","bytesTransferredIn":0.0,"AuthenticationMethod":"AuthHeader","x-amz-id-2":"JYEwSk6jEv2rB/MjwluNXcnKxRSo72GCOz8WP9OYXDFI2FxS1T81K7excoDuo36rJIQz9MWYKEE=","bytesTransferredOut":0.0},"requestID":"E224F90BD7370033","eventID":"77d7ea03-b8a2-4b50-8f81-b8217eacf034","readOnly":true,"resources":[{"type":"AWS::S3::Object","ARNPrefix":"arn:aws:s3:::service_logs_10l51wolgib72/"},{"accountId":"2CCCCCCCCC8","type":"AWS::S3::Bucket","ARN":"arn:aws:s3:::service_logs_10l51wolgib72"}],"eventType":"AwsApiCall","recipientAccountId":"2CCCCCCCCC8","vpcEndpointId":"vpce-3c0ee766"}]}

当前错误建表SQL #

CREATE EXTERNAL TABLE IF NOT EXISTS `default`.testjson4 (
 Records struct<eventVersion:string,
             userIdentity:string,
             eventTime:string,
             eventSource: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://******************/CloudTrail/us-east-1/****/'
TBLPROPERTIES ('classification' = 'json');

正确的建表方法 #

CloudTrail日志的Records是数组类型，且内部字段包含多层嵌套结构体，需完整定义嵌套结构才能正确解析。以下是适配日志结构的建表语句：

CREATE EXTERNAL TABLE IF NOT EXISTS `default`.cloudtrail_logs (
  Records array<struct<
    eventVersion: string,
    userIdentity: struct<
      type: string,
      principalId: string,
      arn: string,
      accountId: string,
      accessKeyId: string,
      sessionContext: struct<
        sessionIssuer: struct<
          type: string,
          principalId: string,
          arn: string,
          accountId: string,
          userName: string
        >,
        attributes: struct<
          creationDate: string,
          mfaAuthenticated: string
        >
      >,
      invokedBy: string
    >,
    eventTime: string,
    eventSource: string,
    eventName: string,
    awsRegion: string,
    sourceIPAddress: string,
    userAgent: string,
    requestParameters: struct<
      bucketName: string,
      Host: string
    >,
    responseElements: string,
    additionalEventData: struct<
      SignatureVersion: string,
      CipherSuite: string,
      bytesTransferredIn: double,
      AuthenticationMethod: string,
      `x-amz-id-2`: string,
      bytesTransferredOut: double
    >,
    requestID: string,
    eventID: string,
    readOnly: boolean,
    resources: array<struct<
      type: string,
      ARNPrefix: string,
      accountId: string,
      ARN: string
    >>,
    eventType: string,
    recipientAccountId: string,
    vpcEndpointId: string
  >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'TRUE',
  'dots.in.keys' = 'FALSE',
  'case.insensitive' = 'TRUE'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://******************/CloudTrail/us-east-1/****/'
TBLPROPERTIES ('classification' = 'json');

关键说明 #

1. Records类型修正：原SQL将Records定义为单个struct，但实际是array<struct>，必须用数组类型才能解析日志中的多条记录。
2. 嵌套结构体定义：userIdentity、sessionContext、additionalEventData等字段都是嵌套结构体，不能简单设为string，需要逐层定义内部字段。
3. 特殊字段处理：像x-amz-id-2这种包含特殊字符的字段，需要用反引号`包裹以避免语法错误。
4. SerDe配置优化：开启ignore.malformed.json可跳过格式错误的日志，避免整表解析失败；移除无效的mapping参数。
5. 查询建议：查询时可使用UNNEST展开Records数组，方便分析单条日志记录，例如：

SELECT 
  r.eventTime,
  r.eventSource,
  r.eventName,
  r.userIdentity.type,
  r.userIdentity.accountId
FROM cloudtrail_logs
CROSS JOIN UNNEST(Records) AS t(r);

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