本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 中繼資料表查詢範例
<a name="metadata-tables-example-queries"></a>

下列範例示範如何使用標準 SQL 查詢，從 S3 Metadata 資料表取得不同類型的資訊。

使用下列範例時，請記住：
+ 這些範例是為了與 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)
  + [列出 S3 生命週期在過去 24 小時內刪除的物件](#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) 金鑰ARNs：

```
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) 金鑰ARNs：

```
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;
```

### 列出 S3 生命週期在過去 24 小時內刪除的物件
<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。您可以查詢這些服務提供的物件中繼資料。例如，下列查詢會包含 `user_metadata` 欄，以判斷是否有物件由 Amazon Bedrock 上傳至一般用途儲存貯體：

```
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^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: 
);
```