Data types and constraints
Data types and constraints are used to define table columns when creating tables via SQL.
Table of contents
Before you begin
Mapping data types
This table provides mapping between FeatureBase SQL data types and internal data types used by the application for configuring ingestion, API calls, etc.
General data type | FeatureBase SQL data type | Internal data type | Additional information |
---|---|---|---|
boolean | bool | bool | |
integer | int | int | |
decimal | decimal | decimal | |
not applicable | id | mutex | Table primary key |
not applicable | * idset * idsetq | set | Used to reduce table rows and make queries more efficient. |
string | string | keyed mutex | |
not applicable | * stringset * stringsetq | keyed set | Used to reduce table rows and make queries more efficient. |
timestamp | timestamp | timestamp | |
vector | vector | vector |
Date/Time data types
The TIMEUNIT
data type converts date/time values to integer values representing the time since midnight January 1, 1970, UTC/GMT (the Unix Epoch).
Numeric data types
User data | Data type |
---|---|
Boolean, yes/no | BOOL |
Floating point | DECIMAL |
Floating point array | VECTOR |
Unsigned integer | ID |
Signed integer | INT |
String data types
Low-cardinality data types
SET
and SETQ
data types are designed for low-cardinality user data and avoid the need for data normalization:
User data | Associated date/time values | Data type |
---|---|---|
Unsigned integer | None | IDSET |
Unsigned integer | Yes | IDSETQ |
String | None | STRINGSET |
String | Yes | STRINGSETQ |
Values are supplied as comma-separated values and once ingested can be queried using SELECT…SETCONTAINS functions:
Constraints
Constraints are applied:
- to data types to modify and optimize how table data is stored and accessed
- when a column is created
Constraint | Data type |
---|---|
MIN, MAX | INT |
SCALE | DECIMAL |
TIMEQUANTUM, TTL | IDSETQ STRINGSETQ |
TIMEUNIT | TIMESTAMP |
Examples
Create Table statement 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';
Further information
- Create a Cloud table using SQL
- Create a Cloud table using the Cloud UI
- Create a Cloud table using the API