Skip to main content

VERTICA EXPORT COMMAND SHOWS 0 Rows Exported . Why ?

  • December 9, 2016
  • 5 replies
  • 19 views
UJJWAL_RANA
Forum|alt.badge.img+2

hi

I am trying to export some records from one node (A) to another node (B).

 

First of assume like the current sys date is 12-09-2016

 

A Node

select datediff(day,'2016-12-03', date(sysdate)); 6

 

select distinct batch_load_date,count(6) from abc_daily group by batch_load_date order by batch_load_date desc

 

 

batch_load_date                count
2016-12-09 00:54:43    18565916
2016-12-08 01:18:02    18521512
2016-12-07 00:49:55    18501251
2016-12-06 00:52:43    18509135
2016-12-05 00:52:49    18517930
2016-12-04 00:52:34    18527800

 

select * from studio.ABC_DAILY where date_val between date(sysdate-7) and date(sysdate) order by date_val desc limit 20

Output shows as well

 

 

NOW WHAT I DID I TRIED to export some records of information from node A table to B node table using export command. So i connected to vsql at NODE B and triggered following query :-

 

studio > connect to VERTICA TestPulse user studio PASSWORD 'xxxx' on '100.170.12.324', 5433;

connected
studio >connect to VERTICA TESTDB user studio PASSWORD 'xxxxxx' on '100.170.12.335', 5433;

connected

BUT THE PROBLEM HERE IS WHEN I TRIGGER FOLLOWING QUERY IT SHOWS 0 ROWS. 

studio > EXPORT TO VERTICA ABCDb.ABC_DAILY_TEST AS select * from studio.ABC_DAILY where batch_load_date between date(sysdate-7) and date(sysdate);
 Rows Exported
---------------
             0
(1 row)

 

 

But if you look at above mentioned NODE A this query does shows an output at node A but while triggering the same query for export at node B it shows rows exported is ZERO WHY ?

 

select * from studio.ABC_DAILY where batch_load_date between date(sysdate-7) and date(sysdate);

 

I wonder why its showing 0 rows exported on output whereas everything is correct. Any help will be highly appreciated.

    5 replies

    Sharon_Cutter
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • December 9, 2016

    Why do you have two CONNECT statements?  Typically you would only connect to the one remote database that you are exporting data to.  Then EXPORT from the database to which you are connected to the remote database.

     

     

    UJJWAL_RANA
    Forum|alt.badge.img+2
    • UJJWAL_RANA
    • Author
    • Participating Frequently
    • December 9, 2016

    Why do you have two CONNECT statements?  Typically you would only connect to the one remote database that you are exporting data to.  Then EXPORT from the database to which you are connected to the remote database.

     

    Answer : Even while connecting to remote db where exporting need to performed. This does not work. I already tried.

    Sharon_Cutter
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • December 9, 2016

    So you have three databases:

     

    - The one you are connected to with vsql or your sql tool

    - TESTDB

    - TestPulse

     

    Does the table ABCDb.ABC_DAILY_TEST exist on all three databases?

     

    Try modifying the EXPORT to refer to the table as TESTDB.ABCDb.ABC_DAILY_TEST or TestPulse.ABCDb.ABC_DAILY_TEST.

     

    Is there a difference in the table definition between the databases?

     

    What if you export only a subset of the columns?

     

     

    UJJWAL_RANA
    Forum|alt.badge.img+2
    • UJJWAL_RANA
    • Author
    • Participating Frequently
    • December 11, 2016

    So you have three databases:

    Answer: I have two database not three. Sorry my bad. There were some mistaken on the information that i have provided you earlier. So only two database and only two nodes available

     

    - TESTDB (Node A) ( DB Name is TESTPULSE )

    - TestPulse (Node B) (DB Name is TESTDB )

     

    - The one you are connected to with vsql or your sql tool
    Answer : I am connected to NODE B for vsql login. So from NODE B i.e. from vsql. I trigger following 3 query and the third one shows 0 Rows wheres this query select * from ABC_DAILY where batch_load_date between date(sysdate-7) and date(sysdate); has a records at node A but while using export i.e. below third query , the output shows 0 Rows WHY ?  :-

     

    FIRST

    studio > connect to VERTICA TestPulse user studio PASSWORD 'xxxx' on '100.170.12.324', 5433;

    connected

     

    SECOND
    studio >connect to VERTICA TESTDB user studio PASSWORD 'xxxxxx' on '100.170.12.335', 5433;

    connected

     

    THIRD

    studio > EXPORT TO VERTICA TESTDB.ABC_DAILY_TEST AS select * from ABC_DAILY where batch_load_date between date(sysdate-7) and date(sysdate);


     Rows Exported
    ---------------
                 0
    (1 row)

     

     

    Does the table ABCDb.ABC_DAILY_TEST exist on all three databases?

     

    Answer :  Like mentioned above, there are ONLY two nodes i.e. two database ONLY. ABC_DAILY table exist on node A and the sam replica of that table  named ABC_DAILY_TEST is available at NODE B.

     

    ABC_DAILY TABLE available on node A , DB name TestPulse

     

    ABC_DAILY_TEST TABLE available on node B , DB name TestDB

     

     

    Try modifying the EXPORT to refer to the table as TESTDB.ABCDb.ABC_DAILY_TEST or TestPulse.ABCDb.ABC_DAILY_TEST.

     

    Answer : I tried it but i could not understand why it showing 0 rows selected while triggering export query

     

    Is there a difference in the table definition between the databases?

     

    Answer : All the column is exactly the same. I have created a replica / duplicate table  of ABC_DAILY table from node a to node b with a name ABC_TABLE_TEST. So definition is correct and if there were any mistaken then it would have shown other error.

     

    What if you export only a subset of the columns?

    Answer : Haven't tried . Need to try but my requirement is i need to copy all according to SYSDATE - (VALUE GIVEN)

     

    Also i see following statement mentioned under HP VERTICA documentation and comparing with this i gave above mentioned query for export but i could not find the reason why its showing 0 ROWS .


    URL :--> https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/EXPORTTOVERTICA.htm

     

    The following statement demonstrates exporting a portion of a table using a simple SELECT statement.

    => EXPORT TO VERTICA testdb.ma_customers AS SELECT customer_key, customer_name, annual_income
    -> FROM customer_dimension WHERE customer_state = 'MA';
    Rows Exported
    ---------------
              3429
    (1 row)

     

    ckotsidimos
    Forum|alt.badge.img+1
    • ckotsidimos
    • Participating Frequently
    • December 16, 2016

    Try this pls

     

    studio >connect to VERTICA TESTDB user studio PASSWORD 'xxxxxx' on '100.170.12.335', 5433;

    connected

     

     

    studio > EXPORT TO VERTICA TESTDB.ABC_DAILY_TEST AS select * from ABC_DAILY where batch_load_date between date(sysdate-7) and date(sysdate);

     

    Due to the fact that you are connected to Node 2 (Pulse) you do not need to reconnect to it

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement