W3C_LOG_PARSE

 W3C_LOG_PARSE( <character-expression>, <format-string> )
 <format-string> := '<predefined-format> | <custom-format>'
 <predefined format> :=
         COMMON
       | COMMON WITH VHOST
       | NCSA EXTENDED
       | REFERER
       | AGENT
       | IIS
 <custom-format> := [an Apache log format specifier]

W3C Predefined Formats

Specifying the following W3C-predefined-format names summarizes using the format specifiers indicated, as shown in the following statement:

 select stream W3C_LOG_PARSE(message, 'COMMON') r  from w3ccommon t;
    

W3C Format Specifiers

The format specifiers are listed below. W3C_LOG_PARSE automatically detects these specifiers and output records with one column for each specifier. The column's type is automatically chosen based on the possible outputs of the specifier. For example, %b represents the number of bytes sent in processing an HTTP request, so the column type is numeric. For %B, however, zero bytes is represented by a dash - forcing the column type to be text. Note A explains what the "..." and "<" or ">" markings shown in the specifier table mean.

The following table lists W3C format specifiers alphabetically by command.

W3C Format Specifiers by Function or Category

The categories are bytes sent, connection status, content of environmental variable, filename, host, IP, notes, protocol, query string, replies, requests, and time. For the markings "..." or "<" or "<", see the previous note.

The W3C format specifiers for the response and time categories are listed following table.

W3C Examples

W3C_LOG_PARSE supports access to logs generated by W3C-compliant applications like the Apache web server, producing output rows with one column for each specifier. The data types are derived from the log entry description specifiers listed in the Apache mod_log_config specification.

Example 1

The input in this example is taken from an Apache log file and is representative of the COMMON log format.

Input

(192.168.254.30 - John [24/May/2004:22:01:02 -0700]
                     "GET /icons/apache_pb.gif HTTP/1.1" 304 0),
(192.168.254.30 - Jane [24/May/2004:22:01:02 -0700]
                     "GET /icons/small/dir.gif HTTP/1.1" 304 0);

DDL

CREATE OR REPLACE PUMP weblog AS
        SELECT STREAM
            l.r.COLUMN1,
            l.r.COLUMN2,
            l.r.COLUMN3,
            l.r.COLUMN4,
            l.r.COLUMN5,
            l.r.COLUMN6,
            l.r.COLUMN7
        FROM (SELECT STREAM W3C_LOG_PARSE(message, 'COMMON')
              FROM "weblog_read) AS l(r);

Output

 192.168.254.30 -  John  [24/May/2004:22:01:02 -0700] GET /icons/apache_pb.gif HTTP/1.1  304  0
 192.168.254.30 -  Jane  [24/May/2004:22:01:02 -0700] GET /icons/small/dir.gif HTTP/1.1  304  0

The specification of COMMON in the FROM clause means the Common Log Format (CLF), which uses the specifiers %h %l %u %t "%r" %>s %b.

The W3C-predefined formats shows the COMMON and other predefined specifier sets.

The specification of COMMON in the FROM clause means the Common Log Format (CLF), which uses the specifiers %h %l %u %t "%r" %>s %b.

The table below, Specifiers used by the Common Log Format, describes the specifiers used by COMMON in the FROM clause.

Specifiers Used by the Common Log Format

Example 2

The DDL in this example shows how to rename output columns and filter out unneeded columns.

DDL

    CREATE OR REPLACE VIEW "Schema1".weblogreduced AS
        SELECT STREAM CAST(s.COLUMN3 AS VARCHAR(5)) AS LOG_USER,
        CAST(s.COLUMN1 AS VARCHAR(15)) AS ADDRESS,
        CAST(s.COLUMN4 AS VARCHAR(30)) as TIME_DATES
        FROM "Schema1".weblog s;

Output

    +----------+-----------------+--------------------------------+
    | LOG_USER |     ADDRESS     |           TIME_DATES           |
    |          |                 |                                |
    +----------+-----------------+--------------------------------+
    | Jane     | 192.168.254.30  | [24/May/2004:22:01:02 -0700]   |
    |          |                 |                                |
    | John     | 192.168.254.30  | [24/May/2004:22:01:02 -0700]   |
    +----------+-----------------+--------------------------------+

W3C Customized Formats

The same results would be created by naming the specifiers directly rather than using the "COMMON" name, as shown following:

    CREATE OR REPLACE FOREIGN STREAM schema1.weblog
        SERVER logfile_server
        OPTIONS (LOG_PATH '/path/to/logfile',
                 ENCODING 'UTF-8',
                 SLEEP_INTERVAL '10000',
                 MAX_UNCHANGED_STATS '10',
                 PARSER 'W3C',
                 PARSER_FORMAT '%h %l %u %t \"%r\" %>s %b');
    or
     CREATE FOREIGN STREAM "Schema1".weblog_read
     SERVER "logfile_server"
     OPTIONS (log_path '/path/to/logfile',
     encoding 'UTF-8',
     sleep_interval '10000',
     max_unchanged_stats '10');
     CREATE OR REPLACE VIEW "Schema1".weblog AS
        SELECT STREAM
            l.r.COLUMN1,
            l.r.COLUMN2,
            l.r.COLUMN3,
            l.r.COLUMN4,
            l.r.COLUMN5,
            l.r.COLUMN6
        FROM (SELECT STREAM W3C_LOG_PARSE(message, '%h %l %u %t \"%r\" %>s %b')
              FROM "Schema1".weblog_read) AS l(r);

        24/May/2004:22:01:02 -0700