Managing namespaces

Eon Mode databases group all schemas and tables in the database into one or more namespaces. Namespaces represent the top level in the Vertica object hierarchy, where every schema—and its constituent tables—belong to a namespace. Each namespace has a distinct shard count that defines the segmentation of its member objects. By default, the database contains a single namespace, default_namespace, which is created on database creation with the shard count specified during setup.

You can create additional namespaces with the CREATE NAMESPACE statement. When you create a table or schema, you can specify the namespace under which to create the object. If no namespace is specified, the table or schema is created under the default_namespace. If you have not created any objects under non-default namespaces, you can ignore namespaces when operating your database because Vertica correctly assumes the namespace of all tables and schemas to be default_namespace.

If you upgrade a database using a Vertica version less than 24.1 to a version 24.1 or later, Vertica creates the default_namespace and assigns to it all schemata and tables in your database.

Create namespaces

To create a namespace, use CREATE NAMESPACE, specifying its name and shard count:

=> CREATE NAMESPACE airport SHARD COUNT 12;
CREATE NAMESPACE

If no shard count is provided, the namespace is created with the shard count of the default_namespace. You can view all namespaces in your database by querying the NAMESPACES system table:

=> SELECT namespace_name, is_default, default_shard_count FROM NAMESPACES;
  namespace_name   | is_default | default_shard_count
-------------------+------------+---------------------
 default_namespace | t          |                   6
 airport           | f          |                   12
(2 rows)

To drop namespaces, use DROP NAMESPACE.

Create schemas and tables under namespaces

When creating schemas and tables, you can specify the namespace in which the object is created. The CREATE TABLE and CREATE SCHEMA statements accept a 3-part naming format, where the top level name identifies the namespace. For example, n.s.t refers to table t in schema s in namespace n. If no namespace is specifed, the schema or table is created in the default_namespace.

Schema names must be unique in the same namespace, but there can be schemas of the same name in different namespaces:

=> CREATE SCHEMA airline;
CREATE SCHEMA

=> CREATE SCHEMA airport.airline;
CREATE SCHEMA

=> SELECT schema_name, schema_namespace_name FROM SCHEMATA WHERE schema_name = 'airline';
 schema_name | schema_namespace_name
-------------+-----------------------
 airline     | default_namespace
 airline     | airport
(2 rows)

All tables created in a given schema exist in the same namespace as that schema. You can use the ALTER TABLE statement to change the schema of a table to another schema in the same namespace, but not to a schema in a different namespace.

Create a flights table in the airline schema under both the airport and default namespace:

=> CREATE TABLE airport.airline.flights(
    flight_number INT,
    leaving_from VARCHAR,
    arriving_at VARCHAR,
    expected_departure DATE,
    gate VARCHAR
);
CREATE TABLE

=> CREATE TABLE airline.flights(
    flight_number INT,
    leaving_from VARCHAR,
    arriving_at VARCHAR,
    expected_departure DATE,
    gate VARCHAR
);
CREATE TABLE

=> SELECT table_name, table_schema, table_namespace_name FROM TABLES WHERE table_name = 'flights';
 table_name | table_schema | table_namespace_name
-------------------+--------------+----------------------
 flights    | airline      | default_namespace
 flights    | airline      | airport
(2 rows)

Backing up, restoring, and replicating namespaces

The vbr backup, restore, and replicate tasks support namespaces. Full-database backup and restore operations work as expected and require no extra configurations for namespaces. However, object-level vbr tasks have the following requirements for databases using multiple namespaces:

• Specify the namespace of objects when identifying them in either the vbr configuration file or command line arguments. If you do not specify an object's namespace, it is assumed to be default_namespace.
• For vbr tasks, namespaces are prefixed with a period. For example, .n.s.t refers to table t in schema s in namespace n.
• Object-level restore and replicate vbr tasks let you specify a target namespace with the --target-namespace command line argument. This is the namespace to which the objects are restored or replicated in the target cluster. The target namespace must already exist in the target cluster and have the same shard count, shard boundaries, and node subscriptions as the object's namespace in the source cluster. For more information, see see Eon Mode database requirements.

Server-based replication also supports namespaces. The source and target namespaces of the replicated tables must have the same shard count and segmentation. The REPLICATE command accepts an optional target namespace parameter that specifies the namespace to which the tables are replicated in the target cluster. If you do not specify a target namespace, objects are replicated to a namespace with the same name as the source namespace. If no such namespace exists in the target cluster, it is created with the same name and shard count as the source namespace. For details, see REPLICATE.

Naming conventions

Database objects in Vertica can be uniquely identified by a 3-part name. The valid components of an object's 3-part name in an Eon Mode database depend on whether the object is in the default namespace:

• Default namespace: 3-part names can take the form database.schema.object or namespace.schema.object. If the first part of a 3-part name is a valid database name, Vertica assumes the object is in default_namespace.
• Non-default namespace: 3-part names must be in the form namespace.schema.object. 4-part names—database.namespace.schema.object—are not supported.

To run federated queries across multiple databases, some third-party tools require that 3-part names be in the form database.schema.object. If any objects in the database are in a non-default namespace, theses federated queries will fail.

Namespace restrictions

Namespaces currently have the following restrictions:

• You can only use the RESHARD_DATABASE function to reshard the default_namespace. If your database has any non-default namespaces, running the function results in an error.
• You can only create, restore, and replicate flex tables to the default_namespace.
• Some third-party libraries do not support namespaces. If your workload relies on one of these libraries, Vertica recommends that you do not use non-default namespaces until the third-party library adds namespace support.