Schema ImportSource

The ImportSource component of a schema provides additional metadata to control and modify the import process.

Multiple ImportSource blocks can be added to allow various import instructions for different sources or types of import. The type attribute is required; currently supported values are "CSV", "JDBC", "JSON", and "XML". The name attribute is optional. When the import runs, if no ImportSource name is specified, the importer will use the first ImportSource block that matches its type. If no matching block is found (for type, and, if specified, for name), then the import will attempt to auto-match columns as it would if no ImportSource blocks existed.

To support multi-partition imports, you must have an ImportColumn for the partition column indicating how to partition each row (e.g., <ImportColumn name="Date" sourceName="trade_date"/> where trade_date is the CSV/JDBC column name).

An ImportSource block can contain multiple types of entries, including:

• ImportColumn
• Imports
• maxError
• arrayDelimiter
• ImportTransform

These options are described below.

ImportColumn

Each entry describes how to map values for one column from the source data to the target table's schema.

Imports

The packages to import. A default set of packages is imported to support built-in functions and import tasks, but additional packages can be specified to provide other functions or support custom transforms. Imports can be just the qualified package/class name, or the full import qualified.package.name.class; line.

Beside the internal imports used by the field writers, these imports are automatically added in the field writer context:

• java.util.Properties
• com.illumon.iris.db.tables.utils.DBDateTime
• com.illumon.iris.db.tables.utils.DBTimeUtils
• com.fishlib.util.DateUtil
• java.util.regex.Pattern
• java.time.format.DateTimeFormatter
• java.time.*

maxError

The maximum number of allowed parsing errors before failing the import. The default is 0. Allowed errors that will increment maxError are things like numeric conversions, overflows, and mismatched values (e.g., a Boolean value being set from a String column that doesn't match any known version of True or False.)

arrayDelimiter

A string to be used as a delimiter when parsing array data. The default delimiter is a comma.

Import Transformers

Import transformers define custom-written Java functions to transform input data into the Deephaven format.

Custom transforms

Custom transforms can be added to the ImportSource block as ImportTransform elements. These elements must:

• Have a Body element that contains the text of the transform function. This is the Java code that will be compiled as part of the setter for an ImportColumn that uses the custom transform.
• Have a name attribute that matches the name used in the transform attribute of ImportColumns that use the transform.
• Implement a getFunction function that returns a function matching the specification of the interface.
  • The ImportColumn's sourceType indicates the data type of the lambda parameter, in this case value.
  • The column's dataType indicates the data type which the returned lambda (or function) must return.

At runtime, getFunction is passed to the ImportColumnDefinition of the column that is being set, and a boolean value indicating whether strict (true) or permissive (false) data checking has been selected. The ImportColumnDefinition allows getFunction to access details like the target column name, the target column type, and any ImportColumn details by using getImportColumn(), which provides access to functions such as getAttributeValue().

The following shows an example of a custom transform element that could be specified in the schema (Note: this is similar to the built-in dbDateTimeFromLong transform):

When compiling the transform function, the target, and, if defined, source, data types are used to find a suitable Java functional interface with which to declare the getFunction. In the case of the example above, the target column type is DBDateTime, and the sourceType="long" attribute is added to the ImportColumn. This combination of source and target types is then used to create and execute the resultant function as a LongFunction<DBDateTime>.

The ImportColumn definition in the ImportSource can now refer to the DateTimeFromLong transformer defined above, specifying the sourceName, sourceType, and the required transformPrecision field.

A much simpler example follows:

If this is used for a column whose type is long, this maps to a LongUnaryOperator, which takes a long and returns a Long. If the sourceType is not specified, the importer attempts to use the target column type for matching a function interface.

If the function being returned from getFunction has a declared return type, this return type must exactly match the target column type for which the transform will be used. For example, a transform that will provide values for a Long column and that has a declared return type, must be declared Long, not long. However, the simpler, and recommended, implementation is to include only the transform body in the return from getFunction, so the system can perform implicit type conversion when compiling the transform.

Custom transforms can be added from the Schema Editor File menu using the View/Edit Schema XML option.

Primitive types and boxed types in transforms

Not every combination of primitive types is supported by the Lambda interfaces used to pass transform functions into field writers. The supported combinations for unboxed primitive types are any combination of source and target of int, double, and long.

Therefore, a transform that reads a long value from the source and transforms it to a double value in the target table would use the primitive types of long and double.

However, a transform that reads a byte value and writes a short value would not be able to use primitive types. The importer will map this transform as a Function<Byte, Short>. Note the capitalization. Byte and Short are the boxed (class-based) versions of the primitive types byte and short. This means there is extra overhead in the transform process to use the larger class-based versions of the data types.

Another thing to be aware of is that primitive types that have a "null" default will be set to values from com.illumon.iris.db.tables.utils.QueryConstants when their sources are null. While these may be boxed when they are passed into a transform, they will not be automatically converted to true null values. If special handling is needed for null values in the transform, either check for equality to the QueryConstants values, or use a boxed type for the sourceType property of the column so these values will be preserved as null.

Array types

The importers support long[] and double[] array types. From the source, these are expected to be strings (either a string field from JDBC or a column value in CSV). The string is formatted as a bracketed list of values. Brackets can be square brackets [ ], parentheses ( ), or curly braces { }. The delimiter can be specified with arrayDelimiter, but the default delimiter is a comma. Empty arrays [] are also supported.

An example array of double strings follows:

[1.5, 6.4, 8.0, 2.2322, 88, 16.02]

Transformation examples

These examples walk through data transformation for a JDBC import. They've been tested against Microsoft SQL Server, using a column called date with type date.

It’s important to test transformations to ensure the expected results are achieved before deploying schemas and Persistent Queries to production servers.

Built-in transformations

The simplest way to transform the source's date column is to define a column using a standard supported type. For this example's remote date column, you can create java.time.Instant or java.time.LocalDate columns by defining the Deephaven columns with those standard types in the schema.

Then the ImportSource definitions for those columns specify the JDBC table's column to use.

For the source_date column, each value in the remote server's date column is transformed into an Instant, which is a point-in-time. This transformation uses midnight at the server's configured time zone to construct that point-in-time.

For the date_localdate column the conversion is trivial, because LocalDate contains the same information as the remote server's date column. Each value is simply converted into its equivalent LocalDate.

Complex formula

The Instant values can be converted into a String column. First define a standard String column in the schema.

This shows a working but intentionally inefficient transformation to point out some pitfalls. Define the formula in the ImportSource.

• sourceName specifies the remote database's column name.
• sourceType indicates the type as determined during the transformation from the remote source - we know Instant works because we used it in the built-in example.
• formula uses a standard Java DateTimeFormatter to convert that Instant into a String of the format yyyy-MM-dd.
  • The usual quotes around the formatting string (in this case "yyyy-MM-dd") have been changed to ", because schema files are XML and must conform to XML syntax rules. XML requires " instead of the double-quote character within attributes.
  • The code within the formula is run for every value in the date column. This means that every parsed value constructs a new DateTimeFormatter instance. While this technically works, it is incredibly inefficient and not recommended.

The result of this formula is a string such as 2025-12-31.

ImportTransform

For a complex transformation like the conversion from an Instant above, using an ImportTransform gives more control, allowing better definition of the control using Java code. As before, define a standard String column in the schema.

The schema's ImportSource section will include two pieces that work together to perform the transformation. First, define a function.

• This transformer is named DateFromInstant.
• Body must start with the getFunction method declaration as shown above.
  • It's not specifically defined in the <Body> block, but getFunction returns a Java Function that's compiled into the importer's setter code. In this case, the Function takes an Instant and returns a String. based on the column-type of the source and destination columns.
  • The ImportColumn definition (below) ensures that the Function's lambda operates on the Instant datatype. This is why the return statement uses (Instant value) -> for the lambda.
  • The Column definition (above) specifies that it's returning a String since that's the column's dataType.
• The function is called once for the column and added to the column's setter logic. This means that the DateTimeFormatter is constructed only once (when getFunction is called during the column's definition logic), not for every row in the source table.

Now the ImportColumn definition in the ImportSource can simply refer to the DateFromInstant transformer defined above, specifying the sourceName and sourceDate.

• sourceName specifies the remote server's column name.
• sourceType indicates the original type as determined during the transformation from the remote source - we know Instant works because we used it in the built-in example.

Partitioning from data

String columns can be used to determine the intraday partitions into which data is imported. Since partitioning by date is common, this column definition frequently appears in a schema and applies to our example.

Continuing the ImportTransform example, when defining the import Persistent Query's options, choose "Multi Partition" for the import target, and "date_transform_string" for the import partition column. The Persistent Query will automatically partition the data based on the date values determined for that column.

Custom CSV parsers

The deephaven-csv library allows for the use of custom parser classes when importing a file and the Core+ CSV import supports this.

• Create a class that implements the io.deephaven.importers.csv.parsers.CsvParserCreator interface, which specifies a method called to create a io.deephaven.importers.csv.Parser.
• Specify the parser creation class in an ImportColumn definition with the csvParserCreatorClass attribute.
• This parser-creator's create method is called by the importer to create the custom parser for the column.

Here's an example of an ImportColumn definition that defines a custom CSV parser.

DateTimeFormatterPatternParserCreator

io.deephaven.importers.csv.DateTimeFormatterParser uses standard Java DateTimeFormatter patterns to parse data. It's created by specifying the io.deephaven.importers.csv.DateTimeFormatterPatternParserCreator creation class in the schema's ImportColumn element. Two attributes define its behavior:

• timeFormat provides the java.time.format.DateTimeFormatter pattern used to parse the data. For example, timeFormat="yyyy-MM-dd'T'HH:mm:ss.SSS" parses dates-times such as 2024-04-01T00:05:12.123.
• An optional timeZone defines the time zone to be used for the time conversions. If it's not included, the format pattern must indicate how to derive the time zone. For example, timeZone="America/Denver".

TimeWithPartitionParserCreator

io.deephaven.importers.csv.TimeWithPartitionParserCreator creates an io.deephaven.importers.csv.DateTimeFormatterParser to parse timestamps, creating the resulting values relative to the column partition's date value. This date must be in standard yyyy-MM-dd format, such as 2024-12-20.

• The time's format must be specified with the timeFormat attribute.
• The timezone is specified by the timeZone attribute in the column's import source. If a time zone isn't specified, it uses the system's default time zone.

For instance, if an ImportColumn includes csvParserCreatorClass="io.deephaven.importers.csv.TimeWithPartitionParserCreator" timeZone="America/New_York" timeFormat="HHmmssnnnnnnnnn, if the partition is the date 2026-02-01, then the value 081918938069760 parses to the date-time 2026-02-01 08:19:18.938069760 New York time.

Related documentation

• Tables and schemas
• Schema table storage