메타데이터 테이블 쿼리 예시

다음 예시에서는 표준 SQL 쿼리를 사용하여 S3 메타데이터 테이블에서 다양한 유형의 정보를 가져오는 방법을 보여줍니다.

다음 예시를 사용할 때는 다음 사항에 유의하세요.

저널 테이블 예제 쿼리

다음 예제 쿼리를 사용하여 저널 테이블을 쿼리할 수 있습니다.

파일 확장명으로 객체 찾기

다음 쿼리는 특정 파일 확장명(이 경우 .jpg)이 있는 객체를 반환합니다.

SELECT key FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal"
WHERE key LIKE '%.jpg'
AND record_type = 'CREATE'

객체 삭제 나열

다음 쿼리는 요청을 제출한 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 암호화 키 나열

다음 쿼리는 객체를 암호화하는 AWS Key Management Service(AWS KMS) 키의 ARN을 반환합니다.

SELECT DISTINCT kms_key_arn
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal";

KMS 키를 사용하지 않는 객체 나열

다음 쿼리는 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 암호화 키 나열

다음 쿼리는 객체를 암호화하는 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;

S3 수명 주기별로 지난 24시간 동안 삭제된 객체 나열

다음 쿼리는 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에서 제공하는 메타데이터 보기

일부 AWS 서비스(예: Amazon Bedrock)는 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}

현재 객체 상태의 이해

다음 쿼리는 객체의 현재 상태를 판단하는 데 도움이 될 수 있습니다. 쿼리는 각 객체의 최신 버전을 식별하고, 삭제된 객체를 필터링하고, 시퀀스 번호를 기반으로 각 객체의 최신 버전을 표시합니다. 결과는 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;

인벤토리 테이블 예제 쿼리

다음 예제 쿼리를 사용하여 인벤토리 테이블을 쿼리할 수 있습니다.

특정 태그를 사용하는 데이터세트 검색

다음 쿼리는 지정된 태그를 사용하는 데이터세트를 반환합니다.

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

SSE-KMS로 암호화되지 않은 객체 나열

다음 쿼리는 SSE-KMS로 암호화되지 않은 객체를 반환합니다.

SELECT key, encryption_status 
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE encryption_status != 'SSE-KMS';

암호화되지 않은 객체 나열

다음 쿼리는 암호화되지 않은 객체를 반환합니다.

SELECT bucket, key, version_id  
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE encryption_status IS NULL;

Amazon Bedrock에서 생성한 객체 나열

다음 쿼리는 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';

인벤토리 테이블과 저널 테이블 조정

다음 쿼리는 버킷의 현재 콘텐츠를 반영한 인벤토리 테이블 형식의 최신 목록을 생성합니다. 더 정확히 말하면, 결과 목록은 인벤토리 테이블의 최신 스냅샷과 저널 테이블의 최신 이벤트를 결합합니다.

이 쿼리가 가장 정확한 결과를 생성하려면 저널과 인벤토리 테이블이 모두 활성 상태여야 합니다.

10억 개(10^9) 미만의 객체를 포함하는 범용 버킷에 이 쿼리를 사용하는 것이 좋습니다.

이 예제 쿼리는 목록 결과에 다음과 같은 단순화를 적용합니다(인벤토리 테이블과 비교 시).

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;

객체의 현재 버전 찾기

다음 쿼리는 인벤토리 테이블을 사용하여 최신 객체 버전을 보여주는 새 출력 테이블을 생성합니다. 출력 테이블은 의도적으로 S3 인벤토리 보고서와 유사한 모습을 가집니다. 출력 테이블에는 객체가 현재 버전인지 여부를 나타내는 is_latest 필드가 포함되어 있습니다. is_latest 필드는 S3 인벤토리 보고서의 IsLatest 필드와 동일합니다.

이 쿼리는 버전 관리가 활성화되거나 버전 관리가 일시 중지된 상태의 S3 버전 관리가 있는 범용 버킷에서 작동합니다.

사전 조건

쿼리는 결과를 새 S3 테이블에 출력하여 추가 쿼리를 지원하고 화면에 행을 출력하는 것보다 성능이 뛰어납니다. 따라서 이 쿼리를 실행하기 전에 다음 조건이 충족됐는지 확인하세요. 결과를 새 테이블에 출력하지 않도록 선택한 경우에는 해당 단계를 건너뛸 수 있습니다.

이 쿼리를 사용하려면 amzn-s3-demo-table-bucket을 새 출력 테이블을 생성할 기존 고객 관리형 테이블 버킷의 이름으로 바꿉니다. existing_namespace를 테이블 버킷에서 출력 테이블을 생성할 네임스페이스의 이름으로 바꿉니다. new_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: 
);