How to migrate User Defined Functions from Enterprise to EON

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.

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

@Jim_Knicely said:
@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!

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

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)