# 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](metadata-tables-optimizing-query-performance.md).
+ Substitua `b_{{general-purpose-bucket-name}}` pelo nome do seu namespace.
+ Para conferir uma lista completa de colunas compatíveis, consulte [Esquema de tabelas de diário do S3 Metadata](metadata-tables-schema.md) e [Esquema de tabelas de inventário em tempo real do S3 Metadata](metadata-tables-inventory-schema.md).
**Contents**
+ [Consultas de exemplo a tabelas de diário](#metadata-tables-example-queries-journal-tables)
+ [Encontrar objetos por extensão de arquivo](#metadata-tables-example-query-object-pattern)
+ [Listar exclusões de objetos](#metadata-tables-example-query-delete-events)
+ [Listar as chaves de criptografia do AWS KMS usadas pelos objetos](#metadata-tables-example-query-objects-using-kms-key)
+ [Listar objetos que não usam chaves do KMS](#metadata-tables-example-query-objects-not-using-kms-key)
+ [Listar as chaves de criptografia do AWS KMS usadas para operações `PUT` nos últimos sete dias](#metadata-tables-example-query-objects-using-kms-key-puts)
+ [Listar objetos excluídos nas últimas 24 horas pela funcionalidade Ciclo de Vida do S3](#metadata-tables-example-query-objects-deleted-lifecycle)
+ [Visualizar metadados fornecidos pelo Amazon Bedrock](#metadata-tables-example-query-bedrock)
+ [Noções básicas sobre o estado atual dos objetos](#metadata-tables-example-query-current-state)
+ [Consultas de exemplo a tabelas de inventário](#metadata-tables-example-queries-inventory-tables)
+ [Descobrir conjuntos de dados que usam tags específicas](#metadata-tables-example-query-datasets-specific-tags)
+ [Listar objetos não criptografados com SSE-KMS](#metadata-tables-example-query-objects-not-kms-encrypted)
+ [Listar objetos que não estão criptografados](#metadata-tables-example-query-objects-not-encrypted)
+ [Listar objetos gerados pelo Amazon Bedrock](#metadata-tables-example-query-objects-generated-bedrock)
+ [Reconciliar a tabela de inventário com a tabela de diário](#metadata-tables-example-query-generate-latest-inventory)
+ [Como encontrar as versões atuais de seus objetos](#metadata-tables-example-query-latest-version)
+ [Unir metadados personalizados com tabelas de metadados do S3](metadata-tables-join-custom-metadata.md)
+ [Visualizar dados de tabelas de metadados com o Amazon Quick](metadata-tables-quicksight-dashboards.md)
## 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_{{general-purpose-bucket-name}}"."journal"
WHERE key LIKE '%.jpg'
AND record_type = 'CREATE'
```
### 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_{{general-purpose-bucket-name}}"."journal"
WHERE record_type = 'DELETE';
```
### 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_{{general-purpose-bucket-name}}"."journal";
```
### 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_{{general-purpose-bucket-name}}"."journal"
WHERE encryption_status NOT IN ('SSE-KMS', 'DSSE-KMS')
AND record_type = 'CREATE';
```
### 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_{{general-purpose-bucket-name}}"."journal"
WHERE record_timestamp > (current_date - interval '7' day)
AND kms_key_arn is NOT NULL;
```
### 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_{{general-purpose-bucket-name}}"."journal"
WHERE requester = 's3.amazonaws.com'
AND record_type = 'DELETE'
AND record_timestamp > (current_date - interval '1' day)
```
### Visualizar metadados fornecidos pelo Amazon Bedrock
Alguns serviços da AWS (como o [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/APIReference/welcome.html)) 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_{{general-purpose-bucket-name}}"."journal"
WHERE record_type = 'CREATE'
AND user_metadata['content-source'] = 'AmazonBedrock';
```
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_{{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;
```
## 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_{{general-purpose-bucket-name}}"."inventory"
WHERE object_tags['key1'] = 'value1'
AND object_tags['key2'] = 'value2';
```
### 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_{{general-purpose-bucket-name}}"."inventory"
WHERE encryption_status != 'SSE-KMS';
```
### 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_{{general-purpose-bucket-name}}"."inventory"
WHERE encryption_status IS NULL;
```
### 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_{{general-purpose-bucket-name}}"."inventory"
WHERE user_metadata['content-source'] = 'AmazonBedrock';
```
### 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` e `checksum_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.
Para executar essa consulta no Amazon Athena, selecione o catálogo `s3tablescatalog/aws-s3` e o banco de dados `b_{{general-purpose-bucket-name}}` para a configuração de metadados do bucket de uso geral que contém suas tabelas de diário e inventário.
```
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;
```
### Como encontrar as versões atuais de seus objetos
A consulta a seguir usa a tabela de inventário para gerar uma nova tabela de saída que mostra quais versões do objeto são atuais. A tabela de saída é intencionalmente semelhante a um relatório do Inventário S3. A tabela de saída inclui um campo `is_latest`, que indica se a versão de um objeto é a atual. O campo `is_latest` é equivalente ao campo **IsLatest** em um [relatório do Inventário S3](storage-inventory.md#storage-inventory-contents).
Essa consulta funciona para buckets de uso geral com [versionamento do S3](Versioning.md) em um estado de versionamento habilitado ou suspenso.
**Pré-requisitos**
A consulta envia os resultados a uma nova tabela do S3 para permitir consultas adicionais e aumentar o desempenho em vez de gerar linhas na tela. Portanto, antes de executar essa consulta, as condições a seguir devem ser atendidas. Se optar por não enviar os resultados a uma nova tabela, poderá ignorar essas etapas.
+ Você deve ter um bucket de tabela existente gerenciado pelo cliente com um namespace existente como local para gerar a nova tabela. Para obter mais informações, consulte [Criar um bucket de tabela](s3-tables-buckets-create.md) e [Criar namespaces](s3-tables-namespace-create.md).
+ Para consultar a nova tabela de saída, você deve configurar um método de acesso. Para obter mais informações, consulte [Acessar dados da tabela](s3-tables-access.md). Se você quiser consultar a tabela de saída com serviços de analytics da AWS, como o Amazon Athena, o bucket de tabela gerenciado pelo cliente deve estar integrado aos serviços de analytics da AWS. Para obter mais informações, consulte [Visão geral sobre a integração da funcionalidade Tabelas do Amazon S3 com serviços de analytics da AWS](s3-tables-integration-overview.md).
Para usar essa consulta, substitua `{{amzn-s3-demo-table-bucket}}` pelo nome do bucket de tabela existente gerenciado pelo cliente no qual você deseja que a nova tabela de saída seja criada. Substitua {{`existing_namespace`}} pelo nome do namespace em que você deseja que a tabela de saída seja criada em seu bucket de tabela. Substitua {{`new_table`}} pelo nome que você deseja usar para a tabela de saída. O nome da tabela de saída deve seguir as [regras de nomenclatura de tabela](s3-tables-buckets-naming.md#naming-rules-table).
Para executar essa consulta no Amazon Athena, selecione o catálogo `s3tablescatalog/aws-s3` e o banco de dados `b_{{general-purpose-bucket-name}}` para a configuração de metadados do bucket de uso geral que contém a tabela de inventário.
```
-- 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:
);
```