Domande di esempio relative alla tabella Journal Domande di esempio relative alla tabella di inventario

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:

Gli esempi sono scritti per funzionare con Amazon Athena. Potrebbe essere necessario modificare gli esempi per lavorare con un motore di query diverso.
Assicurati di capire come ottimizzare le query.
b_general-purpose-bucket-nameSostituiscilo con il nome del tuo namespace.
Per un elenco completo delle colonne supportate, consulta e. Schema delle tabelle del journal S3 Metadata Schema delle tabelle di inventario in tempo reale di S3 Metadata

Indice

Domande di esempio relative alla tabella Journal

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

Ricerca di oggetti per estensione di file

La seguente query restituisce oggetti con un'estensione di file specifica (.jpgin questo caso):


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, incluso l' Account AWS ID o il principale del AWS servizio 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 AWS KMS crittografia utilizzate dai tuoi oggetti

La seguente query restituisce ARNs le chiavi AWS Key Management Service (AWS KMS) che crittografano gli 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 oggetti che non sono crittografati con AWS KMS chiavi:


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 AWS KMS crittografia utilizzate per `PUT` le operazioni negli ultimi 7 giorni

La seguente query restituisce ARNs le chiavi AWS Key Management Service (AWS KMS) che crittografano gli 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 da S3 Lifecycle

La seguente query restituita elenca gli oggetti scaduti nell'ultimo giorno da 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)

Visualizzazione dei metadati forniti da Amazon Bedrock

Alcuni AWS servizi (come Amazon Bedrock) caricano oggetti su Amazon S3. È possibile interrogare i metadati degli oggetti forniti da questi servizi. Ad esempio, la seguente query include la user_metadata colonna per determinare se ci sono oggetti caricati da Amazon Bedrock in un bucket 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;

Domande di esempio relative alla tabella di inventario

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

Scoperta di set di dati che utilizzano tag specifici

La seguente query 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 di oggetti non crittografati con SSE-KMS

La seguente query restituisce 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';

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

Generazione della tabella di inventario più recente

La seguente query genera la tabella di inventario più recente. Questa query funziona quando la configurazione dei metadati è in stato Attivo. Questa query richiede che sia la tabella del diario che quella dell'inventario siano nello stato Attivo. Se la tabella dell'inventario non è aggiornata a causa di autorizzazioni o altri problemi, questa query potrebbe non funzionare.

Ti consigliamo di utilizzare questa query per bucket generici con meno di un miliardo di oggetti.

Questa query riconcilia il contenuto della tabella di inventario con gli eventi recenti della tabella del diario. Quando il diario viene aggiornato con tutte le modifiche apportate nel bucket, il risultato della query corrisponderà a quello del contenuto del bucket.

Questo esempio limita l'output solo ai tasti che terminano con. '%.txt' Per interrogare un sottoinsieme diverso, è possibile modificare l'espressione di tabella comune denominata. "working_set_of_interest"


WITH inventory_time_cte AS (
    -- Reveal the extent of the journal table that has not yet been reflected in the inventory table.
    SELECT COALESCE(inventory_time_from_property, inventory_time_default) AS inventory_time FROM
    (
      SELECT * FROM
        -- The fallback default includes the entirety of the journal table.
        (VALUES (TIMESTAMP '2024-12-01 00:00')) AS T (inventory_time_default)
      LEFT OUTER JOIN
        -- This side queries the Iceberg table property and loads it up in
        -- a column. If the property doesn't exist, then you get 0 rows.
        (
         SELECT from_unixtime(CAST(value AS BIGINT)) AS inventory_time_from_property FROM "journal$properties"
         WHERE key = 'aws.s3metadata.oldest-uncoalesced-record-timestamp' LIMIT 1
        )
      -- Force an unequivocal join.
      ON TRUE
    )
),

-- Select only those journal table events not yet reflected in the inventory table.
my_new_events AS (
    SELECT journal.* FROM 
        (
              journal
            JOIN
              inventory_time_cte 
            -- Include only those rows that have yet to be merged with the inventory table.
            -- Allow some overlap to account for clock skew.
            ON record_timestamp > (inventory_time - interval '1' hour)
        )
),

-- Bring the "journal" and "inventory" table rows to a common inventory schema. 
working_set AS (
    (
        SELECT
            -- Keep the inventory table columns, but drop these journal table columns:
            --   "record_type", "requester", "source_ip_address", "request_id"
            bucket, key, sequence_number,
            version_id,
            is_delete_marker,
            size,
            COALESCE(last_modified_date, record_timestamp) AS last_modified_date,
            e_tag,
            storage_class,
            is_multipart,
            encryption_status,
            is_bucket_key_enabled,
            kms_key_arn,
            checksum_algorithm,
            object_tags,
            user_metadata,
            -- Temporary columns required to align the two tables.
            record_timestamp AS _log_ts,
            (record_type = 'DELETE' AND NOT COALESCE(is_delete_marker, FALSE)) AS _is_perm_delete
        FROM my_new_events
    ) UNION (
            SELECT 
                *,
                last_modified_date as _log_ts,
                FALSE AS _is_perm_delete
            FROM "inventory"
    )
),

-- You can apply a filter over key, tags, or metadata here to restrict your view to a subset of all keys.
working_set_of_interest AS (
    SELECT * FROM working_set WHERE key LIKE '%.txt'
),

most_recent_changes AS (
  -- For each (bucket, key, version_id) stack, find the event that should have
  -- been the ultimate to arrive in the journal table, and confine the results to the
  -- 1-hour window of events (for that key) that preceded that arrival.
  --
  -- This gives preferential treatment to events that arrived later in the journal table
  -- order, and handles cases with uploads that were completed much later after they were
  -- initiated.
  SELECT * FROM (
    SELECT *,
           -- Do not confuse this MAX() with the aggregate function. This is the MAX window function.
           MAX(_log_ts) OVER (PARTITION BY bucket, key, version_id) AS _supremum_ts
    FROM working_set_of_interest
  )
  WHERE _log_ts >= (_supremum_ts - interval '1' hour)
),

-- Among each "1-hour window of most recent mutations" for a given key, identify
-- the one that is reflected in the general purpose bucket.
updated_inventory AS (
  SELECT * FROM (
    SELECT *,
           MAX(sequence_number) OVER (PARTITION BY bucket, key, version_id) AS _supremum_sn
    FROM most_recent_changes
  )
  WHERE sequence_number = _supremum_sn -- Again here, use QUALIFY clause if your planner supports it.
)

-- Finally, project the resulting rows onto the inventory table schema.
SELECT
    bucket,
    key,
    sequence_number,
    version_id,
    is_delete_marker,
    size,
    last_modified_date,
    e_tag,
    storage_class,
    is_multipart,
    encryption_status,
    is_bucket_key_enabled,
    kms_key_arn,
    checksum_algorithm,
    object_tags,
    user_metadata
FROM updated_inventory
WHERE NOT _is_perm_delete
ORDER BY bucket, key ASC, sequence_number ASC

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Ottimizzazione delle prestazioni delle query

Unione di metadati personalizzati