Finding projection definitions

Moderator: NorbertKrupa

Post Reply
allan
Newbie
Newbie
Posts: 9
Joined: Wed Sep 05, 2012 4:13 pm

Finding projection definitions

Post by allan » Thu May 16, 2013 8:39 pm

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

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

Re: Finding projection definitions

Post by JimKnicely » Thu May 16, 2013 8:47 pm

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.
Jim Knicely

Image

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

allan
Newbie
Newbie
Posts: 9
Joined: Wed Sep 05, 2012 4:13 pm

Re: Finding projection definitions

Post by allan » Thu May 16, 2013 9:32 pm

Fantastic, thanks very much!

Allan

me_theonlyone
Newbie
Newbie
Posts: 3
Joined: Fri May 31, 2013 9:24 am

Re: Finding projection definitions

Post by me_theonlyone » Mon Jun 24, 2013 5:17 pm

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.

billykopecki
Beginner
Beginner
Posts: 42
Joined: Thu Apr 19, 2012 9:03 pm

Re: Finding projection definitions

Post by billykopecki » Mon Jun 24, 2013 6:22 pm

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!

bacona
Newbie
Newbie
Posts: 11
Joined: Tue Jun 19, 2012 4:29 pm

Re: Finding projection definitions

Post by bacona » Tue Jun 25, 2013 9:40 am

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.
Andrew Bacon - HP ATP - Vertica Big Data Solutions V1 Certified

sfali16
Newbie
Newbie
Posts: 1
Joined: Mon Aug 26, 2013 5:37 pm

Re: Finding projection definitions

Post by sfali16 » Thu Aug 29, 2013 3:28 pm

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';

Post Reply

Return to “Vertica Performance Tuning”