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

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.

Essa consulta funciona para buckets de uso geral com versionamento do S3 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.

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.

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