Importing XML Files

Deephaven provides tools for inferring a table schema from sample data and importing XML files. Because XML can represent nested/hierarchical data in many different ways, mapping to Deephaven tables is more complex that for a simple format like CSV. The XML importer described here can handle a few common variations - extraction of value from either attributes or element text, and different levels of nesting, but some XML formats may require a custom importer.

Quickstart

The following is an example of how to generate a schema from an XML data file, deploy it, and import a file from the command line. These commands assume a typical Deephaven installation and a sample file located at /data/sample.xml.

<?xml version="1.0" encoding="UTF-8"?>
<Response>
  <Record Name="PI" Value="3.14159"/>
  <Record Name="e" Value="2.71828"/>
  <Record Name="phi" Value="1.61803"/>
</Response>

Generate a schema from the sample XML data file above

iris_exec xml_schema_creator -- -ns XMLExampleNamespace -tn XMLExampleTableName -sf /data/sample.xml -xt Record -av

The schema file will be generated in the "XMLExampleNamespace" directory.

ls -l XMLExampleNamespace/*.schema

Deploy the schema

sudo -u irisadmin /usr/illumon/latest/bin/dhconfig schema import --file <fully qualified schema file name>

See Deploying schemas to Deephaven for further details.

Import a single data file into the specified Intraday partition

sudo -u irisadmin /usr/illumon/latest/bin/dhconfig schema import --file <fully qualified schema file name>

This example will generate a table schema from sample.xml with the namespace XMLExampleNamespace and table name XMLExampleTableName, with a Name column of type String and a Value column of type double. The default behavior creates a directory for the namespace in the current directory and places the output schema file inside this directory. Once deployed, other XML files matching the structure of sample can be imported.

Schema Inference

XML schema inference generates a table schema from a sample XML file. Table columns and types are inferred from the data in a number of different ways, depending on the -xi, -xd, -xt, -ev, -av, and -pv options. Since XML files don't provide metadata in a consistent way, this inference process uses the same algorithm as the CSV schema inference in order to determine column types. This is necessarily imperfect, and generated schemas should always be inspected before deployment.

Command Reference

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

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

Import Query

A persistent query of type "Import - XML" is used to import data from an XML file into an intraday partition. The XML file must be available on the server on which the query will run; it is not for loading a file on the user's system into Deephaven.

Deephaven's support for XML files allows loading files into a single table. See the Tables & Schema section for more details about the layout considerations of XML files when importing into Deephaven.

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

• To proceed with creating a query to import an XML file, 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 XmlImport Settings tab presents a panel with the options pertaining to importing an XML file:

  

XmlImport Settings

• Namespace: This is the namespace into which you want to import the file.
• Table: This is the table into which you want to import the data.
• 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. Import Source: This is 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.
• XML File Reading Settings:
  • Use Elements - Take import values from the contents of XML elements. At least one of Use Elements or Use Attributes must be selected.
  • Use Attributes - Take import values from the values of XML attributes. At least one of Use Elements or Use Attributes must be selected.
  • Named Values - When checked, element or attribute names are used for column names; when cleared, values will be assigned to columns positionally in the order they are found. Note that positional values will be parsed from the XML in the order of the columns in the table. As such, the table schema for documents that do not used Named Values, must closely match the layout of columns in the XML document.
• Data:
  • Start Index - The number of the element, starting with the root itself (0), from the root of the XML document where the importer will expect to find data.
  • Start Depth - How many level down, under the Start Index element, the importer should traverse to find data elements.
  • Max. Depth - When data elements may contain data in child elements, this determines how many levels further down the importer should traverse while looking for import values.
  • Element Type - The string name of the data element type. In some cases, this may be a / delimited path to the types of elements that should be imported.
• Single/Multi Partition: This 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: This is the formula needed to partition the XML being imported. If a specific partition value is used it will need to be surrounded by quotes. For example: currentDateNy() "2017-01-01"
    • Partition Substitution: This is 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: This is 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: This is the name of the database column used to choose the target partition for each row (typically "Date"). There must be an corresponding Import Column present in the schema, which will indicate how to get this value from the source data.
• Source Directory: This is the path to where the XML file is stored on the server on which the query will run.
• Source File: This the name of the XML file to import.
• Source Glob: This is an expression used to match multiple XML file names.
• Constant Value: A String of data to make available as a pseudo-column to fields using the CONSTANT sourceType.

In most cases, it will be easier to test XML parsing settings using the Discover Schema from XML option of the Schema Editor utility. This allows a quick preview of the results of different settings, so the correct index, element type, etc, can be easily determined.

Importing Using Builder

Example

The following script imports a single XML file to a specified partition. This import uses options consistent with the XML Quickstart example.

import com.illumon.iris.importers.util.XmlImport
import com.illumon.iris.importers.ImportOutputMode

rows = new XmlImport.Builder("Test","Sample")
    .setSourceFile("/db/TempFiles/dbquery/staging/data1.xml")
    .setDestinationPartitions("localhost/2018-04-01")
    .setElementType("Record")
    .setUseAttributeValues(true)
    .setOutputMode(ImportOutputMode.REPLACE)
    .build()
    .run()

println "Imported " + rows + " rows."

from deephaven import *

rows = XmlImport.Builder("Test", "Sample")\
    .setSourceFile("/db/TempFiles/dbquery/staging/data1.xml")\
    .setDestinationPartitions("localhost/2018-04-01")\
    .setElementType("Record")\
    .setUseAttributeValues(True)\
    .setOutputMode("REPLACE")\
    .build()\
    .run()

print("Imported {} rows.".format(rows))

Import API Reference

The XML import 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:

nRows = XmlImport.builder(<namespace>,<table>)
    .set<option>(<option value>)
    …
    .build()
    .run()

XML Import Options

* The sourceDirectory parameter will be used in conjunction with sourceFile or sourceGlob. If sourceDirectory is not provided, but sourceFile is, then sourceFile will be used as a fully qualified file name. If sourceDirectory is not provided, but sourceGlob is, then sourceDirectory will default to the configured log file directory from the prop file being used.

Import from Command Line

XML imports can be performed directly from the command line, using the iris_exec tool.

Command Reference

iris_exec xml_import <launch args> -- <xml import args>

XML Import Arguments

The XML importer takes the following arguments:

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

• By default, the XML importer will attempt to interpret string data from the source table 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 would result in a conversion failure that may be continuable, if, for instance, a default is set for the column.
• To convert from other representations of true/false, for example 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 XML value is handled correctly.