Andy, you rock! You've made my day. it works after adding the
deltaImportQuery with the WHERE id =... into the entity section.
BTW, would you have a pointer when to use the variable dataimporter vs dih
'${dataimporter.delta.id}? I see some other online places use dih, when
doing dih.last_index_time
Thanks, again.
On Fri, Dec 1, 2023 at 9:44 AM Andy Webb <[email protected]> wrote:
> hi Vince,
>
> The deltaQuery is used to fetch the IDs of any changed items - DIH then
> runs the deltaImportQuery to fetch the document for each of those IDs,
> using a WHERE clause of the form "WHERE id = '${dataimporter.delta.id}'.
> (You can make it use compound PKs if necessary - and you might want a
> deletedPkQuery too to identify docs to remove.)
>
> hope that helps!
> Andy
>
> On Fri, 1 Dec 2023 at 10:41, Vince McMahon <[email protected]>
> wrote:
>
> > I need help, please.
> >
> > I keep looking at the Configuration, the full import works using the
> > entity "query". I have tested the SELECT statement from deltaQuery in a
> > Teradata terminal and that works.
> >
> > The following teradata query returns 18 rows from the Teradata terminal.
> > SELECT id, updated_at FROM Name1.<cowboyTable> WHERE update_at >=
> > cast(*'2023-12-01
> > 09:46:10'* AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDbHH:MI:SS') - INTERVAL '8'
> > DAY;
> >
> > The differences I can think of between the full import "query" and the
> > "deltaQuery" are those characters after the WHERE update_at ...
> >
> > So, if there is any attribute Solr delta import is looking for, it would
> > had been the variable dih.last_index_time or remaining teradata
> > keywords/syntax is confusing the Solr parser.
> >
> > the dih.last_index_time is from the file dataimport.properties, which
> has:
> > #Fri Dec 01 09:46:15 UTC 2023
> > last_index_time=2023-12-01 09\:46\:10
> > <cowboysEntity>.last_index_time=2023-12-01 09\:46\:10
> >
> >
> > Configuration (db-data-config.xml)
> > <dataConfig>
> > <dataSource type="JdbcDataSource"
> > driver="com.teradata.jdbc.TeraDriver"
> > url=
> > "jdbc:teradata://<teradataServer>/database=Name1,tmode=TERA,charset=UTF8"
> > user="<user>"
> > password="<pwd>"/>
> > <document>
> > <entity name ="<cowboysEntity>"
> > pk ="id"
> > query ="SELECT id, updated_at FROM
> > Name1.<cowboyTable> WHERE updated_at between '2023-01-01 00:00:00' and
> > '2023-01-01 00:30:00' "
> > deltaQuery ="SELECT id, updated_at FROM
> > Name1.<cowboyTable> WHERE updated_at *>= cast('${dih.last_index_time}' AS
> > TIMESTAMP(0) FORMAT 'YYYY-MM-DDbHH:MI:SS') - INTERVAL '8' DAY *">
> > <field column="id" name="id"/>
> > <field column="updated_at" name="updated_at"/>
> > </entity>
> > </document>
> > </dataConfig>
> >
> > On Fri, Dec 1, 2023 at 4:57 AM Vince McMahon <
> > [email protected]>
> > wrote:
> >
> > > Hi,
> > >
> > > Could someone please help me with the what Solr is looking in
> deltaQuery
> > > of Delta Import ?
> > >
> > > I have got 18 rows fetched from the database, but none of the row is
> > > processed as document.
> > >
> > > There are the "WARN" In the solr.log, but, what attribute is not
> > > specified? It seems important because the Raw Debug-Response shows 18
> > rows
> > > fetched and are empty/no processed documents. Thanks.
> > >
> > > "verbose-output": [
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > ...
> > >
> > >
> > > solr.log
> > > 2023-12-01 09:24:38.325 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DataImporter Loading DIH Configuration: db-data-config.xml
> > > 2023-12-01 09:24:38.328 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DataImporter Data Configuration loaded successfully
> > > 2023-12-01 09:24:38.328 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DataImporter Starting Delta Import
> > > 2023-12-01 09:24:38.335 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SimplePropertiesWriter Read dataimport.properties
> > > 2023-12-01 09:24:38.336 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Starting delta collection.
> > > 2023-12-01 09:24:38.338 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Running ModifiedRowKey() for Entity:
> <cowboysEntity>
> > > 2023-12-01 09:24:38.339 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.JdbcDataSource Creating a connection for entity
> <cowboysEntity>
> > > with URL: jdbc:teradata://<teradataServer>/database
> > > =Name1,tmode=TERA,charset=UTF8
> > > 2023-12-01 09:24:39.101 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.JdbcDataSource Time taken for getConnection(): 762
> > > 2023-12-01 09:24:39.695 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Completed ModifiedRowKey for Entity:
> <cowboysEntity>
> > > rows obtained: 18
> > > 2023-12-01 09:24:39.695 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Completed DeletedRowKey for Entity:
> <cowboysEntity>
> > > rows obtained : 0
> > > 2023-12-01 09:24:39.695 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Completed parentDeltaQuery for Entity:
> > <cowboysEntity>
> > > 2023-12-01 09:24:39.696 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:39.940 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:40.138 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:40.337 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:40.532 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:40.728 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:40.924 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:41.121 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:41.322 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:41.524 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:41.724 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:41.928 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:42.129 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:42.350 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:42.551 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:42.758 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:42.955 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:43.155 WARN (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.SqlEntityProcessor 'deltaImportQuery' attribute is not
> > > specified for entity : <cowboysEntity>
> > > 2023-12-01 09:24:43.361 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Delta Import completed successfully
> > > 2023-12-01 09:24:43.361 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.h.d.DocBuilder Time taken = 0:0:5.26
> > > 2023-12-01 09:24:43.361 INFO (qtp548482954-31) [ x:p6]
> > > o.a.s.u.p.LogUpdateProcessorFactory [p6] webapp=/solr path=/dataimport
> > > params={core=p6&debug=true&indent=on&commit=true&name
> > > =dataimport&clean=false&wt=json&command=delta-import&_=1701422662358&
> > > verbose=true}{} 0 5036
> > > 2023-12-01 09:24:43.605 INFO (qtp548482954-30) [ x:p6]
> > > o.a.s.c.S.Request [p6] webapp=/solr path=/dataimport params={indent=on
> > > &wt=json&command=status&_=1701422662358} status=0 QTime=0
> > >
> > >
> > >
> > > Configuration (db-data-config.xml)
> > > <dataConfig>
> > > <dataSource type="JdbcDataSource"
> > > driver="com.teradata.jdbc.TeraDriver"
> > > url=
> > >
> "jdbc:teradata://<teradataServer>/database=Name1,tmode=TERA,charset=UTF8"
> > > user="<user>"
> > > password="<pwd>"/>
> > > <document>
> > > <entity name ="<cowboysEntity>"
> > > pk ="id"
> > > query ="SELECT id, updated_at FROM
> > > Name1.<cowboyTable> WHERE updated_at between '2023-01-01 00:00:00' and
> > > '2023-01-01 00:30:00' "
> > > deltaQuery ="SELECT id, updated_at FROM
> > > Name1.<cowboyTable> WHERE updated_at >= cast('${dih.last_index_time}'
> AS
> > > TIMESTAMP(0) FORMAT 'YYYY-MM-DDbHH:MI:SS') - INTERVAL '8' DAY ">
> > > <field column="id" name="id"/>
> > > <field column="updated_at" name="updated_at"/>
> > > </entity>
> > > </document>
> > > </dataConfig>
> > >
> > >
> > > In manage-schema file I have
> > > <field name="id" type="string" indexed="true" stored="true"
> required=
> > > "true" multiValued="false" />
> > > <field name="update_at" type="string" indexed="true" stored="true"
> > > multiValued="false"/>
> > >
> > > Raw Debug-Response
> > > {
> > > "responseHeader": {
> > > "status": 0,
> > > "QTime": 5266
> > > },
> > > "initArgs": [
> > > "defaults",
> > > [
> > > "config",
> > > "db-data-config.xml"
> > > ]
> > > ],
> > > "command": "delta-import",
> > > "mode": "debug",
> > > "documents": [],
> > > "verbose-output": [
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ],
> > > "entity:<cowboysEntity>",
> > > [
> > > "document#1",
> > > []
> > > ]
> > > ],
> > > "status": "idle",
> > > "importResponse": "",
> > > "statusMessages": {
> > > "Total Requests made to DataSource": "19",
> > > "Total Rows Fetched": "18",
> > > "Total Documents Processed": "0",
> > > "Total Documents Skipped": "0",
> > > "Delta Dump started": "2023-12-01 09:24:38",
> > > "Identifying Delta": "2023-12-01 09:24:38",
> > > "Deltas Obtained": "2023-12-01 09:24:39",
> > > "Building documents": "2023-12-01 09:24:39",
> > > "Total Changed Documents": "18",
> > > "Time taken": "0:0:5.26"
> > > }
> > > }
> > >
> > >
> > >
> > >
> > >
> >
>
