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:

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) 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):

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