createtype(A)
Moderator: NorbertKrupa
createtype(A)
Anyone know what this one ("A") signifies?
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: createtype(A)
Can you provide some context around what you're doing or where you're seeing this?
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: createtype(A)
You must be referring to the projection type. And I believe "A" signifies "A"utomatic projection creation...Anyone know what this one ("A") signifies?
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)
https://my.vertica.com/docs/7.0.x/PDF/H ... Manual.pdf
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: createtype(A)
I think it would be valuable to put together a list of createtypes for future reference. Thoughts?
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: createtype(A)
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
Code: Select all
dbadmin=> select distinct createtype from vs_projections order by 1;
createtype
------------
A
D
L
P
(4 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: createtype(A)
norbertk wrote,
Right; it was in a
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)".
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
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)".
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: createtype(A)
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.