How to create a projection when creating a table?

jiahe1224

The projection is not copied from the source table. If the projection cannot be created when the table is created, does the existing data in the table need to be created when specifying the encoding to create the projection?

Currently I create projection in three steps:
1. CREATE TABLE...
2. INSERT INTO ...
3. CREATE PROJECTION
It feels very troublesome.

VValdar

Hi jiahe1224,

This works:

CREATE TABLE public.test
( c1  integer      not null ENCODING RLE
, c2  timestamp(0) not null ENCODING COMMONDELTA_COMP
, c3  integer      not null
)
ORDER BY c1, c2
SEGMENTED BY hash(c1) ALL NODES
KSAFE 1;

marcothesane

You can do that in two ways: At CREATE TABLE time, but also in two steps: a) CREATE TABLE; b) CREATE PROJECTION.

Here below, I create a table with all projection related info that comes to my mind, then I show its resulting database objects , by firing a SELECT EXPORT_OBJECTS() at it:
```SQL
CREATE TABLE all_inclusive (
is_historic BOOLEAN ENCODING RLE
, from_dt DATE ENCODING RLE
, fname VARCHAR(32) ENCODING AUTO
, lname VARCHAR(32) ENCODING AUTO
, dob DATE ENCODING GCDDELTA
)
ORDER BY
is_historic
, from_dt
UNSEGMENTED ALL NODES;
-- out CREATE TABLE

SELECT EXPORT_OBJECTS('','all_inclusive',FALSE);
-- out CREATE TABLE dbadmin.all_inclusive
-- out (
-- out is_historic boolean,
-- out from_dt date,
-- out fname varchar(32),
-- out lname varchar(32),
-- out dob date
-- out );
-- out
-- out
-- out CREATE PROJECTION dbadmin.all_inclusive
-- out (
-- out is_historic ENCODING RLE,
-- out from_dt ENCODING RLE,
-- out fname,
-- out lname,
-- out dob ENCODING GCDDELTA
-- out )
-- out AS
-- out SELECT all_inclusive.is_historic,
-- out all_inclusive.from_dt,
-- out all_inclusive.fname,
-- out all_inclusive.lname,
-- out all_inclusive.dob
-- out FROM dbadmin.all_inclusive
-- out ORDER BY all_inclusive.is_historic,
-- out all_inclusive.from_dt
-- out UNSEGMENTED ALL NODES;

Vertica_Curtis

If you do an export_objects() on the source definition, you will get the table DDL as well as all underlying projection DDL for that table. If you're creating those in a target location, you can create the table and all the projections at the same time.

If you just create the table DDL, and load nothing into it, you just have the table DDL itself, and no projection. When you load even a single record into that table, Vertica will create what is known as an un-optimized super projection. This is generally going to be a fairly poorly designed projection that you likely won't want to retain, so it'll be better to create the projections at table creation time if you know what works best for your specific use-cases.

jiahe1224

@Vertica_Curtis

Thank you !
" it'll be better to create the projections at table creation time."
How can I do it? Can you give an example? I didn't find a method in the vertica docs.

Vertica_Curtis

dbadmin=> select * from test ;

x

1
2
(2 rows)

dbadmin=> select export_objects('', 'public.test') ;

export_objects

CREATE TABLE public.test
(
x int
);

CREATE PROJECTION public.test /+createtype(L)/
(
x
)
AS
SELECT test.x
FROM public.test
ORDER BY test.x
SEGMENTED BY hash(test.x) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

(1 row)

-- The above is all of the DDL for this table. The table definition, and the projection definition - two statements - CREATE TABLE and a CREATE PROJECTION. Your table could have one or more CREATE PROJECTION statements associated with the table.

In the target database, simply run ALL of the statements. You'll get your table, and any and all projection definitions.

jiahe1224

@Vertica_Curtis

From your SQL statement, for tables that do not exist, if you want to specify an encoding to create a projection, you still need the three steps I added in the question. I don't know if I understand correctly.

viganog

you can give projection creation details during table creation, i.e.:

CREATE TABLE public.test (x int, a char)
ORDER BY x
SEGMENTED BY x ALL NODES
KSAFE 1;

it creates the table and the related projection ordered and segmented by column x, with k-safety=1
https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETABLE.htm

mosheg

@jiahe1224
For large tables with high number of records we recommend to create projections in advance using the following method.
Use Vertica Database Designer (DBD) to create a comprehensive design, which allows you to create projections for all tables in your database, or an incremental design, which creates projections for tables referenced in the queries you supply to it.
Verify in the DBD recommended projections design that large fact table are not replicated and small tables (dimensions) are replicated between the nodes.
Best practice when you run DBD for the 1st time:
1. Load sample data (several millions of rows)
2. Run the DBD to create the needed projections.
3. Verify that there was a “Refresh” on the DBD projections
SELECT START_REFRESH() runs in the background,
SELECT REFRESH() runs in the foreground
4. Truncate data in tables (without using CASCADE)
5. Reload all data
Please note: DBD does not handle partitions, do it yourself in advance.

see: https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/GettingStartedGuide/UsingDatabaseDeisgner/RunningDBDwithAT.htm

jiahe1224

Thank you, I don’t have DBD permission. For my needs, I still need three steps to create a projection (create a table, insert data, and create a projection). It would be great if I could specify the encoding of the projection when creating the table.