Compressed apache logs in Sqlite

user_1422096 0
Viewed 75

I'd like to be able to query my Apache logs with SQL syntax, in a similar way than the tool asql. I'm using the following code to import Apache logs into Sqlite:

import sqlite3, apache_log_parser  # pip install apache_log_parser
conn = sqlite3.connect('logs.db')
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS logs (server TEXT, port INTEGER, ip TEXT, time TEXT, url TEXT, status INTEGER, bytes INTEGER, referer TEXT, useragent TEXT)""")
parser = apache_log_parser.make_parser("%v:%p %h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"")
with open("other_vhosts_access.log") as f:
    for line in f:
        d = parser(line)
        cur.execute("""INSERT INTO logs VALUES (:server_name, :server_port, :remote_host, :time_received_isoformat, :request_url, :status, :bytes_tx, :request_header_referer, :request_header_user_agent)""", d)
cur.close()
conn.commit()
conn.close()

It works. However a one-month other_vhosts_access.log 200 MB file produces a nearly 200 MB Sqlite DB file (there is no compression). So in my case 1 year of log:

  • usually took 500 MB: 2 * 200 MB (2 last months as plain text) + 10 * 10 MB (10 previous months gzipped by logrotate)

  • now takes: 2.4 GB: 12 * 200 MB

Question: Is there a way to have logs.db (automatically?)-compressed but still be able to run read-only SELECT * FROM logs WHERE ... queries with Sqlite?

I've seen Sqlite ZIPVFS but this is not open source (and too expensive for my project).

compression logging sqlite apache python
1 Answers

JSON1 + compress + view

  1. Compile compress (it's previous version that compiles with older libsqlite3-dev, and you'll likely need zlib1g-dev installed) SQLite extension. JSON1 is almost always already there by default.

    gcc -g -fPIC -shared compress.c -o compress.so

  2. Chunk parsed log dicts, say by 1024 (so it compresses well), in a list, json.dumps the list and pass it to the INSERT which compress it. Create a view that would do the reverse on the SQLite's side.

Here is the table and the view.

import sqlite3

conn = sqlite3.connect('/path/to/db.sqlite')
conn.load_extension('/path/to/compress.so')
conn.execute('''
    CREATE TABLE "log_block" (
        "log_block_id"  INTEGER PRIMARY KEY AUTOINCREMENT,
        "created_at"    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        "block"         BLOB NOT NULL
    )
''')
conn.execute('''
    CREATE VIEW log_record AS
    SELECT 
        json_extract(value, '$.server') "server",
        json_extract(value, '$.port') "port",
        json_extract(value, '$.ip') "ip",
        json_extract(value, '$.time') "time",
        json_extract(value, '$.url') "url",
        json_extract(value, '$.status') "status",
        json_extract(value, '$.bytes') "bytes",
        json_extract(value, '$.referer') "referer",
        json_extract(value, '$.useragent') "useragent"
    FROM log_block, json_each(uncompress(log_block.block))
''')

An example of writing into the table is something like this.

import itertools
import json

sample = {
    'server': 'foo.bar',
    'port': '80',
    'ip': '127.0.0.1',
    'time': '2013-08-16T15:45:34',
    'url': '/',
    'status': '200',
    'bytes': '42',
    'referer': 'https://example.com/',
    'useragent': 'Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18)',
}

def chunk(iterable, n):
    return (
        tuple(filter(bool, c)) 
        for c in itertools.zip_longest(*[iter(iterable)] * n)
    )

log_records = itertools.repeat(sample, 10_000)

for c in chunk(log_records, 1024): 
    conn.execute(
        'INSERT INTO log_block(block) VALUES(compress(?))', 
        (json.dumps(c),)
    )
    conn.commit()

Then query the view like you would current uncompressed table.

conn.execute('SELECT * FROM log_record LIMIT 1').fetchone()

It's good for archival, but retrieval won't be fast (though still should be fine for your amount of data). Depending on your queries you can group log records by some fields, say url, instead of chunking arbitrarily and move them to columns of log_block table. Then you can easily index them.

SquashFS

This is only applicable to read-only (historic period) databases. SquashFS provides plenty of compression options:

The original version of Squashfs used gzip compression, although Linux kernel 2.6.34 added support for LZMA and LZO compression, Linux kernel 2.6.38 added support for LZMA2 compression (which is used by xz), Linux kernel 3.19 added support for LZ4 compression, and Linux kernel 4.14 added support for Zstandard compression.

Here's an example (mksquashfs comes from squashfs-tools package).

$ mkdir databases
$ sqlite3 databases/new.db \
  "CREATE TABLE foo(foo_id INT); INSERT INTO foo VALUES(123)"
$ mksquashfs databases/ databases.squashfs -comp xz

$ mkdir uncompressed
$ sudo mount databases.squashfs uncompressed -t squashfs -o loop
$ sqlite3 uncompressed/new.db "SELECT * FROM foo"
123
user_20720350
Related