Easier log analysis in SQL with a standard library for SQLite in Go

This release brings an entire standard library of string, regexp, math, url parsing, best-effort date parsing, statistical aggregation, and hashing/encoding functions to the SQL code panel (and to dsq). This addition makes it much easier to analyse arbitrary data with less code. SQL code panels (and SQLite database panels) now autocomplete all of these new functions and all functions built into SQLite.

This release upgrades the "download panel results" feature, adding more formats and copy-to-clipboard. It also adds formatting support for SQL panels, and other UX improvements. And it improves in-app onboarding and provides sample data and sample projects for folks who want a quick tour.

A standard library for SQL code panels

Taking a step back for a second, DataStation allows you to query many kinds of databases. But it also allows you to script with code. One of the options for scripting with code is an in-app SQL system. This system is based on SQLite. Being able to script with SQL makes some kinds of transformations and joins very easy.

But until now you could only do so as long as your data was already in a format you wanted. SQL code panels use SQLite under the hood and SQLite has relatively few builtin functions.

So I recently built and open-sourced go-sqlite3-stdlib, a collection of libraries for use in DataStation, dsq, and any other Go programs using SQLite.

Here are a few screenshots of the docs for the new functions. Screenshots because this release announcement is not the authority, the go-sqlite3-stdlib repo is the authority.

This is particularly useful for enabling SQL analysis of logs in DataStation. In fact, there's even a new sample project included with DataStation (you'll read more about this below) that demonstrates SQL analysis of nginx logs.

The code editor in DataStation also autocompletes all of these functions and all builtin SQLite ones. But you can always read the docs if you'd prefer.

Download, copy-to-clipboard

Before this release, downloading a panel created a CSV or JSON file depending on if the panel result was table-like. In this release you now have the option to export to CSV, JSON, HTML Table or Markdown Table. And you can copy to clipboard instead of downloading if you know you're just going to paste the result somewhere (like a blog post).

Just remember, the tabular data formats will only be an option if the panel result is an array of objects.

SQL Formatting

This release brings support for reformatting SQL code. In the future this formatting ability will be extended to all code types. For now, just SQL.

In-app onboarding

When you create a new project, you will now have the option to pick from an existing template that uses sample data bundled in with the DataStation install. These are to help new folks try out parts of DataStation easily without needing to bring any of their own data yet.

Now, almost all of the existing tutorials will get you running with only DataStation and Docker. But these templates are even easier. They require nothing other than DataStation. All the sample data comes with the DataStation install.

NOTE: on macOS you will need to copy DataStation into some permanent directory like Applications otherwise macOS appears to put DataStation in a read-only directory that messes with loading bundled sample data.

And when you create a new blank project, the empty project will contain links to tutorials and also an embedded video showing some of DataStation.

There is also now a new Help tab in the navigation sidebar, and associated page.

Misc

• Changes to standard HTML input elements now only take effect onblur; that is, when you click away from the input or switch tabs/windows.
• File panels now run automatically on change similar to tables and graph panels.
• File and HTTP panels now recognized .xslm as an Excel file extension.

Install or upgrade

Get the 0.11.0 release now!

How you can help

Subscribe to updates!

If you are a developer, data scientist, or engineering manager, install DataStation and start using it at work! Report bugs and usability issues (there are surely many).

If you're a developer with some Go experience wanting to contribute to OSS, check out the Good First Projects page.

Share

DataStation 0.11.0 is out! Check out the release notes and give it a go.https://t.co/ekYTUrMpXv

— Multiprocess Labs (@multiprocessio) May 26, 2022