操作步驟 - - 阿里雲

在DLA中執行以下SQL分別為OSS中的記錄檔webserver.log、ngnix_log.log、log4j_sample.log建立對應的表。

登入DLA控制台。
單擊左側導覽列的訪問點管理，然後單擊登入DMS，執行以下SQL建立OSS Schema。
您也可以通過MySQL用戶端或者程式碼等方式連結DLA，然後執行以下SQL建立OSS Schema。
```
 CREATE SCHEMA oss_log_schema with DBPROPERTIES(
   catalog='oss', 
 location = 'oss://oss-bucket-name/log/'
  );
```

catalog：指定建立的Schema類型為OSS。
location：檔案所在的OSS Bucket目錄，需以/結尾。

步驟二：建立表

webserver.log

  CREATE EXTERNAL TABLE webserver_log(
    host STRING,
    identity STRING,
    userName STRING, 
   time STRING,
    request STRING,
    status STRING,
    size INT, 
   referer STRING, 
   agent STRING)
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES ( 
   "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?"
  )
  STORED AS TEXTFILE
  LOCATION 'oss://oss-bucket-name/log/webserver.log';

ngnix_log.log

  CREATE EXTERNAL TABLE ngnix_log(
    remote_address STRING,
    identity STRING,
    remote_user STRING,
    time_local STRING,
    request STRING,
    status STRING,
    body_bytes_sent INT,
    http_referer STRING,
    http_user_agent STRING,
    gzip_ratio STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))? ([^ \"]*|\"[^\"]*\")"
  )
  STORED AS TEXTFILE
  LOCATION 'oss://oss-bucket-name/log/ngnix_log';

log4j_sample.log

  CREATE EXTERNAL TABLE log4j_log(
    date STRING,
    time STRING,
    level STRING, 
   class STRING, 
   details STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    "input.regex" = "^(\\d{4}-\\d{2}-\\d{2})\\s+(\\d{2}.\\d{2}.\\d{2}.\\d{3})\\s+(\\S+)\\s+(\\S+)\\s+(.*)$"
  )
  STORED AS TEXTFILE
  LOCATION 'oss://oss-bucket-name/log/log4j_sample.log';

步驟三：讀取記錄檔資料

表建立成功後，您可以在DLA中通過SELECT查詢並分析OSS記錄檔資料，協助定位故障原因。

log4j_sample.log

  SELECT * FROM oss_log_schema.log4j_log

ngnix_log

  SELECT * FROM oss_log_schema.ngnix_log

webserver_log

  SELECT * FROM oss_log_schema.webserver_log