Planning Your Riak TS Table
Now that you’ve installed Riak TS, you’re almost ready to create a TS table. Before you can create your table, you’ll need to plan it out.
This page provides a basic overview of what you’ll need and some guidelines/limitations. For a deeper dive into planning and designing Riak TS tables, check out Table Architecture.
Riak TS tables are closely tied to SQL tables. If you are unfamiliar with SQL or would like to know more about how Riak TS integrates SQL, check out SQL for Riak TS.
Anatomy of a Schema
In order to create a working Riak TS table, you’ll need plan your table out. Once created, your table cannot be changed. Here is an example Riak TS CREATE TABLE
statement (broken across many lines for clarity):
CREATE TABLE GeoCheckin
(
myfamily varchar not null,
myseries varchar not null,
time timestamp not null,
weather varchar not null,
temperature double,
PRIMARY KEY (
(myfamily, myseries, quantum(time, 15, 'm')),
myfamily, myseries, time
)
)
Fields
Fields, also called columns, refer to the items before the PRIMARY KEY
. Field names (myfamily
, myseries
, etc) must be ASCII strings, in addition to having the correct case. If field names need to contain spaces or punctuation they can be double quoted.
Field names define the structure of the data, taking the format:
name type [not null],
Fields specified as part of the primary key must be defined as not null
.
The types associated with fields are limited. Valid types are:
varchar
- Any string content is valid, including Unicode. Can only be compared using strict equality, and will not be typecast (e.g., to an integer) for comparison purposes. Use single quotes to delimit varchar strings.
boolean
true
orfalse
(any case)
timestamp
- Timestamps are integer values expressing UNIX epoch time in UTC in milliseconds. Zero is not a valid timestamp.
sint64
- Signed 64-bit integer
double
- This type does not comply with its IEEE specification:
NaN
(not a number) andINF
(infinity) cannot be used.
- This type does not comply with its IEEE specification:
Primary Key
The PRIMARY KEY
describes both the partition and local keys. The partition key and the local key are nearly identical, differing only by the definition of the quantum
used to colocate data.
Note: The order of fields in key spec must match the order in which they appear in the table definition.
Partition Key
The partition key is defined as the three named fields in parentheses:
(myfamily, myseries, (quantum(time, 15, 'm')),
The partition key MUST have exactly three fields in the following order:
- The first field (family) is a class or type of data.
- The second field (series) identifies the specific instances of the class/type, such as username or device ID.
- The third field (quantum) sets the time intervals to group data by.
The quantum function takes 3 parameters:
- the name of a field in the table definition of type
timestamp
- a quantity
- a unit of time:
- ’d’ - days
- ‘h’ - hours
- ’m’ - minutes
- ’s’ - seconds
Local Key
The second key (local key) MUST contain the same 3 fields in the same order as the partition key. This ensures that the same fields determining your data’s partition also dictate the sorting of the data within that partition.
Schema Discovery
After creating a table, its schema can be discovered with DESCRIBE
statement, which will return, for each column, the following items:
* Column, field name;
* Type, field type;
* Is Null, true is the field is optional, false otherwise;
* Primary Key, position of this field in the primary key, or blank if it does not appear in the key;
* Local Key, position of this field in the local key, or blank if it does not appear in the key.
More information
Still unsure how best to structure your Riak TS table? Check out our best practice recommendations.
Confused about columns, primary key, etc? Check out Table Architecture for full definitions.
Next Steps
Now that you know how your Riak TS table will be structured, you can move on to creating and activating your table.