Remote (not in same subnet) COPY FROM VERTICA does not work (it hangs)

How do I get COPY FROM VERTICA to work when my 2 Vertica databases are on different subnets?


Comments

  • I know that I need to create a subnet or a network interface to get this to work.  I would rather use something like COPY FROM VERTICA than outputting to CSVs and re-COPYing them to another Vertica cluster.  However, when the Vertica databases (we are trying to automate a "test" environment) are on different subnets, COPY FROM VERTICA does not work.

    https://my.vertica.com/docs/6.1.x/HTML/index.htm#18308.htm

  • Hi David,

    You are correct in saying that you need to create a subnet OR network interfaces to get this to work. This is specifically the case when the private IPs of both clusters can't communicate with each other. We must define the IPs or Subnet which the copy can use to open connection with the other Vertica cluster. 

    Have you been able to successfully make the change as outlined in the doc to get COPY FROM VERTICA working?

    - Mitch
  • You may do this on source and destination cluster for each of the nodes:

    create network interface <interface_name> on v_xxxxxx_node0001 with '<IP>';
    alter node v_xxxx_node0001 export on <interface_name>;

    check the interfaces:
    select * from vs_network_interfaces;



  • Thanks for replying so quickly!  Much appreciated.

    In your example,

    > create network interface <interface_name> on v_xxxxxx_node0001 with '<IP>';

    For v_xxxxxx_node0001, xxxxxx represents the Vertica database node I'm trying to connect to?

    Also, is it necessary to create network interfaces on both Vertica instances in order to get COPY FROM VERTICA to work for Vertica databases on different subnets?

  • For v_xxxxxx_node0001, xxxxxx represents the Vertica database node I'm trying to connect to? - Yes, for each node you do this, this is node name,
    you can get the node names
    select * from nodes;
    yes: do this for each node on both the clusters
  • I am still very confused, as I get "No valid address found for [production_node_export_address] on this node".  Can you please give an example of how to get COPY FROM VERTICA to work?  Specifically, I am interested in what to do on "the cluster where the data resides" and what to do on "the cluster where I am trying to copy data to".

    We have one network which we use for testing purposes, and another network which we use for production machines, and the production network cannot talk to the internal network, but the internal network can access the external network?
  • What is the output of the below command from the two clusters
    select * from nodes;

    select * from vs_network_interfaces;

    ------
    To create interface and set export address as below on all the nodes ( source and destination)

    create network <interface_name> on <node_name>  with '<public_ip_node>';
    ALTER NODE <node_name> EXPORT ON "<interface_name>";


  • "The cluster where the data resides":
    --------------------------------------------------------------------------

    dbadmin=> select * from vs_network_interfaces;
            oid        |    name     | schema |       node        |    address   
    -------------------+-------------+--------+-------------------+---------------
     49539596333614568 | DFExporter1 |      0 | 45035996273704972 | 10.90.128.164
     49539596333616022 | DFExporter2 |      0 | 45035996273719654 | 10.90.128.165
     49539596333616024 | DFExporter3 |      0 | 45035996273719658 | 10.90.128.166
     49539596333616026 | DFExporter4 |      0 | 45035996792604504 | 10.90.128.58
     49539596333616028 | DFExporter5 |      0 | 45035996805467832 | 10.90.128.50
    (5 rows)

    dbadmin=> select * from nodes;
          node_name       |      node_id      | node_state | node_address | export_address |                     catalog_path                     | is_ephemeral
    ----------------------+-------------------+------------+--------------+----------------+------------------------------------------------------+--------------
     v_analytics_node0001 | 45035996273704972 | UP         | 10.90.141.11 | 10.90.128.164  | /data/analytics/v_analytics_node0001_catalog/Catalog | f
     v_analytics_node0002 | 45035996273719654 | UP         | 10.90.141.12 | 10.90.128.165  | /data/analytics/v_analytics_node0002_catalog/Catalog | f
     v_analytics_node0003 | 45035996273719658 | UP         | 10.90.141.13 | 10.90.128.166  | /data/analytics/v_analytics_node0003_catalog/Catalog | f
     v_analytics_node0004 | 45035996792604504 | UP         | 10.90.141.14 | 10.90.128.58   | /data/analytics/v_analytics_node0004_catalog/Catalog | f
     v_analytics_node0005 | 45035996805467832 | UP         | 10.90.141.15 | 10.90.128.50   | /data/analytics/v_analytics_node0005_catalog/Catalog | f
    (5 rows)



    "The cluster where I am trying to copy data to"
    --------------------------------------------------------------------------

    dbadmin=> select * from vs_network_interfaces;
     oid | name | schema | node | address
    -----+------+--------+------+---------
    (0 rows)


    dbadmin=> select * from nodes;
          node_name       |      node_id      | node_state | node_address | export_address |                     catalog_path                     | is_ephemeral
    ----------------------+-------------------+------------+--------------+----------------+------------------------------------------------------+--------------
     v_analytics_node0001 | 45035996273704980 | UP         | 192.168.7.56 | 192.168.7.56   | /data/analytics/v_analytics_node0001_catalog/Catalog | f
     v_analytics_node0002 | 45035996273719008 | UP         | 192.168.7.58 | 192.168.7.58   | /data/analytics/v_analytics_node0002_catalog/Catalog | f
    (2 rows)





  • For the source:
    node_address = 10.90.141.x  and export_address =10.90.128.x

    For the destination:
    192.168.7.x

    which are public address? and which are private?

    Are 10.90.141.x or 10.90.128.x accessible by 192.168.7.x?

  • > which are public address? and which are private?

    The 10.90.141.* and 192.168.* addresses are private.  The 10.90.128.* addresses are public. 

    > Are 10.90.141.x or 10.90.128.x accessible by 192.168.7.x?

    yes





  • Hi David,

     

    I am facing the same issue.

    Even after creating the new network interfaces, the problem did not solve.

    Did you find any solution for that?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file