Consulta do Amazon S3 Inventory com o Amazon Athena

É possível consultar arquivos do Inventário Amazon S3 com consultas SQL padrão usando o Amazon Athena em todas as regiões onde o Athena esteja disponível. Para verificar a disponibilidade da Região da AWS, consulte a Tabela de Região da AWS.

O Athena pode consultar arquivos do Inventário Amazon S3 no formato colunar de linhas otimizado (ORC) do Apache, Apache Parquet ou valores separados por vírgula (CSV). Quando você usar o Athena para consultar arquivos de inventário, use arquivos de inventário no formato ORC ou Parquet. Os formatos ORC e Parquet têm uma performance de consulta mais rápida e custos mais baixos de consulta. ORC e Parquet são formatos de arquivo colunares do tipo autodescritivo projetados para o Apache Hadoop. O formato colunar permite que o leitor leia, descompacte e processe apenas as colunas necessárias para a consulta atual. Os formatos ORC e Parquet para o Inventário Amazon S3 estão disponíveis em todas as Regiões da AWS.

Para usar o Athena para consultar arquivos do Inventário Amazon S3

Crie uma tabela do Athena. Para obter informações sobre como criar uma tabela, consulte Criar tabelas no Amazon Athena no Guia do usuário do Amazon Athena.

Crie sua consulta usando um dos exemplos de modelos de consulta a seguir, dependendo se você está consultando um relatório de inventário no formato ORC, Parquet ou CSV.

Ao usar o Athena para consultar um relatório de inventário no formato ORC, use o exemplo de consulta a seguir como modelo.

O exemplo de consulta a seguir inclui todos os campos opcionais no relatório de inventário no formato ORC.

Para usar esse exemplo de consulta, faça o seguinte:

Substitua your_table_name pelo nome da tabela do Athena que você criou.
Remova todos os campos opcionais que você não selecionou no inventário para que a consulta corresponda aos campos escolhidos.
Substitua o nome do bucket a seguir e o local do inventário (o ID de configuração) conforme apropriado para sua configuração.

s3://DOC-EXAMPLE-BUCKET/config-ID/hive/
Substitua a data 2022-01-01-00-00 abaixo de projection.dt.range com o primeiro dia do intervalo de tempo em que você particiona os dados no Athena. Para obter mais informações, consulte Particionamento de dados no Athena.


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

Ao usar o Athena para consultar um relatório de inventário no formato Parquet, use a consulta de amostra para um relatório no formato ORC. No entanto, use o seguinte Parquet SerDe no lugar do ORC SerDe na declaração ROW FORMAT SERDE.
```
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
```

Ao usar o Athena para consultar um relatório de inventário no formato CSV, use o exemplo de consulta a seguir como modelo.

O exemplo de consulta a seguir inclui todos os campos opcionais no relatório de inventário no formato CSV.

Para usar esse exemplo de consulta, faça o seguinte:

Substitua your_table_name pelo nome da tabela do Athena que você criou.
Remova todos os campos opcionais que você não selecionou no inventário para que a consulta corresponda aos campos escolhidos.
Substitua o nome do bucket a seguir e o local do inventário (o ID de configuração) conforme apropriado para sua configuração.

s3://DOC-EXAMPLE-BUCKET/config-ID/hive/
Substitua a data 2022-01-01-00-00 abaixo de projection.dt.range com o primeiro dia do intervalo de tempo em que você particiona os dados no Athena. Para obter mais informações, consulte Particionamento de dados no Athena.


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

Agora você pode executar várias consultas no inventário, conforme mostrado nos exemplos a seguir. Substitua cada user input placeholder por suas próprias informações.


# Get a list of the latest inventory report dates available.
SELECT DISTINCT dt FROM your_table_name ORDER BY 1 DESC limit 10;
          
# Get the encryption status for a provided report date.
SELECT encryption_status, count(*) FROM your_table_name WHERE dt = 'YYYY-MM-DD-HH-MM' GROUP BY encryption_status;
          
# Get the encryption status for inventory report dates in the provided range.
SELECT dt, encryption_status, count(*) FROM your_table_name 
WHERE dt > 'YYYY-MM-DD-HH-MM' AND dt < 'YYYY-MM-DD-HH-MM' GROUP BY dt, encryption_status;

Quando você configura o Inventário S3 para adicionar o campo Lista de controle de acesso de objetos (ACL de objetos) a um relatório de inventário, o relatório exibe o valor do campo ACL de objetos como uma string codificada em base64. Para obter o valor decodificado em JSON para o campo ACL de objetos, você pode consultar esse campo usando o Athena. Veja os exemplos de consulta a seguir. Para obter mais informações sobre o campo ACL do objeto, consulte Trabalhar com o campo ACL de objetos.


# Get the S3 keys that have Object ACL grants with public access.
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, 'uri') = 'http://acs.amazonaws.com/groups/global/AllUsers'


# Get the S3 keys that have Object ACL grantees in addition to the object owner.
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;


# Get the S3 keys with READ permission that is granted in the Object ACL. 
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';


# Get the S3 keys that have Object ACL grants to a specific canonical user ID.
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';


# Get the number of grantees on the Object ACL.
SELECT key,
       object_access_control_list,
       json_array_length(json_extract(object_access_control_list,'$.grants')) AS grants_count
FROM your_table_name;

Para obter mais informações sobre como usar o Athena, consulte o Guia do usuário do Amazon Athena.

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Localização de inventário

Converter strings de ID de versão vazias em cadeias de caracteres null