在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