Can not create buddy projection ?

Moderator: NorbertKrupa

Post Reply
komaty
Newbie
Newbie
Posts: 6
Joined: Tue Oct 15, 2013 4:01 pm

Can not create buddy projection ?

Post by komaty » Mon Nov 04, 2013 4:44 am

Hi all,
I want to create buddy projection, here my script:

Code: Select all

-- Create projection -- query_01
CREATE PROJECTION if not exists projection_test
(
table_a_column_a,
table_b_column_b
 ) 
 as 
 select tableA.col_a, tableB.col_b
 from tableA, tableB
 where tableA.FK_ID = tableB_PK_ID
 order by tableA.col_a
unsegmented  all nodes ; 

Code: Select all

-- Create BUDDY projection -- query_02
CREATE PROJECTION if not exists projection_test_B1
(
table_a_column_a,
table_b_column_b
 ) 
 as 
 select tableA.col_a, tableB.col_b
 from tableA, tableB
 where tableA.FK_ID = tableB_PK_ID
 order by tableA.col_a
unsegmented  all nodes  OFFSET 1; 
After running query_02, I get syntax error ' ... near OFFSET ...'
I dont know if I use "unsegmented" command, can I create buddy projection ?

Many thanks .

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Can not create buddy projection ?

Post by JimKnicely » Mon Nov 04, 2013 3:15 pm

Hi,

Using the node "offset" option of the create projection command with the "unsegmented all nodes" option isn't supported. If you are using the "unsegmented all nodes" option you'll already be creating the projection on all nodes :)

I often just let Vertica do the work for me using the ksafe option of the create table and create projection statements.

Example:

I created your tables using the following statements to get the super projections created... I am assuming that tableA is a large fact table while tableB is a smaller look up table. Vertica recommends that smaller tables be unsegmented.

Code: Select all

dbadmin=> create table tableB (PK_ID int, col_B int, primary key (PK_ID)) ksafe;
CREATE TABLE
dbadmin=> create table tableA (FK_ID int not null constraint fk1 references tableB(PK_ID), col_A int) unsegmented all nodes;
CREATE TABLE
Now take a look at the super projections created:

Code: Select all

dbadmin=> select get_projections('tableA');
  get_projections                                                                                                                                                                                                  
----------------------------------------------------------------------------------------------------
Current system K is 1.
# of Nodes: 3.
Table testdb.tableA has 3 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
testdb.tableA_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0002, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: No]

(1 row)

dbadmin=> select get_projections('tableB');
  get_projections                                                                                                                                                                                                  
----------------------------------------------------------------------------------------------------
Current system K is 1.
# of Nodes: 3.
Table testdb.tableB has 2 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
testdb.tableB_b1 [Segmented: Yes] [Seg Cols: "testdb.tableB.PK_ID"] [K: 1] [testdb.tableB_b0] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableB_b0 [Segmented: Yes] [Seg Cols: "testdb.tableB.PK_ID"] [K: 1] [testdb.tableB_b1] [Safe: Yes] [UptoDate: Yes] [Stats: No]

(1 row)
Now create the pre-join projection:

Code: Select all

dbadmin=> CREATE PROJECTION if not exists projection_test
dbadmin-> (
dbadmin(> table_a_column_a,
dbadmin(> table_b_column_b
dbadmin(>  )
dbadmin->  as
dbadmin->  select tableA.col_a, tableB.col_b
dbadmin->  from tableA, tableB
dbadmin->  where tableA.FK_ID = tableB.PK_ID
dbadmin->  order by tableA.col_a
dbadmin-> ksafe;
CREATE PROJECTION
Now if we check the projection for tableA we'll see three more unsegmented projections:

Code: Select all

dbadmin=> select get_projections('tableA');

  get_projections                                                                                                                                                                                                  
----------------------------------------------------------------------------------------------------
 Current system K is 1.
# of Nodes: 3.
Table testdb.tableA has 6 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
testdb.projection_test_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.projection_test_node0002, testdb.projection_test_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.projection_test_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.projection_test_node0003, testdb.projection_test_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.projection_test_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.projection_test_node0003, testdb.projection_test_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0002, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: No]

(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

komaty
Newbie
Newbie
Posts: 6
Joined: Tue Oct 15, 2013 4:01 pm

Re: Can not create buddy projection ?

Post by komaty » Tue Nov 05, 2013 2:36 am

Thanks. I've done.

But I get error "ERROR 3587: Insufficient resources to execute plan on pool general [Request exceeds session memory cap: 4611049KB > 2097152KB]" after using query (include join, example: select * from projection_A, projection_B where ...)
You know, I have 3 nodes with 4GB-RAM each node (total 12GB) . As my thinking, Vertica will load part of data in each node. Is it right ?

P/S: i know use "ALTER USER komary MEMORYCAP '5G';" to fix memory cap. But I want to find another way because I can not increase RAM storage.

pborne
Newbie
Newbie
Posts: 20
Joined: Mon Feb 18, 2013 1:37 am

Re: Can not create buddy projection ?

Post by pborne » Sat Jan 11, 2014 6:37 pm

4GB per node?? Come on! I have 16GB in my laptop... Buy some RAM.

Post Reply

Return to “New to Vertica”