User Tables

User tables allow you to store and retrieve data without having to create and deploy schemas manually. Their schemas are based on tables created from query code. They cannot be stored in System namespaces, i.e., namespaces that already contain non-User tables.

User tables can be accessed using the same interface as System tables.

User Table Terminology

It is easier to understand the user table API after understanding the different types of user tables and data, which are associated with their own sets of methods. This section explains the types, and the subsequent section demonstrates API usage.

Partitioned vs Unpartitioned Tables

Partitioned user tables are split into partitions by a partitioning column such as "Date", for which each value (e.g., "2023-01-01", "2023-01-02", "2023-01-03") has its own dataset. Partitioned user tables can have both direct and live partitions. Partitioned user tables must have their schemas added before adding data.

Unpartitioned user tables, however, are not split into partitions. Instead, their data is written and retrieved simply by namespace and table name. Unpartitioned user tables are all direct.

Direct vs Live Data

Direct user data are managed directly on a Unix filesystem (typically NFS so they may be shared across the cluster) in Parquet format. These tables cannot add, remove, or modify rows to preexisting partitions and do not update after retrieval, but are simpler to manage. Direct user data can be partitioned or unpartitioned.

Live user data is managed centrally by the Deephaven system in Deephaven format. Live user tables are ingested and served similarly to System intraday data. These tables tick after retrieval as rows are appended. All live user data is partitioned.

API Examples

Unpartitioned User Tables

The following example demonstrates how to add an unpartitioned user table:

db.addUnpartitionedTable("UserNamespace", "TableName", tableToWrite)

db.add_unpartitioned_table("UserNamespace", "TableName", table_to_write)

The following example demonstrates how to delete an unpartitioned user table:

db.deleteUnpartitionedTable("UserNamespace", "TableName")

db.delete_unpartitioned_table("UserNamespace", "TableName")

This method call returns a boolean that represents whether data was deleted or not.

Managing Partitioned User Tables

The following example demonstrates how to add a partitioned user table schema based on another table's definition:

db.addPartitionedTableSchema("UserNamespace", "TableName", "PartitionColumnName", definitionTable.getDefinition())

db.add_partitioned_table_schema("UserNamespace", "TableName", "PartitionColumnName", definition_table)

Table definitions specify details such as column names and types. You can use any table, empty or not, to base your table definition on. Note that "PartitionColumnName" must not exist in the definition table.

Additionally, you can modify the schema of an existing partitioned user table, as long as you do not try to change the type of a preexisting column:

db.updatePartitionedTableSchema("UserNamespace", "TableName", definitionTable.getDefinition())

db.update_partitioned_table_schema("UserNamespace", "TableName", definition_table)

The following example demonstrates how to delete a partitioned user table, including its persisted schema as well as any existing data partitions, whether direct or live:

db.deletePartitionedTable("UserNamespace", "TableName")

db.delete_partitioned_table("UserNamespace", "TableName")

Note that these methods return a boolean indicating whether the operation succeeded.

Direct Partitioned User Tables

The following example demonstrates how to write a direct table partition under "2023-01-01" to a defined partitioned user table:

db.addTablePartition("UserNamespace", "TableName", "2023-01-01", tableToWrite)

db.add_table_partition("UserNamespace", "TableName", "2023-01-01", table_to_write)

The following example demonstrates how to delete a direct table partition under "2023-01-01" from a defined partitioned user table:

db.deleteTablePartition("UserNamespace", "TableName", "2023-01-01")

db.delete_table_partition("UserNamespace", "TableName", "2023-01-01")

This method call returns a boolean that represents whether data was deleted or not.

Live Partitioned User Tables

The following example demonstrates how to append rows to a live table partition under "2023-01-01" to a defined partitioned user table:

db.appendLiveTable("UserNamespace", "TableName", "2023-01-01", tableToAppend)

db.append_live_table("UserNamespace", "TableName", "2023-01-01", table_to_append)

The following example demonstrates how to append rows to a live table partition under "2023-01-01" to a defined partitioned user table every time rows are added to the table argument. To ensure expected functionality, retain the returned reference. Call close on the reference if you want to stop appending and clean up related resources:

ref = db.appendLiveTableIncremental("UserNamespace", "TableName", "2023-01-01", tableToAppend)
// ...
ref.close()

ref = db.append_live_table_incremental("UserNamespace", "TableName", "2023-01-01", table_to_append)
# ...
ref.close()

The following example demonstrates how to delete a live table partition under "2023-01-01" from a partitioned user table:

db.deleteLiveTablePartition("UserNamespace", "TableName", "2023-01-01")

db.delete_live_table_partition("UserNamespace", "TableName", "2023-01-01")

This method call returns a boolean that indicates whether data was deleted or not.

Non-API Cleanup

In certain cases, it may be necessary to remove user tables using lower-level tools. The dhconfig tool can be used to manipulate schema without the same checks as from a worker's script. The raw data is stored as files on disk, which can be removed without the need to operate through a worker.

To delete a user table schema, you can run the following on the CLI:

sudo -u irisadmin /usr/illumon/latest/bin/dhconfig schemas delete UserNamespace.TableName --force

To delete a user table namespace, you can run the following on the CLI:

sudo -u irisadmin /usr/illumon/latest/bin/dhconfig schemas delete --operate-on-namespace UserNamespace --force

To delete unpartitioned user table data, you can remove the relevant local files following a directory pattern like /db/Users/<UserNamespace>/Tables/<TableName>/.

To delete partitioned direct user table data, you can remove the relevant local files following a directory pattern like /db/Users/<UserNamespace>/Partitions/<InternalPartition>/<ColumnPartition>/<TableName>/.

To delete partitioned live user table data, you can remove the relevant centrally managed files following a directory pattern like /db/IntradayUser/<UserNamespace>/Partitions/<InternalPartition>/<ColumnPartition>/<TableName>/. These files can be found on the same host as the DIS that ingests live user data, which by default is the DIS provided upon installation.