A schema is a container for all database objects, providing namespacing for them.
Namespacing occurs at the schema level and at the category level, meaning that:
- One schema could contain a table with the same name as a table in another schema.
- One schema could contain a data source and data sink with the same name.
- One schema cannot contain an external table and a view with the same name.
Each object in the database will have a containing schema; no object will exist outside of all schemas.
Name Resolution
A database object within a specific schema can be addressed by qualified name, prefixing the object name with the schema name separated by a dot; e.g.:
<schema name>.<object 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 database object
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 other object references that a user makes when the user does not specify a schema explicitly.
When creating a table or other object, 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 other object (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 or after creation.
Home Schema
The home schema is a schema named ki_home. All new users will have the home schema as their default schema.
Built-in Schemas
In addition to the home schema, there are several schemas that may exist in a default Kinetica installation.
| Schema | Description |
|---|---|
| demo | Contains any demo datasets loaded into the database. |
| 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. Note Only visible to users with sys_admin permission. |
| sys_temp | Contains temporary tables created in processing user requests. 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 other objects can be added to the schema at creation time using any endpoints that create them, prefixing the name of the object to create with the schema name, separated by a dot.
A schema can be dropped using the /drop/schema endpoint.
