The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Is there any best practices to follow from any database to Vertica?
hsaxena20
Community Edition User
We need to migrate data from one database (Oracle) to Vertica. Data size approx 5TB and it also has stored procedures. Do we have any step wise step activity? I did earlier but need to know is there any suggestions?
0
Answers
Oracle stored procedures can be converted to Vertica external procedures. Oracle code in terms of UDF's can be translated to Vertica UDF's. For Oracle PL/SQL you will have to rewrite the code as Vertica UDx and there is also an interesting project for PL/SQL interpreter which stores and run PL/SQL code in Vertica database here:
https://github.com/dingqiangliu/vertica-plsql
To build the new schema in Vertica, see the following table to compare the behavior of Oracle data types to Vertica data types.
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Compatibility/Oracle-bill/DataTypeMappingsBetweenVerticaAndOracle.htm
To load the data itself, first create CSV files from Oracle data and use Vertica COPY command to load the data into the database. See also this: https://forum.vertica.com/discussion/232819/export-data-from-bd-oracle-to-vertica-in-less-time
The attached draft script example extracts DDLs from Oracle and converts them into Vertica DDLs
It extracts DDLs only for the schema of the connected user
First rename the .txt file and then run the script from sqlplus, output spools into schema4vertica.sql
Note some of the script limitations:
Converts all oracle special datatypes into VARCHAR(256), so please update output script.
Doesn't support extraction of foreign keys across different schemas
Supports only relational table objects.
The script is just a draft example, for production use your own script.