Database delete

Delete is permanent

Deleting data from a database is permanent and cannot be undone. It is recommended that a backup of the database is taken before performing a delete.

Beta Feature

Deleting data from a database is currently in beta. Delete currently only supports deleting partitioned data from a single HDB tier and reference data.

Performing a delete

A database delete can be performed either REST directly on the Storage Manager.

Hostname

In the example below, $SM is the hostname and port of the Storage Manager process on which to perform the delete operation.

curl -X POST "$SM/delete" \
    -H 'Content-Type: application/json' \
    -d "$(jq -n \
        '{
            session     : "",
            table       : "",
            startTS     : "",
            endTS       : "",
            filter      : []
        }' | jq -cr .)"

Parameters:

name	required	type	description
session	no	string	An optional delete session name which can be used to undo the delete. If a session name is not provided, one will be generated and returned. This field is currently unused but will be supported in a future release.
table	yes	string	The name of the table to perform this delete operation on.
startTS	no	timestamp	The start boundary for performing this delete operation. See endTS.
endTS	no	timestamp	The end boundary for performing this delete operation. See below for how to specify a timestamp. Since we do not support deleting from the object store tier and additional HDB tiers, the time window must restrict the delete to the dates in the first HDB tier. A time window that overlaps with dates in an additional HDB tier or object store will be rejected with an error message.
filter	no	list	A list of triples that can be used for selecting specific data to be deleted. If a filter is provided, any data that matches the filter will be deleted. If no filter is provided, all data between the start and end timestamps will be deleted. See filtering below for more details.

Specifying timestamps

For getting the start and end times using bash, you may use date -u to print formatted dates:

# Get data within the hour
startTS=$(date -u '+%Y.%m.%dD%H:00:00')
endTS=$(date -u '+%Y.%m.%dD%H:%M%:%S')

Times may be extended to contain a variable number of trailing digits to specify milliseconds and nanosecond precision.

Filtering

The filter parameter is used for applying custom filtering to the query. It's specified via a list of triples where each triple has the form (operator; column name; values).

[["<=", "valFloat", 100], ["within", "qual", [0,2]]]

The filters are applied in the order they are defined when selecting from the table.

Supported filter functions

function	parameters	example
in	Filters data that is not in the list of possible alternatives.	["in", "sym", ["AAPL", "MSFT"]] keeps symbols that are either AAPL or MSFT.
within	Keeps numeric data that is within the bounds of a range using inclusive limits.	["within", "price", [100, 200]] keeps prices that are greater than or equal to 100 but less than or equal to 200.
<	Keeps numeric data that is less than a threshold.	["<", "price", 100] keeps data that is less than 100.
>	Keeps numeric data that is greater than a threshold.	[">", "price", 100] keeps data that is greater than 100.
<=	Keeps numeric data that is less than or equal to a threshold.	["<=", "price", 100] keeps data that is less than or equal to 100.
>=	Keeps numeric data that is greater than or equal to a threshold.	[">=", "price", 100] keeps data that is greater than or equal to 100.
=	Keeps numeric data that equals another value.	["=", "sym", "AAPL"] keeps only AAPL data.
<>	Keeps data that is not equal to a value.	["<>", "sym", "AAPL"] keeps all data that is not AAPL.
like	Filters string data that matches a simple expression.	["like", "sym", "A*"] matches any symbols that start with an A.

Filter has restricted order

A filter tuples must be in the form of function, column, value and only static values are supported. For example, ["<", "price", 100] would work, but [">", 100, "price"] is not supported.

Nested filters are not currently supported.