VERTICA EXPORT COMMAND SHOWS 0 Rows Exported . Why ?
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.
Comments
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.
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.
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?
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
(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)
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