Comment puis-je utiliser Athena pour interroger mes rapports d’inventaire Amazon S3 ?

Résolution

Procédez comme suit pour interroger les fichiers d’inventaire Amazon S3 à l’aide d’un rapport d’inventaire au format ORC, Parquet ou CSV.

1. Configurez l’inventaire Amazon S3 pour votre compartiment S3. Notez les valeurs du compartiment de destination dans lequel les rapports d’inventaire sont enregistrés.

2. Ouvrez la console Athena.
   Remarque : avant d’exécuter votre première requête, il peut être nécessaire de configurer un emplacement pour les résultats de requête dans Amazon S3.

3. Dans l’Éditeur de requêtes, exécutez une instruction DDL pour créer une base de données.

   create database s3_inventory_db

4. Créez un schéma de table dans la base de données.

   Exemple de requête pour un rapport d’inventaire au format ORC :

   Remarque : remplacez les variables suivantes par les vôtres :

   • Pour la variable your_table_name, saisissez le nom de votre table Athena.
   • Pour la variable LOCATION, saisissez le compartiment S3 et le chemin du préfixe. Veillez à inclure une barre oblique (/) à la fin du préfixe. Par exemple, s3://doc-example-bucket/prefix/. Si vous n’utilisez pas de préfixe, insérez une barre oblique (/) à la fin du nom du compartiment. Par exemple, s3://doc-example-bucket/.
   • Pour la date 2022-01-01-00-00 sous projection.dt.range, saisissez le premier jour de la plage temporelle au cours de laquelle vous partitionnez les données dans Athena.
   • Supprimez tous les champs facultatifs que vous n’avez pas sélectionnés pour votre inventaire.

   CREATE EXTERNAL TABLE your_table_name(
            bucket string,
            key string,
            version_id string,
            is_latest boolean,
            is_delete_marker boolean,
            size bigint,
            last_modified_date timestamp,
            e_tag string,
            storage_class string,
            is_multipart_uploaded boolean,
            replication_status string,
            encryption_status string,
            object_lock_retain_until_date bigint,
            object_lock_mode string,
            object_lock_legal_hold_status string,
            intelligent_tiering_access_tier string,
            bucket_key_status string,
            checksum_algorithm string,
            object_access_control_list string,
            object_owner string
   ) PARTITIONED BY (
           dt string
   )
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
     STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
     OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
     LOCATION 's3://source-bucket/config-ID/hive/'
     TBLPROPERTIES (
       "projection.enabled" = "true",
       "projection.dt.type" = "date",
       "projection.dt.format" = "yyyy-MM-dd-HH-mm",
       "projection.dt.range" = "2022-01-01-00-00,NOW",
       "projection.dt.interval" = "1",
       "projection.dt.interval.unit" = "HOURS"
     );

   Remarque : pour interroger un rapport d’inventaire au format Parquet, utilisez l’instruction Parquet SerDe suivante à la place de l’instruction ORC SerDe dans le champ ROW FORMAT SERDE :

   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'

   Exemple de requête pour un rapport d’inventaire au format CSV :

   CREATE EXTERNAL TABLE your_table_name(
            bucket string,
            key string,
            version_id string,
            is_latest boolean,
            is_delete_marker boolean,
            size string,
            last_modified_date string,
            e_tag string,
            storage_class string,
            is_multipart_uploaded boolean,
            replication_status string,
            encryption_status string,
            object_lock_retain_until_date string,
            object_lock_mode string,
            object_lock_legal_hold_status string,
            intelligent_tiering_access_tier string,
            bucket_key_status string,
            checksum_algorithm string,
            object_access_control_list string,
            object_owner string
   ) PARTITIONED BY (
           dt string
   )
   ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
     STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
     OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
     LOCATION 's3://source-bucket/config-ID/hive/'
     TBLPROPERTIES (
       "projection.enabled" = "true",
       "projection.dt.type" = "date",
       "projection.dt.format" = "yyyy-MM-dd-HH-mm",
       "projection.dt.range" = "2022-01-01-00-00,NOW",
       "projection.dt.interval" = "1",
       "projection.dt.interval.unit" = "HOURS"
     );

5. Dans le volet de gauche, sous Tables, sélectionnez les points de suspension qui se trouvent à côté du nom de la table, puis choisissez Aperçu de la table. Si les données issues des journaux d’accès au serveur s’affichent dans la fenêtre Résultats, cela signifie que la table Athena a été créée avec succès. Les données ressemblent à des valeurs telles que compartiment, clé, version_id, taille et objectowner.

Vous pouvez désormais interroger les fichiers d’inventaire Amazon S3.

Pour obtenir des instructions détaillées sur la façon d’interroger vos fichiers d’inventaire Amazon S3, consultez la page Querying Amazon S3 Inventory with Amazon Athena.

Exemples de requêtes

Utilisez les exemples de requêtes suivants pour exécuter des rapports d’inventaire Amazon S3. Vous pouvez également créer vos propres requêtes pour votre cas d’utilisation.

Trier les objets par taille et créer un rapport

SELECT DISTINCT size FROM your_table_name ORDER BY 1 DESC limit 10;
SELECT size, count(*) FROM your_table_name GROUP BY size;

Vérifier le statut du chiffrement pour identifier les risques de sécurité afin d’activer le chiffrement

SELECT encryption_status, count(*) FROM your_table_name GROUP BY encryption_status;

Compter par date de dernière modification pour vérifier les données actives

SELECT last_modified_date, count(*) FROM your_table_name GROUP BY last_modified_date;

Compter ou répertorier les objets supérieurs ou égaux à 5 Go à partir d’une date d’inventaire

SELECT COUNT(*) FROM your_table_name WHERE size >= 5000000000;
SELECT bucket,key,size FROM your_table_name WHERE size>5000000000;

Signaler les données rarement consultées par niveau

SELECT intelligent_tiering_tier,count (*) FROM your_table_name GROUP BY intelligent_tiering_tier;

Obtenir les clés S3 disposant d’autorisations d’ACL d’objet avec accès public

SELECT key,
CAST(
json_extract(from_utf8(from_base64(object_access_control_list)), '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))
) AS grants_array
FROM your_table_name
)
SELECT key,
grants_array,
grant
FROM grants, UNNEST(grants_array) AS t(grant)
WHERE element_at(grant, 'uri') = 'http://acs.amazonaws.com/groups/global/AllUsers'

Obtenir les clés S3 qui ont des bénéficiaires d’ACL d’objet et un propriétaire d’objet

WITH grants AS
    (SELECT key,
    from_utf8(from_base64(object_access_control_list)) AS object_access_control_list,
         object_owner,
         CAST(json_extract(from_utf8(from_base64(object_access_control_list)),
         '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))) AS grants_array
    FROM your_table_name)
SELECT key,
       grant,
       objectowner
FROM grants, UNNEST(grants_array) AS t(grant)
WHERE cardinality(grants_array) > 1 AND element_at(grant, 'canonicalId') != object_owner;

Obtenir les clés S3 avec une autorisation READ accordée dans l’ACL d’objet

WITH grants AS (
    SELECT key,
        CAST(
            json_extract(from_utf8(from_base64(object_access_control_list)), '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))
        ) AS grants_array
    FROM your_table_name
)
SELECT key,
       grants_array,
       grant
FROM grants, UNNEST(grants_array) AS t(grant)
WHERE element_at(grant, 'permission') = 'READ';

Obtenir les clés S3 disposant d’autorisations d’ACL d’objet avec un ID d’utilisateur canonique

WITH grants AS (
    SELECT key,
        CAST(
            json_extract(from_utf8(from_base64(object_access_control_list)), '$.grants') AS ARRAY(MAP(VARCHAR, VARCHAR))
        ) AS grants_array
    FROM your_table_name
)
SELECT key,
       grants_array,
       grant
FROM grants, UNNEST(grants_array) AS t(grant)
WHERE element_at(grant, 'canonicalId') = 'user-canonical-id';

Obtenir le nombre de bénéficiaires de l’ACL d’objet

SELECT key,
object_access_control_list,
json_array_length(json_extract(object_access_control_list,'$.grants')) AS grants_count
FROM your_table_name;

Informations connexes

Partitioning data in Athena

Using Athena SQL

Locating your inventory list

Comment puis-je utiliser Amazon Athena pour analyser les journaux d’accès à mon serveur Amazon S3 ?