Table Operations

Accessing and Creating Tables

Getting Tables

db.t("Namespace", "Table") //access historical data

db.i("Namespace", "Table") //access intraday data

db.i("Namespace", "Table", isLive) //access intraday data w/o ticking

db.getNamespaces()

db.getTableNames("Namespace")

db.getCatalog() //get a table with available namespaces and table names

User Tables

Non-Partitioned Tables

db.addTable("Namespace", "TableName", table)

db.replaceTable("Namespace", "TableName", table)

db.removeTable("Namespace", "TableName")

Partitioned Tables

db.addPartitionedTableDefinition("Namespace", "TableName", "PartitioningColumn", table.getDefinition())

db.addPartitionedTableDefinitionIfAbsent("Namespace", "TableName", "PartitioningColumn", table.getDefinition())

db.addTablePartition("Namespace", "TableName", "PartitioningValue", table)

db.replaceTablePartition("Namespace", "TableName", "PartitioningValue", table)

db.removeTablePartition("Namespace", "TableName", "PartitioningValue")

Time Tables

db.timeTable("hh:mm:ss") //ticking table with single Timestamp column

Csv Tables

readCSV("filepath", "delimiter") //read csv file on query server into table

Preemptive Tables

Publishing

table.preemptiveUpdatesTable(milliseconds)

Subscribing

client = PersistentQueryTableHelper.getClientForPersistentQuery(log, "Username", "ScriptName", timeoutMilliseconds)

preemptiveTable = client.getPreemptiveTable("PreemptiveTableName")

Input Tables

Creation

input = InputTable.newInputTable(database, "Namespace", "TableName",
   cKey("ColName", dataType)..., //key columns
    c("ColName", dataType)... //non-key columns
   )

Retrieval

t = input.getTable()

t = InputTable.inputTable(db, "Namespace", "TableName")

Note: you can look at history of changes with db.i("Namespace", "InputTableName").

Layout Hints

Locking Column Order

t.layoutHints(
com.illumon.iris.db.tables.utils.LayoutHintBuilder
   .atFront("ColName")          //locks column(s) to front of table
   .atEnd("ColName")            //locks column(s) to end of table
   .savedLayouts(false))        //prevents layout changes from being saved

AutoFilter

t.layoutHints("autofilter=ColumnA,ColumnB,ColumnC")

Filtering Data

Note: Backticks (`) are used for strings within strings and single quotes (') are used for times within strings.

where

t.where("Condition"…)

Examples

t.where("Date > `2018-01-01`")

t.where("Timestamp <= '2018-01-01T16:00:00 NY'")

t.where("USym = `AAPL`")

t.where("USym in `AAPL`, `MSFT`, `GOOG`") // filters a list of values

t.where("USym icase in `aapl`, `msft`, `goog`") //case insensitive

t.where("USym not in `NVDA`, `AMZN`")

wherein/whereNotIn

t1.whereIn(t2, "Match"…) //filter to rows that match

t1.whereNotIn(t2, "Match"…) //filter to rows that do not match

Examples

t1.whereIn(t2, "USym")

t1.whereIn(t2, "USym=UnderlyingSym") //can match different column names

head/tail

t.head(rows) //first number of rows

t.headPct(decimalNum) //first percentage of rows

t.headBy(rows, "Column"…) //first number of rows for each subset

t.tail(rows) //last number of rows

t.tailPct(decimalNum)

t.tailBy(rows, "Column"…)

Data Selection

In-memory

t.select("ColumnFormula"…)

t.update("ColumnFormula"…) //evaluates data once and stores it in memory

On-the-fly

t.view("ColumnFormula"…)

t.updateView("ColumnFormula"…) //evaluates data but doesn't store results in memory

Other Methods

t.selectDistinct("Column"…) //only keep unique values

t.dropColumns("Column"…)

t.renameColumns("ColumnRename"…)

t.moveColumns(index, "Column"…)

t.moveUpColumns("Column"…)

Sorting Data

t.sort("Column"…)

t.sortDescending("Column"…)

t.restrictSortTo("Column"...) //restricts sorting to specified columns

t.clearSortingRestrictions() //removes sorting restrictions

t.reverse() //reverses order of entire table

Grouping and Aggregating Data

By and Ungroup

Data in columns other than those specified are grouped into arrays:

t.by("Column"…)

Distributes values in specified columns into arrays; each array value becomes its own row:

t.ungroup("Column"…)

ByExternal

tables = t.byExternal("Column"…)

table = tables.get("Key")

tables.getKeySet() //returns array of keys

The following is required if the table is broken down by multiple columns:

import com.fishlib.datastructures.util.SmartKey
table = tables.get(new SmartKey("Key"...))

Simple Aggregations

t.firstBy("Column"…)

t.lastBy("Column"…)

t.sumBy("Column"…)

t.avgBy("Column"…)

t.stdBy("Column"…)

t.varBy("Column"…)

t.medianBy("Column"…)

t.minBy("Column"…)

t.maxBy("Column"…)

t.countBy("CountColumn", "Column"…)

Combined Aggregations

t.by(AggCombo(Agg…), "Column"…)

AggFirst("Column"…)

AggLast("Column"…)

AggSum("Column"…)

AggAvg("Column"…)

AggStd("Column"…)

AggVar("Column"…)

AggMed("Column"…)

AggMin("Column"…)

AggMax("Column"…)

AggCount("Column"…)

AggArray("Column"…)

AggPct(pct, "Column"…)

AggWAvg("Weight column", "Column"…)

Combined aggregation example:

t.by(AggCombo(AggSum("Dollars", "Size"), AggAvg("AvgSize=Size")),"Symbol")

Joining Data from Multiple Tables

t1.naturalJoin(t2, "Match…", "ColumnToAdd…")

t1.exactJoin(t2, "Match…", "ColumnToAdd…")

t1.join(t2, "Match…", "ColumnToAdd…")

t1.leftJoin(t2, "Match…", "ColumnToAdd…")

t1.aj(t2, "Match…, AjMatch", "ColumnToAdd…")

t1.raj(t2, "Match…, AjMatch", "ColumnToAdd…")

Note: in as-of joins, matching subsets need to be ordered.

Formatting Tables

Numeric

t.formatColumns("ColumnName=Decimal(`0.00`)"…) //or Decimal(`\$.00`) or Decimal(`0.00%`)

Note: # means only display non-zero numbers in position; 0 means also display zeroes in position

Color

t.formatColumns("ColumnName=Color")

t.formatColumnWhere("ColumnName", "Condition", "Color")

t.formatRowWhere("Condition", "Color")

t.formatColumns("ColumnName=heatmap(Column, minValue, maxValue, minColor, maxColor)")

Special Table Operations

col("ColumnName", value…)

longCol("ColumnName", value…)

intCol("ColumnName",value…)

shortCol("ColumnName", value…)

byteCol("ColumnName", value…)

charCol("ColumnName", value…)

doubleCol("ColumnName", value…)

floatCol("ColumnName", value…)

Rounds all decimal columns in a given table to whole numbers:

roundDecimalColumns(tableToRound)

Rounds all columns in a given table except for columns specified:

roundDecimalColumnsExcept(tableToRound, "Column"...)

t.getColumn("Column").getDirect() //get column values as an array

t.getColumn("Column").get(index) //get cell value

writeCsv(table, "filePath")

Table Tools

emptyTable(rows)

newTable(column…) //create table with columns specified below

merge(table…) //append table rows to one another with same columns

Metadata

t.getMeta()

Snapshotting Methods

controlTicks will only refresh upon triggerTable refreshing:

controlTicks = triggerTable.snapshot(targetTable, "SnapTime=Timestamp")

With snapshotIncremental, only rows that need to update will update:

controlTicks = triggerTable.snapshotIncremental(targetTable, "SnapTime=Timestamp")

The entire table is appended to itself with updates:

controlTicks = triggerTable.snapshotHistory(targetTable)

Last Updated: 23 September 2019 12:17 -04:00 UTC Deephaven v.1.20181212 (See other versions)