查詢 W3C 擴展日誌文件格式 - Amazon Athena
在 Athena 中為 W3C 擴展日誌創建一個表W3C 擴充日誌選取查詢範例結合日期和時間欄位

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

查詢 W3C 擴展日誌文件格式

W3C 擴充日誌檔案資料格式具有以空格分隔的欄位。W3C 擴充日誌中顯示的欄位是由 Web 伺服器管理員決定，管理員可選擇要包含哪些日誌欄位。下列日誌資料範例具有 date, time、c-ip、s-ip、cs-method、cs-uri-stem、sc-status、sc-bytes、cs-bytes、time-taken 以及 cs-version。

2020-01-19 22:48:39 203.0.113.5 198.51.100.2 GET /default.html 200 540 524 157 HTTP/1.0
2020-01-19 22:49:40 203.0.113.10 198.51.100.12 GET /index.html 200 420 324 164 HTTP/1.0
2020-01-19 22:50:12 203.0.113.12 198.51.100.4 GET /image.gif 200 324 320 358 HTTP/1.0
2020-01-19 22:51:44 203.0.113.15 198.51.100.16 GET /faq.html 200 330 324 288 HTTP/1.0

在 Athena 中為 W3C 擴展日誌創建一個表

在查詢 W3C 擴充日誌之前，您必須建立資料表結構描述，以便讓 Athena 可以讀取日誌資料。

若要在 Athena 中為 W3C 擴充日誌建立資料表

  1. 在 https://console.aws.amazon.com/athena/ 中開啟 Athena 主控台。

  2. 將類似下列的DDL陳述式貼到 Athena 主控台，並注意下列幾點：

    1. 新增或移除範例中的資料欄，以對應至您要查詢的日誌欄位。

    2. W3C 擴充日誌檔案格式中的資料欄名稱包含連字號 (-)。然而，按照 Athena 命名慣例，範例 CREATE TABLE 陳述式會用底線 (_) 替換連字號。

    3. 若要指定空格分隔符號，請使用 FIELDS TERMINATED BY ' '。

    4. 修改 LOCATION 's3://amzn-s3-demo-bucket/w3c-log-folder/' 中的值，以指向 Amazon S3 中的 W3C 擴充日誌。

    CREATE EXTERNAL TABLE `iis_w3c_logs`( 
  date_col string, 
  time_col string, 
  c_ip string,
  s_ip string,
  cs_method string, 
  cs_uri_stem string, 
  sc_status string,
  sc_bytes string,
  cs_bytes string,
  time_taken string,
  cs_version string
  ) 
ROW FORMAT DELIMITED  
  FIELDS TERMINATED BY ' '  
STORED AS INPUTFORMAT  
  'org.apache.hadoop.mapred.TextInputFormat'  
OUTPUTFORMAT  
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
LOCATION   's3://amzn-s3-demo-bucket/w3c-log-folder/'

  3. 在 Athena 主控台中執行查詢來註冊 iis_w3c_logs 資料表。查詢完成時，您就可以從 Athena 查詢日誌了。

W3C 擴充日誌選取查詢範例

下面的查詢範例會從資料表 iis_w3c_logs 中選取日期、時間、請求目標以及請求所花費的時間。該WHERE子句過濾了HTTP方法所在的情況下GET，HTTP狀態代碼是200（成功）。

SELECT date_col, time_col, cs_uri_stem, time_taken
FROM iis_w3c_logs
WHERE cs_method = 'GET' AND sc_status = '200'

下圖顯示 Athena 查詢編輯器中查詢的結果。

Athena 中存放在 Amazon S3 中 W3C 擴充日誌檔案的查詢結果範例。

結合日期和時間欄位

以空格分隔的 date 和 time 欄位是日誌來源資料中的單獨項目，您可以根據需要將它們合併成時間戳記。在SELECT或 CREATETABLEAS SELECT 查詢中使用 concat () 和 date_parse () 函數，將日期和時間資料行串連並轉換為時間戳記格式。下列範例會使用CTAS查詢來建立含有資料行的新derived_timestamp資料表。

CREATE TABLE iis_w3c_logs_w_timestamp AS
SELECT 
  date_parse(concat(date_col,' ', time_col),'%Y-%m-%d %H:%i:%s') as derived_timestamp, 
  c_ip, 
  s_ip, 
  cs_method, 
  cs_uri_stem, 
  sc_status, 
  sc_bytes, 
  cs_bytes, 
  time_taken, 
  cs_version
FROM iis_w3c_logs

建立資料表之後，您就可以直接查詢新的時間戳記資料欄，如下例所示。

SELECT derived_timestamp, cs_uri_stem, time_taken
FROM iis_w3c_logs_w_timestamp
WHERE cs_method = 'GET' AND sc_status = '200'

下圖顯示查詢的結果。

包含衍生時間戳記資料欄的資料表上的 W3C 擴充日誌檔案查詢結果。