Bobcares

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till December 6th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till December 6th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

Create Table for CloudTrail Logs in Athena | How-to

by | Feb 2, 2022

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
Create Table for CloudTrail Logs in Athena

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.