Partitioning a New Table
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
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 ...
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.