Reverse Vertica environment?

usao

Is there a tool/utility which can reverse out a Vertica database?
I need to create a TEST environment based on our DEV environment, but I dont think that we have properly recorded all the deployed DDL, Users, Permissions, Schemas etc...
Looking for something which can reverse out the current DEV environment to be used as a template for deploying to a TEST environment.

ScottL

You mean like copy cluster? https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BackupRestore/CopyingTheDatabaseToAnotherCluster.htm

bose4life

Please what is the analog of a sql function Reverse on vertica.

Vertica_Curtis

Probably should be in a separate thread, but I have to ask - what is the use case of such a function?
And no, Vertica doesn't have this function. You'd have to write it in a UDx.

bose4life

Is from a sql syntax that need to be executed in vertica to return the range values.
Example of sql expression:
'3_'+IIF(LEN(StoreNo)-PATINDEX('%[0-7]%', StoreNo)-PATINDEX('%[0-7]%, REVERSE(StoreNo))>0, (SUBSTRING(StoreNo, PATINDEX('%[0-7]%', StoreNo),LEN(StoreNo)-PATINDEX('%[0-7]%', StoreNo)-PATINDEX('%[0-7]%', REVERSE(StoreNo)))), NULL)

Jim_Knicely

@bose4life - Can you give a couple input and output examples?

bose4life

Input example : StoreNo S1987256300
Output 3_19872
StoreNo S176540926
Output 3_17654

Jim_Knicely

Are you sure that's the output expected? If so, why not just do this?

dbadmin=> SELECT store_no, '3' || '_' || SUBSTR(store_no, 2, 5) output FROM store;
  store_no   | output
-------------+---------
 S1987256300 | 3_19872
 S176540926  | 3_17654
(2 rows)

bose4life

More Input output example : STU1997756600
Output 3_19977
StoreNo HP186920926
Output 3_18692

Jim_Knicely

dbadmin=> SELECT store_no, '3' || '_' || LEFT(regexp_replace(store_no, '[^0-9]', ''), 5) FROM store;
   store_no    | ?column?
---------------+----------
 S1987256300   | 3_19872
 S176540926    | 3_17654
 STU1997756600 | 3_19977
 HP186920926   | 3_18692
(4 rows)

bose4life

Solution worked efficiently. Thank you I appreciate.