Tool to put an HTTP protocol file in a database (with a specific schema)?
So, I want to put last year from Tomcat Combined Format files to the database. There are zillions and zillions of hits.
(The plan is to run ad-hoc and ad-hoc queries against it and match against some other data. We have some questions that the existing log analysis products out there cannot answer us.)
I'm looking for...
- reliable tool to cleanly import my file into the database
- existing (typed, thoughtful, buggy) schema for storage and structure
I'm half a step away from skating on my own, but it's like what I've done before - millions of times.
a source to share
I'll just write a script ..
It may have been written many times before, but I doubt it will be for the correct database or for your specific log configuration (not sure about the W3C extended log format, but with many others you can define your own formatting)
Looking at the log format doc, it should be pretty trivial for each field declaration to create a column for it for it ..
Then, to parse the example log from the log format document:
#Version: 1.0
#Date: 12-Jan-1996 00:00:00
#Fields: time cs-method cs-uri
00:34:23 GET /foo/bar.html
12:21:16 GET /foo/bar.html
12:45:52 GET /foo/bar.html
12:57:34 GET /foo/bar.html
.. the following script will work fine and it will only take a few minutes to write:
import re
import sys
mr = re.compile("^(\d\d:\d\d:\d\d) ([A-Z]+) (.+)$")
def insert_into_database(time, rtype, uri):
print "INSERT INTO database (%s, %s, %s)" % (time, rtype, uri)
for line in open("logfile.log").readlines():
m = mr.match(line)
if not m:
sys.stderr.write("Invalid line: %s\n" % line.strip())
else:
insert_into_database(m.group(1), m.group(2), m.group(3))
May not be the most robust / reliable script ever, but it works (well, except for the insert_into_database function!)
a source to share
This should start you off in the right direction:
Apache Logging to MySQL http://onlamp.com/pub/a/apache/2005/02/10/database_logs.html
It's pretty easy to adapt to a different database or customize the schema. Very little for a schema - just a regular table will do with the appropriate fields and indexes for efficient lookups.
a source to share
The first thing to understand is that depending on the number of log files, this is not easy, and it hasn't been done a million times. If you have gigabytes a day, you get a terabyte database. The flat table approach doesn't do it well.
I am doing this using a star schema. The idea is to keep the fact table (logs) small and move large fields (URL path, user agent, referrer) to dimension tables. The MD5 hashes (stripped down to 64 bits) give me convenient keys to join dimensions to the fact table. In addition, there are materialized views with parsed URL paths.
The good news is that being able to run indexed SQL queries in your log files will actually get you somewhere. Using graphana, we have a weekly data graph showing the percentage of product detail pages that took over a second.
a source to share