Hi,
I am having problems getting the delta import working. Full import works fine.
I am using current version of solr (6.1). I have been looking at this pretty
much all day and can't find what I am not doing correctly... I did try the
Using query attribute for both full and delta import and that worked, but as
soon I ran it for a full import via clean=true my queries performance went very
bad (oracle execution plain must of went bonkers). Anyways, I would appreciate
any help.
Thanks....
Here is my dataimportHandler config:
[hubadm@emcappd43:solr-6.1.0]$ cat ./server/solr/dmtec1/conf/db-data-config.xml
<dataConfig>
<script><![CDATA[
function DiscardNotComplete(row) {
var log =
java.util.logging.Logger.getLogger("transformer");
var LEVEL = java.util.logging.Level.INFO;
if (row.get('DOC_STATE') == 'Complete') {
return row;
}
else {
log.log(LEVEL, "DiscardNotComplete - " +
row.get('ID') + ": Not Complete - removed")
}
return;
}
]]></script>
<dataSource name="oracleDMTECQA"
driver="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@//xxxxxxx:1521/tdm01" user="xxxxx" password="xxxxxxxx"/>
<dataSource type="BinURLDataSource" name="tika"/>
<document>
<entity name="documents" dataSource="oracleDMTECQA" pk="ID"
logLevel="debug" transformer="script:DiscardNotComplete"
query="SELECT 'Current' VAULT, bo.LXOID ID, bt.MXNAME
TYPE, bo.LXNAME NAME, bo.LXREV REV, des.lxdesc TITLE_DESC, st.MXNAME DOC_STATE,
sl1.lxval LOCAL_DOC_TYPE,
sl2.lxval TIN, sl3.lxval ALT_DOC_NAME,
sl4.lxval DOC_STATUS, sl5.lxval ALT_DOC_TITLE, sl6.lxval ACCESS_GROUP,
sl7.lxval CLASS, sl8.lxval PROJECT_NUMBER,
sl9.lxval POINT_OF_CONTACT, sl10.lxval
LOCATION, sl11.lxval VENDOR_ID, avdt.lxval DOC_AVAILABLE_DATE,
res.restriction_detail EXPORT_RESTRICTIONS, usr.mxname OWNER_ID, usr.mxfullname
OWNER_NAME
FROM lxbo_current bo
INNER JOIN MXIDENT i
INNER JOIN MXLATTICE v ON
i.MXLATTICE = v.MXOID AND v.MXNAME = 'Current'
ON bo.LXOID = i.LXOID
INNER JOIN mxbustype bt
INNER JOIN mxdrvinfo mdi
INNER JOIN mxbustype
btp ON btp.mxoid = mdi.mxtype AND btp.mxname = 'DOCUMENTATION'
ON bt.mxoid = mdi.mxobj AND
mxkind = 1
ON bo.lxtype = bt.mxoid
LEFT JOIN lxdesc_current des ON
bo.lxoid = des.lxoid AND des.LXKIND = 0
LEFT JOIN lxstring_current sl1
INNER JOIN mxattrtype attr1 ON
sl1.lxtype = attr1.mxoid AND attr1.mxname LIKE 'Local%'
ON bo.lxoid = sl1.lxoid
LEFT JOIN lxstring_current sl2
INNER JOIN mxattrtype attr2 ON
sl2.lxtype = attr2.mxoid AND attr2.mxname = 'Tyco Internal Number'
ON bo.lxoid = sl2.lxoid
LEFT JOIN lxstring_current sl3
INNER JOIN mxattrtype attr3 ON
sl3.lxtype = attr3.mxoid AND attr3.mxname = 'Alternate Document Name'
ON bo.lxoid = sl3.lxoid
INNER JOIN lxstring_current sl4
INNER JOIN mxattrtype attr4 ON
sl4.lxtype = attr4.mxoid AND attr4.mxname = 'Documentation Status'
ON bo.lxoid = sl4.lxoid
LEFT JOIN lxstring_current sl5
INNER JOIN mxattrtype attr5 ON
sl5.lxtype = attr5.mxoid AND attr5.mxname = 'Alternate Document Title'
ON bo.lxoid = sl5.lxoid
LEFT JOIN lxstring_current sl6
INNER JOIN mxattrtype attr6 ON
sl6.lxtype = attr6.mxoid AND attr6.mxname = 'Access Group'
ON bo.lxoid = sl6.lxoid
LEFT JOIN lxstring_current sl7
INNER JOIN mxattrtype attr7 ON
sl7.lxtype = attr7.mxoid AND attr7.mxname = 'Class'
ON bo.lxoid = sl7.lxoid
LEFT JOIN lxstring_current sl8
INNER JOIN mxattrtype attr8 ON
sl8.lxtype = attr8.mxoid AND attr8.mxname = 'Project Number'
ON bo.lxoid = sl8.lxoid
LEFT JOIN lxstring_current sl9
INNER JOIN mxattrtype attr9 ON
sl9.lxtype = attr9.mxoid AND attr9.mxname = 'Point of Contact'
ON bo.lxoid = sl9.lxoid
LEFT JOIN lxstring_current sl10
INNER JOIN mxattrtype attr10 ON
sl10.lxtype = attr10.mxoid AND attr10.mxname = 'Location'
ON bo.lxoid = sl10.lxoid
LEFT JOIN lxstring_current sl11
INNER JOIN mxattrtype attr11 ON
sl11.lxtype = attr11.mxoid AND attr11.mxname = 'Vendor Identifier'
ON bo.lxoid = sl11.lxoid
INNER JOIN lxdate_current avdt
INNER JOIN mxattrtype avdtt ON
avdt.LXTYPE = avdtt.MXOID AND avdtt.mxname = 'Documentation Available Date'
ON bo.lxoid = avdt.LXOID
LEFT JOIN ST_RESTRICTED_OBJECTS res
INNER JOIN mxbustype rbt on
res.OBJ_TYPE = rbt.MXNAME
ON bo.LXTYPE = rbt.MXOID AND bo.LXNAME
= res.OBJ_NAME AND bo.LXREV = res.OBJ_REV
INNER JOIN mxuser usr ON bo.LXOWNER =
usr.MXOID
INNER JOIN mxstatereq st ON bo.lxstate
= st.mxoid"
deltaImportQuery="SELECT 'Current' VAULT, bo.LXOID ID,
bt.MXNAME TYPE, bo.LXNAME NAME, bo.LXREV REV, des.lxdesc TITLE_DESC, st.MXNAME
DOC_STATE, sl1.lxval LOCAL_DOC_TYPE,
sl2.lxval TIN, sl3.lxval ALT_DOC_NAME,
sl4.lxval DOC_STATUS, sl5.lxval ALT_DOC_TITLE, sl6.lxval ACCESS_GROUP,
sl7.lxval CLASS, sl8.lxval PROJECT_NUMBER,
sl9.lxval POINT_OF_CONTACT, sl10.lxval
LOCATION, sl11.lxval VENDOR_ID, avdt.lxval DOC_AVAILABLE_DATE,
res.restriction_detail EXPORT_RESTRICTIONS, usr.mxname OWNER_ID, usr.mxfullname
OWNER_NAME
FROM lxbo_current bo
INNER JOIN MXIDENT i
INNER JOIN MXLATTICE v ON
i.MXLATTICE = v.MXOID AND v.MXNAME = 'Current'
ON bo.LXOID = i.LXOID
INNER JOIN mxbustype bt
INNER JOIN mxdrvinfo mdi
INNER JOIN mxbustype
btp ON btp.mxoid = mdi.mxtype AND btp.mxname = 'DOCUMENTATION'
ON bt.mxoid = mdi.mxobj AND
mxkind = 1
ON bo.lxtype = bt.mxoid
LEFT JOIN lxdesc_current des ON
bo.lxoid = des.lxoid AND des.LXKIND = 0
LEFT JOIN lxstring_current sl1
INNER JOIN mxattrtype attr1 ON
sl1.lxtype = attr1.mxoid AND attr1.mxname LIKE 'Local%'
ON bo.lxoid = sl1.lxoid
LEFT JOIN lxstring_current sl2
INNER JOIN mxattrtype attr2 ON
sl2.lxtype = attr2.mxoid AND attr2.mxname = 'Tyco Internal Number'
ON bo.lxoid = sl2.lxoid
LEFT JOIN lxstring_current sl3
INNER JOIN mxattrtype attr3 ON
sl3.lxtype = attr3.mxoid AND attr3.mxname = 'Alternate Document Name'
ON bo.lxoid = sl3.lxoid
INNER JOIN lxstring_current sl4
INNER JOIN mxattrtype attr4 ON
sl4.lxtype = attr4.mxoid AND attr4.mxname = 'Documentation Status'
ON bo.lxoid = sl4.lxoid
LEFT JOIN lxstring_current sl5
INNER JOIN mxattrtype attr5 ON
sl5.lxtype = attr5.mxoid AND attr5.mxname = 'Alternate Document Title'
ON bo.lxoid = sl5.lxoid
LEFT JOIN lxstring_current sl6
INNER JOIN mxattrtype attr6 ON
sl6.lxtype = attr6.mxoid AND attr6.mxname = 'Access Group'
ON bo.lxoid = sl6.lxoid
LEFT JOIN lxstring_current sl7
INNER JOIN mxattrtype attr7 ON
sl7.lxtype = attr7.mxoid AND attr7.mxname = 'Class'
ON bo.lxoid = sl7.lxoid
LEFT JOIN lxstring_current sl8
INNER JOIN mxattrtype attr8 ON
sl8.lxtype = attr8.mxoid AND attr8.mxname = 'Project Number'
ON bo.lxoid = sl8.lxoid
LEFT JOIN lxstring_current sl9
INNER JOIN mxattrtype attr9 ON
sl9.lxtype = attr9.mxoid AND attr9.mxname = 'Point of Contact'
ON bo.lxoid = sl9.lxoid
LEFT JOIN lxstring_current sl10
INNER JOIN mxattrtype attr10 ON
sl10.lxtype = attr10.mxoid AND attr10.mxname = 'Location'
ON bo.lxoid = sl10.lxoid
LEFT JOIN lxstring_current sl11
INNER JOIN mxattrtype attr11 ON
sl11.lxtype = attr11.mxoid AND attr11.mxname = 'Vendor Identifier'
ON bo.lxoid = sl11.lxoid
INNER JOIN lxdate_current avdt
INNER JOIN mxattrtype avdtt ON
avdt.LXTYPE = avdtt.MXOID AND avdtt.mxname = 'Documentation Available Date'
ON bo.lxoid = avdt.LXOID
LEFT JOIN ST_RESTRICTED_OBJECTS res
INNER JOIN mxbustype rbt on
res.OBJ_TYPE = rbt.MXNAME
ON bo.LXTYPE = rbt.MXOID AND bo.LXNAME
= res.OBJ_NAME AND bo.LXREV = res.OBJ_REV
INNER JOIN mxuser usr ON bo.LXOWNER =
usr.MXOID
INNER JOIN mxstatereq st ON bo.lxstate
= st.mxoid
WHERE bo.lxoid = '${dataimporter.delta.id}'"
deltaQuery="SELECT bo.LXOID id FROM lxbo_current bo
WHERE bo.LXMODDATE > TO_DATE('${dataimporter.last_index_time}', 'yyyy-mm-dd
HH24:MI:SS')">
<field column="ID" name="ID" />
<field column="TYPE" name="TYPE" />
<field column="NAME" name="NAME" />
<field column="REV" name="REV" />
<field column="TITLE_DESC" name="TITLE_DESC" />
<field column="DOC_STATE" name="DOC_STATE" />
<field column="LOCAL_DOC_TYPE" name="LOCAL_DOC_TYPE" />
<field column="TIN" name="TIN" />
<field column="ALT_DOC_NAME" name="ALT_DOC_NAME" />
<field column="DOC_STATUS" name="DOC_STATUS" />
<field column="ALT_DOC_TITLE" name="ALT_DOC_TITLE" />
<field column="ACCESS_GROUP" name="ACCESS_GROUP" />
<field column="CLASS" name="CLASS" />
<field column="PROJECT_NUMBER" name="PROJECT_NUMBER" />
<field column="POINT_OF_CONTACT" name="POINT_OF_CONTACT" />
<field column="LOCATION" name="LOCATION" />
<field column="VENDOR_ID" name="VENDOR_ID" />
<field column="DOC_AVAILABLE_DATE" name="DOC_AVAILABLE_DATE" />
<field column="EXPORT_RESTRICTIONS" name="EXPORT_RESTRICTIONS" />
<field column="OWNER_ID" name="OWNER_ID" />
<field column="OWNER_NAME" name="OWNER_NAME" />
<entity name="viewables" dataSource="oracleDMTECQA"
logLevel="debug" transformer="com.te.tika.ViewContentsTransformer"
query="SELECT bo.LXOID ID, bt.MXNAME TYPE,
bo.LXNAME NAME, bo.LXREV REV, fl1.LXALIAS STORE_FILE, stor.MXPATH STORE_PATH,
bov.lxname VName, bov.lxrev VREV, slv1.lxval VFType, slv2.lxval
VIEWABLE_LANGUAGE, fl1.lxpath VFileName, slv3.lxval VIEWABLE_ORIGFILENAME
FROM lxbo_current bo
INNER JOIN mxbustype bt
INNER JOIN mxdrvinfo mdi
INNER JOIN
mxbustype btp ON btp.mxoid = mdi.mxtype AND btp.mxname = 'DOCUMENTATION'
ON bt.mxoid = mdi.mxobj
AND mxkind = 1
ON bo.lxtype = bt.mxoid
LEFT JOIN lxro_current ro
INNER JOIN mxreltype
rtv ON ro.lxtype = rtv.mxoid AND rtv.mxname = 'Represented By'
INNER JOIN lxbo_current
bov
INNER JOIN
mxbustype btv ON bov.lxtype = btv.mxoid AND btv.mxname = 'Enterprise Viewable'
INNER JOIN
lxstring_current slv1
INNER
JOIN mxattrtype attrv1 ON slv1.lxtype = attrv1.mxoid AND attrv1.mxname = 'File
Type'
ON bov.lxoid =
slv1.lxoid
INNER JOIN
lxstring_current slv2
INNER
JOIN mxattrtype attrv2 ON slv2.lxtype = attrv2.mxoid AND attrv2.mxname =
'Language'
ON bov.lxoid =
slv2.lxoid
INNER JOIN
lxstring_current slv3
INNER
JOIN mxattrtype attrv3 ON slv3.lxtype = attrv3.mxoid AND attrv3.mxname =
'Original Filename'
ON bov.lxoid =
slv3.lxoid
INNER JOIN
lxfile_current fl1
INNER
JOIN MXSTORE stor ON fl1.LXSTORE = stor.MXOID
ON bov.lxoid =
fl1.lxbo
ON ro.lxtoid = bov.lxoid
ON bo.lxoid = ro.lxfromid
WHERE bo.LXOID = '${documents.ID}'">
<field name="VIEWABLE_NAME" column="VNAME" />
<field name="VIEWABLE_REV" column="VREV" />
<field name="VIEWABLE_TYPE" column="VFTYPE" />
<field name="VIEWABLE_LANGUAGE" column="VLANG"
/>
<field name="VIEWABLE_ORIGFILENAME"
column="VORIGFILENAME" />
<field name="VIEWABLE_URL" column="VURL" />
<field name="VIEWABLE_DATA"
column="VIEWABLE_URL" />
</entity>
<entity name="relParts" dataSource="oracleDMTECQA"
logLevel="debug"
query="SELECT prt.PART_KEY_ID RELATED_ID,
prt.tyco_electronics_corp_part_nbr RELATED_NAME
FROM lxbo_current bo
LEFT JOIN lxro_gts_master rom
INNER JOIN
lxforeign_gts_master fo
INNER JOIN
st_tyco_electronics_parts prt ON fo.LXFOREIGNID = to_char(prt.PART_KEY_ID) AND
fo.LXTYPE = '1'
ON rom.LXFROMID =
fo.LXOID
ON bo.LXOID = rom.LXTOID
WHERE bo.LXOID = '${documents.ID}'">
<field column="RELATED_ID" name="RELATED_ID" />
<field column="RELATED_NAME"
name="RELATED_NAME" />
</entity>
<!--
<entity name="vcontents"
processor="TikaEntityProcessor" url="${documents.VURL}" dataSource="tika"
format="text">
<field name="VIEWABLE_DATA" column="text"/>
</entity>
-->
</entity>
</document>
</dataConfig>
Here is the log output:
2016-08-31 19:45:42.641 INFO (qtp403424356-68) [ x:dmtec1]
o.a.s.h.d.DataImporter Loading DIH Configuration: db-data-config.xml
2016-08-31 19:45:42.648 INFO (qtp403424356-68) [ x:dmtec1]
o.a.s.h.d.DataImporter Data Configuration loaded successfully
2016-08-31 19:45:42.649 INFO (qtp403424356-68) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/dataimport
params={indent=on&wt=json&command=reload-config&_=1472648332418} status=0
QTime=9
2016-08-31 19:45:42.680 INFO (qtp403424356-77) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/admin/mbeans
params={cat=QUERYHANDLER&wt=json&_=1472648332418} status=0 QTime=1
2016-08-31 19:45:42.695 INFO (qtp403424356-84) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/dataimport
params={indent=on&wt=json&command=show-config&_=1472648332418} status=0 QTime=1
2016-08-31 19:45:42.696 INFO (qtp403424356-49) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/dataimport
params={indent=on&wt=json&command=status&_=1472648332418} status=0 QTime=0
2016-08-31 19:45:48.550 INFO (qtp403424356-68) [ x:dmtec1]
o.a.s.h.d.DataImporter Loading DIH Configuration: db-data-config.xml
2016-08-31 19:45:48.558 INFO (qtp403424356-68) [ x:dmtec1]
o.a.s.h.d.DataImporter Data Configuration loaded successfully
2016-08-31 19:45:48.560 INFO (qtp403424356-68) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/dataimport
params={core=dmtec1&optimize=false&indent=on&commit=true&clean=false&wt=json&command=delta-import&_=1472648332418&verbose=false}
status=0 QTime=10
2016-08-31 19:45:48.560 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DataImporter
Starting Delta Import
2016-08-31 19:45:48.574 INFO (Thread-39) [ x:dmtec1]
o.a.s.h.d.SimplePropertiesWriter Read dataimport.properties
2016-08-31 19:45:48.576 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Starting delta collection.
2016-08-31 19:45:48.577 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Running ModifiedRowKey() for Entity: viewables
2016-08-31 19:45:48.577 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed ModifiedRowKey for Entity: viewables rows obtained : 0
2016-08-31 19:45:48.577 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed DeletedRowKey for Entity: viewables rows obtained : 0
2016-08-31 19:45:48.577 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed parentDeltaQuery for Entity: viewables
2016-08-31 19:45:48.577 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Running ModifiedRowKey() for Entity: relParts
2016-08-31 19:45:48.578 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed ModifiedRowKey for Entity: relParts rows obtained : 0
2016-08-31 19:45:48.578 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed DeletedRowKey for Entity: relParts rows obtained : 0
2016-08-31 19:45:48.578 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed parentDeltaQuery for Entity: relParts
2016-08-31 19:45:48.578 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Running ModifiedRowKey() for Entity: documents
2016-08-31 19:45:48.578 INFO (Thread-39) [ x:dmtec1]
o.a.s.h.d.JdbcDataSource Creating a connection for entity documents with URL:
jdbc:oracle:thin:@//tdmdbq01:1521/tdm01
2016-08-31 19:45:48.585 INFO (qtp403424356-74) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/dataimport
params={indent=on&wt=json&command=status&_=1472648332418} status=0 QTime=1
2016-08-31 19:45:48.631 INFO (Thread-39) [ x:dmtec1]
o.a.s.h.d.JdbcDataSource Time taken for getConnection(): 52
2016-08-31 19:45:48.632 DEBUG (Thread-39) [ x:dmtec1]
o.a.s.h.d.JdbcDataSource Executing SQL: SELECT bo.LXOID id FROM lxbo_current bo
WHERE bo.LXMODDATE > TO_DATE('2016-08-28 19:28:07', 'yyyy-mm-dd HH24:MI:SS')
2016-08-31 19:45:48.646 TRACE (Thread-39) [ x:dmtec1]
o.a.s.h.d.JdbcDataSource Time taken for sql :13
2016-08-31 19:45:48.742 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed ModifiedRowKey for Entity: documents rows obtained : 0
2016-08-31 19:45:48.742 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed DeletedRowKey for Entity: documents rows obtained : 0
2016-08-31 19:45:48.743 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Completed parentDeltaQuery for Entity: documents
2016-08-31 19:45:48.744 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Delta Import completed successfully
2016-08-31 19:45:48.744 INFO (Thread-39) [ x:dmtec1] o.a.s.h.d.DocBuilder
Time taken = 0:0:0.169
2016-08-31 19:45:48.744 INFO (Thread-39) [ x:dmtec1]
o.a.s.u.p.LogUpdateProcessorFactory [dmtec1] webapp=/solr path=/dataimport
params={core=dmtec1&optimize=false&indent=on&commit=true&clean=false&wt=json&command=delta-import&_=1472648332418&verbose=false}
status=0 QTime=10{} 0 195
2016-08-31 19:45:50.576 INFO (qtp403424356-83) [ x:dmtec1] o.a.s.c.S.Request
[dmtec1] webapp=/solr path=/dataimport
params={indent=on&wt=json&command=status&_=1472648332418} status=0 QTime=0
That exact same query executed in dbeaver SQL client:
SELECT bo.LXOID id FROM lxbo_current bo WHERE bo.LXMODDATE >
TO_DATE('2016-08-28 19:28:07', 'yyyy-mm-dd HH24:MI:SS')
1477893927
353805427
562839910
627759986
425917955
1325791266
-785363068
-2076031884
:
:
SELECT COUNT(bo.LXOID) FROM lxbo_current bo WHERE bo.LXMODDATE >
TO_DATE('2016-08-28 19:28:07', 'yyyy-mm-dd HH24:MI:SS')
250
