Version:

Schemas

A schema (formerly collection) is a container for tables and/or views. It provides namespacing for those contained database objects, meaning that one schema could contain a table with the same name as a table in another schema. Each table & view in the database will have a containing schema; no table or view will exist outside of all schemas.

Name Resolution

A database object within a specific schema can be addressed by qualified name, prefixing the table name with the schema name separated by a dot; e.g.:

<schema name>.<table/view name>

Database objects referenced without a qualified name will be looked for in the user's default schema, if assigned.

Naming Criteria

Each schema 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 among schemas--cannot have the same name as another schema, but can have the same name as any other table or view

Visibility

A schema will be visible to any user in any of the following situations:

  • The user has system_admin, system_read, or system_write permission
  • The user has table_admin or table_read permission on the schema
  • The user has table_admin or table_read permission on at least one table or view within the schema

The home schema will always be visible to all users.

Default Schema

A default schema can be associated with any user, to apply a default containing schema to any table or view references that a user makes when the user does not specify a schema explicitly.

When creating a table or view, including the result table of any operation, without specifying the schema, that object will be created in the user's default schema. If the user has no default schema, an error will be returned.

When attempting to perform an operation on an existing table or view (reading, writing, altering, etc.) without specifying a schema, the object will be looked for in the user's default schema. If it is not found there, or if the user has no default schema, an error will be returned.

A user can be assigned a default schema during creation, using the /create/user/internal endpoint, or after creation, using the /alter/user endpoint.

Home Schema

Upgrades from releases of Kinetica prior to version 7.1 will have a home schema named ki_home.

Any top-level tables (those not contained within a schema) that existed prior to the upgrade will be moved to the home schema during the upgrade.

Any users that existed prior to the upgrade will be given the home schema as their default schema, so that any unqualified references to those top-tables in existing applications will continue to function without modification. All new users will have the home schema as their default schema by default as well, for consistency.

Built-in Schemas

In addition to the home schema, there are several other schemas that may exist in a Kinetica installation.

Schema Description
demo Contains any demo datasets loaded into the database; in releases prior to 7.1, this schema was named MASTER
filesystem

Contains virtual files managed through KiFS

Note

Only available if KiFS is enabled

ki_home The Kinetica home schema, and the default schema for all users
sys_security

Contains temporary tables created during row/column security processing

Note

Only visible to users with sys_admin permission

sys_sql_temp

Contains temporary tables created during the execution of SQL queries; in releases prior to 7.1, this schema was named __SQL_TEMP

Note

Only visible to users with sys_admin permission

sys_temp

Contains temporary tables created in processing user requests; in releases prior to 7.1, this schema was named __TEMP

Note

Only visible to users with sys_admin permission

SYSTEM Contains the ITER virtual table

Schema Management

Schemas have the same naming criteria as tables, and cannot be named any of the following, regardless of case:

  • KINETICA
  • PUBLIC
  • ROOT
  • SYSTEM

A schema can be created using the /create/schema endpoint.

After a schema is created, tables and views can be added to the schema at creation time using any endpoints that create them, prefixing the name of the table or view to create with the schema name, separated by a dot.

A schema can be dropped using the /drop/schema endpoint.

Limitations & Cautions

  • Collections created in releases prior to v7.1 that have periods in their names will become schemas in v7.1 and have those periods replaced with pound signs; for instance, a collection with the following name in v7.0:

    schema.from.v7.0

    ...would now be a schema, referenced as follows:

    schema#from#v7#0

    A table named table_from_v7_0 in that schema would be referenced like this:

    schema#from#v7#0.table_from_v7_0

  • Time-to-Live (TTL) no longer applies to schemas.