:Create tables based on OSS logs and read data from the logs

Create tables based on OSS logs

Create a table based on 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://analyticdb-bucket/log/webserver.log';

Create a table based on 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://analyticdb-bucket/log/ngnix_log';

Create a table based on 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://analyticdb-bucket/log/log4j_sample.log';

Read data from log files

Read data from log4j_sample.log

SELECT * FROM oss_log_schema.log4j_log

Read data from ngnix.log

SELECT * FROM oss_log_schema.ngnix_log

Read data from webserver.log

SELECT * FROM oss_log_schema.webserver_log

Subsequent operations

You can analyze the data that is obtained from the logs to locate failure causes.