amazon-web-services - AWS Athena - 将 CloudFront 日志时间字段转换为时间戳

Question

我正在遵循示例AWS documentation用于在 Athena 中创建 CloudFront 日志表。

CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://your_log_bucket/prefix/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

使用时间字段作为字符串创建表不允许我运行条件查询。我尝试使用以下内容重新创建表格:

CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
  `date` DATE,
  time timestamp,
....

不幸的是，这不起作用，当我预览表格时，我在时间字段中没有收到任何结果。

有人有将时间转换为我可以用来查询的内容的经验吗？

Answer 1

将日期和时间连接到子查询中的时间戳中:

WITH ds AS 
    (SELECT *,
         parse_datetime( concat( concat( format_datetime(date,
         'yyyy-MM-dd'), '-' ), time ),'yyyy-MM-dd-HH:mm:ss') AS datetime
    FROM default.cloudfront_www
    WHERE requestip = '207.30.46.111')
SELECT *
FROM ds
WHERE datetime
    BETWEEN timestamp '2018-11-19 06:00:00'
        AND timestamp '2018-11-19 12:00:00'