Vertica "Copy From" command

To import data from a database we can use the copy from command as described here -make sure that the object already exists(the DDL) on the database, if not get the DDL of it with the function and create on the new database, to do so use the following commands:

vsql$:>select export_objects('','obj name');

After this run the output on to the target database.

Test the " CONNECT " command:

-the connect command enables you to connect to another Vertica database that will subsequently enable export and copy from commands. Syntax of the CONNECT command is :

vsql$:>CONNECT TO VERTICA database USER username PASSWORD 'password' ON 'host',port;

Where the options are :

database-the target database name;

user-username used in to connect;

password-the password of the user;

host-hostname or ipaddress of one of the nodes;

port-the port on which the Vertica service is running on;

You cannot open more then one connection to at the same time, and the connection will be available until you use the DISCONECT command.

Test the " DISCONECT " command:

Closes a previously-established connection to another Vertica database. Syntax of the DISCONECT command is :

DISCONNECT database name

Now back to our COPY FROM command, and see how this works. Syntax for the COPY FROM command:

vsql$:>CONNECT TO VERTICA target_db USER dbadmin PASSWORD 'dbadmin' ON 'ip address',port number;
       CONNECT
       COPY source_db.schema.tbl_name FROM VERTICA target_db.schema.tbl_name DIRECT;
       DISCONNECT ;

To be able to run this command you need to have the following permisions:

SELECT privileges on the source table.

USAGE privilege on source table schema.

INSERT privileges for the destination table in target database.

USAGE privilege on destination table schema.

Example showing how to copy several columns from a table in the source database into a table in the local database.

=> CONNECT TO VERTICA target_db USER admin_db PASSWORD 'blabla' ON 'Host01.doamain',5433;
CONNECT
=> COPY table_ACE (name, gender) FROM VERTICA
-> target_db.backup_ACE (name,gender);
 Rows Loaded
-------------
      1000000
(1 row)
=> DISCONNECT target_db;
DISCONNECT