To make it easier for you to create tables in Athena for CloudTrail logs, our Technical Support team have placed together this handy guide:<\/p>\n\n\n\n
Consider the following example of manually creating tables for a CloudTrail using the Athena console.<\/p>\n\n\n\n
s3:\/\/CloudTrail_bucket_name\/AWSLogs\/Account_ID\/CloudTrail\/<\/code><\/pre>\n\n\n\n- Then we’ll double-check that all of the fields are listed correctly.<\/li><\/ol>\n\n\n\n
- The query will then be run through the Athena console.<\/li><\/ol>\n\n\n\n
- The partitions should then be loaded using the ALTER TABLE ADD PARTITION command.<\/li><\/ol>\n\n\n\n
For example,<\/p>\n\n\n\n
ALTER TABLE table_name ADD \n PARTITION (region='us-east-1',\n year='2019',\n month='02',\n day='01')\n LOCATION 's3:\/\/CloudTrail_bucket_name\/AWSLogs\/Account_ID\/CloudTrail\/us-east-1\/2021\/02\/01\/'<\/code><\/pre>\n\n\n\nCreate Tables For CloudTrail Logs In Athena Via Partition Projection<\/strong><\/h3>\n\n\n\nWith the Athena partition projection feature, we can clarify the partition scheme in advance, decrease query run time, and automate partition management. Whenever new data is added, this feature creates new partitions.<\/p>\n\n\n\n
Furthermore, it eliminates the need to manually create a partition using the ALTER TABLE ADD PARTITION command.<\/p>\n\n\n\n
For example, in the example below, the CREATE TABLE statement automatically projects partitions in CloudTrail logs from a given date to the current date for a specific AWS Region. Here, we must replace the placeholders bucket, account-id, and AWS-region with accurate values in the LOCATION and storage.location.template.clauses.<\/p>\n\n\n\n
Furthermore, we must replace 2020\/01\/01 with the preferred start date. We can query the table once the query has been successfully run. Moreover, the ALTER TABLE ADD PARTITION command is not required to load the partitions.<\/p>\n\n\n\n
CREATE EXTERNAL TABLE cloudtrail_logs_pp(\n eventVersion STRING,\n userIdentity STRUCT<\n type: STRING,\n principalId: STRING,\n arn: STRING,\n accountId: STRING,\n invokedBy: STRING,\n accessKeyId: STRING,\n userName: STRING,\n sessionContext: STRUCT<\n attributes: STRUCT<\n mfaAuthenticated: STRING,\n creationDate: STRING>,\n sessionIssuer: STRUCT<\n type: STRING,\n principalId: STRING,\n arn: STRING,\n accountId: STRING,\n userName: STRING>>>,\n eventTime STRING,\n eventSource STRING,\n eventName STRING,\n awsRegion STRING,\n sourceIpAddress STRING,\n userAgent STRING,\n errorCode STRING,\n errorMessage STRING,\n requestParameters STRING,\n responseElements STRING,\n additionalEventData STRING,\n requestId STRING,\n eventId STRING,\n readOnly STRING,\n resources ARRAY<STRUCT<\n arn: STRING,\n accountId: STRING,\n type: STRING>>,\n eventType STRING,\n apiVersion STRING,\n recipientAccountId STRING,\n serviceEventDetails STRING,\n sharedEventID STRING,\n vpcEndpointId STRING\n )\nPARTITIONED BY (\n `timestamp` string)\nROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'\nSTORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'\nOUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\nLOCATION\n 's3:\/\/bucket\/AWSLogs\/account-id\/CloudTrail\/aws-region'\nTBLPROPERTIES (\n 'projection.enabled'='true', \n 'projection.timestamp.format'='yyyy\/MM\/dd', \n 'projection.timestamp.interval'='1', \n 'projection.timestamp.interval.unit'='DAYS', \n 'projection.timestamp.range'='2020\/01\/01,NOW', \n 'projection.timestamp.type'='date', \n 'storage.location.template'='s3:\/\/bucket\/AWSLogs\/account-id\/CloudTrail\/aws-region\/${timestamp}')<\/code><\/pre>\n\n\n\n