IDSETQ data type
IDSETQ is a FeatureBase datatype used to associate multiple TIMESTAMP values with integer values within a single row.
Syntax
IDSETQ [TIMEQUANTUM {value} [TTL '{value}']]
Arguments
Argument | Description |
---|---|
IDSETQ | Data type used where there is a need to set multiple ID values for a single column |
TIMEQUANTUM | Create a view on IDSET and STRINGSET columns that allow range queries down to the specified time, measured as a sequential combination of Y ,M ,D ,H . e.g. YMD . TIMEQUANTUM associates a time with each value in the column. |
TTL | Used to reduce the growth of a data footprint by deleting older views after the stated amount<int> and unit h (hour), m (minute) or s (second) of time. Defaults to 0s |
Additional information
The IDSETQ data type:
- has a
set
internal data type - one standard view by default unless a timeQuantum is set.
- is used for:
- grouping by
- searching for discrete values
Use the INT data type to perform range queries using <
or >
TIMEQUANTUM
TIMEQUANTUM creates a view of your data based on the specified time. This allows for lower latency queries at the cost of increased storage. For example, set TIMEQUANTUM to:
MD
for queries that include a range of monthsD
for queries that include a small number of days
NOTE: Queries run on mismatched time granularities are slower but will function correctly. For example: Querying days on a IDSET or STRINGSET column with TIMEQUANTUM set to YM
.
You can omit but not skip time granularity.
- YM is valid
- YH is invalid
TIMEQUANTUM is used when:
- times need to be associated with column data for query purposes
- database space is not at a premium
Valid Timequantum constraint values
IDSETQ()
and STRINGSETQ()
Timequantum constraints store integer value UNIX-epoch timestamps which are associated with specific values.
TTL (Time To Live)
NOTE: FeatureBase recommends using a TTL of 1h
or more to improve results.
- TTL enables the deletion of time views where a time range exceeds the stated Time To Live.
- The default TTL of
0s
indicates TIMEQUANTUM views will not be deleted. - TTL runs:
- when FeatureBase starts and every hour to make view deletion consistent
- are not guaranteed to run at a specific time
-
error: unknown unit
is generated if an incorrect value is used (e.g., TTL is set to60second
) - TTL should not be used if you require complete and consistent historical data.
TTL order of events
This example demonstrates the deletion dates of three column views where TTL is set to 30d
View date | ttl date of deletion | Explanation |
---|---|---|
2022 | January 30, 2023 | Date assumed to be end of 2022 |
2022-09 | October 30, 2022 | Date assumed to be end of September |
2022-09-02 | October 2, 2022 | Deletion after 30 days as intended |
TIMEQUANTUM
values and TUPLE()
function
When the TUPLE()
function is used in SELECT
and BULK INSERT
statements, the following values are returned:
Source data type | Returns |
---|---|
IDSETQ | TIMESTAMP and associated integer value |
STRINGSETQ | TIMESTAMP and associated string value |
Examples
CREATE TABLE with SETQ Timequantum
CREATE TABLE timeq (
_id id,
stringsetcolq stringsetq timequantum 'YMD',
idsetcolq idsetq timequantum 'YMD'
);
CREATE TABLE with all data types
create table allcoltypes
(
_id id,
intcol int min 0 max 10000,
boolcol bool,
timestampcol timestamp timeunit 'ms',
decimalcol decimal(2),
stringcol string,
stringsetcol stringset,
stringsetcolq stringsetq timequantum 'YMD' ttl '24h',
idcol id,
idsetcol idset,
idsetcolq idsetq timequantum 'YMD' ttl '24h',
vectorcol vector(768)
)
with comment 'table containing all column types';