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

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

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

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

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.