Create buddy projections based on k-safety with a single CREATE PROJECTION statement

Sajan_Sangraula

How can we use a single CREATE PROJECTION statement to create the correct number of buddy projections based on the k-safety of the cluster? I have a deployment script that creates tables and projections with segmentation. When deployed in a 3-node cluster with k-safety=1, I would like the script to create a pair of buddy projections, and when deployed in a single node instance with k-safety=0, the script should only create a single projection. 

Creating 2 buddy projections with offset=1 irrespective of k-safety works, but that results in 2 identical copies of the projection in a single node, just taking disk space and extra load time. I looked at projections created by dbdesigner as well. DBD seems to be adding CREATE PROJECTION statements for each buddy projection required which is not ideal for maintaining a single deployment script for different cluster size.

Thanks

[Deleted User]

Hi Sajan,

Have you considered using CREATE TABLE, rather than CREATE PROJECTION?

CREATE PROJECTION does what it says -- it creates one projection.  CREATE TABLE will create a table, along with an appropriate number of projections depending on your K-safety (unless you manually issue CREATE PROJECTION statements yourself).

Vertica's CREATE TABLE command has a number of extensions that allow you to specify things like the segmentation, sort order, column encoding, etc., of the automatically-created projections:

https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETABLE.h...

Are there projection parameters that you would like to set, that are not available on CREATE TABLE?  If so, I would encourage you to post an idea to the "Ideas" section of this site suggesting that the functionality be added.

The DBD generates custom SQL programmatically every time it's run, tuned for the specific database and data set that it's running on.  So, at the moment, it chooses to be very verbose and explicit about exactly what it's doing.  This can be helpful in some cases, should you need to hand-edit DBD rules.  But it's not always the best example when trying to write portable scripts.

Adam

Sajan_Sangraula

Hi Adam,
Thanks a lot for your response. The CREATE TABLE option works well for creating superprojections, and its great that we can specify segmentation, sort order and column encoding. However, I also have a number of projections additional to the superprojections that don't include all the columns, and differ in segmentation clause and sort order from the superprojections. Is there a way to control the number of buddies for these additional projections based on k-safety?

I will post an idea once you confirm whether this feature exists or not.

Thanks

[Deleted User]

Hi Sajan,

Ah, yes, non-super-projections would be important too!  Good point.

Interesting -- I hadn't tried to do this before; but, playing around with CREATE PROJECTION:

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEPROJE...

Normally, you would create a projection without specifying a K-safety, or with specifying "KSAFE 0" or "KSAFE 1" at the end of the projection declaration.  But if you declare it with just "KSAFE", and omit the number, Vertica will use the system K-safety level.

Does that help here?

Adam

Sajan_Sangraula

That's perfect. I would always follow KSAFE with a value. Just declaring KSAFE and having Vertica pick up the right fault tolerance level takes care of my requirement to have a single deployment script.

Thanks a lot.