Deephaven Core Query Writing

References

Do NOT guess or rely on memory. Deephaven APIs have specific patterns that differ from similar libraries.

Core Principles

YOU NEVER ADD PRINT STATEMENTS TO PRINT TABLES, DO NOT CONVERT TO PANDAS JUST TO PRINT

Filter early. Place partition/grouping column filters first in where() to exclude data early.

Do as much in-engine as possible. Deephaven's Java engine is highly optimized. Prefer built-in operations over Python UDFs.

Avoid Python UDFs in query strings. Each Python call crosses the Python-Java boundary (slower). Use built-in functions from java.lang.Math, time functions, and auto-imported query language functions instead.

Don't use pandas for intermediate steps. Converting to pandas and back is slow and can cause memory issues. Use Deephaven's native operations. Avoid pandas unless specifically asked for.

All imports at the top of the file. Import only the modules you need. Never use __import__() or inline imports mid-script.

Example Table Creation

from deephaven import agg, empty_table, merge, new_table, read_csv, time_table, ui
from deephaven.column import double_col, string_col
from deephaven.plot import express as dx

# Empty table with formulas
t = empty_table(100).update(["X = i", "Y = X * 2"])

# New table from columns
t = new_table(
    [string_col("Sym", ["AAPL", "GOOG"]), double_col("Price", [150.0, 140.0])]
)

# Ticking time table (real-time)
t = time_table("PT1s")  # accepts durations

# Ring table (bounded size, keeps last N rows)
source = time_table("PT1s")
t = ring_table(source, capacity=1000)

Extracting Scalar Values

print(table) only shows the object reference. To get actual values for a specific cell:

from deephaven import empty_table

table = empty_table(1).update(["ColName = 42.5"])

# Get a scalar from a 1-row result table
value = table.j_table.getColumnSource("ColName").get(
    table.j_table.getRowSet().firstRowKey()
)
print(f"Result: {value:,.2f}")

Column Operations

from deephaven import empty_table

t = empty_table(100).update(["A = i", "B = i * 2", "OldName = i", "Unwanted = i"])

# select — returns ONLY named columns; stores results in RAM
# Best for: column subset with expensive formulas or frequently accessed results
t.select(["A", "B", "C = A + B"])

# view — returns ONLY named columns; recalculates on every access (no RAM cost)
# Best for: column subset with cheap formulas, sparse access, or memory pressure
t.view(["A", "B", "C = A + B"])

# update — keeps ALL columns + adds new; stores results in RAM
# Best for: results that feed downstream ops (joins, aggs, further updates)
t.update(["C = A + B", "D = sqrt(C)"])

# update_view — keeps ALL columns + adds new; recalculates on every access
# Best for: display-only columns, or when memory is a concern
t.update_view(["C = A + B"])

# lazy_update — keeps ALL columns + adds new; memoizes by input value
# Best for: few distinct inputs relative to row count (e.g. category lookups)
t.lazy_update(["C = A + B"])

# drop_columns — remove columns
t.drop_columns(["Unwanted"])

# select_distinct — unique rows for named columns
t.select_distinct(["A"])  # unique values of A
t.select_distinct(["A", "B"])  # unique (A, B) pairs

# rename_columns — rename columns
t.rename_columns(["NewName = OldName"])

Filtering

import datetime

from deephaven import new_table
from deephaven.column import datetime_col, double_col, string_col

t = new_table(
    [
        string_col("Sym", ["AAPL", "GOOG", "MSFT", "AMZN"]),
        double_col("Price", [150.0, 140.0, 200.0, 50.0]),
        string_col("Description", ["no error", "has error", "fine", "ok"]),
        datetime_col(
            "Timestamp",
            [
                datetime.datetime(2024, 6, 1, tzinfo=datetime.timezone.utc),
                datetime.datetime(2024, 6, 2, tzinfo=datetime.timezone.utc),
                datetime.datetime(2024, 6, 3, tzinfo=datetime.timezone.utc),
                datetime.datetime(2024, 6, 4, tzinfo=datetime.timezone.utc),
            ],
        ),
    ]
)

filter_table = new_table([string_col("Sym", ["AAPL", "GOOG"])])

# Basic where
t.where("Price > 100")
t.where(["Sym = `AAPL`", "Price > 100"])  # AND logic

# String matching (Java String methods)
t.where("Sym.startsWith(`A`)")
t.where("Description.contains(`error`)")

# Set membership (fast)
t.where("Sym in `AAPL`, `GOOG`, `MSFT`")
t.where_in(filter_table, "Sym")
t.where_not_in(filter_table, "Sym")

# Time filtering
t.where("Timestamp > parseInstant(`2024-01-01T00:00:00 America/New_York`)")

Joins Overview

Read references/joins.md before using joins.

Vertical stacking: from deephaven import merge / merge([t1, t2]) — tables must have matching column names and types.

Aggregations Overview

Read references/aggregations.md before using aggregations.

Dedicated aggs operate on ALL non-key columns. If any are non-numeric (String, Timestamp), they throw UnsupportedOperationException. Fix: add .view() before the agg to keep only key + numeric columns, or use agg_by with explicit cols.

Query String Syntax

Literals:

• Boolean: true, false (lowercase)
• Int: 42, Long: 42L, Double: 3.14 (underscores like 1_000L are NOT supported). Bare integers in division produce double: (year / 10) * 10 → 1987.0. Fix: year - year % 10 or (int)(year / 10) * 10
• String: backticks `hello`
• DateTime: parseInstant(\2024-01-01T12:00:00 America/New_York`)(short aliases likeNY` do NOT work)
• Duration: 'PT1h30m', Period: 'P1y2m3d'

Built-in variables:

• i - row index (0-based)
• ii / k - row key (stable identifier)

Ternary operator:

from deephaven import empty_table

t = empty_table(10).update(["Price = i * 25.0"])
t.update(["Category = Price > 100 ? `High` : `Low`"])

Null handling:

from deephaven import empty_table

t = empty_table(10).update(["Value = i % 2 == 0 ? NULL_INT : i"])
t.update(["Safe = isNull(Value) ? 0 : Value"])

Data Import/Export

from deephaven import empty_table, read_csv, write_csv
from deephaven.parquet import read, write

t = empty_table(10).update(["X = i", "Y = X * 2"])

# CSV
write_csv(t, "/tmp/output.csv")
t_csv = read_csv("/tmp/output.csv")

# Parquet
write(t, "/tmp/output.parquet")
t_parquet = read("/tmp/output.parquet")

Converted and distributed by TomeVault — claim your Tome and manage your conversions.