createtype(A)

Moderator: NorbertKrupa

Post Reply
dbmsuser7
Intermediate
Intermediate
Posts: 71
Joined: Tue Jan 28, 2014 3:03 am

createtype(A)

Post by dbmsuser7 » Thu Feb 27, 2014 7:48 pm

Anyone know what this one ("A") signifies?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: createtype(A)

Post by NorbertKrupa » Thu Feb 27, 2014 7:50 pm

Can you provide some context around what you're doing or where you're seeing this?
Checkout vertica.tips for more Vertica resources.

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

Re: createtype(A)

Post by JimKnicely » Thu Feb 27, 2014 8:33 pm

Anyone know what this one ("A") signifies?
You must be referring to the projection type. And I believe "A" signifies "A"utomatic projection creation...

Example:

If you use CREATE TABLE AS SELECT to create a table from the results of a query, the table is created first and a projection is created immediately after, using some of the properties of the underlying SELECT query.

Code: Select all

dbadmin=> create table public.jim (col1 int);
CREATE TABLE
dbadmin=> create table public.jim2 as select * from jim;
CREATE TABLE
dbadmin=> select export_objects('', 'public.jim2');
                                                                                                                     export_objects                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

CREATE TABLE public.jim2
(
    col1 int
);



CREATE PROJECTION public.jim2 /*+createtype(A)*/ 
(
 col1
)
AS
 SELECT jim2.col1
 FROM public.jim2
 ORDER BY jim2.col1
SEGMENTED BY hash(jim2.col1) ALL NODES KSAFE 1;


SELECT MARK_DESIGN_KSAFE(1);

(1 row)
Check the "Automatic Projection Creation" section in the SQL Guide for more details!

https://my.vertica.com/docs/7.0.x/PDF/H ... Manual.pdf
Jim Knicely

Image

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

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: createtype(A)

Post by NorbertKrupa » Thu Feb 27, 2014 8:43 pm

I think it would be valuable to put together a list of createtypes for future reference. Thoughts?
Checkout vertica.tips for more Vertica resources.

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

Re: createtype(A)

Post by JimKnicely » Thu Feb 27, 2014 8:48 pm

I think this is a complete list:
  • "A" is for automatic projection creation
    "D" is for Database Designer
    "L" if creation was delayed meaning the table was created, but waiting for data to be inserted
    "P" is the projection was created with a projection clause
At least it is in my DB:

Code: Select all

dbadmin=> select distinct createtype from vs_projections order by 1;
 createtype 
------------
 A
 D
 L
 P
(4 rows)
Jim Knicely

Image

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

dbmsuser7
Intermediate
Intermediate
Posts: 71
Joined: Tue Jan 28, 2014 3:03 am

Re: createtype(A)

Post by dbmsuser7 » Thu Feb 27, 2014 10:14 pm

norbertk wrote,
Can you provide some context around what you're doing or where you're seeing this?

Right; it was in a

Code: Select all

CREATE PROJECTION
statement.

I'm in the middle of migrating our user schemas from a 6-node version 6 cluster to a 4-node version 7 cluster.

I wrote a python script which first dumps out the table definitions using EXPORT_OBJECTS(). After "importing" the definitions on the new cluster by running the SQL output by EXPORT_OBJECTS(), I copy the data over using COPY FROM VERTICA.

For my own schema, it was fine. But when I tested it on another user, the script choked because there were projections that actually listed nodes (instead of using ALL NODES), and coming from a 6-node cluster, there were references to nodes 5 and 6 which failed on the new 4-node cluster.

Anyway, it was while trying to figure out those projection definitions that I became puzzled by "createtype(A)".

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: createtype(A)

Post by NorbertKrupa » Mon Mar 03, 2014 8:27 pm

Just to add more to this. It's recommended to not use create types when writing manual projections as the type is utilized in storing the projection.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Database Development”