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

  1. 登录DLA控制台

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

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

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

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

步骤二:创建表

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

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

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

  • log4j_sample.log

      SELECT * FROM oss_log_schema.log4j_log

  • ngnix_log

      SELECT * FROM oss_log_schema.ngnix_log

  • webserver_log

      SELECT * FROM oss_log_schema.webserver_log