全部产品
云市场

操作步骤

更新时间:2020-04-09 15:16:56

步骤一:创建Schema

  1. 登录DLA控制台

  2. 单击左侧导航栏的访问点管理,然后单击登录DMS,执行以下SQL创建OSS Schema。

    您也可以通过MySQL客户端或者程序代码等方式连接DLA,然后执行以下SQL创建OSS Schema。

    1. CREATE SCHEMA oss_log_schema with DBPROPERTIES(
    2. catalog='oss',
    3. location = 'oss://oss-bucket-name/log/'
    4. );
  • catalog:指定创建的Schema类型为OSS。

  • location:文件所在的OSS Bucket目录,需以/结尾。

步骤二:创建表

在DLA中执行以下SQL分别为OSS中的日志文件webserver.log、ngnix_log.log、log4j_sample.log创建对应的表。

  • 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://oss-bucket-name/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://oss-bucket-name/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://oss-bucket-name/log/log4j_sample.log';

步骤三:读取日志文件数据

表创建成功后,您可以在DLA中通过SELECT查询并分析OSS日志文件数据,帮助定位故障原因。

  • 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