How to migrate User Defined Functions from Enterprise to EON
Alim
Vertica Employee
HI Team,
I want to migrate User Defined Functions from Enterprise to EON Vertica Database , so please let me know if anyone have steps .
Thanks,
Alim Shaikh
0
Best Answers
-
Sankarmn
Community Edition User ✭✭
I think you can do with EXPORT_ functions (COPY FROM VERTICA then EXPORT TO VERTICA), else to recreate as direct migration between modes doesn't exists.
Hopefully we can expect direct migration in the later versions.
5 -
Jim_Knicely
- Select Field - Administrator
You can compare the data in the USER_FUNCTIONS system table from each DB.
Quick example:
On the EON mode DB:
dbadmin=> CREATE TABLE remote_user_functions AS SELECT * FROM user_functions LIMIT 0; CREATE TABLE
Copy the data from the Enterprise mode DB:
dbadmin=> \! vsql -h enterprise_db_ip_address -U dbadmin -w password -Atc "SELECT * FROM user_functions;" | vsql -c "COPY remote_user_functions FROM STDIN DIRECT;"
Look for the differences:
dbadmin=> SELECT schema_name, function_name FROM user_functions MINUS SELECT schema_name, function_name FROM remote_user_functions LIMIT 1; schema_name | function_name -------------+--------------- public | ThreeGrams (1 row)
5
Answers
Actually, you'd want the EXPORT_OBJECTS function.
Example:
dbadmin=> \df randomdate List of functions procedure_name | procedure_return_type | procedure_argument_types ----------------+-----------------------+---------------------------- randomdate | Integer | c Integer randomdate | Timestamp | d1 Timestamp, d2 Timestamp (2 rows) dbadmin=> SELECT export_objects('', 'randomdate', false); export_objects ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE FUNCTION public.randomdate(d1 Timestamp, d2 Timestamp) RETURN timestamp AS BEGIN RETURN (to_timestamptz(((date_part('epoch', d1) + randomint((floor((date_part('epoch', d2) - date_part('epoch', d1))))::int)))::float))::timestamp; END; CREATE FUNCTION public.randomdate(c Integer) RETURN int AS BEGIN RETURN 1; END; (1 row)See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/EXPORT_OBJECTS.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/CopyExportData/ExportingObjects.htm
@Sankarmn - There is a migration tool coming real soon to Vertica! So the journey from Enterprise mode to Eon mode will be much , much easier!
@Jim_Knicely, Good to know.
Thanks for sharing
Considering the overheads and time consuming process, for smaller data movement, is database link also being planned sooner?
Also let me and the community know what other features are coming soon.
Hi Jim and Sankarmn,
Thanks for the update ... !!!
yes i did with EXPORT_OBJECTS function and manually run the DDL on EON .
And what are the checks we need to check and compare for UDF migrations ?
Thanks ,
Alim Shaikh