使用分区投影功能在 Athena 中为 ALB 连接日志创建表

由于 ALB 日志具有一个可以预先指定其分区方案的已知结构，您可以使用 Athena 分区投影功能，以此来减少查询运行时间并自动管理分区。当添加新数据时，分区投影会自动添加新分区。这样就不必使用 ALTER TABLE ADD PARTITION 手动添加分区了。

从指定日期开始到当前日期为止，以下示例 CREATE TABLE 语句会自动在 ALB 连接日志上为单个 AWS 区域使用分区投影。该语句以上一部分中的示例为基础，但添加了 PARTITIONED BY 和 TBLPROPERTIES 子句以启用分区投影。在 LOCATION 和 storage.location.template 子句中，将占位符替换为标识 ALB 连接日志在 Amazon S3 存储桶中位置的值。有关连接日志文件位置的更多信息，请参阅《User Guide for Application Load Balancers》中的 Connection log files。对于 projection.day.range，将 2023/01/01 替换为要使用的开始日期。成功运行查询后，您可以查询表。您无需运行 ALTER TABLE ADD PARTITION 来加载分区。有关每个日志文件字段的信息，请参阅 Connection log entries。

CREATE EXTERNAL TABLE IF NOT EXISTS alb_connection_logs (
         time string,
         client_ip string,
         client_port int,
         listener_port int,
         tls_protocol string,
         tls_cipher string,
         tls_handshake_latency double,
         leaf_client_cert_subject string,
         leaf_client_cert_validity string,
         leaf_client_cert_serial_number string,
         tls_verify_status string,
         conn_trace_id string
         )
            PARTITIONED BY
            (
             day STRING
            )
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
            WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' =
             '([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([A-Za-z0-9.-]*) ([^ ]*) ([-.0-9]*) \"([^\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) ?([^ ]*)?( .*)?'
            )
            LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/'
            TBLPROPERTIES
            (
             "projection.enabled" = "true",
             "projection.day.type" = "date",
             "projection.day.range" = "2023/01/01,NOW",
             "projection.day.format" = "yyyy/MM/dd",
             "projection.day.interval" = "1",
             "projection.day.interval.unit" = "DAYS",
             "storage.location.template" = "s3://amzn-s3-demo-bucket/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
            )

更多有关分区投影的信息，请参阅 将分区投影与 Amazon Athena 结合使用。