title: Inventory List from multiple S3 buckets using Athena
body: <p>I am trying query AWS S3 <a href="https://docs.aws.amazon.com/AmazonS3/latest/dev/storage-inventory.html" rel="nofollow noreferrer">Invetory List</a> using Athena. I can do this if I have only one source bucket. I am not sure how to configure this to work with multiple source buckets.</p>
  
  <p>we are using all the default configuration options with data format as CSV. S3 Inventory destination bucket name pattern for hive is like this:</p>
  
  <pre><code> destination-prefix/source-bucket/config-ID/hive/dt=YYYY-MM-DD-HH-MM/symlink.txt
  </code></pre>
  
  <p>So when I am creating an Athena table I have to use static hive path. </p>
  
  <pre><code>CREATE EXTERNAL TABLE your_table_name(
    //column names
  )
  PARTITIONED BY (dt string)
  //options ignored
  LOCATION 's3://destination-prefix/source-bucket/config-ID/hive/';
  </code></pre>
  
  <p>So if I want to query inventory data for multiple source buckets, it seems like I have to create a table for each "source-bucket".</p>
  
  <p>Alternatively, with out using Athena, I am trying to do this with AWS CLI</p>
  
  <pre><code>aws s3 ls s3://our-bucket-name/prefix/abc --recursive | awk ‘$1 &gt; “2019-04-01”
  </code></pre>
  
  <p>But this gets every single file first as there is no option to set --include or --exclude with "S3 ls"</p>
  
  <p>finally, the questions are:</p>
  
  <ol>
  <li><p>Can I configure AWS Inventory to generate inventory for multiple S3 buckets to that it puts everything into the same "hive" directory (i.e. ignore "source-bucket" prefix while generating Invetory)?</p></li>
  <li><p>Is it possible to configure Athena read from multiple hive locations? But with the possibility of new buckets getting created and dropped, I guess this gets ugly.</p></li>
  <li><p>Is there any alternative way to query inventory list instead of Athena or AWS CLI or writing a custom code to use manifest.json file to get these csv files.</p></li>
  </ol>