All Products
Search
Document Center

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

Last Updated: May 13, 2019

Create tables based on OSS logs

Create a table based on webserver.log

  1. CREATE EXTERNAL TABLE webserver_log(
  2. host STRING,
  3. identity STRING,
  4. userName STRING,
  5. time STRING,
  6. request STRING,
  7. status STRING,
  8. size INT,
  9. referer STRING,
  10. agent STRING)
  11. ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  12. WITH SERDEPROPERTIES (
  13. "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?"
  14. )
  15. STORED AS TEXTFILE
  16. LOCATION 'oss://analyticdb-bucket/log/webserver.log';

Create a table based on ngnix_log.log

  1. CREATE EXTERNAL TABLE ngnix_log(
  2. remote_address STRING,
  3. identity STRING,
  4. remote_user STRING,
  5. time_local STRING,
  6. request STRING,
  7. status STRING,
  8. body_bytes_sent INT,
  9. http_referer STRING,
  10. http_user_agent STRING,
  11. gzip_ratio STRING
  12. )
  13. ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  14. WITH SERDEPROPERTIES (
  15. "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))? ([^ \"]*|\"[^\"]*\")"
  16. )
  17. STORED AS TEXTFILE
  18. LOCATION 'oss://analyticdb-bucket/log/ngnix_log';

Create a table based on log4j_sample.log

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

Read data from log files

Read data from log4j_sample.log

  1. SELECT * FROM oss_log_schema.log4j_log

Query result 1

Read data from ngnix.log

  1. SELECT * FROM oss_log_schema.ngnix_log

Query result 2

Read data from webserver.log

  1. SELECT * FROM oss_log_schema.webserver_log

Query result 3

Subsequent operations

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