Create Table for CloudTrail Logs in Athena like a pro with a little help from the experts at Bobcares. 

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team is ready to help customers with creating tables for CloudTrail Logs in Athena

How to create tables for CloudTrail Logs in Athena

Our Support Engineers have put together this handy guide to create tables in Athena for CloudTrail logs:

• Creating the Table for CloudTrail Logs in Athena via Manual Partitioning
• Creating the Table for CloudTrail Logs in Athena via Partition Projection

How to create tables for CloudTrail Logs in Athena via Manual Partitioning

Let’s take a look at how we can manually create tables for a CloudTrail via the Athena console.

1. First, we will copy the DDL statement from the Create a table in Amazon Athena dialog box in the CloudTrail console. We will paste this DDL statement into the Athena console after adding a “PARTITIONED BY” clause in order to partition the table.
2. Next, modify the code below so that it points to the Amazon S3 bucket that contains the log data:

   s3://CloudTrail_bucket_name/AWSLogs/Account_ID/CloudTrail/
3. Then, we will ensure the fields are listed accurately.
4. After that, we will run the query via the Athena console.
5. Next, use the ALTER TABLE ADD PARTITION command and load the partitions. For instance:

   ALTER TABLE table_name ADD 
      PARTITION (region='us-east-1',
                 year='2019',
                 month='02',
                 day='01')
      LOCATION 's3://CloudTrail_bucket_name/AWSLogs/Account_ID/CloudTrail/us-east-1/2021/02/01/'

How to create tables for CloudTrail Logs in Athena via Partition Projection

CloudTrail logs have a structure where we can specify the partition scheme in advance, reduce query run time as well as automate partition management with the Athena partition projection feature. In fact, this feature adds new partitions each time new data is added.

Moreover, it takes away the need to manually add a partition with the ALTER TABLE ADD PARTITION command.

For instance, in the example below the CREATE TABLE statement uses partitions projection in CloudTrail logs automatically from a specific date till the present date for a particular AWS Region. Here, we have to place bucket, account-id, and aws-region placeholders with the accurate values in the LOCATION and storage.location.template clauses.

Moreover, we have to replace 2020/01/01 with the starting date we prefer. Once we run the query successfully, we can query the table. Additionally, we do not have to run ALTER TABLE ADD PARTITION command to load the partitions.

CREATE EXTERNAL TABLE cloudtrail_logs_pp(
    eventVersion STRING,
    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>>>,
    eventTime STRING,
    eventSource STRING,
    eventName STRING,
    awsRegion STRING,
    sourceIpAddress STRING,
    userAgent STRING,
    errorCode STRING,
    errorMessage STRING,
    requestParameters STRING,
    responseElements STRING,
    additionalEventData STRING,
    requestId STRING,
    eventId STRING,
    readOnly STRING,
    resources ARRAY<STRUCT<
        arn: STRING,
        accountId: STRING,
        type: STRING>>,
    eventType STRING,
    apiVersion STRING,
    recipientAccountId STRING,
    serviceEventDetails STRING,
    sharedEventID STRING,
    vpcEndpointId STRING
  )
PARTITIONED BY (
   `timestamp` string)
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket/AWSLogs/account-id/CloudTrail/aws-region'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2020/01/01,NOW', 
  'projection.timestamp.type'='date', 
  'storage.location.template'='s3://bucket/AWSLogs/account-id/CloudTrail/aws-region/${timestamp}')

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to create table for CloudTrail Logs in Athena manually as well as via partition projection feature.