Schema Inference

Deephaven provides schema generation tools to make schema creation easier. This guide shows you how to generate schemas from various data sources. Schemas can be inferred from:

• CSV, XML, and JSON files
• JDBC data sources
• Avro schema and Protobuf descriptors

CSV

CSV refers to a variety of text file data formats. Although "comma-separated" is in the name, the delimiter could, in fact, be some other character (such as a tab or semicolon). Other details of formatting are similarly flexible. There is no single official standard for CSV formatting, so Deephaven provides flexibility when defining how to interpret and transform text data from CSV files into structured storage in Deephaven tables.

Because CSV is a text file format that does not provide metadata, the schema generation tool must analyze the data it finds in a sample data file. Column names are typically derived from column headers, but there are options for CSV files with and without headers. The schema generation process can be simplified and accelerated by using a sample of data, as long as there are enough rows to clearly demonstrate the formats and ranges of data values that will be included in normal imports.

For non-String columns, the schema generator will scan all the values in the supplied CSV file to ensure the inferred data type remains valid for all values in the column. As an example, the first value for a column might be 4. This would cause the schema generator to tentatively categorize this column as an integer type (short or long, depending on settings). If a 5.5 were encountered in a subsequent row, the generator would "promote" the column to a floating point datatype. If another value in the column included non-numeric characters, such as XYZ, the generator would then mark the column as a String column. Once a column has been designated as a String column, no other checking is done, as Strings are always valid to accept any data from a CSV.

If a column is empty for all rows, it is marked as a String, and a warning message is written to the log. In this case, the administrator should examine and validate the generated schema before adding it to Deephaven.

Date/time values are also a special case, where the schema generator will attempt to match the format of the date/time string to a known format for which it can provide a conversion formula. If a date/time column has multiple formats, it will be marked as a String column, since no single formula could be found that matched all the column's data.

This example creates CSVExampleNamespace.CSVExampleTableName.schema in the /tmp directory:

iris_exec csv_schema_creator -- --namespace CSVExampleNamespace --tableName CSVExampleTableName --sourceFile /data/sample.csv --schemaPath /tmp

Command Reference

iris_exec csv_schema_creator <launch args> -- <schema creator args>

The following arguments are available when running the CSV schema creator:

JDBC

By default, the JDBC schema generator requests the data types of the result set columns from the data source. These data types are mapped to Deephaven data types to provide the schema details.

For cases where the data source cannot provide column data type information, or where the source database is not ideally configured to store the data (e.g., numbers or date/times in String fields), the JDBC schema generator provides options to scan data as is done in the CSV schema generator, or to use a combination of data scanning and metadata.

This example creates ExNamespace.Trade.schema in the /tmp directory:

iris_exec jdbc_schema_creator -- \
--namespace ExNamespace \
--tableName Trade \
---connectionUrl "jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>" \
--jdbcDriver "com.microsoft.sqlserver.jdbc.SQLServerDriver" \
--jdbcQuery "SELECT TOP 1000 * FROM trade" \
--schemaPath /tmp

Note that we limit the size of the result to avoid pulling the entire table into memory.

Command Reference

iris_exec jdbc_schema_creator <launch args> -- <schema creator args>

The following arguments are available when running the JDBC schema creator:

JSON

JSON is a hierarchical format representing data objects and arrays. In order to convert this format to a Deephaven table, JSON data is recursively converted into a stream of lines equivalent to a CSV file (with one "line" per top-level JSON object). The type inference then operates in the same was as it does on CSV data.

The input file should contain plain JSON objects, not comma-separated or an array. For example, the following is a legal file with three objects, which will result in three rows:

{a:1} {a:2} {a:3}

You may have multiple JSON objects on a single line, and/or the JSON objects may span multiple lines.

Column names for a table inferred from JSON are derived from the field names contained in the JSON objects. Since there is no "header" in JSON, all or a large portion of the data is explored to enumerate all columns (since any one JSON object may be missing a given field). JSON array elements are converted into column names by combining the field name with a numeric suffix reflecting the position in the array.

Nested objects are recursively expanded, with the column name for each field derived from the full "path" to that field. The path separator can be specified with the --fieldPathSeparator argument. Array elements are numbered with a zero-based index.

For example, the following maps to a schema with the columns "a", "b_c", "b_d", "e0", "e1", "e2" and a row with values 1, 2, 3, 4, 5, 6:

{ "a":1, "b":{ "c":2, "d":3 }, "e":[4,5,6] }

This example creates JSONExampleNamespace.JSONExampleTableName.schema in the /tmp directory:

iris_exec json_schema_creator -- --namespace JSONExampleNamespace --tableName JSONExampleTableName --sourceFile /data/sample.json --schemaPath /tmp

Command Reference

iris_exec json_schema_creator <launch args> -- <schema creator args>

The JSON schema creator takes the following arguments:

XML

XML by itself does not provide or guarantee a particular layout to the data. Simpler data sets that use XML typically have one element per record, are not hierarchical, and use either attributes or element values to store record values. More complex XML feeds may include metadata, common data updates, and hierarchically related records. Deephaven provides "generic" XML data import capabilities that can accommodate most of the simpler forms. The more complex XML data feeds, including those that include data that must be imported to multiple tables in a single operation, require custom importers that are specific to the data format encapsulated within the XML (e.g., FIXML).

Although XML documents can include metadata, there is no standard as to how such column data types and other such information should be formatted. Therefore, for generic XML imports, Deephaven treats XML files similarly to how it handles CSV files. To find column data types, the schema generation tool analyzes all data that it finds in a sample data file. In addition, if named (rather than positional) values are used, the schema generator will also scan all importable elements to find column names that are needed for the table.

This example creates XMLExampleNamespace.XMLExampleTableName.schema in the /tmp directory:

iris_exec xml_schema_creator -- --namespace XMLExampleNamespace --tableName XMLExampleTableName --sourceFile /data/sample.xml --elementType Record --useAttributeValues --schemaPath /tmp

Command Reference

iris_exec xml_schema_creator <launch args> -- <schema creator args>

The following arguments are available when running the XML schema creator:

Kafka

Deephaven can generate schemas from Avro schema and Protobuf descriptors. For examples and an explanation of how the schema inference works, see Discovering a Deephaven Schema from an Avro Schema and Discovering a Deephaven Schema from a Protocol Buffer Descriptor.

Related Documentation:

• Avro schema and Protobuf descriptors
• Schemas
• dhconfig schemas