Note: Backticks ` are used for strings within strings.
Note: Single quotes ' rather than backticks ` are used within strings.
// access historical data
db.t("Namespace", "Table")
// access intraday data
db.i("Namespace", "Table")
// access intraday data w/o ticking
db.i("Namespace", "Table", isLive)
db.getNamespaces()
db.getTableNames("Namespace")
// get a table with available namespaces and table names
db.getCatalog()
t.where("Condition"…)
// examples
t.where("Date > `2018-01-01`")
t.where("Timestamp <= '2018-01-01T16:00:00 NY'")
t.where("USym = `AAPL`")
// filters a list of values
t.where("USym in `AAPL`, `MSFT`, `GOOG`")
// case insensitive
t.where("USym icase in `aapl`, `msft`, `goog`")
t.where("USym not in `NVDA`, `AMZN`")
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")
// can match different column names
t1.whereIn(t2, "USym=UnderlyingSym")
// first number of rows
t.head(100)
// first percentage of rows
t.headPct(0.25)
// first number of rows for each subset
t.headBy(rows, "Column"…)
// last rows
t.tail(rows)
t.tailPct(decimalNum)
t.tailBy(rows, "Column"…)
t.sort("Column"…)
t.sortDescending("Column"…)
// reverses order of entire table
t.reverse()
// restricts sorting to specified columns
t.restrictSortTo("Column"...)
// removes sorting restrictions
t.clearSortingRestrictions()
t.select("ColumnFormula"…)
// evaluates data once and stores it in memory
t.update("ColumnFormula"…)
t.view("ColumnFormula"…)
// evaluates data but doesn't store results in memory
t.updateView("ColumnFormula"…)
// only keep unique values
t.selectDistinct("Column"…)
t.dropColumns("Column"…)
t.renameColumns("ColumnRename"…)
t.moveColumns(index, "Column"…)
t.moveUpColumns("Column"…)
// 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"…)
tables = t.byExternal("Column"…)
tables.getKeySet() //returns array of keys
table = tables.get("Key")
// The following is required if the table broken down by multiple columns
import com.fishlib.datastructures.util.SmartKey
table = tables.get(new SmartKey("Key"…))
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"…)
t.by(AggCombo(Agg…), "Column"…)
AggFirst("Column"…)
AggLast("Column"…)
AggSum("Column"…)
AggAvg("Column"…)
AggStd("Column"…)
AggVar("Column"…)
AggMedian("Column"…)
AggMin("Column"…)
AggMax("Column"…)
AggCount("Column")
// combined aggregation example
t.by(AggCombo(AggSum("Dollars", "Size"), AggAvg("AvgSize=Size")),"Symbol")
t1.naturalJoin(t2, "Match…", "ColumnToAdd…")
t1.exactJoin(t2, "Match…", "ColumnToAdd…")
t1.join(t2, "Match…", "ColumnToAdd…")
t1.leftJoin(t2, "Match…", "ColumnToAdd…")
// as-of joins matching subsets need to be ordered
t1.aj(t2, "Match…, AjMatch", "ColumnToAdd…")
t1.raj(t2, "Match…, AjMatch", "ColumnToAdd…")
yyyy-mm-ddThh:mm:ss.nanos TZ // date-time
#y#m#w#dT#h#m#s // period
hh:mm:ss.nanos // duration
// constants representing nanoseconds
threeSeconds = 3 * SECOND
fiveMinutes = 5 * MINUTE
twoHours = 2 * HOUR
sevenDays = 7 * DAY
currentDateNy()
lastBusinessDateNy()
formatDateNy(DateTime) //converts to a date string
currentTime()
t.formatColumns("ColumnName=Decimal(`.00#`)"…)
// or Decimal(`\$.00#`) or Decimal(`0.00#`)
// # means only display non-zero numbers in position
// 0 means display zeroes in position
t.formatColumns("ColumnName=Color")
t.formatColumnsWhere("ColumnName", "Condition", "Color")
t.formatRowWhere("Condition", "Color")
t.formatColumns("ColumnName=heatmap(Column, minValue, maxValue, minColor, maxColor)")
emptyTable(rows)
// create table with columns specified below
newTable(column…)
// append table rows to one another with same columns
merge(table…)
t.getMeta()