Exemplos de consultas a tabelas de metadados
Os exemplos a seguir mostram como você pode obter diferentes tipos de informações das tabelas do S3 Metadata usando consultas SQL padrão.
Lembre-se do seguinte ao usar esses exemplos:
-
Os exemplos foram escritos para funcionar com o Amazon Athena. Talvez seja necessário modificar os exemplos para trabalhar com um mecanismo de consulta diferente.
-
Entenda como otimizar as consultas.
-
Substitua
b_
pelo nome do seu namespace.general-purpose-bucket-name
-
Para conferir uma lista completa de colunas compatíveis, consulte Esquema de tabelas de diário do S3 Metadata e Esquema de tabelas de inventário em tempo real do S3 Metadata.
Sumário
Unir metadados personalizados com tabelas de metadados do S3
Visualizar dados de tabelas de metadados com o Amazon QuickSight
Consultas de exemplo a tabelas de diário
Você pode usar os exemplos de consulta a seguir para consultar tabelas de diário.
Encontrar objetos por extensão de arquivo
A seguinte consulta exibe objetos com uma extensão de arquivo específica (.jpg
, neste caso):
SELECT key FROM "s3tablescatalog/aws-s3"."
b_
"."journal" WHERE key LIKE '%.jpg' AND record_type = 'CREATE'general-purpose-bucket-name
Listar exclusões de objetos
A seguinte consulta exibe eventos de exclusão de objetos, incluindo o ID da Conta da AWS ou a entidade principal do serviço da AWS que fez a solicitação:
SELECT DISTINCT bucket, key, sequence_number, record_type, record_timestamp, requester, source_ip_address, version_id FROM "s3tablescatalog/aws-s3"."
b_
"."journal" WHERE record_type = 'DELETE';general-purpose-bucket-name
Listar as chaves de criptografia do AWS KMS usadas pelos objetos
A seguinte consulta exibe o ARN das chaves do AWS Key Management Service (AWS KMS) que criptografam os objetos:
SELECT DISTINCT kms_key_arn FROM "s3tablescatalog/aws-s3"."
b_
"."journal";general-purpose-bucket-name
Listar objetos que não usam chaves do KMS
A seguinte consulta exibe objetos que não estão criptografados com chaves do AWS KMS:
SELECT DISTINCT kms_key_arn FROM "s3tablescatalog/aws-s3"."
b_
"."journal" WHERE encryption_status NOT IN ('SSE-KMS', 'DSSE-KMS') AND record_type = 'CREATE';general-purpose-bucket-name
Listar as chaves de criptografia do AWS KMS usadas para operações PUT
nos últimos sete dias
A seguinte consulta exibe o ARN das chaves do AWS Key Management Service (AWS KMS) que criptografam os objetos:
SELECT DISTINCT kms_key_arn FROM "s3tablescatalog/aws-s3"."
b_
"."journal" WHERE record_timestamp > (current_date - interval '7' day) AND kms_key_arn is NOT NULL;general-purpose-bucket-name
Listar objetos excluídos nas últimas 24 horas pela funcionalidade Ciclo de Vida do S3
A seguinte consulta exibe listas de objetos expirados no último dia pela funcionalidade Ciclo de Vida do S3:
SELECT bucket, key, version_id, last_modified_date, record_timestamp, requester FROM "s3tablescatalog/aws-s3"."
b_
"."journal" WHERE requester = 's3.amazonaws.com' AND record_type = 'DELETE' AND record_timestamp > (current_date - interval '1' day)general-purpose-bucket-name
Visualizar metadados fornecidos pelo Amazon Bedrock
Alguns serviços da AWS (como o Amazon Bedrock) fazem upload de objetos no Amazon S3. Você pode consultar os metadados de objetos fornecidos por esses serviços. Por exemplo, a seguinte consulta inclui a coluna user_metadata
para determinar se há objetos carregados pelo Amazon Bedrock em um bucket de uso geral:
SELECT DISTINCT bucket, key, sequence_number, record_type, record_timestamp, user_metadata FROM "s3tablescatalog/aws-s3"."
b_
"."journal" WHERE record_type = 'CREATE' AND user_metadata['content-source'] = 'AmazonBedrock';general-purpose-bucket-name
Se o Amazon Bedrock tiver feito upload de um objeto no bucket, a coluna user_metadata
exibirá os seguintes metadados associados ao objeto no resultado da consulta:
user_metadata {content-additional-params -> requestid="CVK8FWYRW0M9JW65", signedContentSHA384="38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b", content-model-id -> bedrock-model-arn, content-source -> AmazonBedrock}
Noções básicas sobre o estado atual dos objetos
A consulta a seguir pode ajudar você a determinar o estado atual dos objetos. A consulta identifica a versão mais recente de cada objeto, filtra para eliminar os objetos excluídos e marca a versão mais recente de cada objeto com base nos números de sequência. Os resultados são ordenados pelas colunas bucket
, key
e 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_
"."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;general-purpose-bucket-name
Consultas de exemplo a tabelas de inventário
Você pode usar os exemplos de consulta a seguir para consultar tabelas de inventário.
Descobrir conjuntos de dados que usam tags específicas
A seguinte consulta exibe o conjunto de dados que usa as tags especificadas:
SELECT * FROM "s3tablescatalog/aws-s3"."
b_
"."inventory" WHERE object_tags['key1'] = 'value1' AND object_tags['key2'] = 'value2';general-purpose-bucket-name
Listar objetos não criptografados com SSE-KMS
A seguinte consulta exibe objetos que não estão criptografados com SSE-KMS:
SELECT key, encryption_status FROM "s3tablescatalog/aws-s3"."
b_
"."inventory" WHERE encryption_status != 'SSE-KMS';general-purpose-bucket-name
Listar objetos que não estão criptografados
A seguinte consulta exibe objetos que não estão criptografados:
SELECT bucket, key, version_id FROM "s3tablescatalog/aws-s3"."
b_
"."inventory" WHERE encryption_status IS NULL;general-purpose-bucket-name
Listar objetos gerados pelo Amazon Bedrock
A seguinte consulta lista objetos que foram gerados pelo Amazon Bedrock:
SELECT DISTINCT bucket, key, sequence_number, user_metadata FROM "s3tablescatalog/aws-s3"."
b_
"."inventory" WHERE user_metadata['content-source'] = 'AmazonBedrock';general-purpose-bucket-name
Reconciliar a tabela de inventário com a tabela de diário
A consulta a seguir gera uma lista semelhante a uma tabela de inventário que está de acordo com o conteúdo atual do bucket. Mais precisamente, a lista resultante combina o snapshot mais recente da tabela de inventário com os eventos mais recentes na tabela de diário.
Para que essa consulta produza os resultados mais precisos, as tabelas de diário e inventário devem estar no status “Ativo”.
Recomendamos usar essa consulta para buckets de uso geral que contêm menos de 1 bilhão (10^9) de objetos.
Esse exemplo de consulta aplica as seguintes simplificações aos resultados da lista (em comparação com a tabela de inventário):
-
Omissões de coluna: as colunas
bucket
,is_multipart
,encryption_status
,is_bucket_key_enabled
,kms_key_arn
echecksum_algorithm
não fazem parte dos resultados finais. Manter o conjunto de colunas opcionais no mínimo melhora o desempenho. -
Inclusão de todos os registros: a consulta exibe todas as chaves e versões do objeto, inclusive a versão nula (em buckets sem versionamento ou com versionamento suspenso) e os marcadores de exclusão. Para ver exemplos de como filtrar os resultados para mostrar somente as chaves pelas quais você tem interesse, consulte a cláusula
WHERE
no final da consulta. -
Reconciliação acelerada: em casos raros, a consulta pode relatar temporariamente objetos que não estão mais no bucket. Essas discrepâncias serão eliminadas assim que o próximo snapshot da tabela de inventário estiver disponível. Esse comportamento é uma concessão entre desempenho e precisão.
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