Consultas de ejemplo de tablas de diario Consultas de ejemplo de tablas de inventario

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.
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 y Esquema de las tablas de inventario en directo de Metadatos de S3.

Contenido

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), 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';

Generación de la tabla de inventario más reciente

La siguiente consulta genera la tabla de inventario más reciente. Esta consulta funciona cuando la configuración de metadatos se encuentra en estado Activo. Esta consulta requiere que tanto la tabla de diario como la de inventario se encuentren en estado Activo. Si la tabla de inventario no está actualizada debido a permisos u otros problemas, es posible que esta consulta no funcione.

Recomendamos utilizar esta consulta para buckets de uso general con menos de mil millones de objetos.

Esta consulta reconcilia el contenido de la tabla de inventario con los eventos recientes de la tabla de diario. Cuando el diario se actualiza con todos los cambios que se han producido en el bucket, el resultado de la consulta coincidirá con el contenido del bucket.

Este ejemplo restringe la salida solo a aquellas claves que terminan en '%.txt'. Para consultar un subconjunto diferente, puede ajustar la expresión de tabla común denominada "working_set_of_interest".


WITH inventory_time_cte AS (
    -- Reveal the extent of the journal table that has not yet been reflected in the inventory table.
    SELECT COALESCE(inventory_time_from_property, inventory_time_default) AS inventory_time FROM
    (
      SELECT * FROM
        -- The fallback default includes the entirety of the journal table.
        (VALUES (TIMESTAMP '2024-12-01 00:00')) AS T (inventory_time_default)
      LEFT OUTER JOIN
        -- This side queries the Iceberg table property and loads it up in
        -- a column. If the property doesn't exist, then you get 0 rows.
        (
         SELECT from_unixtime(CAST(value AS BIGINT)) AS inventory_time_from_property FROM "journal$properties"
         WHERE key = 'aws.s3metadata.oldest-uncoalesced-record-timestamp' LIMIT 1
        )
      -- Force an unequivocal join.
      ON TRUE
    )
),

-- Select only those journal table events not yet reflected in the inventory table.
my_new_events AS (
    SELECT journal.* FROM 
        (
              journal
            JOIN
              inventory_time_cte 
            -- Include only those rows that have yet to be merged with the inventory table.
            -- Allow some overlap to account for clock skew.
            ON record_timestamp > (inventory_time - interval '1' hour)
        )
),

-- Bring the "journal" and "inventory" table rows to a common inventory schema. 
working_set AS (
    (
        SELECT
            -- Keep the inventory table columns, but drop these journal table columns:
            --   "record_type", "requester", "source_ip_address", "request_id"
            bucket, key, sequence_number,
            version_id,
            is_delete_marker,
            size,
            COALESCE(last_modified_date, record_timestamp) AS last_modified_date,
            e_tag,
            storage_class,
            is_multipart,
            encryption_status,
            is_bucket_key_enabled,
            kms_key_arn,
            checksum_algorithm,
            object_tags,
            user_metadata,
            -- Temporary columns required to align the two tables.
            record_timestamp AS _log_ts,
            (record_type = 'DELETE' AND NOT COALESCE(is_delete_marker, FALSE)) AS _is_perm_delete
        FROM my_new_events
    ) UNION (
            SELECT 
                *,
                last_modified_date as _log_ts,
                FALSE AS _is_perm_delete
            FROM "inventory"
    )
),

-- You can apply a filter over key, tags, or metadata here to restrict your view to a subset of all keys.
working_set_of_interest AS (
    SELECT * FROM working_set WHERE key LIKE '%.txt'
),

most_recent_changes AS (
  -- For each (bucket, key, version_id) stack, find the event that should have
  -- been the ultimate to arrive in the journal table, and confine the results to the
  -- 1-hour window of events (for that key) that preceded that arrival.
  --
  -- This gives preferential treatment to events that arrived later in the journal table
  -- order, and handles cases with uploads that were completed much later after they were
  -- initiated.
  SELECT * FROM (
    SELECT *,
           -- Do not confuse this MAX() with the aggregate function. This is the MAX window function.
           MAX(_log_ts) OVER (PARTITION BY bucket, key, version_id) AS _supremum_ts
    FROM working_set_of_interest
  )
  WHERE _log_ts >= (_supremum_ts - interval '1' hour)
),

-- Among each "1-hour window of most recent mutations" for a given key, identify
-- the one that is reflected in the general purpose bucket.
updated_inventory AS (
  SELECT * FROM (
    SELECT *,
           MAX(sequence_number) OVER (PARTITION BY bucket, key, version_id) AS _supremum_sn
    FROM most_recent_changes
  )
  WHERE sequence_number = _supremum_sn -- Again here, use QUALIFY clause if your planner supports it.
)

-- Finally, project the resulting rows onto the inventory table schema.
SELECT
    bucket,
    key,
    sequence_number,
    version_id,
    is_delete_marker,
    size,
    last_modified_date,
    e_tag,
    storage_class,
    is_multipart,
    encryption_status,
    is_bucket_key_enabled,
    kms_key_arn,
    checksum_algorithm,
    object_tags,
    user_metadata
FROM updated_inventory
WHERE NOT _is_perm_delete
ORDER BY bucket, key ASC, sequence_number ASC

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Optimización del rendimiento de las consultas

Unión de metadatos personalizados