使用 Amazon Athena 查询 Amazon S3 清单 - Amazon Simple Storage Service
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

使用 Amazon Athena 查询 Amazon S3 清单

您可以在 Athena 可用的所有区域中，通过 Amazon Athena 使用标准 SQL 查询来查询 Amazon S3 清单文件。要检查 Amazon Web Services 区域 可用性，请参阅 Amazon Web Services 区域 表。

Athena 可以采用经 Apache 优化的行列式（ORC）、Apache Parquet 或逗号分隔值（CSV）格式来查询 Amazon S3 清单文件。当您使用 Athena 查询清单文件时，我们建议您使用 ORC 格式或 Parquet 格式的清单文件。ORC 和 Parquet 格式提供了更快的查询性能并能够降低查询成本。ORC 和 Parquet 是自我描述、可感知类型的列式文件格式，专为 Apache Hadoop 而设计。列式格式允许读取器仅读取、解压缩并处理当前查询所需的列。所有 Amazon Web Services 区域均提供 ORC 和 Parquet 格式的 Amazon S3 清单。

使用 Athena 查询 Amazon S3 清单文件

  1. 创建 Athena 表。有关创建表的信息，请参阅《Amazon Athena 用户指南》中的在 Amazon Athena 中创建表。

  2. 使用以下示例查询模板之一创建查询，具体取决于您查询的是 ORC 格式、Parquet 格式还是 CSV 格式的清单报告。

    • 当您使用 Athena 查询 ORC 格式的清单报告时，请使用以下示例查询作为模板。

      下面的示例查询在 ORC 格式的清单报告中包含所有可选字段。

      要使用此示例查询，请执行以下操作：

      • 将 your_table_name 替换为您创建的 Athena 表的名称。

      • 删除您没有为清单选择的所有可选字段，以便查询对应于您的清单的选定字段。

      • 根据您的配置替换以下桶名称和清单位置（配置 ID）。

        s3://DOC-EXAMPLE-BUCKET/config-ID/hive/

      • 将 projection.dt.range 下的 2022-01-01-00-00 日期替换为在 Athena 中对数据进行分区的时间范围的第一天。有关更多信息，请参阅 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"
  );

    • 当您使用 Athena 查询 Parquet 格式的清单报告时，请使用 ORC 格式报告的示例查询。但是，请使用以下 Parquet SerDe 代替 ROW FORMAT SERDE 语句中的 ORC SerDe。

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

    • 当您使用 Athena 查询 CSV 格式的清单报告时，请使用以下示例查询作为模板。

      下面的示例查询在 CSV 格式的清单报告中包含所有可选字段。

      要使用此示例查询，请执行以下操作：

      • 将 your_table_name 替换为您创建的 Athena 表的名称。

      • 删除您没有为清单选择的所有可选字段，以便查询对应于您的清单的选定字段。

      • 根据您的配置替换以下桶名称和清单位置（配置 ID）。

        s3://DOC-EXAMPLE-BUCKET/config-ID/hive/

      • 将 projection.dt.range 下的 2022-01-01-00-00 日期替换为在 Athena 中对数据进行分区的时间范围的第一天。有关更多信息，请参阅 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"
  );

  3. 现在，您可以对清单运行各种查询，如以下示例所示。将每个 user input placeholder 替换为您自己的信息。

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

    当您配置 S3 清单以将对象访问控制列表（对象 ACL）字段添加到清单报告时，报告将“对象 ACL”字段的值显示为以 base64 编码的字符串。要以 JSON 格式获取“对象 ACL”字段的解码值，可以使用 Athena 查询此字段。请参阅以下查询示例。有关“对象 ACL”字段的更多信息，请参阅使用“对象 ACL”字段。

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

有关使用 Athena 的更多信息，请参阅 Amazon Athena 用户指南。