Vertica Query returns zero records and when re-run returns some records
Vertica query returns zero records and when re-run same query, it returns some records.
This behavior is random but frequent.
Background on query :
Below query is executed using vsql command and run as unix korn job
table1, table2, table3 and table4 are populated using another unix kron job.
primary_id
,b
,w
,c FROM (
SELECT
primary_id
,b
,w
,c
,r
FROM table1
JOIN table2
JOIN table3
) sub WHERE r=1
AND primary_id IN (SELECT DISTINCT primary_id FROM table4 )
GROUP BY primary_id ,b, w,c
Any pointers are appreciated, Thanks in advance
This behavior is random but frequent.
Background on query :
Below query is executed using vsql command and run as unix korn job
table1, table2, table3 and table4 are populated using another unix kron job.
SELECT
primary_id
,b
,w
,c FROM (
SELECT
primary_id
,b
,w
,c
,r
FROM table1
JOIN table2
JOIN table3
) sub WHERE r=1
AND primary_id IN (SELECT DISTINCT primary_id FROM table4 )
GROUP BY primary_id ,b, w,c
Any pointers are appreciated, Thanks in advance
0
Comments
Looks like query we see is not actual query, but simplified version.
Provided information is very limited and it is hard to pinpoint issue.
Here are 3 things I have on my mind:
1. Cron job, which is loading table1, table2 and table3 does it in separate transactions. When query run with load cron job in progress joins can return unexpected results.
2. If your join conditions have TIMESTAMP field in them check time on every node in the cluster. If time on nodes out of sync you may get very unusual results
3. you not mentioning table4. data changes in table4 will greatly impact results of this query.
Thank you for your response !
Sorry; I could not paste original query as it might have some restrictions.
so i converted it to generic.
point #1 : I am same opinion too. i am varifying schedules of all tables with this query.
will keep everyone posted.
point#2 : i need to figureout; how to do it
point#3 : edited my original question: table 4 is also updated as corn job at different schedule.
On point #2:
To check time you need to ssh in to every node on the cluster and check server system time.
Vertica recommends network time protocol (NTP) daemon running on all nodes.
If NTP daemon running and functioning normally then you should be fine.
sure: will check. Thanks
sorry for delayed response.
Suggested solution did not work.
For shortterm, we are re-running failed jobs.
For longterm, we are planning to optimize long running jobs and reduce ingestion load by tweaking job schedules.
Thanks for your help guys