Is there any best practices to follow from any database to Vertica?

hsaxena20

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?

mosheg

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

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

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

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