Downsample Imports

Downsample imports are used to import a downsampled version of data from an existing time-series table into an intraday partition. The target table must already exist. Unlike other styles of import, Deephaven provides no "schema creator" tool, as the target table typically takes a very similar or identical form to the source table. The downsampler query reads historical data and produces a new historical table.

Quickstart

The following is an example of how to run a downsample import from the command line (this document also describes how to import via a script or a Deephaven UI Import Job). This example assumes a typical Deephaven installation and that the target table already exists. Note that this process imports from an existing historical partition.

sudo -u dbmerge iris_exec downsample_import -- \
-ns TargetNamespace -tn TargetQuoteTable \
-sn SourceNamespace -st SourceQuoteTable -sp 2018-09-26 \
-tc Timestamp -p 00:00:10 -kc Sym -last Bid,Ask,BidSize,AskSize \
-dp localhost/2018-09-26

This example downsamples the 2018-09-26 partition of the table SourceNamespace.SourceQuoteTable and stores the result in the target table (TargetNamespace.TargetQuoteTable). The Timestamp column is used to downsample the source table every 10 seconds for each key. "Sym" is identified as the key column, and the last values from each time interval from the Bid, Ask, BidSize and AskSize columns are selected.

Import Query

When Import - Downsample is selected, the Persistent Query Configuration Editor window shows the following options:

• To proceed with creating a query to import downsampled data, you will need to select a DB Server and enter the desired value for Memory (Heap) Usage (GB).

• Options available in the Show Advanced Options section of the panel are typically not used when importing or merging data. To learn more about this section, please refer to the Persistent Query Configuration Viewer/Editor.

• The Access Control tab presents a panel with the same options as all other configuration types, and gives the query owner the ability to authorize Admin and Viewer Groups for this query. For more information, please refer to Access Control.

• Clicking the Scheduling tab presents a panel with the same scheduling options as all other configuration types. For more information, please refer to Scheduling.

• Clicking the DownsampleImport Settings tab presents a panel with relevant options:

  

Downsample Import Settings

• Target Namespace: This is the namespace into which you want to import the file.
• Target Table: This is the table into which you want to import the data. The schema of this table must be consistent with the output of the downsampling process (as specified by the other options on this panel).
• Output Mode: This determines what happens if data is found in the fully-specified partition for the data. The fully-specified partition includes both the internal partition (unique for the import job) and the column partition (usually the date).
  • Safe - if existing data is found in the fully-specified partition, the import job will fail.
  • Append - if existing data is found in the fully-specified partition, data will be appended to it.
  • Replace - if existing data is found in the fully-specified partition, it will be replaced. This does not replace all data for a column partition value, just the data in the fully-specified partition.
  • Strict Import - has no meaning (at present) for this type of import.
• Downsample Parameters:
  • By Strategy - The Deephaven "byStrategy" to use when performing the downsample aggregate operations.
  • AJ Strategy - The Deephaven join strategy to use when joining source data to time bins (applicable only when setAllBins(true)).
  • Natural Join Strategy - The Deephaven join strategy to use when filtering time bins.
  • Number Threads - the number of threads to use when downsampling. For large downsampling jobs where "generate all time bins" is true, this can improve performance by parallelizing the downsample time-join on a per-key basis.
  • Partition Formula: Specifies which source column partition to import. This style of import supports only single-partition imports. A typical value for this might be currentDateNy().
  • Source Namespace: Namespace of the table that will be downsampled.
  • Source Table: Name of the table to downsample. This table must contain the timestamp and key columns specified in this panel. For performance reasons, it is recommended that large source tables be grouped by the key column(s).
  • Timestamp Column: Name of the column in the source table to use for generating time bins. Must be of type DBDateTime.
  • Period: Interval to use for generating time bins in HH:MM:SS format. Examples: 00:05:00 for 5 minute bins, 00:01:00 for 1 minute bins.
  • Calendar: The name of a calendar to use for filtering time stamps.
  • Time Bin Mode: How to assign timestamps in the source data to time bins. Given the time of 01:32:34 and a period of 5 minutes, UPPER would assign this time to the 01:35:00 bin, while LOWER would assign to the 01:30:00 bin. UPPER is a more typical choice.
  • Generate All Time Bins: Whether or not to generate time bins for the entire time range spanned by the underlying data, even if no data falls into the bin. Setting this to true requires one or more joins and hence will cause the downsampling process to be significantly slower.
  • Key Columns: Optional columns to use for grouping the source data. The downsampling will produce a row for each (time bin, key) combination. For financial series, the instrument identifier is a typical choice for this.
  • Aggregate Columns: Comma separated list of aggregate/data columns names desired in the downsampler output. These may match columns in the source table or rename then using the OutputColumn=OriginalColumn format. Typically the latter would be used for additional aggregates of source columns (for example Volume=Size used in combination with a sum aggregate on trade data).
  • Aggregates: Comma separated list of aggregates to use for each column specified in the Aggregate Columns field. There number of aggregates must exactly match the number of aggregate columns. Legal values for aggregates are: Last, First, Sum, Min, Max, Avg, Std, Var, and Array (Count is not available).
  • Maintain State Columns: Comma separated list of columns for which to maintain state from one time bin to the next if no data when generating all time bins. Typically would be used for Last aggregate columns where the value is "good until changed" (such as Bid and Ask in quote data).

Importing Using Builder

Downsample imports may be performed from inside a Deephaven Groovy or Python script. This permits more elaborate logic with respect to existing data. These scripts may be executed as a persistent query or from the command line using the iris_exec run_local_script tool. All imports should be performed as the dbmerge user (or from a persistent query, running on a merge server).

The DownsampleImport class provides a static builder method, which produced an object used to set parameters for the import. The builder returns a DownsampleImport object from the build() method. Imports are executed via the run() method and if successful, return the number of rows imported. All other parameters and options for the import are configured via the setter methods described below. The pattern when executing a Downsample Import follows:

nRows = DownsampleImport.builder(<db>, <namespace>, <table>, <timestamp column>, <period>, <key column>...)
  .set<option>(<option value>)
  ...
  .build()
  .run()

Required Builder Parameters

Import Options

Import from Command Line

Downsample imports from the command line work in a very similar way to the scripted imports. The primary difference is that since the source table is specified by name (instead of a Table object), you must also supply a source partition (i.e., Date) from which to import.

The syntax for running downsample imports from the command line is described below. You may also issue the import command without arguments to see them described in the console.

Command Reference

iris_exec downsample_import <launch args> -- <downsample import args>

Downsample Import Arguments