全部产品
云市场

创建OSS日志文件表并读取日志数据

更新时间:2019-03-28 12:28:32

创建OSS日志文件表

创建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';

创建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';

创建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';

读取日志文件数据

读取log4j_sample.log数据

  1. SELECT * FROM oss_log_schema.log4j_log

查询结果1

读取ngnix_log数据

  1. SELECT * FROM oss_log_schema.ngnix_log

查询结果2

读取webserver_log数据

  1. SELECT * FROM oss_log_schema.webserver_log

查询结果3

后续操作

读取日志数据后,您就可以对日志数据进行分析,帮助定位故障原因。