Arctera Data Insight Administrator's Guide

To Create an Athena table for a trail using the Athena console

1. Log in to at https://console.aws.amazon.com/athena/
2. Open the Query Editor tab and enter the following Hive data definition language (DDL) command CREATE DATABASE <myDataBase> . Replace myDatabase with the name of the database that you want to create.
3. Select Run Query or press Ctrl+ENTER.
4. From the Database menu, select your database. It will then be considered as the current database.
5. Navigate to the Query Editor and under Database select Create table >> Create table option
6. Paste the query given below in the query window

   Note:

   Replace trail-log-location with the location copied from AWS. For more information, refer to See Configuring Audit Events in AWS .

   CREATE EXTERNAL TABLE `cloudtrail_logs_aws_data_events`(

   `eventversion` string COMMENT 'from deserializer',

   `useridentity`

   struct<type:string,principalid:string,arn:string,accountid:string,
   invokedby:string,acces

   skeyid:string,username:string,sessioncontext:struct
   <attributes:struct<mfaauthenticat

   ed:string,creationdate:string>,sessionissuer:struct
   <type:string,principalid:string,arn:st

   ring,accountid:string,username: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',

   `resources` array<struct<arn:string,accountid:string,type:string>> 
   COMMENT 'from deserializer',

   `eventtype` string COMMENT 'from deserializer',

   `apiversion` string COMMENT 'from deserializer',

   `readonly` string COMMENT 'from deserializer',

   `recipientaccountid` string COMMENT 'from deserializer',

   `serviceeventdetails` string COMMENT 'from deserializer',

   `sharedeventid` string COMMENT 'from deserializer',

   `vpcendpointid` string COMMENT 'from deserializer')

   PARTITIONED BY (`region` string,

   `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://<trail-log-location>/CloudTrail/${region}'

   Note:

   Replace trail-log-location with the location copied from AWS. For more information, refer to See Configuring Audit Events in AWS .

   TBLPROPERTIES (

   'classification'='cloudtrail',

   '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',

   'projection.region.type'='ENUM',

   'projection.region.values'='us-east-2,us-east-1,us-west-1,
   us-west-2,ca-central-1,af-south-1,ap-east-1,ap-south-1,ap-northeast-3,ap-northeast-2,
   ap-southeast-1,ap-southeast-2,ap-northeast-1,cn-north-1,cn-northwest-1,eu-central-1,
   eu-north-1,eu-west-1,eu-west-2,eu-west-3,eu-south-1,me-south-1,us-gov-east-1,
   us-gov-west-1,sa-east-1',

   'storage.location.template'='s3://[trail-log-location]/CloudTrail/
   ${region}/${timestamp}')

   Note:

   Replace trail-log-location with the location copied from AWS. For more information, refer to See Configuring Audit Events in AWS .

   Note:

   AWS Athena stores every query results in the bucket. There is no way to prevent Athena from doing it. Data Insight recommends to set a life-cycle policy on the bucket or folder. For example, you could delete query results periodically.