元数据表查询示例 - Amazon Simple Storage Service

元数据表查询示例

以下示例说明如何使用标准 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}

了解对象的当前状态

以下查询有助于您确定对象的当前状态。该查询可识别每个对象的最新版本,筛选掉已删除的对象,并根据序列号标记每个对象的最新版本。结果按 bucketkeysequence_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^9)的通用存储桶。

此示例查询将以下简化应用于列表结果(与清单表相比):

  • 列忽略:列 bucketis_multipartencryption_statusis_bucket_key_enabledkms_key_arnchecksum_algorithm 不是最终结果的一部分。将可选列集保持在最低限度可以提高性能。

  • 包含所有记录:查询返回所有对象键和版本,包括空版本(在未进行版本控制或暂停版本控制的存储桶中)和删除标记。有关如何筛选结果以仅显示您感兴趣的键的示例,请参阅查询末尾的 WHERE 子句。

  • 加速协调:在极少数情况下,查询可能会临时报告不再位于存储桶中的对象。一旦清单表的下一个快照变为可用,就会消除这些差异。这种行为是在性能与准确性之间进行权衡。

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