CODEWITHSUNDEEP
greenplumhawq
NEO
NEO

Reputation: 399

Pivotal Greenplum - Incremental Data issue

When i try to capture Incremental Load in One SQL transaction. Update is not working. Basically, It Keeps on Executing for infinite time for 90k rows.

Input SQL transaction

BEGIN;
INSERT INTO IncrementalLoad_Dest.dbo.tblDest
(ColID, ColA, ColB, ColC)
SELECT s.ColID, s.ColA, s.ColB, s.ColC 
FROM IncrementalLoad_Source.dbo.tblSource s
LEFT JOIN IncrementalLoad_Dest.dbo.tblDest d ON d.ColID = s.ColID
WHERE d.ColID IS NULL ;
UPDATE d
SET
d.ColA = s.ColA
,d.ColB = s.ColB
,d.ColC = s.ColC
FROM IncrementalLoad_Dest.dbo.tblDest d
INNER JOIN IncrementalLoad_Source.dbo.tblSource s ON s.ColID = d.ColID
WHERE (
(d.ColA != s.ColA)
OR (d.ColB != s.ColB) 
OR (d.ColC != s.ColC)
);
END;

Update statement is not executing from psql utility. Why it keeps on executing not even throwing the error.

Also, tried checking Active lock on target file. No lock exists.

Please consider column in double quote around each column and SQL transaction written in greenplum SQL syntax.

Any help on it would be much appreciated .

Upvotes: 0

Views: 281

Answers (1)

Jon Roberts
Jon Roberts

Reputation: 2106

Try this:

UPDATE IncrementalLoad_Dest_dbo.tblDest d
SET d.ColA = s.ColA,
    d.ColB = s.ColB,
    d.ColC = s.ColC
FROM IncrementalLoad_Source_dbo.tblSource s 
WHERE s.ColID = d.ColID
and ((d.ColA != s.ColA) OR (d.ColB != s.ColB) OR (d.ColC != s.ColC));

You basically have to change your SQL Server syntax to be more ANSI compliant so it works in Greenplum.

Also, you may want to avoid UPDATE statements. You'll definitely want to VACUUM before you execute an UPDATE. You'll probably want to make the tables Append Optimized (appendonly=true) when you create the table.

Upvotes: 1

Related Questions