# Consultas de ejemplo de tabla de metadatos
En los siguientes ejemplos se muestra cómo puede obtener diferentes tipos de información de las tablas de Metadatos de S3 mediante el uso de consultas SQL estándar.
Recuerde al usar estos ejemplos:
+ Los ejemplos se han escrito para funcionar con Amazon Athena. Es posible que tenga que modificar los ejemplos para que funcionen con un motor de consulta diferente.
+ Asegúrese de que entiende cómo [optimizar las consultas](metadata-tables-optimizing-query-performance.md).
+ Reemplace `b_general-purpose-bucket-name` por el nombre del espacio de nombres.
+ Para ver una lista completa de las columnas admitidas, consulte [Esquema de tablas de diario de Metadatos de S3](metadata-tables-schema.md) y [Esquema de las tablas de inventario en directo de Metadatos de S3](metadata-tables-inventory-schema.md).
**Contents**
+ [Consultas de ejemplo de tablas de diario](#metadata-tables-example-queries-journal-tables)
+ [Búsqueda de objetos por extensión de archivo](#metadata-tables-example-query-object-pattern)
+ [Enumeración de las eliminaciones de objetos](#metadata-tables-example-query-delete-events)
+ [Enumeración de las claves de cifrado de AWS KMS que utilizan los objetos](#metadata-tables-example-query-objects-using-kms-key)
+ [Enumeración de los objetos que no utilizan claves de KMS](#metadata-tables-example-query-objects-not-using-kms-key)
+ [Enumeración de claves de cifrado de AWS KMS utilizadas para operaciones `PUT` en los últimos siete días](#metadata-tables-example-query-objects-using-kms-key-puts)
+ [Enumeración de objetos eliminados en las últimas 24 horas por S3 Lifecycle](#metadata-tables-example-query-objects-deleted-lifecycle)
+ [Visualización de los metadatos proporcionados por Amazon Bedrock](#metadata-tables-example-query-bedrock)
+ [Descripción del estado actual de los objetos](#metadata-tables-example-query-current-state)
+ [Consultas de ejemplo de tablas de inventario](#metadata-tables-example-queries-inventory-tables)
+ [Detección de conjuntos de datos que utilizan etiquetas específicas](#metadata-tables-example-query-datasets-specific-tags)
+ [Enumeración de objetos no cifrados con SSE-KMS](#metadata-tables-example-query-objects-not-kms-encrypted)
+ [Muestra de objetos que no están cifrados](#metadata-tables-example-query-objects-not-encrypted)
+ [Enumeración de objetos generados por Amazon Bedrock](#metadata-tables-example-query-objects-generated-bedrock)
+ [Conciliación de la tabla de inventario con la tabla de diario](#metadata-tables-example-query-generate-latest-inventory)
+ [Búsqueda de las versiones actuales de los objetos](#metadata-tables-example-query-latest-version)
+ [Unión de metadatos personalizados con tablas de metadatos de S3](metadata-tables-join-custom-metadata.md)
+ [Visualización de datos de tablas de metadatos con Amazon Quick](metadata-tables-quicksight-dashboards.md)
## Consultas de ejemplo de tablas de diario
Puede utilizar las siguientes consultas de ejemplo para consultar las tablas de diario.
### Búsqueda de objetos por extensión de archivo
La siguiente consulta devuelve objetos con una extensión de archivo específica (`.jpg` en este caso):
```
SELECT key FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal"
WHERE key LIKE '%.jpg'
AND record_type = 'CREATE'
```
### Enumeración de las eliminaciones de objetos
La siguiente consulta devuelve eventos de eliminación de objetos, incluido el ID de Cuenta de AWS o la entidad principal de servicio de AWS que realizó la solicitud:
```
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';
```
### Enumeración de las claves de cifrado de AWS KMS que utilizan los objetos
La siguiente consulta devuelve los ARN de las claves de AWS Key Management Service (AWS KMS) que cifran los objetos:
```
SELECT DISTINCT kms_key_arn
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal";
```
### Enumeración de los objetos que no utilizan claves de KMS
La siguiente consulta devuelve objetos que no están cifrados con claves de 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';
```
### Enumeración de claves de cifrado de AWS KMS utilizadas para operaciones `PUT` en los últimos siete días
La siguiente consulta devuelve los ARN de las claves de AWS Key Management Service (AWS KMS) que cifran los 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;
```
### Enumeración de objetos eliminados en las últimas 24 horas por S3 Lifecycle
La siguiente consulta devuelve una lista de los objetos que S3 Lifecycle ha hecho caducar en el último día:
```
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)
```
### Visualización de los metadatos proporcionados por Amazon Bedrock
Algunos servicios de AWS (como [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/APIReference/welcome.html)), cargan objetos en Amazon S3. Puede consultar los metadatos de los objetos proporcionados por estos servicios. Por ejemplo, la siguiente consulta incluye la columna `user_metadata` para determinar si hay objetos cargados por Amazon Bedrock en un bucket de uso general:
```
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';
```
Si Amazon Bedrock ha cargado un objeto en el bucket, la columna `user_metadata` mostrará los siguientes metadatos asociados al objeto en el resultado de la consulta:
```
user_metadata
{content-additional-params -> requestid="CVK8FWYRW0M9JW65", signedContentSHA384="38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b", content-model-id -> bedrock-model-arn, content-source -> AmazonBedrock}
```
### Descripción del estado actual de los objetos
La siguiente consulta puede ayudarlo a determinar el estado actual de los objetos. La consulta identifica la versión más reciente de cada objeto, filtra los objetos eliminados y marca la última versión de cada objeto según los números de secuencia. Los resultados se ordenan por las columnas `bucket`, `key` y `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 ejemplo de tablas de inventario
Puede utilizar las siguientes consultas de ejemplo para consultar las tablas de inventario.
### Detección de conjuntos de datos que utilizan etiquetas específicas
La siguiente consulta devuelve el conjunto de datos que utiliza las etiquetas especificadas:
```
SELECT *
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE object_tags['key1'] = 'value1'
AND object_tags['key2'] = 'value2';
```
### Enumeración de objetos no cifrados con SSE-KMS
La siguiente consulta devuelve objetos que no están cifrados con SSE-KMS:
```
SELECT key, encryption_status
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE encryption_status != 'SSE-KMS';
```
### Muestra de objetos que no están cifrados
La siguiente consulta devuelve objetos que no están cifrados:
```
SELECT bucket, key, version_id
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE encryption_status IS NULL;
```
### Enumeración de objetos generados por Amazon Bedrock
La siguiente consulta enumera los objetos que Amazon Bedrock ha generado:
```
SELECT DISTINCT bucket, key, sequence_number, user_metadata
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE user_metadata['content-source'] = 'AmazonBedrock';
```
### Conciliación de la tabla de inventario con la tabla de diario
En la siguiente consulta, se genera una lista similar a una tabla de inventario que está actualizada con el contenido actual del bucket. De forma más precisa, la lista resultante combina la última instantánea de la tabla de inventario con los últimos eventos de la tabla de diario.
Para que esta consulta produzca los resultados más precisos, las tablas de diario y las de inventario deben estar en estado Activo.
Recomendamos utilizar esta consulta para buckets de propósito general que contienen menos de mil millones de objetos (10^9).
En esta consulta de ejemplo, se aplican las siguientes simplificaciones a los resultados de la lista (en comparación con la tabla de inventario):
+ **Omisiones de columnas**: las columnas `bucket`, `is_multipart`, `encryption_status`, `is_bucket_key_enabled`, `kms_key_arn` y `checksum_algorithm` no forman parte de los resultados finales. El mantenimiento del conjunto de columnas opcionales al mínimo mejora el rendimiento.
+ **Inclusión de todos los registros**: la consulta devuelve todas las claves y versiones del objeto, incluida la versión nula (en buckets sin control de versiones o con control de versiones suspendido) y los marcadores de eliminación. Para ver ejemplos de cómo filtrar los resultados para mostrar solo las claves que le interesan, consulte la cláusula `WHERE` al final de la consulta.
+ **Reconciliación acelerada**: en raras ocasiones, la consulta podría informar temporalmente de objetos que ya no están en el bucket. Esas discrepancias se eliminan en cuanto está disponible la siguiente instantánea de la tabla de inventario. Este comportamiento supone una compensación entre el rendimiento y la precisión.
Para ejecutar esta consulta en Amazon Athena, asegúrese de seleccionar el catálogo `s3tablescatalog/aws-s3` y la base de datos `b_general-purpose-bucket-name` para la configuración de metadatos del bucket de uso general que contiene las tablas de inventario y diario.
```
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;
```
### Búsqueda de las versiones actuales de los objetos
La siguiente consulta utiliza la tabla de inventario para generar una nueva tabla de resultados que muestra qué versiones de objetos son actuales. La tabla de resultados es intencionalmente similar a un informe de inventario de S3. La tabla de resultados incluye un campo `is_latest` que indica si un objeto es la versión actual. El campo `is_latest` equivale al campo **IsLatest** en un [informe de inventario de S3](storage-inventory.md#storage-inventory-contents).
Esta consulta funciona para los buckets de uso general con el [control de versiones de S3](Versioning.md) habilitado o suspendido.
**Requisitos previos**
La consulta envía los resultados a una nueva tabla de S3 para admitir más consultas y obtener un mayor rendimiento en comparación con la generación de filas en pantalla. Por lo tanto, antes de ejecutar esta consulta, asegúrese de que cumple las siguientes condiciones. Si elige no enviar los resultados a una nueva tabla, puede omitir estos pasos.
+ Debe tener un bucket de tablas administrado por el cliente con un espacio de nombres existente como lugar para generar la nueva tabla. Para obtener más información, consulte [Crear un bucket de tablas](s3-tables-buckets-create.md) y [Creación de un espacio de nombres](s3-tables-namespace-create.md).
+ Para consultar la nueva tabla de resultados, debe configurar un método de acceso para consultarla. Para obtener más información, consulte [Acceso a los datos de tablas](s3-tables-access.md). Si desea consultar la tabla de resultados con servicios de análisis de AWS como Amazon Athena, el bucket de tablas administrado por el cliente debe estar integrado con los servicios de análisis de AWS. Para obtener más información, consulte [Integración de tablas de Amazon S3 con información general de servicios de análisis de AWS](s3-tables-integration-overview.md).
Para usar esta consulta, sustituya `amzn-s3-demo-table-bucket` por el nombre del bucket de tablas existente administrado por el cliente en el que deseas que se cree la nueva tabla de resultados. Sustituya *`existing_namespace`* por el nombre del espacio de nombres en el que desea que se cree la tabla de resultados en el bucket de tablas. Sustituya *`new_table`* por el nombre que desea usar para la tabla de resultados. Asegúrese de que el nombre de la tabla de resultados siga las [reglas de nomenclatura de las tablas](s3-tables-buckets-naming.md#naming-rules-table).
Para ejecutar esta consulta en Amazon Athena, asegúrese de seleccionar el catálogo `s3tablescatalog/aws-s3` y la base de datos `b_general-purpose-bucket-name` para la configuración de metadatos del bucket de uso general que contiene la tabla de inventario.
```
-- 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:
);
```