title: find the owner of EC2 instance by Athena and CloudTrail
body: <p>In order to know the owner of each <code>EC2 instance</code>, I query the <code>cloudtrail logs</code> stored in S3 by <code>Athena</code>. </p>
  
  <p>I have a table in Athena with the following stucture:</p>
  
  <pre><code>CREATE EXTERNAL TABLE cloudtrail_logs (
  eventversion STRING,
  useridentity STRUCT&lt;
                 type:STRING,
                 principalid:STRING,
                 arn:STRING,
                 accountid:STRING,
                 invokedby:STRING,
                 accesskeyid:STRING,
                 userName:STRING,
  sessioncontext:STRUCT&lt;
  attributes:STRUCT&lt;
                 mfaauthenticated:STRING,
                 creationdate:STRING&gt;,
  sessionissuer:STRUCT&lt;  
                 type:STRING,
                 principalId:STRING,
                 arn:STRING, 
                 accountId:STRING,
                 userName:STRING&gt;&gt;&gt;,
  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&lt;STRUCT&lt;
                 ARN:STRING,
                 accountId:STRING,
                 type:STRING&gt;&gt;,
  eventtype STRING,
  apiversion STRING,
  readonly STRING,
  recipientaccountid STRING,
  serviceeventdetails STRING,
  sharedeventid STRING,
  vpcendpointid STRING
  )
  PARTITIONED BY (account string, region string, year 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://&lt;BUCKET&gt;/AWSLogs/';
  </code></pre>
  
  <p>I want to find the identity of the user who launch an EC2 instances so I need to parse the field <code>responseelements</code>  and only get the rows with <code>responseelements</code> that has a particular <code>instanceID</code>.</p>
  
  <p>the field <code>responseelements</code> is like this:</p>
  
  <pre><code>{
  "requestId":"cab34472-31cc-44cd-ae32-a84077e55cb6",
  "reservationId":"r-05964c8549788ac50",
  "ownerId":"xxxxxxxxxx",
  "groupSet":{},
  "instancesSet":{
      "items":[
      {"instanceId":"i-043543cb4c12",
      "imageId":"ami-078df974",
      "instanceState":{"code":0,"name":"pending"},
      "privateDnsName":"ip-444444.eu-west-1.compute.internal",
      "keyName":"key-dev","amiLaunchIndex":0,"productCodes":{},
      "instanceType":"t2.large",
      "launchTime":1488438050000,
      "placement":{"availabilityZone":"eu-west-1b","tenancy":"default"},
      "monitoring":{"state":"pending"},
      "subnetId":"subnet-d8fffff",
      "vpcId":"vpc-444435",
      "privateIpAddress":"10.0.42.49",
      "stateReason":{"code":"pending","message":"pending"},
      "architecture":"x86_64",
      "rootDeviceType":"ebs",
      "rootDeviceName":"/dev/xvda",
      "blockDeviceMapping":{},
      "virtualizationType":"hvm",
      "hypervisor":"xen",
      "clientToken":"c6e53004-c561-437d-a642-196489ff297c_subnet-fffffffff",
      "groupSet":{"items":[{"groupId":"sg-64878700","groupName":"MetamSecurityGroup"}]},
      "sourceDestCheck":true,
      "networkInterfaceSet":{
          "items":[
          {"networkInterfaceId":"eni-b16b66f0",
          "subnetId":"subnet-dffffff",
          "vpcId":"vpc-50fffff35",
          "ownerId":"xxxxxxxx",
          "status":"in-use",
          "macAddress":"fdsfdsfsdfqdsf",
          "privateIpAddress":"10.0.42.34234213",
          "privateDnsName":"ip-1dddddd.eu-west-1.compute.internal",
          "sourceDestCheck":true,
          "groupSet":{"items":[{"groupId":"sg-64878700","groupName":"MetamSecurityGroup"}]},
          "attachment":{"attachmentId":"eni-attach-45619121","deviceIndex":0,"status":"attaching","attachTime":1488438050000,"deleteOnTermination":true},
          "privateIpAddressesSet":{"item":[{"privateIpAddress":"10ffffff","privateDnsName":"ip-ffffff.eu-west-1.compute.internal","primary":true}]},
          "ipv6AddressesSet":{},
          "tagSet":{}}]}
      ,"iamInstanceProfile":{"arn":"arn:aws:iam::xxxxx:instance-profile/infra-EC2InstanceProfile-1D59C5YR0LIYJ","id":"eeeeeeeeeeeeeeeeee"},
      "ebsOptimized":false}
      ]
      },
      "requesterId":"226008221399"
  }
  </code></pre>
  
  <p>This is my query that I tried:</p>
  
  <pre><code>SELECT DISTINCT eventsource, eventname, useridentity.userName, eventtime, json_extract(responseelements, '$.instanceId') as instance_id  
  FROM cloudtrail_logs
  WHERE account = 'xxxxxxxxxxxxxxx' 
  AND eventname = 'RunInstances';
  </code></pre>
  
  <p>but this gives <code>instance_id</code>   as an empty column.
   How to properly get only <code>instance_id</code> from the <code>resposneelement</code>?</p>