Contoh kueri tabel metadata

Contoh berikut menunjukkan bagaimana Anda bisa mendapatkan informasi jenis yang berbeda dari tabel Metadata S3 Anda dengan menggunakan kueri SQL standar.

Ingat saat menggunakan contoh-contoh ini:

• Contohnya ditulis untuk bekerja dengan Amazon Athena. Anda mungkin harus memodifikasi contoh untuk bekerja dengan mesin kueri yang berbeda.

• Pastikan Anda memahami cara mengoptimalkan kueri Anda.

• Ganti b_general-purpose-bucket-name dengan nama namespace Anda.

• Untuk daftar lengkap kolom yang didukung, lihat Skema tabel jurnal Metadata S3 danSkema tabel inventaris langsung Metadata S3.

Daftar Isi

• Contoh pertanyaan tabel jurnal

  • Menemukan objek dengan ekstensi file

  • Daftar penghapusan objek

  • Daftar kunci AWS KMS enkripsi yang digunakan oleh objek Anda

  • Daftar objek yang tidak menggunakan kunci KMS

  • Daftar kunci AWS KMS enkripsi yang digunakan untuk PUT operasi dalam 7 hari terakhir

  • Mencantumkan objek yang dihapus dalam 24 jam terakhir oleh Siklus Hidup S3

  • Melihat metadata yang disediakan oleh Amazon Bedrock

  • Memahami keadaan objek Anda saat ini

• Contoh pertanyaan tabel inventaris

  • Menemukan kumpulan data yang menggunakan tag tertentu

  • Daftar objek yang tidak dienkripsi dengan SSE-KMS

  • Daftar objek yang tidak dienkripsi

  • Daftar objek yang dihasilkan oleh Amazon Bedrock

  • Rekonsiliasi tabel inventaris dengan tabel jurnal

• Bergabung dengan metadata kustom dengan tabel metadata S3

• Memvisualisasikan data tabel metadata dengan Amazon QuickSight

Contoh pertanyaan tabel jurnal

Anda dapat menggunakan contoh query berikut untuk query tabel jurnal Anda.

Menemukan objek dengan ekstensi file

Query berikut mengembalikan objek dengan ekstensi file tertentu (.jpgdalam hal ini):

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

Daftar penghapusan objek

Kueri berikut mengembalikan peristiwa penghapusan objek, termasuk Akun AWS ID atau prinsip AWS layanan yang membuat permintaan:

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

Daftar kunci AWS KMS enkripsi yang digunakan oleh objek Anda

Query berikut mengembalikan ARNs kunci AWS Key Management Service (AWS KMS) mengenkripsi objek Anda:

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

Daftar objek yang tidak menggunakan kunci KMS

Kueri berikut mengembalikan objek yang tidak dienkripsi dengan AWS KMS kunci:

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

Daftar kunci AWS KMS enkripsi yang digunakan untuk PUT operasi dalam 7 hari terakhir

Query berikut mengembalikan ARNs kunci AWS Key Management Service (AWS KMS) mengenkripsi objek Anda:

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;

Mencantumkan objek yang dihapus dalam 24 jam terakhir oleh Siklus Hidup S3

Hasil kueri berikut mencantumkan objek yang kedaluwarsa pada hari terakhir oleh Siklus Hidup 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)

Melihat metadata yang disediakan oleh Amazon Bedrock

Beberapa AWS layanan (seperti Amazon Bedrock), mengunggah objek ke Amazon S3. Anda dapat menanyakan metadata objek yang disediakan oleh layanan ini. Misalnya, kueri berikut menyertakan user_metadata kolom untuk menentukan apakah ada objek yang diunggah oleh Amazon Bedrock ke bucket tujuan umum:

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

Jika Amazon Bedrock mengunggah objek ke bucket Anda, user_metadata kolom akan menampilkan metadata berikut yang terkait dengan objek dalam hasil kueri:

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

Memahami keadaan objek Anda saat ini

Kueri berikut dapat membantu Anda menentukan keadaan objek Anda saat ini. Kueri mengidentifikasi versi terbaru dari setiap objek, menyaring objek yang dihapus, dan menandai versi terbaru dari setiap objek berdasarkan nomor urut. Hasil diurutkan olehbucket,key, dan sequence_number kolom.

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;

Contoh pertanyaan tabel inventaris

Anda dapat menggunakan contoh kueri berikut untuk menanyakan tabel inventaris Anda.

Menemukan kumpulan data yang menggunakan tag tertentu

Query berikut mengembalikan dataset yang menggunakan tag tertentu:

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

Daftar objek yang tidak dienkripsi dengan SSE-KMS

Kueri berikut mengembalikan objek yang tidak dienkripsi dengan SSE-KMS:

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

Daftar objek yang tidak dienkripsi

Kueri berikut mengembalikan objek yang tidak dienkripsi:

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

Daftar objek yang dihasilkan oleh Amazon Bedrock

Kueri berikut mencantumkan objek yang dihasilkan oleh 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';

Rekonsiliasi tabel inventaris dengan tabel jurnal

Kueri berikut menghasilkan inventory-table-like daftar yang up to date dengan konten bucket saat ini. Lebih tepatnya, daftar yang dihasilkan menggabungkan snapshot terbaru dari tabel inventaris dengan peristiwa terbaru di tabel jurnal.

Agar kueri ini menghasilkan hasil yang paling akurat, baik jurnal maupun tabel inventaris harus dalam status Aktif.

Kami merekomendasikan penggunaan kueri ini untuk ember tujuan umum yang berisi kurang dari satu miliar (10^9) objek.

Contoh kueri ini menerapkan penyederhanaan berikut untuk hasil daftar (dibandingkan dengan tabel inventaris):

• Kelalaian kolom — Kolom bucketis_multipart,encryption_status,,is_bucket_key_enabled,kms_key_arn, dan checksum_algorithm bukan bagian dari hasil akhir. Menjaga set kolom opsional seminimal mungkin meningkatkan kinerja.

• Penyertaan semua catatan - Kueri mengembalikan semua kunci objek dan versi, termasuk versi null (dalam bucket yang tidak berversi atau ditangguhkan versi) dan menghapus penanda. Untuk contoh cara memfilter hasil agar hanya menampilkan kunci yang Anda minati, lihat WHERE klausa di akhir kueri.

• Rekonsiliasi yang dipercepat - Kueri dapat, dalam kasus yang jarang terjadi, melaporkan sementara objek yang tidak lagi ada di ember. Perbedaan tersebut dihilangkan segera setelah snapshot berikutnya dari tabel inventaris tersedia. Perilaku ini merupakan tradeoff antara kinerja dan akurasi.

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