¿Cómo puedo usar la proyección de particiones para crear tablas de CloudTrail para las consultas de Athena?

Resolución

El tamaño de los registros de CloudTrail puede aumentar con el tiempo, incluso si fragmenta la tabla de CloudTrail en particiones para reducir la versión ejecutable de las consultas. Las consultas en una tabla con muchas particiones requieren mucho tiempo de planificación y no se completan rápidamente.

Para solucionar el problema de tiempo de espera, utilice la proyección de particiones para crear una tabla de CloudTrail manualmente. Esto permite a Athena calcular dinámicamente el valor de las tablas de CloudTrail, lo que reduce la versión ejecutable de las consultas. Con la proyección de particiones, no necesita administrar las particiones porque los valores y las ubicaciones de las particiones se calculan a partir de la configuración.

Para crear una tabla de CloudTrail con particiones en función de la marca de tiempo mediante la proyección de particiones, consulte Creating the table for CloudTrail logs in Athena using partition projection.

Para crear una tabla de CloudTrail desde varias cuentas que esté fragmentada en particiones en función del año, el mes y el día mediante la proyección de particiones, utilice un comando similar al siguiente:

CREATE EXTERNAL TABLE ctrail_pp_ymd (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,
resources ARRAY<STRUCT<
               ARN:STRING,
               accountId:STRING,
               type:STRING>>,
eventtype STRING,
apiversion STRING,
readonly STRING,
recipientaccountid STRING,
serviceeventdetails STRING,
sharedeventid STRING,
vpcendpointid STRING
)
PARTITIONED BY (account string, region string, year string, month string, day 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://doc_example_bucket/AWSLogs/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.day.type'='integer',
  'projection.day.range'='01,31',
  'projection.day.digits'='2',
  'projection.month.type'='integer',
  'projection.month.range'='01,12',
  'projection.month.digits'='2',
  'projection.region.type'='enum',
  'projection.region.values'='us-east-1,us-east-2,us-west-2',
  'projection.year.type'='integer',
  'projection.year.range'='2015,2021',
  'projection.account.type'='enum',
  'projection.account.values'='111122223334444,5555666677778888',  
  'storage.location.template'='s3://doc_example_bucket/AWSLogs/${account}/CloudTrail/${region}/${year}/${month}/${day}'
)

Asegúrese de reemplazar las siguientes variables en la consulta anterior:

• ctrail_pp_ymd por el nombre de la tabla de CloudTrail.
• doc_example_bucket por el nombre del bucket de Amazon Simple Storage Service (Amazon S3) en el que desee crear la tabla de CloudTrail.
• 1111222233334444 y 5555666677778888 por los ID de las cuentas para las que desea crear la tabla de CloudTrail.
• us-east-1,us-east-2,us-west-2 por la región de AWS para la que desea crear la tabla de CloudTrail.
• Atributos y propiedades de la tabla según su caso de uso.
• Los rangos de proyección se basan en su caso de uso. Por ejemplo, si sus datos de CloudTrail solo están disponibles desde el año 2018, sustituya el rango de proyección de la columna de partición año por «2018,2021».

Si desea crear una tabla de CloudTrail para varias cuentas de una misma organización, utilice un comando como este:

CREATE EXTERNAL TABLE ctrail_pp_ymd_org (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,
resources ARRAY<STRUCT<
               ARN:STRING,
               accountId:STRING,
               type:STRING>>,
eventtype STRING,
apiversion STRING,
readonly STRING,
recipientaccountid STRING,
serviceeventdetails STRING,
sharedeventid STRING,
vpcendpointid STRING
)
PARTITIONED BY (account string, region string, year string, month string, day 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://doc_example_bucket/AWSLogs/doc_example_orgID/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.day.type'='integer',
  'projection.day.range'='01,31',
  'projection.day.digits'='2',
  'projection.month.type'='integer',
  'projection.month.range'='01,12',
  'projection.month.digits'='2',
  'projection.region.type'='enum',
  'projection.region.values'='us-east-1,us-east-2,us-west-2',
  'projection.year.type'='integer',
  'projection.year.range'='2010,2100',
  'projection.account.type'='enum',
  'projection.account.values'='111122223334444,5555666677778888',  
  'storage.location.template'='s3://doc_example_bucket/AWSLogs/doc_example_orgID/${account}/CloudTrail/${region}/${year}/${month}/${day}'
)

Nota: Si necesita consultar los datos de CloudTrail antes del año 2010, no olvide actualizar el intervalo de años en la propiedad projection.year.range.

Asegúrese de reemplazar las siguientes variables en la consulta anterior:

• ctrail_pp_ymd_org por el nombre de la tabla de CloudTrail.
• doc_example_bucket por el nombre del bucket de Amazon S3 en el que desee crear la tabla de CloudTrail.
• doc_example_orgID por el ID de su organización.
• 1111222233334444 y 5555666677778888 por los ID de las cuentas para las que desea crear la tabla de CloudTrail.
• us-east-1, us-east-2 y us-west-2 por las regiones en las que desea crear la tabla de CloudTrail.
• Atributos y propiedades de la tabla según su caso de uso.
• Los rangos de proyección se basan en su caso de uso. Por ejemplo, si sus datos de CloudTrail solo están disponibles desde el año 2018, sustituya el rango de proyección de la columna de partición año por «2018,2021».

Al ejecutar las consultas, asegúrese de incluir condiciones restrictivas en las columnas de partición de las consultas. Esto permite que Athena escanee menos datos y disminuye el tiempo de procesamiento de las consultas.

Por ejemplo, puede ejecutar un comando similar al siguiente para averiguar qué usuario envió la solicitud GetObject al bucket de S3. La tabla de esta consulta utiliza el formato de partición por año, mes y día.

Nota: Asegúrese de tener activado el registro de eventos para Amazon S3 en CloudTrail.

SELECT useridentity.arn, eventtime  FROM "ctrail_pp_ymd"where eventname = 'GetObject'
and year = '2021'
and month = '05'
and region = 'us-east-1'
and cast(json_extract(requestparameters, '$.bucketName')as varchar) ='doc_example_bucket'

Asegúrese de reemplazar las siguientes variables en la consulta anterior:

• ctrail_pp_ymd por el nombre de la tabla de CloudTrail.
• doc_example_bucket por el nombre del bucket de S3 en el que desee crear la tabla de CloudTrail.
• Condiciones restrictivas basadas en su caso de uso.

Si recibe problemas de tiempo de espera, consulte ¿Cómo puedo solucionar los problemas de tiempo de espera cuando consulto datos de CloudTrail con Athena?

Para obtener más información, consulte Consulta de registros de AWS CloudTrail y ¿Cómo puedo crear y usar tablas con particiones en Amazon Athena?

Información relacionada

¿Cómo soluciono el error de proyección de partición «INVALID_TABLE_PROPERTY» cuando consulto una tabla en Athena?

Solución de problemas en Athena