You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by shruti suri <er...@gmail.com> on 2018/07/06 11:53:26 UTC
Delta import not working with Oracle in Solr
HI,
Please help me with delta import form one oracle table into solr. I don't
have any primary key in the table. We need to use composite key using
(LOCAL_MASTER_ID,LOCAL_ID).
<entity name="item" pk="ID" transformer="TemplateTransformer"
query="select * from data.vw_data_ref"
deltaQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) as ID from
data.vw_data_ref where UPDATED_DATE >
to_date('${dataimporter.last_index_time}', 'YYYY-MM-DD HH24:MI:SS')"
deltaImportQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) from
data.vw_data_ref where ID='${item.ID}'">
<field column="ID" template="${item.LOCAL_ID}${item.LOCAL_MASTER_ID}" />
</entity>
-----
Regards
Shruti
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Re: Delta import not working with Oracle in Solr
Posted by Erick Erickson <er...@gmail.com>.
It's actually quite easy to index from a DB to Solr via SolrJ, here's
an example.
https://lucidworks.com/2012/02/14/indexing-with-solrj/
On Tue, Jul 10, 2018 at 6:06 AM, Shawn Heisey <ap...@elyograg.org> wrote:
> On 7/8/2018 9:44 AM, shruti suri wrote:
>>
>> I am using solr-6.1.0 version. This is the response I am getting. But
>> every
>> time I run delta import , it fetches same number of records but didn't
>> commit them.
>>
>> <lst name="statusMessages">
>> <str name="Time Elapsed">0:0:42.255</str>
>> <str name="Total Requests made to DataSource">2</str>
>> <str name="Total Rows Fetched">10208</str>
>> <str name="Total Documents Processed">0</str>
>> <str name="Total Documents Skipped">0</str>
>> <str name="Delta Dump started">2018-07-08 15:37:31</str>
>> <str name="Identifying Delta">2018-07-08 15:37:31</str>
>> <str name="Deltas Obtained">2018-07-08 15:38:13</str>
>> <str name="Building documents">2018-07-08 15:38:13</str>
>> <str name="Total Changed Documents">1</str>
>> </lst>
>
>
> The DIH response won't tell you what went wrong. It just contains numbers.
>
> You're going to need to find and examine the file named solr.log on the
> server side, look for ERROR and/or WARN log messages after the point where
> the import is started.
>
> If the logfile has been rotated, which happens by default in 6.x if it
> reaches 4MB in size, then there may be more parts to the filename beyond
> solr.log. Exactly where this file lives is going to depend on how you
> installed and started Solr. Default locations can vary, from a relative
> path of "server/logs" to an absolute path of "/var/solr/logs/solr.log". If
> defaults have been overridden, then I can't tell you where the file will be
> without additional information from you.
>
> Thanks,
> Shawn
>
Re: Delta import not working with Oracle in Solr
Posted by Shawn Heisey <ap...@elyograg.org>.
On 7/8/2018 9:44 AM, shruti suri wrote:
> I am using solr-6.1.0 version. This is the response I am getting. But every
> time I run delta import , it fetches same number of records but didn't
> commit them.
>
> <lst name="statusMessages">
> <str name="Time Elapsed">0:0:42.255</str>
> <str name="Total Requests made to DataSource">2</str>
> <str name="Total Rows Fetched">10208</str>
> <str name="Total Documents Processed">0</str>
> <str name="Total Documents Skipped">0</str>
> <str name="Delta Dump started">2018-07-08 15:37:31</str>
> <str name="Identifying Delta">2018-07-08 15:37:31</str>
> <str name="Deltas Obtained">2018-07-08 15:38:13</str>
> <str name="Building documents">2018-07-08 15:38:13</str>
> <str name="Total Changed Documents">1</str>
> </lst>
The DIH response won't tell you what went wrong. It just contains numbers.
You're going to need to find and examine the file named solr.log on the
server side, look for ERROR and/or WARN log messages after the point
where the import is started.
If the logfile has been rotated, which happens by default in 6.x if it
reaches 4MB in size, then there may be more parts to the filename beyond
solr.log. Exactly where this file lives is going to depend on how you
installed and started Solr. Default locations can vary, from a relative
path of "server/logs" to an absolute path of "/var/solr/logs/solr.log".
If defaults have been overridden, then I can't tell you where the file
will be without additional information from you.
Thanks,
Shawn
Re: Delta import not working with Oracle in Solr
Posted by Rahul Singh <ra...@gmail.com>.
Agreed. DIH is not an industrial grade ETL tool.. may want to consider other options. May want to look into Kafka Connect as an alternative. It has connectors for JDBC into Kafka, and from Kafka into Solr.
--
Rahul Singh
rahul.singh@anant.us
Anant Corporation
On Jul 9, 2018, 6:14 AM -0500, Alexandre Rafalovitch <ar...@gmail.com>, wrote:
> I think you are moving so fast it is hard to understand where you need help.
>
> Can you setup one clean smallest issue (maybe as test) and try our original
> suggestions.
>
> Otherwise, nobody has enough attention energy to figure out what is
> happening.
>
> And even then, this list is voluntary help, we are just trying to give you
> pointers the best we can. It is quite possible you have outgrown DIH and
> need to move up to a propper stand alone ETL tool.
>
> Regards,
> Alex
>
> On Sun, Jul 8, 2018, 11:49 PM shruti suri, <er...@gmail.com> wrote:
>
> > Still not working, same issue documents are not getting pushed to index.
> >
> >
> >
> > -----
> > Regards
> > Shruti
> > --
> > Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
> >
Re: Delta import not working with Oracle in Solr
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
I think you are moving so fast it is hard to understand where you need help.
Can you setup one clean smallest issue (maybe as test) and try our original
suggestions.
Otherwise, nobody has enough attention energy to figure out what is
happening.
And even then, this list is voluntary help, we are just trying to give you
pointers the best we can. It is quite possible you have outgrown DIH and
need to move up to a propper stand alone ETL tool.
Regards,
Alex
On Sun, Jul 8, 2018, 11:49 PM shruti suri, <er...@gmail.com> wrote:
> Still not working, same issue documents are not getting pushed to index.
>
>
>
> -----
> Regards
> Shruti
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>
Re: Delta import not working with Oracle in Solr
Posted by shruti suri <er...@gmail.com>.
Still not working, same issue documents are not getting pushed to index.
-----
Regards
Shruti
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Re: Delta import not working with Oracle in Solr
Posted by Karthik S <ks...@gmail.com>.
I have as well faced the problem when we have composite primary key in the
table, so below is how have went with workaround.
deltaQuery retrieve concat value with time criteria (that should retrieves
only modified rows) and use it in deltaImportQuery with where clause.
<entity name="item" pk="data_id" transformer="TemplateTransformer"
query="select concat(LOCAL_ID,LOCAL_MASTER_ID) as data_id, * from
data.vw_data_ref"
deltaImportQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID)
as data_id, * from data.vw_data_ref where
concat(LOCAL_ID,LOCAL_MASTER_ID)='${dataimporter.delta.data_id}'"
deltaQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) as data_id
from data.vw_data_ref
where UPDATE_DATE > to_date('${dataimporter.last_index_time}', 'YYYY-MM-DD
HH24:MI:SS')"
>
</entity>
On Sun, Jul 8, 2018 at 11:56 AM, shruti suri <er...@gmail.com>
wrote:
> Dataconfig I am using now
>
> <entity name="item" pk="data_id" transformer="TemplateTransformer"
>
> query="select * from data.vw_data_ref"
> deltaImportQuery="select * from data.vw_data_ref where
> concat(LOCAL_ID,LOCAL_MASTER_ID)='${dataimporter.delta.data_id}'"
> deltaQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) as unique_id
> from data.vw_data_ref
> where UPDATE_DATE > to_date('${dataimporter.last_index_time}',
> 'YYYY-MM-DD
> HH24:MI:SS')"
> >
>
> <field column="data_id"
> template="${item.LOCAL_MASTER_ID}${item.LOCAL_ID}" />
> </entity>
>
> *managed-schema*
>
> <uniqueKey>data_id</uniqueKey>
>
>
>
>
> -----
> Regards
> Shruti
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>
Re: Delta import not working with Oracle in Solr
Posted by shruti suri <er...@gmail.com>.
Dataconfig I am using now
<entity name="item" pk="data_id" transformer="TemplateTransformer"
query="select * from data.vw_data_ref"
deltaImportQuery="select * from data.vw_data_ref where
concat(LOCAL_ID,LOCAL_MASTER_ID)='${dataimporter.delta.data_id}'"
deltaQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) as unique_id
from data.vw_data_ref
where UPDATE_DATE > to_date('${dataimporter.last_index_time}', 'YYYY-MM-DD
HH24:MI:SS')"
>
<field column="data_id"
template="${item.LOCAL_MASTER_ID}${item.LOCAL_ID}" />
</entity>
*managed-schema*
<uniqueKey>data_id</uniqueKey>
-----
Regards
Shruti
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Re: Delta import not working with Oracle in Solr
Posted by shruti suri <er...@gmail.com>.
My oracle table doesn't have any primary key and delta import requires a
primary key, that's why I am creating it by concatenating 2 columns. For now
just for testing I am using only one column.
I am using solr-6.1.0 version. This is the response I am getting. But every
time I run delta import , it fetches same number of records but didn't
commit them.
<lst name="statusMessages">
<str name="Time Elapsed">0:0:42.255</str>
<str name="Total Requests made to DataSource">2</str>
<str name="Total Rows Fetched">10208</str>
<str name="Total Documents Processed">0</str>
<str name="Total Documents Skipped">0</str>
<str name="Delta Dump started">2018-07-08 15:37:31</str>
<str name="Identifying Delta">2018-07-08 15:37:31</str>
<str name="Deltas Obtained">2018-07-08 15:38:13</str>
<str name="Building documents">2018-07-08 15:38:13</str>
<str name="Total Changed Documents">1</str>
</lst>
-----
Regards
Shruti
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Re: Delta import not working with Oracle in Solr
Posted by Shawn Heisey <ap...@elyograg.org>.
On 7/6/2018 5:53 AM, shruti suri wrote:
> Please help me with delta import form one oracle table into solr. I don't
> have any primary key in the table. We need to use composite key using
> (LOCAL_MASTER_ID,LOCAL_ID).
>
> <entity name="item" pk="ID" transformer="TemplateTransformer"
> query="select * from data.vw_data_ref"
> deltaQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) as ID from
> data.vw_data_ref where UPDATED_DATE >
> to_date('${dataimporter.last_index_time}', 'YYYY-MM-DD HH24:MI:SS')"
> deltaImportQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) from
> data.vw_data_ref where ID='${item.ID}'">
>
> <field column="ID" template="${item.LOCAL_ID}${item.LOCAL_MASTER_ID}" />
> </entity>
I'm going to say much the same thing that Alexandre did, and maybe give
a little more detail.
Your message did not ask a question or indicate what your problem is.
Your subject says that delta import isn't working. But you didn't
provide any information about what "not working" means -- no error
messages, no indication of what's happening and how that compares with
what you expect to happen.
Here's some concrete information I can give you from your config:
You have pk="ID" in your entity definition. This means that *ALL* of
your SQL queries *must* return a field with the name "ID". If one of
them does not contain that field, Solr is going to throw an error and
refuse to run the import.
The "deltaImportQuery" does not return a field named "ID". If your
table does not contain this field, then the "query" isn't returning it
either. Also, your deltaImportQuery is only returning one field. This
means that even if you can get the import working (which might involve
adding "as ID" to the query as Alexandre mentioned), only the one field
being returned will be contained in any documents imported by a
delta-import.
Thanks,
Shawn
Re: Delta import not working with Oracle in Solr
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
Which version of Solr is it and in which way is it not working.
And should not the deltaQuery and deltaImportQuery both have "as ID" part?
Regards,
Alex.
On 6 July 2018 at 07:53, shruti suri <er...@gmail.com> wrote:
> HI,
>
> Please help me with delta import form one oracle table into solr. I don't
> have any primary key in the table. We need to use composite key using
> (LOCAL_MASTER_ID,LOCAL_ID).
>
> <entity name="item" pk="ID" transformer="TemplateTransformer"
> query="select * from data.vw_data_ref"
> deltaQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) as ID from
> data.vw_data_ref where UPDATED_DATE >
> to_date('${dataimporter.last_index_time}', 'YYYY-MM-DD HH24:MI:SS')"
> deltaImportQuery="select concat(LOCAL_ID,LOCAL_MASTER_ID) from
> data.vw_data_ref where ID='${item.ID}'">
>
> <field column="ID" template="${item.LOCAL_ID}${item.LOCAL_MASTER_ID}" />
> </entity>
>
>
>
> -----
> Regards
> Shruti
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html