Tables
The concept of tables is at the heart of Kinetica interactions. A table
is a data container associated with a specific type schema
(set of columns & types), much like tables in other database platforms. When
querying a table, the result set will also have a single type schema
associated with it.
Tables may exist either as top-level objects or as members of
collections.
Each table is identified by a name, which must meet the following criteria:
- Between 1 and 200 characters long
- Alphanumeric, including spaces and these symbols:
_ { } [
] . : - ( )
- First character is alphanumeric or an underscore
- Unique system-wide--cannot have the same name as another table,
view, or collection, regardless of whether it is top-level or
a member of a collection
Column names must meet the following criteria:
- Between 1 and 200 characters long
- Alphanumeric, including these symbols:
_ { } [ ] .
:
- First character is alphanumeric or an underscore
Using the /create/table endpoint, you can create a blank table (after
you've created a type schema to support the table) that can later hold records.
For example, in Python:
gpudb.create_table(
table_name = table_name,
type_id = type_id
)
Table data can be distributed across the Kinetica cluster
using one of two schemes: sharding & replication. Within the sharding
scheme, data can be distributed either by key or
randomly in the absence of a
shard key.
Sharding is the distribution of table data by hashing a
particular value for each record, and by that hash, determining on which
Kinetica cluster node the record will reside.
The benefit of sharding is that it allows distributed queries to be run
against a given data set, with each node responsible for managing its portion of
the overall data set, while only storing each record once across the cluster.
The parallelism inherent in the distributed queries allows for the query
performance to scale linearly with the addition of each cluster node.
Another benefit of sharding is that it increases the performance of any
aggregation run against the table where the aggregation group includes all of
the shard key columns.
A limitation of sharding is that two sharded data sets can only be
joined together if they are sharded in the same way, so that
their corresponding records will all reside on the same nodes. Given that, in a
typical database model, each pair of related data sets is associated by a
different key, a single query may only be able to join together at most two
sharded data sets along their relation.
Since sharding maximizes data storage & query efficiency, it is recommended
that the largest table in the system (e.g. the fact table in a data warehouse)
be sharded. The second largest table against which that table is joined
should also be sharded along the join relation (e.g. the column on each side
of the foreign key relationship).
Given that sharding allows for greater-performant aggregations, it is also
recommended that any large tables subject to frequent aggregation queries also
be sharded on the grouping columns.
For example, if the largest joined tables in a system were customer &
order, and there was a foreign key relationship between the customer_id
column of the order table & the id column of the customer table,
the order table should be sharded on customer_id and the
customer table should be sharded on id.
If, for example, there were also an order_history table, which was
frequently aggregated on store_id, it could be sharded on store_id to
increase the performance of those queries.
Specifying a shard key requires that you create a type with at least one
column that has the shard_key property when calling the
/create/type endpoint. See Shard Keys for details.
Random sharding is the distribution of table data by randomly
selecting a shard for each batch of records during ingestion. Since all of
the records from each batch will go to the selected shard, it is important to
configure the batch size appropriately to ensure an even distribution of records
across the cluster over the entire ingestion process.
Random sharding has the same benefit as sharding with respect to distributed
query parallelism and storage minimization. It lacks, however, the benefit of
greater-performant aggregations over the shard key, as the randomly-sharded
data set lacks one.
A limitation of random sharding is that a randomly-sharded data set can only
be joined with replicated data sets--it cannot be joined with
other sharded or randomly-sharded data sets.
Given that random sharding maximizes data storage & query efficiency in the
same way that sharding does, it is recommended that large tables in the
system that are not good candidates for sharding with respect
to joins & aggregations be randomly-sharded.
For example, if a large table in a system would only ever be joined with
replicated tables and would never have aggregations run against it, the
table would be a good candidate for random sharding.
Random sharding is the default distribution technique applied to tables that
meet the following criteria:
- No primary key specified
- No shard key specified
- Not specified as replicated
Replication is the distribution of table data by locating its
entire data set on every Kinetica cluster node simultaneously, instead of
being distributed across them.
The benefit of replication is that it allows data sets to be
joined together when those data sets are not
sharded on the columns being associated.
Normally, joining two data sets together requires them being joined on their
shard keys so that the joining of the two data sets can
occur locally on each processor shard. Since replicated data sets exist on
all shards, they appear local to any other data set and can be joined on each
shard as if they were local. The result sets from each shard are then
accumulated into one and returned as the final result set.
Since replicated data sets exist in their entirety on all processors, it is
recommended that they be relatively small. For example, a table containing
one gigabyte of data, when replicated across a 10-node cluster will occupy 10
gigabytes of cluster memory overall.
A table is specified as replicated at creation time, by calling the
/create/table endpoint with the is_replicated option set to
true.
For example, in Python:
gpudb.create_table(
table_name = set_id,
type_id = type_id,
options = {'is_replicated': 'true'}
)
Primary key is a designation that can be applied to one or more columns in a
table. A primary key composed of multiple columns is known as
a composite primary key. The primary key is used to ensure the uniqueness
of the data contained in the keyed column(s).
The uniqueness constraint is enforced upon insert in two ways:
- If a record to be inserted has key values that match those of an already
existing record in the target table, the new record’s values will either:
- Overwrite the existing record’s values, if the
update_on_existing_pk
option is set to true
- Be ignored (the new record effectively discarded), if the
update_on_existing_pk option is set to false or not set
- If two or more records within a given batch insert have the same key values,
with respect to the primary key of the target table, the entire batch of
records to insert is rejected
By default, a table has no primary key. One must be explicitly designated
in the creation of the type schema associated with the
table. Only one primary key can exist per table.
The primary key for a table not created as replicated
becomes its implicit shard key, used for distributing its
records across processors. Replicated tables, by definition, are not
sharded and will necessarily have no shard key, implicit or otherwise.
This implicit shard key for non-replicated tables can only be overridden
when the primary key is a composite primary key and one or more of the
composite primary key columns is explicitly designated as the shard key.
The primary key designation also applies a
primary key index to the primary key columns.
Lastly, the primary key designation enables the column to serve as the target
of a foreign key.
Assigning a primary key to a table column requires two steps. The first is
to create a type schema, using /create/type, marking the
primary key field(s) with the primary_key property. For example, to
create a product type schema with a primary key on product_id, in
Python:
response = gpudb.create_type(
type_definition = """{
"type": "record",
"name": "product_type",
"fields": [
{"name":"product_id","type":"int"},
{"name":"product_name","type":"string"}
]
}""",
label = 'product_type',
properties = {'product_id': ['primary_key','int16']}
)
Note that the type_definition is a JSON string defining the type schema.
The second step is to create a table with that type schema, using
/create/table, providing it the type_id that is returned from the
/create/type call. Continuing from the previous example:
gpudb.create_table(
table_name = 'product',
type_id = response['type_id']
)
Shard key is a designation that can be applied to one or more columns in a
table. A shard key composed of multiple columns is known as a
composite shard key. The shard key is used in distributing records across
processors. This distribution allows for processing of queries against a
sharded table to be performed in parallel.
By default, a hash is computed for each record in a table and serves as the
key by which the associated record is distributed to its corresponding
processor, or shard. A shard key can be explicitly designated by assigning
the SHARD_KEY property to one or more columns of a table.
Note
Store-only columns cannot be used as all
or part of a shard key.
A shard key is implicitly designated when a primary key
is assigned to any table not created as replicated. By
default, all columns involved in the primary key are used as the shard key.
If a primary key exists on a table, one or more of the columns composing the
primary key can be designated as the shard key; only primary key columns
may be used--columns not part of the primary key may not be designated as
shard key columns. Designating a shard key does not automatically create a
corresponding primary key with the same column(s) for a table.
Only one shard key can exist per table.
Assigning a shard key to a table column requires two steps. The first is
to create a type schema, using /create/type, marking the
shard key field(s) with the primary_key or shard_key property (either
will work, as a primary key is a table's shard key, by default). For
example, to create a product type schema with a shard key on sku, in
Python:
response = gpudb.create_type(
type_definition = """{
"type": "record",
"name": "product_type",
"fields": [
{"name":"product_id","type":"int"},
{"name":"sku","type":"int"},
{"name":"product_name","type":"string"}
]
}""",
label = 'product_type',
properties = {
'product_id': ['int16'],
'sku': ['shard_key']
}
)
Note that the type_definition is a JSON string defining the type schema.
The second step is to create a table with that type schema, using
/create/table, providing it the type_id that is returned from the
/create/type call. Continuing from the previous example:
gpudb.create_table(
table_name = 'product',
type_id = response['type_id']
)
Note also that sharding applies only to non-replicated tables, and the
default /create/table distribution scheme implied in the example
above is the non-replicated one. If an attempt is made to create a table
as replicated from a type that specifies a shard key that is not the same
as the primary key, the request will fail.
A foreign key is a tuning option for joining
tables. It acts as a relational index from a source table to
corresponding records in a target table. As new records are inserted into the
source table, the index is updated with references to the target records
associated with them. If a foreign key is not used, the join relationships
are established during query time.
In order for the linkage to be made successfully, it must connect the
shard key column of a source table with either the
similarly sharded primary key column of a
target table or the primary key column of a replicated
target table. The sharding requirement exists to ensure the related records
of each table being joined are located on the same processing node. The
replication alternative alleviates the sharding requirement, as all of the
records from the replicated target table will exist on all processor nodes,
and thus, appear to be local to all records from the source table involved in
the relation.
Foreign keys link single source columns to single primary key target
columns; multi-column foreign keys are not supported.
Multiple foreign keys can exist on a source table. Multiple foreign keys
may also exist on target tables; though, since foreign key targets must be
primary keys, the foreign keys would all have to target the same
primary key column.
Foreign keys do not provide referential integrity checks.
Foreign keys are designated at the time of table creation, using the
/create/table endpoint. Example (in Python):
gpudb.create_table(
table_name = my_FK_table,
type_id = my_type_id,
options = {
"foreign_key":"fk_column1 references my_PK_table(pk_column1)"
}
)
