Athena SQL

emr.type.sql

SELECT
eventtime,
CASE
when useridentity.type = 'IAMUser' then useridentity.username
when useridentity.type = 'Root' then useridentity.invokedby
when useridentity.type = 'AssumedRole' then useridentity.arn 
ELSE
'undefined' 
END as "userid",
sourceipaddress,
eventname,
json_extract_scalar( requestparameters, '$.name' ) as "hostname",
json_extract_scalar( requestparameters, '$.instances.slaveInstanceType' ) as "slave",
json_extract_scalar( requestparameters, '$.instances.instanceGroups' ) as "instancegroups",
json_extract_scalar( requestparameters, '$.instances.instanceGroups[0].instanceType' ) as "instancegrouptype0",
json_extract_scalar( requestparameters, '$.instances.instanceGroups[1].instanceType' ) as "instancegrouptype1",
json_extract_scalar( requestparameters, '$.instances.instanceGroups[2].instanceType' ) as "instancegrouptype2",
errorcode,
errormessage
FROM cloudtrail_logs_2017
WHERE eventsource = 'elasticmapreduce.amazonaws.com'
AND eventname in ('RunJobFlow')
AND useridentity.username = 'pipeline-controller'
ORDER BY eventtime