# メタデータテーブルクエリの例
<a name="metadata-tables-example-queries"></a>

次の例は、標準 SQL クエリを使用して S3 メタデータテーブルからさまざまなタイプの情報を取得する方法を示しています。

これらの例を使用する際は、次の点に注意してください。
+ 例は、Amazon Athena で動作するように記述されています。別のクエリエンジンで動作するためには、例を変更する必要がある場合があります。
+ [クエリを最適化](metadata-tables-optimizing-query-performance.md)する方法を理解していることを確認してください。
+ `b_{{general-purpose-bucket-name}}` を名前空間の名前に置き換えます。
+ サポートされている列の完全なリストについては、「[S3 Metadata ジャーナルテーブルスキーマ](metadata-tables-schema.md)」および「[S3 Metadata ライブインベントリテーブルスキーマ](metadata-tables-inventory-schema.md)」を参照してください。

**Contents**
+ [ジャーナルテーブルのクエリ例](#metadata-tables-example-queries-journal-tables)
  + [ファイル拡張子によるオブジェクトの検索](#metadata-tables-example-query-object-pattern)
  + [オブジェクトの削除の一覧表示](#metadata-tables-example-query-delete-events)
  + [オブジェクトで使用される AWS KMS 暗号化キーの一覧表示](#metadata-tables-example-query-objects-using-kms-key)
  + [KMS キーを使用しないオブジェクトの一覧表示](#metadata-tables-example-query-objects-not-using-kms-key)
  + [過去 7 日間の `PUT` オペレーションに使用される AWS KMS 暗号化キーの一覧表示](#metadata-tables-example-query-objects-using-kms-key-puts)
  + [過去 24 時間以内に削除されたオブジェクトを S3 ライフサイクル別に一覧表示する](#metadata-tables-example-query-objects-deleted-lifecycle)
  + [Amazon Bedrock が提供するメタデータの表示](#metadata-tables-example-query-bedrock)
  + [オブジェクトの現状の把握](#metadata-tables-example-query-current-state)
+ [インベントリテーブルのクエリ例](#metadata-tables-example-queries-inventory-tables)
  + [特定のタグを使用するデータセットの検出](#metadata-tables-example-query-datasets-specific-tags)
  + [SSE-KMS で暗号化されていないオブジェクトの一覧表示](#metadata-tables-example-query-objects-not-kms-encrypted)
  + [暗号化されていないオブジェクトの一覧表示](#metadata-tables-example-query-objects-not-encrypted)
  + [Amazon Bedrock によって生成されたオブジェクトの一覧表示](#metadata-tables-example-query-objects-generated-bedrock)
  + [インベントリテーブルとジャーナルテーブルの照合](#metadata-tables-example-query-generate-latest-inventory)
  + [オブジェクトの最新バージョンの検索](#metadata-tables-example-query-latest-version)
+ [カスタムメタデータと S3 メタデータテーブルの結合](metadata-tables-join-custom-metadata.md)
+ [Amazon Quick を使用したメタデータテーブルデータの視覚化](metadata-tables-quicksight-dashboards.md)

## ジャーナルテーブルのクエリ例
<a name="metadata-tables-example-queries-journal-tables"></a>

次のクエリ例を使用して、ジャーナルテーブルをクエリできます。

### ファイル拡張子によるオブジェクトの検索
<a name="metadata-tables-example-query-object-pattern"></a>

次のクエリは、特定のファイル拡張子 (この場合は `.jpg`) を持つオブジェクトを返します。

```
SELECT key FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
WHERE key LIKE '%.jpg'
AND record_type = 'CREATE'
```

### オブジェクトの削除の一覧表示
<a name="metadata-tables-example-query-delete-events"></a>

次のクエリは、リクエストを行った AWS アカウント ID または AWS サービスプリンシパルを含むオブジェクト削除イベントを返します。

```
SELECT DISTINCT bucket, key, sequence_number, record_type, record_timestamp, requester, source_ip_address, version_id
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
WHERE record_type = 'DELETE';
```

### オブジェクトで使用される AWS KMS 暗号化キーの一覧表示
<a name="metadata-tables-example-query-objects-using-kms-key"></a>

次のクエリは、オブジェクトを暗号化する AWS Key Management Service (AWS KMS) キーの ARN を返します。

```
SELECT DISTINCT kms_key_arn
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal";
```

### KMS キーを使用しないオブジェクトの一覧表示
<a name="metadata-tables-example-query-objects-not-using-kms-key"></a>

次のクエリは、AWS KMS キーで暗号化されていないオブジェクトを返します。

```
SELECT DISTINCT kms_key_arn
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
WHERE encryption_status NOT IN ('SSE-KMS', 'DSSE-KMS')
AND record_type = 'CREATE';
```

### 過去 7 日間の `PUT` オペレーションに使用される AWS KMS 暗号化キーの一覧表示
<a name="metadata-tables-example-query-objects-using-kms-key-puts"></a>

次のクエリは、オブジェクトを暗号化する AWS Key Management Service (AWS KMS) キーの ARN を返します。

```
SELECT DISTINCT kms_key_arn 
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
WHERE record_timestamp > (current_date - interval '7' day)
AND kms_key_arn is NOT NULL;
```

### 過去 24 時間以内に削除されたオブジェクトを S3 ライフサイクル別に一覧表示する
<a name="metadata-tables-example-query-objects-deleted-lifecycle"></a>

次のクエリは、S3 ライフサイクルによって最終日に期限切れになったオブジェクトを一覧表示します。

```
SELECT bucket, key, version_id, last_modified_date, record_timestamp, requester
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
WHERE requester = 's3.amazonaws.com'
AND record_type = 'DELETE' 
AND record_timestamp > (current_date - interval '1' day)
```

### Amazon Bedrock が提供するメタデータの表示
<a name="metadata-tables-example-query-bedrock"></a>

一部の AWS サービス ([Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/APIReference/welcome.html) など) は、Amazon S3 にオブジェクトをアップロードします。これらのサービスによって提供されるオブジェクトメタデータをクエリできます。例えば、次のクエリには、Amazon Bedrock によって汎用バケットにアップロードされたオブジェクトがあるかどうかを判断する `user_metadata` 列が含まれています。

```
SELECT DISTINCT bucket, key, sequence_number, record_type, record_timestamp, user_metadata
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
WHERE record_type = 'CREATE'
AND user_metadata['content-source'] = 'AmazonBedrock';
```

Amazon Bedrock がバケットにオブジェクトをアップロードした場合、`user_metadata` 列には、クエリ結果のオブジェクトに関連付けられた次のメタデータが表示されます。

```
user_metadata
{content-additional-params -> requestid="CVK8FWYRW0M9JW65", signedContentSHA384="38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b", content-model-id -> bedrock-model-arn, content-source -> AmazonBedrock}
```

### オブジェクトの現状の把握
<a name="metadata-tables-example-query-current-state"></a>

次のクエリは、オブジェクトの現在の状態を判断するのに役立ちます。クエリは、各オブジェクトの最新バージョンを識別し、削除されたオブジェクトを除外し、シーケンス番号に基づいて各オブジェクトの最新バージョンをマークします。結果は、`bucket`、`key`、および `sequence_number` 列で順序付けられます。

```
WITH records_of_interest as (
   -- Start with a query that can narrow down the records of interest.
    SELECT * from "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."journal"
),

version_stacks as (
   SELECT *,
          -- Introduce a column called 'next_sequence_number', which is the next larger
          -- sequence_number for the same key version_id in sorted order.
          LEAD(sequence_number, 1) over (partition by (bucket, key, coalesce(version_id, '')) order by sequence_number ASC) as next_sequence_number
   from records_of_interest
),

-- Pick the 'tip' of each version stack triple: (bucket, key, version_id).
-- The tip of the version stack is the row of that triple with the largest sequencer.
-- Selecting only the tip filters out any row duplicates.
-- This isn't typical, but some events can be delivered more than once to the table
-- and include rows that might no longer exist in the bucket (since the
-- table contains rows for both extant and extinct objects).
-- In the next subquery, eliminate the rows that contain deleted objects.
current_versions as (
    SELECT * from version_stacks where next_sequence_number is NULL
),

-- Eliminate the rows that are extinct from the bucket by filtering with
-- record_type. An object version has been deleted from the bucket if its tip is
-- record_type==DELETE.
existing_current_versions as (
    SELECT * from current_versions where not (record_type = 'DELETE' and is_delete_marker = FALSE)
),

-- Optionally, to determine which of several object versions is the 'latest',
-- you can compare their sequence numbers. A version_id is the latest if its
-- tip's sequencer is the largest among all other tips in the same key.
with_is_latest as (
    SELECT *,
           -- Determine if the sequence_number of this row is the same as the largest sequencer for the key that still exists.
           sequence_number = (MAX(sequence_number) over (partition by (bucket, key))) as is_latest_version
    FROM existing_current_versions
)

SELECT * from with_is_latest
ORDER BY bucket, key, sequence_number;
```

## インベントリテーブルのクエリ例
<a name="metadata-tables-example-queries-inventory-tables"></a>

次のクエリ例を使用して、インベントリテーブルをクエリできます。

### 特定のタグを使用するデータセットの検出
<a name="metadata-tables-example-query-datasets-specific-tags"></a>

次のクエリは、指定されたタグを使用するデータセットを返します。

```
SELECT * 
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."inventory"
WHERE object_tags['key1'] = 'value1'
AND object_tags['key2'] = 'value2';
```

### SSE-KMS で暗号化されていないオブジェクトの一覧表示
<a name="metadata-tables-example-query-objects-not-kms-encrypted"></a>

次のクエリは、SSE-KMS キーで暗号化されていないオブジェクトを返します。

```
SELECT key, encryption_status 
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."inventory"
WHERE encryption_status != 'SSE-KMS';
```

### 暗号化されていないオブジェクトの一覧表示
<a name="metadata-tables-example-query-objects-not-encrypted"></a>

次のクエリは、暗号化されていないオブジェクトを返します。

```
SELECT bucket, key, version_id  
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."inventory"
WHERE encryption_status IS NULL;
```

### Amazon Bedrock によって生成されたオブジェクトの一覧表示
<a name="metadata-tables-example-query-objects-generated-bedrock"></a>

次のクエリは、Amazon Bedrock によって生成されたオブジェクトを一覧表示します。

```
SELECT DISTINCT bucket, key, sequence_number, user_metadata
FROM "s3tablescatalog/aws-s3"."b_{{general-purpose-bucket-name}}"."inventory"
WHERE user_metadata['content-source'] = 'AmazonBedrock';
```

### インベントリテーブルとジャーナルテーブルの照合
<a name="metadata-tables-example-query-generate-latest-inventory"></a>

次のクエリは、バケットの現在のコンテンツを含む最新の inventory-table-like リストを生成します。より正確には、結果のリストはインベントリテーブルの最新スナップショットとジャーナルテーブルの最新イベントを組み合わせます。

このクエリで最も正確な結果を生成するには、ジャーナルテーブルとインベントリテーブルの両方がアクティブステータスである必要があります。

10 億 (10^9) 未満のオブジェクトを含む汎用バケットに、このクエリを使用することをお勧めします。

このクエリ例では、リスト結果に次の簡略化を適用します (インベントリテーブルと比較)。
+ **列の省略** – 列 `bucket`、`is_multipart`、`encryption_status`、`is_bucket_key_enabled`、`kms_key_arn`、`checksum_algorithm` は最終結果の一部ではありません。オプションの列のセットを最小限に抑えると、パフォーマンスが向上します。
+ **すべてのレコードを含める** – クエリは、null バージョン (バージョニングされていないバケットまたはバージョニングが停止されたバケット内) と削除マーカーを含むすべてのオブジェクトキーとバージョンを返します。目的のキーのみを表示するように結果をフィルタリングする方法の例については、クエリの最後にある `WHERE` 句を参照してください。
+ **高速調整** – クエリは、まれに、バケットになくなったオブジェクトを一時的にレポートすることがあります。これらの不一致は、インベントリテーブルの次のスナップショットが利用可能になるとすぐに削除されます。この動作は、パフォーマンスと精度のトレードオフです。

Amazon Athena でこのクエリを実行するには、ジャーナルテーブルとインベントリテーブルを含む汎用バケットメタデータ設定の `s3tablescatalog/aws-s3` カタログと `b_{{general-purpose-bucket-name}}` データベースを必ず選択してください。

```
WITH inventory_time_cte AS (
    SELECT COALESCE(inventory_time_from_property, inventory_time_default) AS inventory_time FROM
    (
      SELECT * FROM
        (VALUES (TIMESTAMP '2024-12-01 00:00')) AS T (inventory_time_default)
      LEFT OUTER JOIN
        (
         SELECT from_unixtime(CAST(value AS BIGINT) / 1000.0) AS inventory_time_from_property FROM "journal$properties"
         WHERE key = 'aws.s3metadata.oldest-uncoalesced-record-timestamp' LIMIT 1
        )
      ON TRUE
    )
),

working_set AS (
    SELECT
        key,
        sequence_number,
        version_id,
        is_delete_marker,
        size,
        COALESCE(last_modified_date, record_timestamp) AS last_modified_date,
        e_tag,
        storage_class,
        object_tags,
        user_metadata,
        (record_type = 'DELETE' AND NOT COALESCE(is_delete_marker, FALSE)) AS _is_perm_delete
    FROM journal j
    CROSS JOIN inventory_time_cte t
    WHERE j.record_timestamp > (t.inventory_time - interval '15' minute)

    UNION ALL

    SELECT
        key,
        sequence_number,
        version_id,
        is_delete_marker,
        size,
        last_modified_date,
        e_tag,
        storage_class,
        object_tags,
        user_metadata,
        FALSE AS _is_perm_delete
    FROM inventory i
),

updated_inventory AS (
    SELECT * FROM (
        SELECT *,
            MAX(sequence_number) OVER (PARTITION BY key, version_id) AS _supremum_sn
        FROM working_set
    )
    WHERE sequence_number = _supremum_sn
)

SELECT
    key,
    sequence_number,
    version_id,
    is_delete_marker,
    size,
    last_modified_date,
    e_tag,
    storage_class,
    object_tags,
    user_metadata
FROM updated_inventory
-- This filter omits only permanent deletes from the results. Delete markers will still be shown.
WHERE NOT _is_perm_delete
-- You can add additional filters here. Examples:
--    AND object_tags['department'] = 'billing'
--    AND starts_with(key, 'reports/')
ORDER BY key ASC, sequence_number DESC;
```

### オブジェクトの最新バージョンの検索
<a name="metadata-tables-example-query-latest-version"></a>

次のクエリでは、インベントリテーブルを使用して、どのオブジェクトバージョンが最新であるかを示す新しい出力テーブルを生成します。出力テーブルは、意図的に S3 インベントリレポートに似せています。出力テーブルには、オブジェクトが現在のバージョンであるかどうかを示す `is_latest` フィールドが含まれています。`is_latest` フィールドは、[S3 インベントリレポート](storage-inventory.md#storage-inventory-contents)の **IsLatest** フィールドと同等です。

このクエリは、バージョニングが有効またはバージョニングが停止状態の [S3 バージョニング](Versioning.md)を使用する汎用バケットで機能します。

**前提条件**  
クエリは、結果を新しい S3 テーブルに出力して、さらなるクエリをサポートし、画面上に行を出力する場合よりもパフォーマンスを向上させます。したがって、このクエリを実行する前に、次の条件を満たしていることを確認してください。結果を新しいテーブルに出力しない場合は、以下の手順をスキップできます。
+ 新しいテーブルを出力する場所として、既存の名前空間を持つ既存のカスタマーマネージドのテーブルバケットが必要です。詳細については、「[テーブルバケットの作成](s3-tables-buckets-create.md)」および「[ネームスペースの作成](s3-tables-namespace-create.md)」を参照してください。
+ 新しい出力テーブルをクエリするには、そのテーブルをクエリするためのアクセス方法を設定する必要があります。詳細については、「[テーブルデータへのアクセス](s3-tables-access.md)」を参照してください。Amazon Athena などの AWS 分析サービスを使用して出力テーブルをクエリする場合は、カスタマーマネージドのテーブルバケットを AWS 分析サービスと統合する必要があります。詳細については、「[Amazon S3 Tables と AWS 分析サービスの統合の概要](s3-tables-integration-overview.md)」を参照してください。

このクエリを使用するには、`{{amzn-s3-demo-table-bucket}}` を新しい出力テーブルを作成する既存のカスタマーマネージドのテーブルバケットの名前に置き換えます。{{`existing_namespace`}} を、テーブルバケットに出力テーブルを作成する名前空間の名前に置き換えます。{{`new_table`}} は、出力テーブルに使用する名前に置き換えます。出力テーブルの名前が[テーブルの命名規則](s3-tables-buckets-naming.md#naming-rules-table)に従っていることを確認します。

Amazon Athena でこのクエリを実行するには、インベントリテーブルを含む汎用バケットメタデータ設定の `s3tablescatalog/aws-s3` カタログと `b_{{general-purpose-bucket-name}}` データベースを必ず選択してください。

```
-- If you don't want to output the results to a new table, remove the following two lines 
-- (everything before the WITH clause). 
CREATE TABLE "s3tablescatalog/{{amzn-s3-demo-table-bucket}}"."{{existing_namespace}}"."{{new_table}}" 
as (
WITH 
my_inventory AS (
  SELECT 
        bucket,
        key,
        version_id,
        sequence_number,
        is_delete_marker,
        size,
        last_modified_date,
        storage_class
  FROM inventory
-- For prefix filtering, use a WHERE clause with % at the end.
--     WHERE key LIKE 'prefix%'
  ),
 
inventory_with_is_latest as (
SELECT *,
       ROW_NUMBER() OVER (
         PARTITION BY key 
         ORDER BY sequence_number DESC
       ) = 1 AS is_latest
FROM my_inventory
    )

SELECT
        bucket,
        key,
        version_id,
        sequence_number,
        is_delete_marker,
        size,
        last_modified_date,
        storage_class,
        is_latest

FROM inventory_with_is_latest

-- If you want only the current version of each key, uncomment the following WHERE clause.
-- WHERE is_latest = TRUE
-- If you aren't outputting the results to a new table, remove the next line: 
);
```