combined-to-sql.pl

#!/usr/bin/env perl

use 5.010;
use strict;
use warnings;

INIT {
    say 'USE mykeyspace;';

    # CREATE KEYSPACE mykeyspace WITH replication = {'class':'SimpleStrategy', 'replication_factor':1};
    # CREATE TABLE access_log (id UUID PRIMARY KEY, ip TEXT, ident TEXT, user TEXT, timestamp INT, method TEXT, url TEXT, status INT, byte BIGINT, referer TEXT, useragent TEXT);

    while (<STDIN>) {
        chomp;

        if (/^(\S+) (\S+) (\S+) \[(\S+) (\S+)\] "(\S+) (\S+) (\S+)" (\d+) (\d+) "(\S+)" "([^"]+)"/) {
            my $ip = $1;
            my $ident = $2;
            my $user = $3;
            my $timedate = $4;
            my $timezone = $5;
            my $method = $6;
            my $url = $7;
            my $http = $8;
            my $status = $9;
            my $byte = $10;
            my $referer = $11;
            my $useragent = $12;

            printf
                "INSERT INTO access_log (id, ip, ident, user, timestamp, method, url, status, byte, referer, useragent) VALUES (uuid(), '%s', '%s', '%s', %d, '%s', '%s', %d, %d, '%s', '%s');\n",
                sql_escape($ip),
                sql_escape($ident),
                sql_escape($user),
                0,
                sql_escape($method),
                sql_escape($url),
                $status,
                $byte,
                sql_escape($referer),
                sql_escape($useragent);
        }
    }
}

sub sql_escape
{
    my $str = shift;
    $str =~ s{\\}{\\\\}g;
    $str =~ s{\'}{\\\"}g;
    $str;
}

__END__