Hi,
I created a projection but have forgotten what it was and stupidly never wrote it down. Now I'd like to document it and use it in a another identical database. I can't seem to find anything in the manual that reveals the structure of a projection, does anyone know if this is possible?
Allan
Finding projection definitions
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Finding projection definitions
Hi,
Are you looking for the SQL to re-create the projection for a table?
If so, you can use the EXPORT_OBJECTS function to generate the SQL for all projections for a table.
For example:
The double single quote will tell the function to generate the script on screen.
Are you looking for the SQL to re-create the projection for a table?
If so, you can use the EXPORT_OBJECTS function to generate the SQL for all projections for a table.
For example:
Code: Select all
SELECT export_objects('', <<table_name>>);
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: Finding projection definitions
Fantastic, thanks very much!
Allan
Allan
-
- Newbie
- Posts: 3
- Joined: Fri May 31, 2013 9:24 am
Re: Finding projection definitions
When I use this function on one of my views I get the following error:
Any idea what could be the cause of this?
The database object does exist.
Code: Select all
db_mgr=> select export_objects('', 'db_calc.agg_constituent_beta');
INTERNAL 5445: VIAssert(origSize > viewsToDependents.size()) failed
DETAIL: /scratch_a/release/vbuild/vertica/DBDesigner/ExportCatalog.cpp: 977
HINT: Please report this error to Vertica; try restating your query
The database object does exist.
-
- Beginner
- Posts: 42
- Joined: Thu Apr 19, 2012 9:03 pm
Re: Finding projection definitions
Hello #me_theonlyone,
I had a problem like yours in the past (at least I think it was similar ).
Can you run a select statement against the view without error?
I wonder if somehow the view definition is different on your nodes?
You can use this query to get the view definition. Is it the same on each node?
select view_definition from views where tables_schema = 'db_calc' and table_name = 'agg_constituent_beta';
I had to drop the view and recreate it using the view definition from the above query.
Good luck!
I had a problem like yours in the past (at least I think it was similar ).
Can you run a select statement against the view without error?
I wonder if somehow the view definition is different on your nodes?
You can use this query to get the view definition. Is it the same on each node?
select view_definition from views where tables_schema = 'db_calc' and table_name = 'agg_constituent_beta';
I had to drop the view and recreate it using the view definition from the above query.
Good luck!
Re: Finding projection definitions
This is a known issue in certain versions of Vertica with the export_objects command.
We use export_catalog to get the entire catalog and then grab the view/projection/table definitions from there.
The views table is only useful if the view definition is less than 2000 characters, we have very large views so querying the views table doesn't always help.
We use export_catalog to get the entire catalog and then grab the view/projection/table definitions from there.
The views table is only useful if the view definition is less than 2000 characters, we have very large views so querying the views table doesn't always help.
Andrew Bacon - HP ATP - Vertica Big Data Solutions V1 Certified
Re: Finding projection definitions
The following sql should get you the projection definition:
select * from projection_columns where projection_name='name_of_your_projection' order by sort_position, column_position;
To find the projection_name:
select * from projections where anchor_table_name='table_for_which_projection_was_created';
select * from projection_columns where projection_name='name_of_your_projection' order by sort_position, column_position;
To find the projection_name:
select * from projections where anchor_table_name='table_for_which_projection_was_created';