Page 1 of 1
Finding projection definitions
Posted: Thu May 16, 2013 8:39 pm
by allan
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
Re: Finding projection definitions
Posted: Thu May 16, 2013 8:47 pm
by JimKnicely
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:
Code: Select all
SELECT export_objects('', <<table_name>>);
The double single quote will tell the function to generate the script on screen.
Re: Finding projection definitions
Posted: Thu May 16, 2013 9:32 pm
by allan
Fantastic, thanks very much!
Allan
Re: Finding projection definitions
Posted: Mon Jun 24, 2013 5:17 pm
by me_theonlyone
When I use this function on one of my views I get the following error:
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
Any idea what could be the cause of this?
The database object does exist.
Re: Finding projection definitions
Posted: Mon Jun 24, 2013 6:22 pm
by billykopecki
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!
Re: Finding projection definitions
Posted: Tue Jun 25, 2013 9:40 am
by bacona
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.
Re: Finding projection definitions
Posted: Thu Aug 29, 2013 3:28 pm
by sfali16
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';