Loading data Vertica to Vertica

mpedroso

Hello guys

I need to load data from a SELECT from Vertica to VERTICE itself.

If I use an ETL tool to capture the data and later save the data in Vertica I will face a network process that I do not want. The alternative to this was to create an "INSERT INTO TABLE SELECT" script, however, so the load information in the "load_streams" table is not written. Is there any way to write data from a SELECT from within Vertica and that the loader generates information in the "load_streams" table?

Something like:

COPY FROM VERTICA SELECT

scottpedersoli

You can use COPY with a view, I use it to limit the data we copy to another cluster to one year. The view contains the logic. You can put your select in a view and then use it in the COPY. I have also used the COPY on the same database to build a table rather than doing "insert into table select" (was faster in our case)
CONNECT info here;
COPY TargetSchema.TargetTable from VERTICA database.schema.viewname DIRECT STREAM NAME 'StreamName';
DISCONNECT database;

Jim_Knicely

Did you connect to the remote cluster?

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CONNECT.htm

The "db‑spec" of the CONNECT TO VERTICA command becomes the databaseName in your COPY FROM VERTICA command.

FYI ... You can query the DATABASE_CONNECTIONS system table to see what database you are connected to:

dbadmin=> CONNECT TO VERTICA test_db USER dbadmin PASSWORD 'password' ON 'XXX.XXX.XXX.XXX',5433;
CONNECT

dbadmin=> SELECT database FROM database_connections;
 database
----------
 test_db
(1 row)

Jim_Knicely

Are you trying to COPY data from a remote Vertica DB? If so, you have to connect to the remote Vertica DB first, then you run the COPY FROM VERTICA command!

And you can run it from dBeaver:

If you are just copy DATA in the same database, then use an INSERT:

INSERT /*+ DIRECT */ INTO schemaDestination.tableDestination SELECT * FROM schemaName.viewName;

mpedroso

Show!
Thank you very much.

mpedroso

@scottpedersoli

I am trying to run the following command as per its orientation:

COPY schemaDestination.tableDestination FROM VERTICA databaseName.schemaName.viewName DIRECT;

Except that it returns the following error:

That is, it says that my database does not exist. Would you help me?

mpedroso

I'm not connected using vsql or vertica admin tools. Can I run this command using DBeaver, without having to use "connect" and don't set my user and password?

scottpedersoli

Yes, you can run in dbbeaver. You have to use CONNECT to enable data import (using the COPY FROM VERTICA statement) or export (using the EXPORT statement). Have to do this it this way because you indicated you wanted to use COPY to see in load streams.