Esempi di query di tabelle di metadati

Gli esempi seguenti mostrano come sia possibile ottenere informazioni di diverso tipo dalle tabelle dei metadati S3 utilizzando query SQL standard.

Quando utilizzi questi esempi, ricorda che:

Query sulle tabelle del diario di esempio

È possibile utilizzare le seguenti query di esempio per eseguire query sulle tabelle del diario.

Ricerca di oggetti per estensione di file

La seguente query restituisce gli oggetti con una specifica estensione di file (in questo caso .jpg):

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

Elenco delle eliminazioni di oggetti

La seguente query restituisce gli eventi di eliminazione degli oggetti, compreso l’ID dell’Account AWS o il principale del servizio AWS che ha effettuato la richiesta:

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

Elenco delle chiavi di crittografia AWS KMS utilizzate dagli oggetti

La seguente query restituisce gli ARN delle chiavi AWS Key Management Service (AWS KMS) che eseguono la crittografia degli oggetti:

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

Elenco di oggetti che non utilizzano le chiavi KMS

La seguente query restituisce gli oggetti non crittografati con le chiavi 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';

Elenco delle chiavi di crittografia AWS KMS utilizzate per operazioni PUT negli ultimi 7 giorni

La seguente query restituisce gli ARN delle chiavi AWS Key Management Service (AWS KMS) che eseguono la crittografia degli oggetti:

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;

Elenco degli oggetti eliminati nelle ultime 24 ore dal ciclo di vita S3

La seguente query restituisce l’elenco degli oggetti scaduti nell’ultimo giorno del ciclo di vita 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)

Visualizzazione dei metadati forniti da Amazon Bedrock

Alcuni servizi AWS (come Amazon Bedrock) caricano gli oggetti su Amazon S3. È possibile interrogare i metadati degli oggetti forniti da questi servizi. Ad esempio, la seguente query include la colonna user_metadata per determinare se vi sono oggetti caricati da Amazon Bedrock in un bucket per uso generico:

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 Amazon Bedrock ha caricato un oggetto nel bucket, la colonna user_metadata mostrerà i seguenti metadati associati all'oggetto nel risultato della query:

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

Comprensione dello stato attuale degli oggetti

La seguente query può aiutare a determinare lo stato attuale degli oggetti. La query identifica la versione più recente di ogni oggetto, filtra gli oggetti eliminati e contrassegna la versione più recente di ogni oggetto in base ai numeri di sequenza. I risultati sono ordinati in base alle colonne 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;

Query sulle tabelle di inventario di esempio

È possibile utilizzare le seguenti query di esempio per eseguire query sulle tabelle di inventario.

Individuazione dei set di dati che utilizzano tag specifici

La query seguente restituisce il set di dati che utilizza i tag specificati:

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

Elenco degli oggetti non crittografati con SSE-KMS

La seguente query restituisce gli oggetti non crittografati con SSE-KMS:

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

Elenco degli oggetti non crittografati

La seguente query restituisce gli oggetti non crittografati:

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

Elenco degli oggetti generati da Amazon Bedrock

La seguente query elenca gli oggetti generati da 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';

Riconciliazione della tabella di inventario con la tabella del diario

La seguente query genera un elenco simile a una tabella di inventario aggiornato con il contenuto corrente del bucket. Più precisamente, l’elenco risultante combina l’ultimo snapshot della tabella di inventario con gli eventi più recenti della tabella del diario.

Affinché questa query produca risultati accurati, sia la tabella del diario che quella di inventario devono essere nello stato Attivo.

È consigliabile utilizzare questa query per bucket per uso generico contenenti meno di un miliardo (10^9) di oggetti.

Questa query di esempio applica le seguenti semplificazioni ai risultati dell’elenco (rispetto alla tabella di inventario):

Per eseguire questa query in Amazon Athena, assicurati di selezionare il catalogo s3tablescatalog/aws-s3 e il database b_general-purpose-bucket-name per la configurazione dei metadati del bucket per uso generico che contiene le tabelle del diario e di inventario.

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;

Individuazione delle versioni correnti degli oggetti

La seguente query utilizza la tabella di inventario per generare una nuova tabella di output che mostra le versioni correnti degli oggetti. La tabella di output è intenzionalmente simile a un report di Inventario S3. La tabella di output include un campo is_latest che indica se l’oggetto è alla versione corrente. Il campo is_latest è equivalente al campo IsLatest di un report di Inventario S3.

Questa query funziona per bucket per uso generico con Controllo delle versioni S3 nello stato di controllo delle versioni abilitato o di controllo delle versioni sospeso.

Prerequisiti

La query restituisce i risultati in una nuova tabella S3 per supportare ulteriori query e per ottenere prestazioni più elevate rispetto all’output di righe sullo schermo. Pertanto, prima di eseguire questa query, assicurati che siano soddisfatte le seguenti condizioni. Se scegli di non inviare i risultati in una nuova tabella, puoi saltare queste fasi.

Per utilizzare questa query, sostituisci amzn-s3-demo-table-bucket con il nome del bucket di tabelle gestito dal cliente esistente in cui desideri creare la nuova tabella di output. Sostituisci existing_namespace con il nome del namespace in cui desideri creare la tabella di output nel bucket di tabelle. Sostituisci new_table con il nome che desideri utilizzare per la tabella di output. Assicurati che il nome della tabella di output segua le regole di denominazione delle tabelle.

Per eseguire questa query in Amazon Athena, assicurati di selezionare il catalogo s3tablescatalog/aws-s3 e il database b_general-purpose-bucket-name per la configurazione dei metadati del bucket per uso generico che contiene la tabella di inventario.

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