How To dump data from Oracle to Vertica

Question

I want to transfer data from Oracle to Vertica and I want to know what is best way to do it? I s there a tool for it?

I tried to dump the Oracle data and load it to Vertica using 'Copy Direct' command but I fount lots of problems specially in the data and timestamps columns.

Answer 1

Try to export the data into csv format, and recreate the table in Vertica using the DDL from Oracle. Check the types as they differ from Oracle to Vertica.

Example: assuming the following table structure on Oracle:

CREATE TABLE PEOPLE
(
    ID NUMBER (10),
    NAME VARCHAR2 (50),
    ADDRESS VARCHAR2 (250),
    PRIMARY KEY (ID)
)

Assuming a compatible table structure on Vertica:

CREATE TABLE PEOPLE
(
    ID BIGINT NOT NULL    /*ORACLE datatype: NUMBER(10, 0)  */,
    NAME VARCHAR(50)      /*ORACLE datatype: VARCHAR2(50)   */,
    ADDRESS VARCHAR(250)  /*ORACLE datatype: VARCHAR2(250)  */,
    PRIMARY KEY (ID)
);

Load the table content using the copy command in Vertica:

copy "table name" from 'full csv file path'
    delimiter ',' null as '' exceptions 'full error file path';

Done!