Skip to main content

How to manipulate data which is on another Vertica cluster ?

  • September 22, 2017
  • 5 replies
  • 20 views
Isabelle_Vela
Forum|alt.badge.img+1

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

    5 replies

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • September 22, 2017

    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.

    Isabelle_Vela
    Forum|alt.badge.img+1
    • Isabelle_Vela
    • Author
    • Participating Frequently
    • September 26, 2017

    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.

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • September 26, 2017

    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.

    Isabelle_Vela
    Forum|alt.badge.img+1
    • Isabelle_Vela
    • Author
    • Participating Frequently
    • September 27, 2017

    Thank you, this is clear.

    Ganesh_2
    • Ganesh_2
    • Participating Frequently
    • September 27, 2017

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement