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.