Skip to main content

Schema and Keyspace Creation

Data Orchestrator automatically takes care of keyspaces, tables creation and intra-version migrations (those tasks are performed by astarte_housekeeping or astarte_realm_management, depending on the context). The following documentation is just a reference about Data Orchestrator's internal statements, and is related to the release series referenced by the documentation.

Data Orchestrator Instance ID​

The Data Orchestrator Instance ID is needed in order to run multiple Data Orchestrator instances on a shared database, as it provides proper namespace isolation. It is configurable by setting the ASTARTE_INSTANCE_ID environment variable, and when set to an empty string (the default), maintains the same behavior as previous installations. The Data Orchestrator Instance ID is fully supported by the Data Orchestrator Operator. For the usage with the operator you can refer to the corresponding documentation.

Inner workings​

When the Data Orchestrator Instance ID is set, it is used as a prefix for the Cassandra keyspace name. If the resulting keyspace name would exceed Cassandra’s length limits, suitable adjustments are automatically applied to ensure compliance.

Data Orchestrator Keyspace​

Data Orchestrator needs an astarte keyspace to store its own data.

The keyspace replication strategy can be of type SimpleStrategy or NetworkTopologyStrategy, this can be configured via the HOUSEKEEPING_ASTARTE_KEYSPACE_REPLICATION_STRATEGY environment variable. If the strategy is not explicitly configured the NetworkTopologyStrategy mode will be enforced, with replication map derived from the live database cluster topology.

astarte keyspace and tables are created with following CQL statements (the examples are given with SimpleStrategy settings):

CREATE KEYSPACE astarte
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': <replication factor>} AND
durable_writes = true;
Tablets

In recent versions of scylladb, we explicitly disable the tablets feature as they break lightweight transactions, which are used within astarte

CREATE KEYSPACE astarte
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': <replication factor>} AND
durable_writes = true AND
tablets = { 'enabled': false };
CREATE TABLE astarte.realms (
realm_name varchar,
device_registration_limit bigint,

PRIMARY KEY (realm_name)
);
CREATE TABLE astarte.kv_store (
group varchar,
key varchar,
value blob,
PRIMARY KEY (group, key)
);

Realm Creation​

Each realm needs several tables to store data for all the functionalities. Realm tables can be grouped in the following functionalities:

  • Configuration & key-value store
  • Interfaces schema
  • Device management
  • Groups management
  • Triggers storage
  • Data storage

Some data storage tables might be created when required, whereas all other tables are created when a keyspace is created, using the following statements:

CREATE KEYSPACE <realm name>
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': :replication_factor} AND
durable_writes = true;
Tablets

In recent versions of scylladb, we explicitly disable the tablets feature as they break lightweight transactions, which are used within astarte

CREATE KEYSPACE <realm name>
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': :replication_factor} AND
durable_writes = true AND
tablets = { 'enabled': false };

If not explicitly stated in the context of the realm creation request, the replication strategy of the realm keyspace is inherited from the astarte keyspace configuration.

CREATE TYPE <realm name>.capabilities (
purge_properties_compression_format int
);
CREATE TABLE <realm name>.kv_store (
group varchar,
key varchar,
value blob,

PRIMARY KEY ((group), key)
);
CREATE TABLE <realm name>.names (
object_name varchar,
object_type int,
object_uuid uuid,

PRIMARY KEY ((object_name), object_type)
);
CREATE TABLE <realm_name>.devices (
device_id uuid,
aliases map<ascii, varchar>,
introspection map<ascii, int>,
introspection_minor map<ascii, int>,
old_introspection map<frozen<tuple<ascii, int>>, int>,
protocol_revision int,
first_registration timestamp,
credentials_secret ascii,
inhibit_credentials_request boolean,
cert_serial ascii,
cert_aki ascii,
first_credentials_request timestamp,
last_connection timestamp,
last_disconnection timestamp,
connected boolean,
pending_empty_cache boolean,
total_received_msgs bigint,
total_received_bytes bigint,
exchanged_bytes_by_interface map<frozen<tuple<ascii, int>>, bigint>,
exchanged_msgs_by_interface map<frozen<tuple<ascii, int>>, bigint>,
last_credentials_request_ip inet,
last_seen_ip inet,
attributes map<varchar, varchar>,

groups map<varchar, timeuuid>,
capabilities capabilities,

PRIMARY KEY (device_id)
);
CREATE TABLE <realm name>.grouped_devices (
group_name varchar,
insertion_uuid timeuuid,
device_id uuid,

PRIMARY KEY ((group_name), insertion_uuid, device_id)
);
CREATE TABLE <realm name>.endpoints (
interface_id uuid,
endpoint_id uuid,
interface_name ascii,
interface_major_version int,
interface_minor_version int,
interface_type int,
endpoint ascii,
value_type int,
reliability int,
retention int,
expiry int,
database_retention_ttl int,
database_retention_policy int,
allow_unset boolean,
explicit_timestamp boolean,
description varchar,
doc varchar,

PRIMARY KEY ((interface_id), endpoint_id)
);
CREATE TABLE <realm name>.interfaces (
name ascii,
major_version int,
minor_version int,
interface_id uuid,
storage_type int,
storage ascii,
type int,
ownership int,
aggregation int,
automaton_transitions blob,
automaton_accepting_states blob,
description varchar,
doc varchar,

PRIMARY KEY (name, major_version)
);
CREATE TABLE <realm name>.simple_triggers (
object_id uuid,
object_type int,
parent_trigger_id uuid,
simple_trigger_id uuid,
trigger_data blob,
trigger_target blob,

PRIMARY KEY ((object_id, object_type), parent_trigger_id, simple_trigger_id)
);
CREATE TABLE <realm name>.individual_datastreams (
device_id uuid,
interface_id uuid,
endpoint_id uuid,
path varchar,
value_timestamp timestamp,
reception_timestamp timestamp,
reception_timestamp_submillis smallint,
binaryblob_value blob,
binaryblobarray_value list<blob>,
boolean_value boolean,
booleanarray_value list<boolean>,
datetime_value timestamp,
datetimearray_value list<timestamp>,
double_value double,
doublearray_value list<double>,
integer_value int,
integerarray_value list<int>,
longinteger_value bigint,
longintegerarray_value list<bigint>,
string_value varchar,
stringarray_value list<varchar>,
PRIMARY KEY ((device_id, interface_id, endpoint_id, path), value_timestamp, reception_timestamp, reception_timestamp_submillis)
);
CREATE TABLE <realm name>.individual_properties (
device_id uuid,
interface_id uuid,
endpoint_id uuid,
path varchar,
reception_timestamp timestamp,
reception_timestamp_submillis smallint,
double_value double,
integer_value int,
boolean_value boolean,
longinteger_value bigint,
string_value varchar,
binaryblob_value blob,
datetime_value timestamp,
doublearray_value list<double>,
integerarray_value list<int>,
booleanarray_value list<boolean>,
longintegerarray_value list<bigint>,
stringarray_value list<varchar>,
binaryblobarray_value list<blob>,
datetimearray_value list<timestamp>,

PRIMARY KEY((device_id, interface_id), endpoint_id, path)
);
CREATE TABLE <realm name>.deletion_in_progress (
device_id uuid,
vmq_ack boolean,
dup_start_ack boolean,
dup_end_ack boolean,
groups set<text>,

PRIMARY KEY (device_id)
);

The following table is generated upon datastream interface creation for keeping all data sent to Data Orchestrator through the interface.

The table name is derived from lower case interface name where . and - have been replaced by _ and "" (empty string), then the major version is appended with a _v prefix. For example, com.Data Orchestrator.TestInterface version 1 becomes com_astarte_testinterface_v1.

If, after all the required transformations, the resulting name is too long (>45 chars), it will be encoded and truncated.

CREATE TABLE <interpolated interface name>_v<major_version> (
device_id uuid,
path varchar,
reception_timestamp timestamp,
reception_timestamp_submillis smallint,
v_<property_mapping> <property_type>,
v_<property_mapping> <property_type>,
...
PRIMARY KEY ((device_id, path), reception_timestamp, reception_timestamp_submillis)
);