Execute SQL queries in Deephaven

Structured Query Language (SQL) is the most popular programming language for database management and access. Its popularity can be attributed to a number of factors, including its simplicity, readability, and interoperability. Deephaven supports executing SQL queries against in-memory Deephaven tables using the Sql and SqlAdapter Java classes.

This guide will show you how to execute SQL queries on Deephaven tables in your script session.

The `Sql` class

The Sql class provides high-level methods for executing SQL queries against tables in your script session. It offers two main methods:

evaluate - Executes a SQL query and returns a Deephaven table.
dryRun - Parses a SQL query into a TableSpec without executing it. Useful for validation.

These methods automatically use tables from your script session's query scope as the catalog for SQL queries.

Execute a query with `evaluate`

The simplest way to execute SQL against Deephaven tables is with evaluate():

import io.deephaven.engine.sql.Sql

// Create some example tables in the script session
source = emptyTable(10).update("X = i", "Y = i * 2")
other = emptyTable(10).update("X = i + 5", "Y = i * 3")

// Execute SQL query against the tables
result = Sql.evaluate("SELECT source.X, other.Y FROM source JOIN other ON source.X = other.X")

The query references tables by their variable names in the script session (source and other). The result is a new Deephaven table.

Supported SQL features

Deephaven's SQL implementation supports:

SELECT statements with column selection and aliasing.
WHERE clauses for filtering.
JOIN operations (INNER, LEFT, RIGHT, FULL).
GROUP BY and aggregate functions (SUM, COUNT, AVG, MIN, MAX).
ORDER BY for sorting.
LIMIT and OFFSET for pagination.
Standard SQL expressions and operators.

Validate queries with `dryRun`

Use dryRun to validate SQL syntax and parse the query without executing it:

import io.deephaven.engine.sql.Sql

source = emptyTable(10).update("X = i", "Y = i * 2")
other = emptyTable(10).update("X = i + 5", "Y = i * 3")

// Parse the query without executing
tableSpec = Sql.dryRun("SELECT source.X, other.Y FROM source JOIN other ON source.X = other.X")

// Execute the same query to get a table
result = Sql.evaluate("SELECT source.X, other.Y FROM source JOIN other ON source.X = other.X")

This is useful for:

Validating SQL syntax before execution.
Validating a query quickly without the overhead of execution.
Inspecting the query plan.
Building tools that work with SQL queries.

Advanced usage with `SqlAdapter`

For more control over SQL parsing, use SqlAdapter.parseSql(). This method allows you to specify an explicit Scope (catalog) instead of using the script session's query scope.

import io.deephaven.engine.table.Table
import io.deephaven.qst.column.header.ColumnHeader
import io.deephaven.qst.table.TableHeader
import io.deephaven.qst.type.Type
import io.deephaven.qst.table.TicketTable
import io.deephaven.sql.ScopeStaticImpl
import io.deephaven.sql.TableInformation
import io.deephaven.sql.SqlAdapter
import io.deephaven.engine.sql.TableCreatorTicketInterceptor
import io.deephaven.engine.table.impl.TableCreatorImpl

// Create tables
t1 = emptyTable(10).update("X = i", "Y = i * 2")
t2 = emptyTable(10).update("X = i + 1", "Y = i * 3")

// Helper to convert TableDefinition to TableHeader
def headerFrom = { Table t ->
    def builder = TableHeader.builder()
    for (cd in t.getDefinition().getColumns()) {
        builder.addHeaders(ColumnHeader.of(cd.getName(), Type.find(cd.getDataType(), cd.getComponentType())))
    }
    builder.build()
}

// Create TicketTable specs for catalog entries
def tt1 = TicketTable.of(("sqlref/t1").getBytes("UTF-8"))
def tt2 = TicketTable.of(("sqlref/t2").getBytes("UTF-8"))

// Build explicit Scope with table metadata
def scope = ScopeStaticImpl.builder()
    .addTables(TableInformation.of(["t1"], headerFrom(t1), tt1))
    .addTables(TableInformation.of(["t2"], headerFrom(t2), tt2))
    .build()

// Parse SQL with explicit scope
def tableSpec = SqlAdapter.parseSql("SELECT t1.X, t2.Y FROM t1 JOIN t2 ON t1.X = t2.X", scope)

// Materialize the TableSpec with TicketTable mapping
def ticketMap = [(tt1): t1, (tt2): t2]
result = tableSpec.logic().create(new TableCreatorTicketInterceptor(TableCreatorImpl.INSTANCE, ticketMap))

This approach is useful when:

Building custom SQL tooling.
Integrating SQL parsing into other systems.
You need full control over the catalog/scope.

Choose the right method

Each of the above methods serves different use cases.

evaluate: This is the simplest and most direct way to run SQL queries against tables in your script session. Use it if you don't need control over the scope.
dryRun: Use this when you want to validate SQL syntax without executing it. It's a lightweight way to check your queries.
parseSql: Use this for advanced use cases requiring explicit scope control or custom SQL tooling, or if you want to use tables not in your script session.

Execute SQL queries in Deephaven

The Sql class

Execute a query with evaluate

Supported SQL features

Validate queries with dryRun

Advanced usage with SqlAdapter

Choose the right method

Related documentation

The `Sql` class

Execute a query with `evaluate`

Validate queries with `dryRun`

Advanced usage with `SqlAdapter`