Beispielabfragen für Journaltabellen Beispielabfragen zur Inventartabelle

Beispiel für Abfragen von Metadatentabellen

Die folgenden Beispiele zeigen, wie Sie mithilfe von Standard-SQL-Abfragen Informationen verschiedener Typen aus Ihren S3-Metadata-Tabellen abrufen können.

Denken Sie an Folgendes, wenn Sie diese Beispiele verwenden:

Die Beispiele wurden für die Verwendung mit Amazon Athena geschrieben. Möglicherweise müssen Sie die Beispiele ändern, damit sie mit einer anderen Abfrage-Engine funktionieren.
Stellen Sie sicher, dass Sie wissen, wie Sie Ihre Abfragen optimieren können.
b_general-purpose-bucket-nameErsetzen Sie es durch den Namen Ihres Namespaces.
Eine vollständige Liste der unterstützten Spalten finden Sie unter Schema der Journaltabellen in S3, Metadaten undSchema der Live-Inventartabellen für S3-Metadaten.

Inhalt

Beispielabfragen für Journaltabellen

Sie können die folgenden Beispielabfragen verwenden, um Ihre Journaltabellen abzufragen.

Suchen von Objekten anhand der Dateierweiterung

Die folgende Abfrage gibt Objekte mit einer bestimmten Dateierweiterung zurück (.jpgin diesem Fall):


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

Auflisten von Objektlöschungen

Die folgende Abfrage gibt Ereignisse zum Löschen von Objekten zurück, einschließlich der AWS-Konto ID oder des AWS Dienstprinzipals, der die Anfrage gestellt hat:


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

Listet die von Ihren Objekten verwendeten AWS KMS Verschlüsselungsschlüssel auf

Die folgende Abfrage gibt die ARNs AWS Key Management Service (AWS KMS) -Schlüssel zurück, mit denen Ihre Objekte verschlüsselt wurden:


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

Auflisten von Objekten, die keine KMS-Schlüssel verwenden

Die folgende Abfrage gibt Objekte zurück, die nicht mit AWS KMS Schlüsseln verschlüsselt sind:


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

Listet die AWS KMS Verschlüsselungsschlüssel auf, die in den letzten 7 Tagen für `PUT` Operationen verwendet wurden

Die folgende Abfrage gibt die ARNs Schlüssel AWS Key Management Service (AWS KMS) zurück, mit denen Ihre Objekte verschlüsselt wurden:


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;

Listet Objekte auf, die in den letzten 24 Stunden von S3 Lifecycle gelöscht wurden

Die folgende Abfrage gibt eine Liste der Objekte zurück, die am letzten Tag von S3 Lifecycle abgelaufen sind:


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)

Anzeigen von Metadaten, die von Amazon Bedrock bereitgestellt wurden

Einige AWS Dienste (wie Amazon Bedrock) laden Objekte auf Amazon S3 hoch. Sie können die von diesen Diensten bereitgestellten Objektmetadaten abfragen. Die folgende Abfrage enthält beispielsweise die user_metadata Spalte, um zu ermitteln, ob es Objekte gibt, die von Amazon Bedrock in einen Allzweck-Bucket hochgeladen wurden:


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

Wenn Amazon Bedrock ein Objekt in Ihren Bucket hochgeladen hat, werden in der user_metadata-Spalte die folgenden Metadaten angezeigt, die mit dem Objekt im Abfrageergebnis verknüpft sind:


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

Verstehen des derzeitigen Status Ihrer Objekte

Die folgende Abfrage kann Ihnen helfen, den aktuellen Status Ihrer Objekte zu bestimmen. Die Abfrage identifiziert die neueste Version jedes Objekts, filtert gelöschte Objekte heraus und markiert die neueste Version jedes Objekts anhand von Sequenznummern. Die Ergebnisse sind nach den Spalten bucket, key und sequence_number sortiert.


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;

Beispielabfragen zur Inventartabelle

Sie können die folgenden Beispielabfragen verwenden, um Ihre Inventartabellen abzufragen.

Ermitteln von Datensätzen, die bestimmte Tags verwenden

Die folgende Abfrage gibt den Datensatz zurück, der die angegebenen Tags verwendet:


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

Auflisten von Objekten, die nicht mit SSE-KMS verschlüsselt sind

Die folgende Abfrage gibt Objekte zurück, die nicht mit SSE-KMS verschlüsselt sind:


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

Auflisten von Objekten, die von Amazon Bedrock generiert wurden

Die folgende Abfrage listet Objekte auf, die von Amazon Bedrock generiert wurden:


SELECT DISTINCT bucket, key, sequence_number, user_metadata
FROM "s3tablescatalog/aws-s3"."b_general-purpose-bucket-name"."inventory"
WHERE user_metadata['content-source'] = 'AmazonBedrock';

Generierung der neuesten Inventartabelle

Die folgende Abfrage generiert die neueste Inventartabelle. Diese Abfrage funktioniert, wenn sich Ihre Metadatenkonfiguration im Status Aktiv befindet. Für diese Abfrage müssen sich sowohl die Journal- als auch die Inventartabelle im Status Aktiv befinden. Wenn Ihre Inventartabelle aufgrund von Zugriffsrechten oder anderen Problemen nicht auf dem neuesten Stand ist, funktioniert diese Abfrage möglicherweise nicht.

Wir empfehlen, diese Abfrage für allgemeine Buckets mit weniger als einer Milliarde Objekten zu verwenden.

Diese Abfrage gleicht den Inhalt der Inventartabelle mit den jüngsten Ereignissen in der Journaltabelle ab. Wenn Ihr Journal mit allen Änderungen in Ihrem Bucket auf dem neuesten Stand ist, entspricht das Abfrageergebnis dem des Inhalts des Buckets.

In diesem Beispiel wird die Ausgabe auf die Schlüssel beschränkt, die mit '%.txt' enden. Um eine andere Teilmenge abzufragen, können Sie den gemeinsamen Tabellenausdruck mit dem Namen anpassen. "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

Warnung JavaScript ist in Ihrem Browser nicht verfügbar oder deaktiviert.

Zur Nutzung der AWS-Dokumentation muss JavaScript aktiviert sein. Weitere Informationen finden auf den Hilfe-Seiten Ihres Browsers.

Dokumentkonventionen

Optimieren der Abfrageleistung

Verknüpfen von benutzerdefinierten Metadaten