# Amazon Athena で Amazon S3 インベントリをクエリする
<a name="storage-inventory-athena-query"></a>

Athena を使用できるすべてのリージョンで、Amazon Athena で標準 SQL クエリを実行し、Amazon S3 インベントリのファイルをクエリできます。使用可能な AWS リージョン を確認するには、[AWS リージョン 表](https://aws.amazon.com/about-aws/global-infrastructure/regional-product-services/)を参照してください。

Athena は、[Apache Optimized Row Columnar (ORC)](https://orc.apache.org/)、[https://parquet.apache.org/](https://parquet.apache.org/)、またはカンマ区切りの値 (CSV) 形式で Amazon S3 インベントリファイルをクエリできます。Athena を使用してインベントリファイルをクエリする場合は、ORC 形式または Parquet 形式のインベントリファイルを使用することをお勧めします。ORC 形式および Parquet 形式は、より高速なクエリパフォーマンスとより低いクエリコストを提供します。ORC および Parquet は、[http://hadoop.apache.org/](http://hadoop.apache.org/) 向けに設計された自己記述型、型認識型の列指向ファイル形式です。列形式の場合、リーダーは現在のクエリに必要な列だけを読み取り、圧縮解除し、処理することができます。Amazon S3 インベントリの ORC 形式および Parquet 形式は、すべての AWS リージョンで使用できます。

**Athena を使用して Amazon S3 インベントリファイルをクエリするには**

1. Athena テーブルを作成します。テーブルの作成の詳細については、*Amazon Athena ユーザーガイド*の「 [Amazon Athena でのテーブルの作成](https://docs.aws.amazon.com/athena/latest/ug/creating-tables.html) 」を参照してください。

1. クエリ対象のインベントリレポートが ORC 形式、Parquet 形式、CSV 形式のいずれであるかによって、以下のサンプルクエリテンプレートのいずれかを使用してクエリを作成します。
   + Athena を使用して ORC 形式のインベントリレポートをクエリするときは、次のサンプルクエリをテンプレートとして使用します。

     次のサンプルクエリには、ORC 形式のインベントリレポートのすべてのオプションフィールドが含まれています。

     このサンプルクエリを使用するには、以下を実行します。
     + `{{your_table_name}}` を、作成した Athena テーブルの名前に置き換えます。
     + インベントリ用に選択しなかったすべてのオプションフィールドを削除し、クエリとインベントリ用に選択したフィールドを対応させます。
     + 設定に応じて、次のバケット名とインベントリの場所 (設定 ID) を置き換えます。

       `s3://{{amzn-s3-demo-bucket}}/{{config-ID}}/hive/`
     + `projection.dt.range` の `{{2022-01-01-00-00}}` 日付を、Athena でデータをパーティション化する時間範囲の最初の日に置き換えます。詳細については、「[Athena でのデータのパーティション化](https://docs.aws.amazon.com/athena/latest/ug/partitions.html)」を参照してください。

     ```
     CREATE EXTERNAL TABLE {{your_table_name}} (
              bucket string,
              key string,
              version_id string,
              is_latest boolean,
              is_delete_marker boolean,
              size bigint,
              last_modified_date timestamp,
              e_tag string,
              storage_class string,
              is_multipart_uploaded boolean,
              replication_status string,
              encryption_status string,
              object_lock_retain_until_date bigint,
              object_lock_mode string,
              object_lock_legal_hold_status string,
              intelligent_tiering_access_tier string,
              bucket_key_status string,
              checksum_algorithm string,
              object_access_control_list string,
              object_owner string,
              lifecycle_expiration_date timestamp
     ) PARTITIONED BY (
             dt string
     )
     ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
       STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
       OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
       LOCATION 's3://{{amzn-s3-demo-bucket}}/{{config-ID}}/hive/'
       TBLPROPERTIES (
         "projection.enabled" = "true",
         "projection.dt.type" = "date",
         "projection.dt.format" = "yyyy-MM-dd-HH-mm",
         "projection.dt.range" = "{{2022-01-01-00-00}},NOW",
         "projection.dt.interval" = "1",
         "projection.dt.interval.unit" = "HOURS"
       );
     ```
   + Athena を使用して Parquet 形式のインベントリレポートをクエリする場合は、ORC 形式のレポートでサンプルクエリを使用します。ただし、`ROW FORMAT SERDE` ステートメントで ORC SerDe の代わりに次の Parquet SerDe を使用します。

     ```
     ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
     ```
   + Athena を使用して CSV 形式のインベントリレポートをクエリするときは、次のサンプルクエリをテンプレートとして使用します。

     次のサンプルクエリには、CSV 形式のインベントリレポートのすべてのオプションフィールドが含まれています。

     このサンプルクエリを使用するには、以下を実行します。
     + `{{your_table_name}}` を、作成した Athena テーブルの名前に置き換えます。
     + インベントリ用に選択しなかったすべてのオプションフィールドを削除し、クエリとインベントリ用に選択したフィールドを対応させます。
     + 設定に応じて、次のバケット名とインベントリの場所 (設定 ID) を置き換えます。

       `s3://{{amzn-s3-demo-bucket}}/{{config-ID}}/hive/`
     + `projection.dt.range` の `{{2022-01-01-00-00}}` 日付を、Athena でデータをパーティション化する時間範囲の最初の日に置き換えます。詳細については、「[Athena でのデータのパーティション化](https://docs.aws.amazon.com/athena/latest/ug/partitions.html)」を参照してください。

     ```
     CREATE EXTERNAL TABLE {{your_table_name}} (
              bucket string,
              key string,
              version_id string,
              is_latest boolean,
              is_delete_marker boolean,
              size string,
              last_modified_date string,
              e_tag string,
              storage_class string,
              is_multipart_uploaded boolean,
              replication_status string,
              encryption_status string,
              object_lock_retain_until_date string,
              object_lock_mode string,
              object_lock_legal_hold_status string,
              intelligent_tiering_access_tier string,
              bucket_key_status string,
              checksum_algorithm string,
              object_access_control_list string,
              object_owner string,
              lifecycle_expiration_date string
     ) PARTITIONED BY (
             dt string
     )
     ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
       STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
       OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
       LOCATION 's3://{{amzn-s3-demo-bucket}}/{{config-ID}}/hive/'
       TBLPROPERTIES (
         "projection.enabled" = "true",
         "projection.dt.type" = "date",
         "projection.dt.format" = "yyyy-MM-dd-HH-mm",
         "projection.dt.range" = "{{2022-01-01-00-00}},NOW",
         "projection.dt.interval" = "1",
         "projection.dt.interval.unit" = "HOURS"
       );
     ```

1. 以下の例に示すように、インベントリに対してさまざまなクエリを実行できるようになりました。`{{user input placeholder}}` を、ユーザー自身の情報に置き換えます。

   ```
   # Get a list of the latest inventory report dates available.
   SELECT DISTINCT dt FROM {{your_table_name}} ORDER BY 1 DESC limit 10;
             
   # Get the encryption status for a provided report date.
   SELECT encryption_status, count(*) FROM {{your_table_name}} WHERE dt = '{{YYYY-MM-DD-HH-MM}}' GROUP BY encryption_status;
             
   # Get the encryption status for inventory report dates in the provided range.
   SELECT dt, encryption_status, count(*) FROM {{your_table_name}} 
   WHERE dt > '{{YYYY-MM-DD-HH-MM}}' AND dt < '{{YYYY-MM-DD-HH-MM}}' GROUP BY dt, encryption_status;
   ```

   オブジェクトアクセスコントロールリスト (オブジェクト ACL) フィールドをインベントリレポートに追加するように S3 インベントリを設定すると、レポートにはオブジェクト ACL フィールドの値が base64 エンコードされた文字列として表示されます。オブジェクト ACL フィールドのデコードされた値を JSON で取得するには、Athena を使用してこのフィールドをクエリできます。以下のクエリの例を参照してください。オブジェクト ACL フィールドの詳細については、「[オブジェクト ACL フィールドの使用](objectacl.md)」を参照してください。

   ```
   # Get the S3 keys that have Object ACL grants with public access.
   WITH grants AS (
       SELECT key,
           CAST(
               json_extract(from_utf8(from_base64(object_access_control_list)), '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))
           ) AS grants_array
       FROM {{your_table_name}}
   )
   SELECT key,
          grants_array,
          grant
   FROM grants, UNNEST(grants_array) AS t(grant)
   WHERE element_at(grant, 'uri') = 'http://acs.amazonaws.com/groups/global/AllUsers'
   ```

   ```
   # Get the S3 keys that have Object ACL grantees in addition to the object owner.
   WITH grants AS 
       (SELECT key,
       from_utf8(from_base64(object_access_control_list)) AS object_access_control_list,
            object_owner,
            CAST(json_extract(from_utf8(from_base64(object_access_control_list)),
            '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))) AS grants_array
       FROM {{your_table_name}})
   SELECT key,
          grant,
          objectowner
   FROM grants, UNNEST(grants_array) AS t(grant)
   WHERE cardinality(grants_array) > 1 AND element_at(grant, 'canonicalId') != object_owner;
   ```

   ```
   # Get the S3 keys with READ permission that is granted in the Object ACL. 
   WITH grants AS (
       SELECT key,
           CAST(
               json_extract(from_utf8(from_base64(object_access_control_list)), '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))
           ) AS grants_array
       FROM {{your_table_name}}
   )
   SELECT key,
          grants_array,
          grant
   FROM grants, UNNEST(grants_array) AS t(grant)
   WHERE element_at(grant, 'permission') = 'READ';
   ```

   ```
   # Get the S3 keys that have Object ACL grants to a specific canonical user ID.
   WITH grants AS (
       SELECT key,
           CAST(
               json_extract(from_utf8(from_base64(object_access_control_list)), '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))
           ) AS grants_array
       FROM {{your_table_name}}
   )
   SELECT key,
          grants_array,
          grant
   FROM grants, UNNEST(grants_array) AS t(grant)
   WHERE element_at(grant, 'canonicalId') = 'user-canonical-id';
   ```

   ```
   # Get the number of grantees on the Object ACL.
   SELECT key,
          object_access_control_list,
          json_array_length(json_extract(object_access_control_list,'$.grants')) AS grants_count
   FROM {{your_table_name}};
   ```

Athena の詳しい使用方法については、「[Amazon Athena ユーザーガイド](https://docs.aws.amazon.com/athena/latest/ug/)」を参照してください。