lilo
is a CLI to download and tail Google Cloud Platform logs to a SQLite database.
# Install
npm install -g bun
npm install -g lilo-cli
# Note: `gcloud` also needs to be installed and signed in.
# Usage
lilo \
--resource-names "projects/YOUR_GCP_PROJECT_ID_HERE" \
--filter "timestamp > 2023-06-01" \
--db ./db.sqlite \
--watch 2000
See lilo help
for detailed flag docs.
-
Log JSON values from your application and ask questions later with SQL queries.
- Avoid creating a data schema for ad-hoc events/data when developing prototypes.
- GCP Logging supports JSON lines - your application can log JSON lines to stdout.
-
Use fast & familiar local tools.
- Use a native GUI such as TablePlus for viewing tables.
- Use SQLites SQL dialect instead of BigQuery.
- SQLite indexes on expressions can index a JSON path to speed up queries.
- Like this:
create index i_01 on logs(jsonPayload ->> "$.msg")
- Like this:
- Use the SQLite CLI to query and pipe output JSON to Excel or other tools to create reports.
- Any programming language that supports SQLite.
- Fast interactivity for small to medium datasets.
-
Avoid having to store large amounts of data forever in the cloud.
- Set your Logging retention to one month, and use lilo to archive your data to a local SQLite DB.
-
Query the db.sqlite on the server to respond to events.
- Use polling SQL queries to observe events.
-
Understand what's happening on your GCP account/audit logs.
-
Convert the SQLite file to DuckDB for fast analytical queries.
Name origin: SQLite GCP Logs
Example table schema and rows in TablePlus
How long does it take to do a full download?
- It depends on how many logs you have, but by default GCP accounts are limited to 60 HTTP read calls per minute, and each of those has 1000 entries.
-
lilo
begins downloading from the last log entry it downloaded.- You can start and stop the CLI at any time.
-
lilo
only downloads the log entries since the last run.
Why Bun?
- It has SQLite built in, so SQLite will not need to be compiled when installing
lilo
via NPM. - I wanted to test it out.
-
Set up a log sink to BigQuery, then pipe a BigQuery query result to Google Sheets.
-
Copy your existing logs to a Storage Bucket
- Use for log entries that were written before you set up a streaming log sink.
- https://cloud.google.com/logging/docs/routing/copy-logs
-
https://cloud.google.com/logging/docs/samples/logging-tail-log-entries
-
https://cloud.google.com/logging/docs/reference/v2/rest/v2/entries/tail
- The tail endpoint does not work with JSON, only GRPC.
- Bun does not currently support GRPC, so
lilo
pollsentries:list
-
gcloud alpha logging tail
-
https://til.simonwillison.net/cloudrun/tailing-cloud-run-request-logs