Exemples de requêtes de table 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 :

Exemples d’interrogation de table de journal

Vous pouvez utiliser les exemples de requête suivants pour interroger vos tables de journal.

Recherche d’objets par extension de fichier

La requête suivante renvoie les objets associés à une extension de fichier spécifique (.jpg dans 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’ID du Compte AWS ou le principal du service AWS à 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';

Affichage de la liste des clés de chiffrement AWS KMS utilisées par vos objets

La requête suivante renvoie les ARN des 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 les objets qui ne sont pas chiffrés à l’aide de clés 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';

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

La requête suivante renvoie les ARN des 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 que S3 Lifecycle a fait expirer la veille :

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 services AWS (comme Amazon Bedrock) chargent des objets dans Amazon S3. Vous pouvez interroger les métadonnées des objets fournies par ces services. Par exemple, la requête suivante inclut la colonne user_metadata permettant de déterminer si des objets ont été chargés par Amazon Bedrock dans un compartiment à 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 d’interrogation de table d’inventaire

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

Identification des jeux de données qui utilisent des balises spécifiques

La requête suivante renvoie le jeu 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';

Liste des objets non chiffrés avec SSE-KMS

La requête suivante renvoie les 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';

Liste des objets non chiffrés

La requête suivante renvoie les 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;

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

Rapprochement de la table d’inventaire et de la table de journal

La requête suivante génère une liste semblable à une table d’inventaire mise à jour avec le contenu actuel du compartiment. Plus précisément, la liste obtenue combine le dernier instantané de la table d’inventaire avec les derniers événements de la table de journal.

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

Nous recommandons d’utiliser cette requête pour les compartiments à 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) :

Pour exécuter cette requête dans Amazon Athena, assurez-vous de sélectionner le catalogue s3tablescatalog/aws-s3 et la base de données b_general-purpose-bucket-name pour la configuration des métadonnées du compartiment à usage général contenant vos tables de journal et d’inventaire.

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;

Recherche des versions en cours de vos objets

La requête suivante utilise la table d’inventaire pour générer une nouvelle table de sortie qui indique les versions en cours des objets. La table de sortie est intentionnellement semblable à un rapport S3 Inventory. La table de sortie inclut un champ is_latest qui indique si l’objet est la version en cours. Le champ is_latest est équivalent au champ IsLatest d’un rapport S3 Inventory.

Cette requête fonctionne pour les compartiments à usage général dont la gestion des versions S3 est activée ou suspendue.

Prérequis

La requête affiche les résultats dans une nouvelle table S3 afin de prendre en charge d’autres requêtes et d’améliorer les performances par rapport à l’affichage de lignes à l’écran. Par conséquent, avant d’exécuter cette requête, assurez-vous de remplir les conditions suivantes. Si vous choisissez de ne pas afficher les résultats dans une nouvelle table, vous pouvez ignorer ces étapes.

Pour utiliser cette requête, remplacez amzn-s3-demo-table-bucket par le nom du compartiment de table géré par le client existant dans lequel vous souhaitez créer la table de sortie. Remplacez existing_namespace par le nom de l’espace de noms dans lequel vous souhaitez créer la table de sortie dans votre compartiment de table. Remplacez new_table par le nom que vous souhaitez donner à votre table de sortie. Assurez-vous que le nom de votre table de sortie respecte les règles de dénomination des tables.

Pour exécuter cette requête dans Amazon Athena, assurez-vous de sélectionner le catalogue s3tablescatalog/aws-s3 et la base de données b_general-purpose-bucket-name pour la configuration des métadonnées du compartiment à usage général contenant votre table d’inventaire.

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