How to migrate User Defined Functions from Enterprise to EON

Alim

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

Sankarmn

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.

Jim_Knicely

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)

Jim_Knicely

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

Jim_Knicely

@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!

Sankarmn

@Jim_Knicely, Good to know.

Thanks for sharing

Sankarmn

@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.

Alim

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