0

I had simple problem with etl on staging table that was answered here:

Process staging table by getting unique rows only

Now after apllying solution I spotted that will be unusable in production enviroment due to amount of data (around 1 mln rows daily) and that will be longering execution of etl statement every day by 1 minute at least on my dba infrastructure, then it will be unusable after few months.

Also staging table can't be locked in any time for INSERT operations.

How to do that etl in incremential fashion? like:

  1. open transaction
  2. take rows from staging
  3. process that new rows (eliminate rows that exists in final table, not agaist staging table, like in non-incremential solution answered in thread linked above)
  4. insert new rows into final table
  5. delete rows from staging table taken at point 2.
  6. commit transaction

Problematic is only 3 point, can anyone help?

  • You're not actually creating the final table every time are you? – Zane Feb 12 '15 at 18:12
  • @zane: actually I did, why I'm need this incremential solution to not do that and process (insert to final) only new rows every operation – Svisstack Feb 12 '15 at 18:15
2

I would timestamp your staging loads and hash your unique attributes in a persisted computed column so as to allow indexing tuning. It's a time / space trade-off, but space is much, much cheaper than time these days. The ratio will be in your favor as well, executing dozens of times faster in the long run at the expense of about three times the storage space for the staging table. In any incremental load scenario, you'd also be in a position to happily truncate your staging tables after every warehouse load, so from my point of view, it's win / win.

The staging table ( from your previous question, with some tweaks ) would be set up like this:

CREATE TABLE dbo.StagingTable   
( 
    [DateInserted]  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [HashBytes]     AS HASHBYTES( 'SHA1', 
                        CONVERT( VARBINARY( 4 ), COALESCE( [A], 0 ) ) 
                        + CONVERT( VARBINARY( 4 ), COALESCE( [B], 0 ) ) 
                        + CONVERT( VARBINARY( 32 ), COALESCE( [C], '' ) )
                        ) PERSISTED,
    [KEY]           INTEGER NOT NULL,
    [A]             INTEGER,
    [B]             INTEGER,
    [C]             VARCHAR( 32 )
);

ALTER TABLE dbo.StagingTable
ADD CONSTRAINT PK__StagingTable
    PRIMARY KEY CLUSTERED ( [KEY] );

CREATE NONCLUSTERED INDEX IX__StagingTable__DateInserted__HashBytes
    ON  dbo.StagingTable ( [DateInserted], [HashBytes] )
INCLUDE ( [A], [B], [C] );
GO

The "work" here is figuring out a decent expression for running through your hashing algorithm ( SHA1 is chosen here because it's probably good enough, but if you honestly believe you're making your way into exobyte territory in the near future, feel free to use SHA2 instead ). Simply converting all of the necessary attributes into a VARCHAR column is problematic, since ( 13, 1, 'A' ) and ( 1, 13, 'A' ) could result in the same final string, so binary concatenation has been chosen for this implementation.

Since [A] and [B] are INTEGER types, converting them to VARBINARY( 4 ) is sufficient to represent them. If they were BIGINT, we'd have used VARBINARY( 8 ). The added [C] column is a somewhat more obvious example. The built-in DATALENGTH function can help assist you in determining suitable values for binary concatenation scenarios.

An index is added ( despite the general rule to not index your staging tables since they slow down inserts and increase your storage footprint ), largely to facilitate maintaining historical loads. As your confidence grows in your solution, you may consider removing the indexes, favoring the truncation method mentioned earlier.

For the warehouse ( or fact ) table, I would set it up similar to the following:

CREATE TABLE dbo.WarehouseTable
(
    [DateInserted]  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [DateStaged]    DATETIME,
    [HashBytes]     VARBINARY( 32 ),
    [KEY]           INTEGER NOT NULL IDENTITY( 1, 1 ),
    [A]             INTEGER,
    [B]             INTEGER,
    [C]             VARCHAR( 32 )
);

ALTER TABLE dbo.WarehouseTable
ADD CONSTRAINT PK__WarehouseTable
    PRIMARY KEY CLUSTERED ( [KEY] );

CREATE NONCLUSTERED INDEX IX__WarehouseTable__HashBytes__DateStaged
    ON  dbo.WarehouseTable ( [HashBytes], [DateStaged] );
GO

The [DateStaged] column is not entirely necessary in the truncation scenario, but included to again facilitate performing SELECT against a large staging table. The [DateInserted] column is not specifically necessary to the solution, but such a control column is invaluable and I balked when I thought about removing it. I tried, really.

The non-clustered index is added with the [HashBytes] column as the most significant key, since joins against the table from staging will heavily leverage that column. With these two structures in place, you have a loading query culminating as follows:

INSERT INTO dbo.WarehouseTable ( [DateStaged], [HashBytes], [A], [B], [C] )
SELECT  DISTINCT s.[DateInserted], s.[HashBytes], s.[A], s.[B], s.[C]
FROM    dbo.StagingTable s
WHERE   s.DateInserted > COALESCE( (    SELECT  MAX( [DateStaged] )
                                        FROM    dbo.WarehouseTable ), 0 )
    AND NOT EXISTS (    SELECT  [HashBytes]
                        FROM    dbo.WarehouseTable
                        WHERE   [HashBytes] = s.[HashBytes] );
GO

The results of a few mock loads can be seen in this SQL Fiddle. As @RFL points out, there's a good chance you'll want to consider tools designed for this sort of thing as well.

2

OK, let's look at a simple change to the code snippet from the previous post.

-- Create indexes to support the unique merge of the two tables.
CREATE TABLE StagingTable ([Key] INT, A INT, B INT); 
  CREATE INDEX Staging_MergeHelp ON StagingTable (A, B);
CREATE TABLE FinalTable ([Key] INT IDENTITY, A INT, B INT);
  CREATE INDEX Final_MergeHelp ON FinalTable (A, B);
-- 
--  A first run of your job to insert unique values
--
INSERT INTO STAGINGTABLE VALUES( 1,1,1)
INSERT INTO STAGINGTABLE VALUES( 2,1,2)
INSERT INTO STAGINGTABLE VALUES( 3,1,1)
INSERT INTO STAGINGTABLE VALUES( 4,1,2)
INSERT INTO STAGINGTABLE VALUES( 5,2,1)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--
--  First Answer
SELECT * FROM FinalTable
-- 
--  A second run of your job to insert NEW unique values
--
INSERT INTO STAGINGTABLE VALUES( 6,1,1)
INSERT INTO STAGINGTABLE VALUES( 7,1,2)
INSERT INTO STAGINGTABLE VALUES( 8,3,1)
INSERT INTO STAGINGTABLE VALUES( 9,1,2)
INSERT INTO STAGINGTABLE VALUES( 10,2,4)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--   
-- Cumulative answer after including newer values  
SELECT * FROM FinalTable
-- 
DROP TABLE FinalTable
DROP TABLE StagingTable

If this FinalTable gets really big, then there will be some serious overhead. That is why this time I created indexes on both StagingTable and FinalTable for columns A and B. This will help the joins run more efficiently.

However, there are also more powerful tools, such as SSIS which could be a big help as the job gets bigger. The code above is just a simple picture of how to construct the logic.

  • this actually fail when column is nullable and have NULL, then row is not find in final table and added again, but this row exists. did you know what is wrong? – Svisstack Feb 13 '15 at 1:57
  • Because NULL = NULL is false (stackoverflow.com/questions/1075142/…) – Svisstack Feb 13 '15 at 2:05
  • Used: INSERT TO F SELECT FROM S EXCEPT SELECT FROM F. – Svisstack Feb 13 '15 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.