Amazon Athena で Amazon S3 インベントリをクエリする

Athena を使用できるすべてのリージョンで、Amazon Athena で標準 SQL クエリを実行し、Amazon S3 インベントリのファイルをクエリできます。使用可能な AWS リージョンを確認するには、AWS リージョン表を参照してください。

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

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

Athena テーブルを作成します。テーブルの作成の詳細については、Amazon Athena ユーザーガイドの「 Amazon Athena でのテーブルの作成」を参照してください。
クエリ対象のインベントリレポートが ORC 形式、Parquet 形式、CSV 形式のいずれであるかによって、以下のサンプルクエリテンプレートのいずれかを使用してクエリを作成します。
- Athena を使用して ORC 形式のインベントリレポートをクエリするときは、次のサンプルクエリをテンプレートとして使用します。
  
  次のサンプルクエリには、ORC 形式のインベントリレポートのすべてのオプションフィールドが含まれています。
  
  このサンプルクエリを使用するには、以下を実行します。
  - your_table_name を、作成した Athena テーブルの名前に置き換えます。
  - インベントリ用に選択しなかったすべてのオプションフィールドを削除し、クエリとインベントリ用に選択したフィールドを対応させます。
  - 設定に応じて、次のバケット名とインベントリの場所 (設定 ID) を置き換えます。
    
    s3://DOC-EXAMPLE-BUCKET/config-ID/hive/
  - projection.dt.range の 2022-01-01-00-00 日付を、Athena でデータをパーティション化する時間範囲の最初の日に置き換えます。詳細については、「Athena でのデータのパーティション化」を参照してください。
```
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
) 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://source-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://DOC-EXAMPLE-BUCKET/config-ID/hive/
  - projection.dt.range の 2022-01-01-00-00 日付を、Athena でデータをパーティション化する時間範囲の最初の日に置き換えます。詳細については、「Athena でのデータのパーティション化」を参照してください。
```
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
) 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://source-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"
  );
```

以下の例に示すように、インベントリに対してさまざまなクエリを実行できるようになりました。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 フィールドの使用」を参照してください。


# 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 ユーザーガイド」を参照してください。

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

インベントリの検索

空のバージョン ID 文字列を NULL 文字列に変換します。