We have our data in a datawarehouse where any changes are made by adding
another row and marking the previous row as old my the means of a timestamp.
So, for instance, the name of the table is Employee and has the following
structure.
Name|Address|eventtimestamp|endtimestamp
John|NYC| 2010-09-21 12:10:11.164638|null
- Primary key is Name
- When you update, say the address, the database entries become
Name|Address|eventtimestamp|endtimestamp
John|NYC| 2010-09-21 13:10:11.164638|2010-09-21 13:10:11.164638
John|CHG| 2010-09-21 13:10:11.164638|null
Here is my db config -
<dataConfig>
<dataSource jndiName="java:jdbc/Tickstore" type="JdbcDataSource"
holdability="HOLD_CURSORS_OVER_COMMIT" />
<document>
<entity name="item" pk="symbol"
query="SELECT name, address from employee where endtimestamp is
null"
deletedPkQuery="SELECT DISTINCT name FROM employee
eventtimestamp > '${dataimporter.last_index_time}' "
deltaQuery= "SELECT DISTINCT name FROM employee
eventtimestamp > '${dataimporter.last_index_time}' "
deltaImportQuery="SELECT name, address from employee where endtimestamp is
null and name='${deltaimport}'" >
</entity>
</document>
</dataConfig>
When I do delta import, I end up with two rows for the same employee. Any ideas
or experiences regarding implementation of delta import are welcome too.
Thanks
Papiya
________________________________
Pink OTC Markets Inc. provides the leading inter-dealer quotation and trading
system in the over-the-counter (OTC) securities market. We create innovative
technology and data solutions to efficiently connect market participants,
improve price discovery, increase issuer disclosure, and better inform
investors. Our marketplace, comprised of the issuer-listed OTCQX and
broker-quoted Pink Sheets, is the third largest U.S. equity trading venue for
company shares.
This document contains confidential information of Pink OTC Markets and is only
intended for the recipient. Do not copy, reproduce (electronically or
otherwise), or disclose without the prior written consent of Pink OTC Markets.
If you receive this message in error, please destroy all copies in your
possession (electronically or otherwise) and contact the sender above.
