Partitioning a New Table

Use CREATE TABLE to partition a new table, as specified by the PARTITION BY clause:

CREATE TABLE table-name... PARTITION BY partition‑expression [ GROUP BY group‑expression ] [ REORGANIZE ];

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' DIRECT;
41834

This COPY statement specifies DIRECT, so Vertica loads the new table data directly into ROS storage. As it does so, the Tuple Mover executes this table's partition clause by dividing yearly order into separate partitions.

The Tuple Mover regularly executes moveout and mergeout operations, which respectively move new data from WOS into ROS, and consolidate ROS containers. If a table specifies partitioning, the Tuple Mover merges its data into ROS containers accordingly.

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_unseg_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_unseg_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.