How to manipulate data which is on another Vertica cluster ?
Hello
We are developing a tool to automate and secure data transfer between different DBs (ex : DEV-->TEST). The tool works as follows
- it has to run on the cluster of the source DB
- it runs EXPORT command, to be able to transfer either a full object or the result of a query
We would like to include operations on **target tables **: optional truncate before copy, analyse_histograms after copy
But, even though we issue a successful remote connection to the target DB for the transfer, we cannot execute other commands, they are not allowed :
Ex:
connect to vertica DEVDB user dbadmin password '*****' on 'HOST',5433;
SELECT * FROM DEVDB.COMMON.T1_TABLE;
11:22:54 [CONNECT - 0 rows, 0.064 secs] Command processed. No rows were affected
** 11:22:54 [SELECT - 0 rows, 0.058 secs] [Code: 2983, SQL State: 0A000] [Vertica]VJDBC ERROR: Database "GBADEV" does not exist**
Should we do a specific configuration on Server side? Is Vertica handling the notion known as dblink in Oracle ? Or is the cross-DB connection completely forbidden in Vertica's world because the clusters are separated ?
Thank you for any help on this topic
Isabelle
Comments
Hi,
The "Exporting Data to Another Vertica Database" feature is not like an Oracle DB link. You can only issue the EXPORT TO VERTICA (Source to Target) or COPY FROM VERTICA (Target to Source) commands.
Looks like you want the COPY FROM VERTICA option:
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPYFROMVERTICA.htm
Example:
COPY local_table FROM VERTICA COMMON.T1_TABLE DIRECT;Note, the reason you got the Database "GBADEV" does not exist** error is because you refernced the table like this: DEVDB.COMMON.T1_TABLE. Just use COMMON.T1_TABLE.
Thank you for this explanation about COPY, that I already knew about. We don't use COPY, we use EXPORT, to be able to manage filtered data transfers.
My question was more to know if it was possible to connect to a remote cluster to perform DB actions, before or after the export/transferwhich is the topic for which we need an automated reliable tool in my organisation.
You can only perform the COPY FROM VERTICA and EXPORT TO VERTICA commands after establishing a connection to a remote database via the CONNECT TO VERTICA command. You cannot issue SQL commands like SELECT, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, ANALYZE_HISTOGRAMS, etc.
Thank you, this is clear.
One other option you can consider is vbr.py; You can copy full table or schema from source to target cluster. we have some good experience with this solution for sometime.