Sqlite support for query-cloudwatch

[matthinz]

This PR updates bin/query-cloudwatch to support --sqlite as an option.

When used, it will write Cloudwatch log records to a Sqlite database (events.db by default).

A few notes:

• Your query must return @timestamp and @message fields. If it does not, you will get an error
• It's optimized for working with events.log, where @message is always JSON, but it will work with other logs as well
• You can't use --count-distinct in combination with --format sqlite

Schema

The command will write events to a table with the following schema:

CREATE TABLE IF NOT EXISTS events (
  id TEXT PRIMARY KEY NOT NULL,
  timestamp TEXT NOT NULL,
  name TEXT NULL,
  user_id TEXT NULL,
  success INTEGER NULL,
  message TEXT NOT NULL,
  log_stream TEXT NULL,
  log TEXT NULL  
)

timestamp contains an ISO-8601 timestamp (in UTC). You can use SQLite's built-in date and time functions to work with it, e.g.:

SELECT timestamp, timediff(current_timestamp, timestamp) AS time_ago FROM events;

For events.log, message contains the original JSON, and you can use SQLite's JSON functions to work with that, e.g.:

SELECT json_extract(message, '$.visitor_id') AS visitor_id, COUNT(*) FROM events GROUP BY visitor_id;

name, user_id, and success are all automatically populated when using events.log.

log_stream and log are optional, and will be set to @logStream and @log if your query includes them.

Example

Grab 10 records and put them in events.db:

aws-vault exec prod-analytics -- bin/query-cloudwatch --app idp --env prod --log events.log --from 1h --sqlite <<QUERY
fields @timestamp, @message
| limit 10
QUERY

Wait how does sqlite work

You can open an interactive session like this:

$ sqlite3 events.db

From there:

• .headers on turns on table headers
• .tables lists the available tables
• If you are selecting a lot of columns, you might want to try .mode line to make the output easier to read in a terminal
• Put a ; at the end of your query