Ability to insert multi-line files

[jeqo]

I was looking into how to parse application log files that contain multiline text (e.g. Java stack traces) into sqlite.
I can see that at the moment --lines helps, but falls short when processing multi-line texts.

I wonder if this functionality would be useful for sqlite-utils. A similar approach to Elastic logstash/filebeat can be adopted: https://www.elastic.co/guide/en/beats/filebeat/current/multiline-examples.html

Potential changes:

• add a --multiline option
• additional properties for
  • multiline-pattern (regex expression)
  • multiline-negate: true/false
  • multiline-what: previous or next

Or if this is achievable in a different way, please share. Thanks!

[simonw]

It should be possible to achieve this with the --text option: https://sqlite-utils.datasette.io/en/stable/cli.html?highlight=text#convert-with-text

Given an example like this in multiline.log:

2022-03-01T12:04:52: Here is a log message
  that spans multiple lines
2022-03-01T12:04:52: This is a single line
2022-03-01T12:04:52: Here is another message
  that spans multiple lines

You should be able to run something like this:

sqlite-utils insert /tmp/log.db log multiline.log --text --convert "
import re

r = re.compile(r'^(?P<datetime>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}):(?P<log>.*)', re.MULTILINE)

def convert(text):
    return [m.groupdict() for m in r.finditer(text)]
"

After running this I get:

sqlite-utils rows /tmp/log.db log
[{"datetime": "2022-03-01T12:04:52", "log": " Here is a log message"},
 {"datetime": "2022-03-01T12:04:52", "log": " This is a single line"},
 {"datetime": "2022-03-01T12:04:52", "log": " Here is another message"}]

[jeqo]

🤯 this is beautiful. Thanks @simonw !

[jeqo]

For completeness, the regex requires a bit more dark magic to capture the following lines, here is a working expression: https://regex101.com/r/rsuEcs/1

sqlite-utils insert /tmp/log.db log multiline.log --text --convert "
import re

r = re.compile(r'^(?P<datetime>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})(?:\:\s)(?P<log>(.*\s\s.*|.*)+)', re.MULTILINE)

def convert(text):
    return [m.groupdict() for m in r.finditer(text)]
"

BEGIN TRANSACTION;
CREATE TABLE [log] (
   [datetime] TEXT,
   [log] TEXT
);
INSERT INTO "log" VALUES('2022-03-01T12:04:52','Here is a log message
  that spans multiple lines');
INSERT INTO "log" VALUES('2022-03-01T12:04:52','This is a single line');
INSERT INTO "log" VALUES('2022-03-01T12:04:52','Here is another message
  that spans multiple lines');
COMMIT;

[simonw]

Just saw your great write-up on this: https://jeqo.github.io/notes/2022-09-24-ingest-logs-sqlite/