CODEWITHSUNDEEP
partitioningverticahierarchical
javauser71
javauser71

Reputation: 5259

Vertica Hierarchical Partitioned Table creation throws error


I am using Vertica Analytic Database v8.1.1-8.
I have created a table with simple partitioning clause as:

CREATE TABLE public.test
(
    id timestamp NOT NULL,
    cid numeric(37,15) NOT NULL DEFAULT 0
) 
UNSEGMENTED ALL NODES PARTITION BY id::DATE;

Table got successfully created and I inserted few rows into it. But when I execute following SQL,

SELECT DUMP_PARTITION_KEYS();

I see following:

Partition keys on node v_public_node0001
  Projection 'test_super'
   No of partition keys: 0

 Partition keys on node v_public_node0003
  Projection 'test_super'
   No of partition keys: 0

I was expecting there must be some valid "partition keys".
Thus, wondering have I missed any step here ?

How do I verify that my table really got "partitioned" ?

2) Next I tried "Hierarchical Partitioning" with CALENDAR_HIERARCHY_DAY meta-function to leverages partition grouping. But this time table creation itself failed.

CREATE TABLE public.test
(
    id timestamp NOT NULL,
    cid numeric(37,15) NOT NULL DEFAULT 0
) 
UNSEGMENTED ALL NODES PARTITION BY id::DATE 
 GROUP BY CALENDAR_HIERARCHY_DAY(id::DATE, 2, 2);

with following error:

16:45:14  [CREATE - 0 rows, 0.130 secs]  [Code: 4856, SQL State: 42601]  [Vertica][VJDBC](4856) ERROR: Syntax error at or near "GROUP"
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.130/0.000 sec  [0 successful, 1 errors]

Can anyone pls. suggest what wrong I did?
My goal is to create a table with Hierarchical Partitioning.

Many Thanks in advance,
- Kuntal

Upvotes: 0

Views: 700

Answers (1)

A. Saunders
A. Saunders

Reputation: 875

1) The reason why you are not seeing partition keys right after an insert, is because partitioning only happens on disk (per node, per projection). When you insert rows into a table, those rows are written to Write-optimized-store (WOS) or memory in other words. After a given interval, the data in memory (WOS) is written to disk or Read-optimized-store (ROS). At that point you will see the partition keys.

The process of data being copied from WOS to ROS is performed by the tuple mover (https://www.vertica.com/docs/latest/HTML/Content/Authoring/Glossary/TupleMover.htm).

In short to see the partition keys, either wait 5 min or so for the tuple mover to initiate an automatic moveout, or you can force data from WOS to be written to ROS by executing a manual moveout.

SELECT DO_TM_TASK('moveout', 'public.test');

Then you should see the keys.

2) Hierarchical partitioning is a Vertica 9 feature. You will need to upgrade to at least Vertica 9.0 in order to use that feature.

https://www.vertica.com/blog/whats-new-vertica-9-0-hierarchical-partitioning/

Upvotes: 1

Related Questions