Partition clause

Specifies partitioning of table data, through a PARTITION BY clause in the table definition:.

Specifies partitioning of table data, through a PARTITION BY clause in the table definition:

PARTITION BY partition-expression [ GROUP BY group-expression ] [ active-partition-count-expr ]
PARTITION BY partition-expression
For each table row, resolves to a partition key that is derived from one or more table columns.
GROUP BY group-expression
For each table row, resolves to a partition group key that is derived from the partition key. Vertica uses group keys to merge partitions into separate partition groups. GROUP BY must use the same expression as PARTITION BY. For example: 
...PARTITION BY (i+j) GROUP BY (
     CASE WHEN (i+j) < 5 THEN 1
          WHEN (i+j) < 10 THEN 2
          ELSE 3);

For details on partitioning table data by groups, see Partition grouping and Hierarchical partitioning.

active-partition-count-expr
Specifies how many partitions are active for this table, specified as follows:

  • In partition clause of CREATE TABLE:

    ACTIVEPARTITIONCOUNT integer

  • In partition clause of ALTER TABLE:

    SET ACTIVEPARTITIONCOUNT integer

This setting supersedes configuration parameter ActivePartitionCount. For details on usage, see Active and inactive partitions.

Partitioning requirements and restrictions

PARTITION BY expressions can specify leaf expressions, functions, and operators. The following requirements and restrictions apply:

  • All table projections must include all columns referenced in the expression; otherwise, Vertica cannot resolve the expression.
  • The expression can reference multiple columns, but it must resolve to a single non-null value for each row.
  • All leaf expressions must be constants or table columns.
  • All other expressions must be functions and operators. The following restrictions apply to functions: * They must be immutable—that is, they return the same value regardless of time and locale and other session- or environment-specific conditions. * They cannot be aggregate functions. * They cannot be Vertica meta-functions.
  • The expression cannot include queries.
  • The expression cannot include user-defined data types such as Geometry.

GROUP BY expressions do not support modulo (%) operations.

Examples

The following statements create the store_orders table and load data into it. The CREATE TABLE statement includes a simple partition clause that specifies to partition data by year:

=> CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
UNSEGMENTED ALL NODES
PARTITION BY YEAR(order_date);
CREATE TABLE
=> COPY store_orders FROM '/home/dbadmin/export_store_orders_data.txt';
41834

As COPY loads the new table data into ROS storage, the Tuple Mover executes the table's partition clause by dividing orders for each year into separate partitions, and consolidating these partitions in ROS containers.

In this case, the Tuple Mover creates four partition keys for the loaded data—2017, 2016, 2015, and 2014—and divides the data into separate ROS containers accordingly:

=> SELECT dump_table_partition_keys('store_orders');
... Partition keys on node v_vmart_node0001
  Projection 'store_orders_super'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2017
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2016
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2015
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2014

 Partition keys on node v_vmart_node0002
  Projection 'store_orders_super'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2017
...

(1 row)

As new data is loaded into store_orders, the Tuple Mover merges it into the appropriate partitions, creating partition keys as needed for new years.

See also

Partitioning tables