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

메타데이터 테이블 쿼리 예시 (Example metadata table queries)

다음 예시는 표준 SQL 쿼리를 사용해 S3 Metadata 테이블에서 다양한 유형의 정보를 얻는 방법을 보여줘요.

출처: 문서

본문

이 예시를 사용할 때 기억할 점:

예시는 Amazon Athena에서 작동하도록 작성됐어요. 다른 쿼리 엔진에서 작동하려면 예시를 수정해야 할 수 있어요.
쿼리를 최적화하는 방법을 이해했는지 확인하세요.
b_general-purpose-bucket-name을 자신의 네임스페이스 이름으로 바꾸세요.
지원되는 전체 열 목록은 S3 Metadata 저널 테이블 스키마와 S3 Metadata 라이브 인벤토리 테이블 스키마를 참고하세요.

목차 (Contents)

저널 테이블 예시 쿼리

파일 확장자로 객체 찾기
객체 삭제 나열
객체가 사용하는 AWS KMS 암호화 키 나열
KMS 키를 사용하지 않는 객체 나열
지난 7일 동안 PUT 작업에 사용된 AWS KMS 암호화 키 나열
지난 24시간 동안 S3 Lifecycle이 삭제한 객체 나열
Amazon Bedrock이 제공한 메타데이터 보기
객체의 현재 상태 이해

인벤토리 테이블 예시 쿼리

특정 태그를 사용하는 데이터 세트 발견
SSE-KMS로 암호화되지 않은 객체 나열
암호화되지 않은 객체 나열
Amazon Bedrock이 생성한 객체 나열
인벤토리 테이블과 저널 테이블 조정
객체의 현재 버전 찾기

주석 테이블 예시 쿼리

특정 객체의 모든 주석 나열
특정 주석이 있는 모든 객체 찾기
주석 테이블과 인벤토리 테이블 조인
커스텀 메타데이터를 S3 메타데이터 테이블과 조인
Amazon QuickSight로 메타데이터 테이블 데이터 시각화

저널 테이블 예시 쿼리 (Journal table example queries)

다음 예시 쿼리를 사용해 저널 테이블을 조회할 수 있어요.

파일 확장자로 객체 찾기 (Finding objects by file extension)

다음 쿼리는 특정 파일 확장자(여기서는 .jpg)를 가진 객체를 반환해요.

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

객체 삭제 나열 (Listing object deletions)

다음 쿼리는 요청한 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 암호화 키 나열 (Listing AWS KMS encryption keys used by your objects)

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

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

KMS 키를 사용하지 않는 객체 나열 (Listing objects that don't use KMS keys)

다음 쿼리는 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 암호화 키 나열 (Listing AWS KMS encryption keys used for PUT operations in the last 7 days)

다음 쿼리는 객체를 암호화하는 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 Lifecycle이 삭제한 객체 나열 (Listing objects deleted in the last 24 hours by S3 Lifecycle)

다음 쿼리는 S3 Lifecycle이 지난 하루 동안 만료시킨 객체를 나열해요.

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이 제공한 메타데이터 보기 (Viewing metadata provided by Amazon Bedrock)

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

객체의 현재 상태 이해 (Understanding the current state of your objects)

다음 쿼리는 객체의 현재 상태를 파악하는 데 도움을 줘요. 이 쿼리는 각 객체의 가장 최근 버전을 식별하고, 삭제된 객체를 필터링하며, 시퀀스 번호를 기준으로 각 객체의 최신 버전을 표시해요. 결과는 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;

인벤토리 테이블 예시 쿼리 (Inventory table example queries)

다음 예시 쿼리를 사용해 인벤토리 테이블을 조회할 수 있어요.

특정 태그를 사용하는 데이터 세트 발견 (Discovering datasets that use specific tags)

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

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

SSE-KMS로 암호화되지 않은 객체 나열 (Listing objects not encrypted with SSE-KMS)

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

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

암호화되지 않은 객체 나열 (Listing objects that aren't encrypted)

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

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

Amazon Bedrock이 생성한 객체 나열 (Listing objects generated by 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';

인벤토리 테이블과 저널 테이블 조정 (Reconciling the inventory table with the journal table)

다음 쿼리는 버킷의 현재 내용과 최신 상태를 맞춘 인벤토리 테이블 형태의 목록을 생성해요. 더 정확히 말하면, 결과 목록은 인벤토리 테이블의 최신 스냅샷과 저널 테이블의 최신 이벤트를 결합해요.

이 쿼리가 가장 정확한 결과를 내려면 저널 테이블과 인벤토리 테이블이 모두 활성(Active) 상태여야 해요.

이 쿼리는 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;

객체의 현재 버전 찾기 (Finding the current versions of your objects)

다음 쿼리는 인벤토리 테이블을 사용해 어떤 객체 버전이 현재 버전인지 보여주는 새 출력 테이블을 생성해요. 출력 테이블은 의도적으로 S3 Inventory 보고서와 유사해요. 출력 테이블에는 객체가 현재 버전인지 나타내는 is_latest 필드가 포함돼요. is_latest 필드는 S3 Inventory 보고서의 IsLatest 필드와 동일해요.

이 쿼리는 버전 관리 활성화 또는 버전 관리 일시 중단 상태에 있는 S3 Versioning을 가진 범용 버킷에서 작동해요.

사전 조건 이 쿼리는 결과를 화면에 행으로 출력하는 것보다 더 나은 성능과 추가 쿼리 지원을 위해 결과를 새 S3 테이블로 출력해요. 따라서 이 쿼리를 실행하기 전에 다음 조건을 충족했는지 확인하세요. 결과를 새 테이블로 출력하지 않기로 선택했다면 이 단계를 건너뛸 수 있어요.

새 테이블을 출력할 위치로 기존 고객 관리형 테이블 버킷에 기존 네임스페이스가 있어야 해요. 자세한 내용은 테이블 버킷 만들기와 네임스페이스 만들기를 참고하세요.
새 출력 테이블을 조회하려면 조회를 위한 접근 방법을 설정해야 해요. 자세한 내용은 테이블 데이터 접근을 참고하세요. Amazon Athena 같은 AWS 분석 서비스로 출력 테이블을 조회하려면 고객 관리형 테이블 버킷이 AWS 분석 서비스와 통합되어 있어야 해요. 자세한 내용은 Amazon S3 Tables와 AWS 분석 서비스 통합 개요를 참고하세요.

이 쿼리를 사용하려면 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: 
);

주석 테이블 예시 쿼리 (Annotation table example queries)

S3 Metadata 구성에서 주석 테이블을 활성화했다면 Athena 또는 Apache Iceberg 테이블을 지원하는 기타 분석 서비스를 사용해 주석 데이터를 조회할 수 있어요. 주석 테이블은 "s3tablescatalog/aws-s3"."b_bucket-name"."annotation" 명명 규칙을 사용해요.

특정 객체의 모든 주석 나열 (Listing all annotations for a specific object)

다음 쿼리는 특정 객체의 모든 주석을 반환해요.

SELECT name, size, last_modified_date, replication_status
FROM "s3tablescatalog/aws-s3"."b_amzn-s3-demo-bucket"."annotation"
WHERE object_key = 'documents/report.pdf'
ORDER BY name

특정 주석이 있는 모든 객체 찾기 (Finding all objects that have a specific annotation)

다음 쿼리는 특정 주석이 있는 모든 객체를 반환해요.

SELECT object_key, object_version_id, size, last_modified_date
FROM "s3tablescatalog/aws-s3"."b_amzn-s3-demo-bucket"."annotation"
WHERE name = 'classification'
ORDER BY last_modified_date DESC

주석 테이블과 인벤토리 테이블 조인 (Joining the annotation table with the inventory table)

다음 쿼리는 주석 테이블과 인벤토리 테이블을 조인해 주석 데이터를 인벤토리 메타데이터와 결합해요.

SELECT i.key, i.storage_class, a.name, a.size, a.text_value
FROM "s3tablescatalog/aws-s3"."b_amzn-s3-demo-bucket"."inventory" i
JOIN "s3tablescatalog/aws-s3"."b_amzn-s3-demo-bucket"."annotation" a
  ON i.bucket = a.bucket
  AND i.key = a.object_key
  AND i.object_join_key = a.object_join_key
WHERE a.name = 'classification'
LIMIT 10

주석 테이블과 인벤토리 테이블을 조인할 때는 일관성을 보장하기 위해 object_join_key 열을 사용해요. 이 열은 새 버전마다 또는 null 버전이 생성되거나 덮어써질 때 할당되는 객체의 고유 식별자를 포함해요. 주석 행이 올바른 객체 버전 상태에 대응하도록 보장해요.

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