Ingest CSV data into system tables

This guide demonstrates how to ingest CSV data files into Deephaven system tables, covering both command-line and UI-driven methods. For command-line operations, these instructions assume a typical Deephaven installation.

Schema preparation

Before ingesting any CSV data, whether via the command line or the UI, a schema defining the data's structure must be generated and deployed. This schema is crucial for Deephaven to correctly interpret and store your data.

Generate a schema

The first step is to generate a schema file from a sample of your CSV data. This schema will define column names, data types, and other ingestion parameters.

See the Schema inference page for a detailed guide on generating a schema from a sample CSV file.

Deploy the schema

Once you have a schema file (e.g., /tmp/MyNamespace.MyTable.schema), you need to deploy it. The dhconfig schema import command makes this schema definition known to your Deephaven instance.

Replace /tmp/MyNamespace.MyTable.schema with the actual path to your generated schema file.

CSV import Persistent Query

Once you have deployed a schema, you can import CSV data into Deephaven using a Persistent Query (PQ) configured for CSV import. This method allows you to schedule and manage imports directly from the Deephaven UI.

To create a CSV Import Persistent Query (PQ), click the +New button above the Query List in the Query Monitor and fill in the options.

• Name your query appropriately, as it will appear in the Query List.
• Select the type Import - CSV.
• Select an appropriate DB Server (import PQs must use a merge server).
• Choose the Engine (Core+ or Legacy).
• Select an appropriate value for Heap Size.
• Options available in the Show Advanced Options section of the panel are typically not used when importing or merging data.

This screenshot shows a CSV import PQ called CSV Import PQ using the Core+ engine.

• The Permissions tab gives the query owner the ability to authorize Admin and Viewer Groups for this query.
• The Scheduling tab provides options on when the PQ should run.

Click the CSV Import Settings tab to choose the options pertaining to importing a CSV file.

These screenshots show the top and bottom of the panel for a table called ColCsvParsing in the namespace Test, using an import source called SeeEssVee.

CSV import settings

• Namespace: The namespace into which you want to import the file.
• Table: The table into which you want to import the data.
• Import Source: The import source section of the associated schema file that specifies how source data columns will be set as Deephaven columns.
  • Strict Import will fail if a file being imported has missing column values (nulls), unless those columns allow or replace the null values using a default attribute in the ImportColumn definition.
• Output Mode: 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 - The import job will fail if existing data is found in the fully-specified partition.
  • Append - Data will be appended to it if existing data is found in the fully-specified partition.
  • Replace - Data will be replaced if existing data is found in the fully-specified partition. This does not replace all data for a column partition value, just the data in the fully-specified partition(s).
• Single/Multi Partition: Controls the import mode. In single-partition, all of the data is imported into a single Intraday partition. In multi-partition mode, you must specify a column in the source data that will control to which partition each row is imported.
• Single-partition configuration:
  • Partition Formula: A formula that specifies the partition into which the data is imported. If a specific partition value is used, it must be surrounded by quotes. For example: today() "2017-01-01"
  • Partition Substitution: A token used to substitute the determined column partition value in the source directory, source file, or source glob, to allow the dynamic determination of these fields. For example, if the partition substitution is "PARTITION_SUB", and the source directory includes "PARTITION_SUB" in its value, that PARTITION_SUB will be replaced with the partition value determined from the partition formula.
  • Substitution Date Format: The date format that will be used when a Partition Substitution is used. The standard Deephaven date partition format is yyyy-MM-dd (e.g., 2018-05-30), but this allows substitution in another format. For example, if the filename includes the date in yyyyddMM format instead (e.g., 20183005), that could be used in the Date Substitution Format field. All the patterns from the Java DateTimeFormatter class are allowed.
• Multi-partition configuration:
  • Import Partition Column: The name of the database column used to choose the target partition for each row (typically "Date"). There must be a corresponding Import Column present in the schema, which will indicate how to get this value from the source data.
• File Format: The format of the data in the CSV file being imported. Options include DEFAULT, TRIM, EXCEL, TDF, MYSQL, RFC4180, and BPIPE*.
• Delimiter: This can be used to specify a custom delimiter character if something other than a comma is used in the file.
• Skip Lines: The number of lines to skip before beginning parse (before header line, if any).
• Skip Footer Lines: The number of footer lines to skip at the end of the file.
• Source Directory: The path to where the CSV file is stored on the server on which the query will run.
• Source File: The name of the CSV file to import.
• Source Glob: An expression used to match multiple CSV file names.
• Constant Value: A String of data to make available as a pseudo-column to fields using the CONSTANT sourceType.

• Note: BPIPE is the format used for Bloomberg's Data License product.

Worker-scripted CSV imports

Deephaven provides tools for importing CSV files from a Deephaven Groovy script running in a Core+ worker. Compared to other methods, this permits more elaborate logic with respect to existing data. These scripts may be executed as a PQ or code studio. All imports should be performed as the dbmerge user or from a persistent query running on a merge server.

Example

The following Core+ script imports a single CSV file to a specified partition:

Instead of supplying a file location, you can also directly use an input stream created to read your file.

You can also use the fromStream method to supply the input stream.

Import API reference

The CsvImportOptions class provides a static builder method, which produces an object used to set parameters for the import. The builder returns an import 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 general pattern when scripting an import is:

For further information on the Builder, see the API docs for the io.deephaven.importers.csv.CsvImportOptions.Builder class.

CSV import options

See the import settings for the further information on these fields.

Notes

1. intradayPartitionColumn, destinationDirectory, and destinationPartitions specify where to write the data.
   • Either destinationDirectory or destinationPartitions can be specified but not both.
   • If destinationDirectory is set, or destinationPartitions specifies both the internal and column partitions, then intradayPartitionColumn cannot be set.
   • If destinationPartitions is set and specifies only the internal partition, then intradayPartitionColumn must specify the partitioning column.
2. inputStream, sourceDirectory, sourceFile and sourceGlob determine the sources used to perform the import.
   • If inputStream is provided, this stream is used to read the CSV data. In this case sourceDiretory, sourceFile, and sourceGlob cannot be specified.
   • If inputStream is not specified, then either sourceFile or sourceGlob must be specified, but not both.
   • If sourceDirectory is not provided but sourceFile is, then sourceFile is used as a fully qualified file name.
   • If sourceDirectory and sourceFile are both provided, then sourceFile is relative to sourceDirectory and can include subdirectories.
   • If sourceGlob is provided, then sourceDirectory must provide the directory in which to look for files.

File formats

Five common formats are supported based on the original Apache CSV parser.

• DEFAULT – default format if none is specified:
  • Comma-separated field values.
  • Newline row terminators.
  • Double-quotes around field values that contain embedded commas, newline characters, or double-quotes.
  • Does not trim spaces around quoted or non-quoted values.
  • Ignore empty lines.
• EXCEL – The Microsoft Excel CSV format. This is similar to DEFAULT but doesn't ignore empty lines.
• MYSQL – The MySQL CSV format.
  • Tab characters as delimiters.
  • null as the quote character.
  • "\\N" as the null value.
  • Does not ignore empty lines.
• RFC4180 – IETF RFC 4180 MIME text/csv format. This is similar to DEFAULT but doesn't ignore empty lines.
• TDF – Tab-delimited format. This is similar to DEFAULT but uses tab characters as delimiters and trims spaces around non-quoted values.
• TRIM - Similar to DEFAULT, but will trim all white space around values, both within and outside quoted values.

Command-line import

With a schema prepared and deployed, you can ingest CSV data using the iris_exec csv_import command. This tool is part of the Deephaven installation and is typically found in /usr/illumon/latest/bin/.

CSV ingestion jobs can be performed directly from the command line, using the iris_exec tool.

General syntax

The iris_exec csv_import command follows this general structure. Arguments before the -- (double dash) are launch arguments for the iris_exec utility itself, while arguments after the -- are specific to the csv_import operation.

It's common to run this command as the dbmerge user, especially when writing to Deephaven's data directories.

Example command

This example demonstrates ingesting a CSV file named trades.csv located in /data/incoming/ into the TradeData table within the MarketOps namespace. The data is targeted for the 2024-07-15 partition, managed under the prod_importer internal partition name.

CSV importer arguments

The CSV importer takes the following arguments:

Handling Boolean Values

One special case when ingesting CSV data is columns in the target table with a type of Boolean.

• By default, the CSV importer will attempt to interpret string data from the source CSV with 0, F, f, or any case of false, being written as a Boolean false.
• Similarly, 1, T, t, or any case of true would be a Boolean true.
• Any other value results in a conversion failure. This failure may be continuable if a default value is set for the column or if the relaxedChecking argument is TRUE. (If relaxedChecking is TRUE and the conversion is for a Boolean column, unrecognized values are treated as FALSE, as noted in the argument's description.)
• To convert from other representations of true/false (e.g., from foreign language strings), a formula or transform would be needed, along with a sourceType="String" to ensure the reading of the data from the CSV field is handled correctly.

Related documentation

• Binary logs
• JSON
• JDBC
• Parquet
• Quandl
• XML
• Custom Importer
• Deephaven Schemas