Exemples de requêtes dans une table de journal Exemples de requêtes de table d'inventaire

Exemples de requêtes de tables de métadonnées

Les exemples suivants montrent comment obtenir différents types d’informations à partir de vos tables de métadonnées S3 à l’aide de requêtes SQL standard.

Gardez à l’esprit les points suivants lorsque vous utilisez ces exemples :

Ces exemples sont conçus pour fonctionner avec Amazon Athena. Vous devrez peut-être les modifier pour qu’ils fonctionnent avec un autre moteur de requête.
Assurez-vous de comprendre comment optimiser vos requêtes.
Remplacez b_general-purpose-bucket-name par le nom de votre espace de noms.
Pour obtenir la liste complète des colonnes prises en charge, consultez le Schéma des tables de journal des métadonnées S3 etSchéma des tables d'inventaire en temps réel des métadonnées S3.

Table des matières

Exemples de requêtes dans une table de journal

Vous pouvez utiliser les exemples de requêtes suivants pour interroger les tables de votre journal.

Recherche d’objets par extension de fichier

La requête suivante renvoie des objets dotés d'une extension de fichier spécifique (.jpgdans ce cas) :


SELECT key FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal"
WHERE key LIKE '%.jpg'
AND record_type = 'CREATE'

Affichage de la liste des suppressions d’objets

La requête suivante renvoie les événements de suppression d'objets, y compris l' Compte AWS identifiant ou le principal AWS de service à l'origine de la demande :


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

Liste des clés de AWS KMS chiffrement utilisées par vos objets

La requête suivante renvoie ARNs les clés AWS Key Management Service (AWS KMS) chiffrant vos objets :


SELECT DISTINCT kms_key_arn
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."journal";

Affichage de la liste des objets qui n’utilisent pas de clés KMS

La requête suivante renvoie des objets qui ne sont pas chiffrés à l'aide de AWS KMS clés :


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

Liste des clés de AWS KMS chiffrement utilisées pour les `PUT` opérations au cours des 7 derniers jours

La requête suivante renvoie ARNs les clés AWS Key Management Service (AWS KMS) chiffrant vos objets :


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;

Liste des objets supprimés au cours des dernières 24 heures par S3 Lifecycle

La requête suivante renvoie la liste des objets expirés le dernier jour par S3 Lifecycle :


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)

Affichage de la liste des métadonnées fournies par Amazon Bedrock

Certains AWS services (comme Amazon Bedrock) chargent des objets sur Amazon S3. Vous pouvez interroger les métadonnées des objets fournies par ces services. Par exemple, la requête suivante inclut la user_metadata colonne permettant de déterminer si des objets ont été chargés par Amazon Bedrock dans un bucket à usage général :


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 a chargé un objet dans votre compartiment, la colonne user_metadata affiche les métadonnées suivantes associées à cet objet dans le résultat de la requête :


user_metadata
{content-additional-params -> requestid="CVK8FWYRW0M9JW65", signedContentSHA384="38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b", content-model-id -> bedrock-model-arn, content-source -> AmazonBedrock}

Identification de l’état actuel de vos objets

La requête suivante peut vous aider à déterminer l’état actuel de vos objets. Cette requête identifie la version la plus récente de chaque objet, filtre les objets supprimés et marque la dernière version de chacun d’eux selon des numéros de séquence. Les résultats sont triés en fonction des colonnes bucket, key et 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;

Exemples de requêtes de table d'inventaire

Vous pouvez utiliser les exemples de requêtes suivants pour interroger vos tables d'inventaire.

Découvrir les ensembles de données qui utilisent des balises spécifiques

La requête suivante renvoie l'ensemble de données qui utilise les balises spécifiées :


SELECT * 
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE object_tags['key1'] = 'value1'
AND object_tags['key2'] = 'value2';

Répertorier les objets non chiffrés avec SSE-KMS

La requête suivante renvoie des objets qui ne sont pas chiffrés avec SSE-KMS :


SELECT key, encryption_status 
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE encryption_status != 'SSE-KMS';

Répertorier les objets non chiffrés

La requête suivante renvoie des objets qui ne sont pas chiffrés :


SELECT bucket, key, version_id  
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE encryption_status IS NULL;

Répertorier les objets générés par Amazon Bedrock

La requête suivante répertorie les objets générés par 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';

Réconcilier le tableau d'inventaire avec le tableau du journal

La requête suivante génère une inventory-table-like liste actualisée avec le contenu actuel du bucket. Plus précisément, la liste qui en résulte combine le dernier instantané du tableau d'inventaire avec les derniers événements du tableau du journal.

Pour que cette requête produise les résultats les plus précis, le journal et les tables d'inventaire doivent avoir le statut Actif.

Nous recommandons d'utiliser cette requête pour les buckets à usage général contenant moins d'un milliard (10^9) objets.

Cet exemple de requête applique les simplifications suivantes aux résultats de la liste (par rapport à la table d'inventaire) :

Omissions dans les colonnes : les colonnes bucket is_multipartencryption_status,is_bucket_key_enabled,kms_key_arn,, et checksum_algorithm ne font pas partie des résultats finaux. Le fait de limiter au maximum l'ensemble de colonnes facultatives améliore les performances.
Inclusion de tous les enregistrements : la requête renvoie toutes les clés et versions des objets, y compris la version nulle (dans les compartiments non versionnés ou suspendus aux versions) et les marqueurs de suppression. Pour des exemples de filtrage des résultats afin d'afficher uniquement les clés qui vous intéressent, consultez la WHERE clause à la fin de la requête.
Réconciliation accélérée : dans de rares cas, la requête peut signaler temporairement des objets qui ne se trouvent plus dans le compartiment. Ces écarts sont éliminés dès que le prochain instantané du tableau d'inventaire est disponible. Ce comportement est un compromis entre performance et précision.


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

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Optimisation des performances des requêtes

Jonction de métadonnées personnalisées