runreveal / pql

Pipelined Query Language

pipelined query language

This Go library compiles a pipelined-based query language (inspired by the Kusto Query Language) into SQL. It has been specifically tested to work with the Clickhouse SQL dialect, but the generated SQL is intentionally database agnostic. This repository contains a Go library, and a CLI to invoke the library.

For example, the following expression:

StormEvents
| where DamageProperty > 5000 and EventType == "Thunderstorm Wind"
| top 3 by DamageProperty

will be compiled to SQL that is similar to:

SELECT *
FROM StormEvents
WHERE DamageProperty > 5000 AND EventType = 'Thunderstorm Wind'
ORDER BY DamageProperty DESC
LIMIT 3;

Getting Started

If you'd like to see a demo along with some examples, check out https://pql.dev.

To use pql in your go code, a minimal example might look like this

package main

import (
	"github.com/runreveal/pql"
)

func main() {
	sql, err := pql.Compile("users | project id, email | limit 5")
	if err != nil {
		panic(err)
	}
	println(sql)
}

Running this program should give you the following output

$ go run test.go

WITH "__subquery0" AS (SELECT "id" AS "id", "email" AS "email" FROM "users")
SELECT * FROM "__subquery0" LIMIT 5;

Documentation

The following tabular operators are supported and the Microsoft KQL documentation is representative of the current pql api.

• as
• count
• join
• project
• extend
• sort/order
• summarize
• take/limit
• top
• where

The following scalar functions are implemented within pql. Functions not in this list will be passed through to the underlying SQL engine. This allows the usage of the full APIs implemented by the underlying engine.

• not
• now
• isnull and isnotnull
• strcat
• iff/iif
• count
• countif
• tolower
• toupper

Column names with special characters can be escaped with backticks.

Get involved

• Join our discord
• Contribute a scalar function

License

Apache 2.0

pql's Issues

Eliminate unnecessary parentheses for simple subexpressions

To preserve evaluation order, I'm inserting parentheses around all binary operators. However, for subexpressions that do not contain another binary operator in the resulting SQL, these parentheses are unnecessary.

Create `clickhouse local` test harness

Ensures correctness of generated queries beyond the manual review required in #3.

Create golden-output integration test suite

Used to detect regressions in SQL output as changes are made. Should support a record mode.

Implement `join` operator

Reference

Add a basic CLI/REPL

Implement `take`/`limit` operator

Create parser

• Tabular expression structure
• #23
• Parse count tabular operator
• Parse where tabular operator
• Parse take tabular operator
• Parse project tabular operator
• Parse summarize tabular operator
• Parse sort tabular operator

Question on Validation

How can we validate the generated query. For e.g.

Allowed Databases, Allowed Tables, Allowed Fields specific to each Table, on Where conditions checking for certain conditions, on limits checking the min and max limit value that the user can pass etc

If the user will be writing the query in PQL format. We want to validate for valid tables, but also limit the way the user queries instead of allowing them to query everything.

Implement translation for some basic functions

• strcat should be converted to the SQL || operator
• isnull and isnotnull
• not
• others...?

Add extra string operators

There are some extra string operators that have a variety of keywords and forms that can be investigated.

Add operators and parentheses to lexer

Part of #5

• Logical operators
• Numerical operators
• String operators

Add lexing for comments

Set up GitHub Actions CI

Add "let" operator

The let operator allows users to assign variables to be used in queries. Example follows.

let n = 10;  // number
let place = "Dallas";  // string
let cutoff = ago(62d); // datetime 
Events 
| where timestamp > cutoff 
    and city == place 
| take n

Kusto docs:

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/let-statement

Expression parsing

Needed for #8

Support for Parameterized Queries

We should figure out how to support parameterized queries for databases.

They have various distinct ways of being implemented that we should be able to support

We may be able to use the passthrough mechanisms plus some side-channel loading of parameters somehow, as an idea.

Implement `where` operator

Parse `in` and `!in` operators

Somewhat a combination of function and binary operators, since the syntax is x in (y, z, ...).

Implement `top` operator

Reference

Equivalent to sort by x | take y

Apply open source license

• Select license
• Add file license headers
• Add VSCode copyright snippets

Add an LSP / Autocompletion support

Not sure what the best way to approach this problem is, but support for an LSP would be huge for obvious reasons.

Theoretically, this should be more straight forward than SQL because we have better context at the point of typing.

That is, context flows through the pipe, versus SQL SELECT a, b, c FROM X where we don't know the possible values for a, b, or c until the table X is specified.

Add string constants to lexer

Part of #5

Document how to add new functions and operators

From @ejcx:

One thing that might be helpful to add [...] is a CONTRIBUTING.md and information on the general how to add new operators and translation target syntaxes.

Decide on name

Keeping an issue open here to decide on the language/repository name and rename things as appropriate.

Create lexer

• Identifiers
• Quoted identifiers
• Pipes
• #15
• #18
• #19
• #20

Implement `summarize` operator

Reference

Add numeric constants to lexer

Part of #5.

Implement `as` operator

Reference

Since we're already extracting CTEs anyway, we can just have a no-op that captures aliases at particular points of a pipeline.

Add parser tests for errors

How does it differ from PRQL?

This seems very similar to the effort done by the PRQL team.

How does this project differ and have you considered joining forces instead of creating a new SQL competitor.

Handle subquery/CTE with operators

Implement `count` operator

Map key access

From @abraithwaite:

Support for map column type would be helpful I think. Not sure the best way to do this, but the syntax for a map key access is: mapcol['strkey'].

Functions in project

Not sure if expected or not. I want to do some post-processing of columns that are selected.

runreveal_logs
	| project toLower(eventName), notEmpty(eventName)
	| limit 5

Are things like this currently supported? I couldn't quite figure out if they were or not with another syntax. This way I'm currently doing it I get this error:

pql: parse pipeline query language: 2:26: expected '|', got '('
pql: one or more statements could not be compiled

Implement `project` operator

Add lexing for quoted identifiers

Syntax for ["foo"] or ['foo']. Details

Implement built-in identifiers

• true
• false
• null

Decide how to approach Nix dependencies in GitHub Actions CI

In other projects, I use Nix to manage the CI steps (example). This guarantees that running nix flake check locally reproduces the CI build exactly, but puts Nix in a more prominent place in the build process, which may not be desirable.

Alternatively, we could use Nix just as a package manager and use it to install Clickhouse (or possibly Go as well) in CI.

Implement `sort`/`order` operator