Fulltext search SOLR

[lukyer]

Hello,

i have a simple foreign table defined in my VDB. I would like to allow users to query that table via some kind of fulltext engine. So i set up SOLR server which connects to teiid VDB via JDBC Teiid driver. It works as expected. However i don't know how to update documents in SOLR automatically from foreign table. E.g. if new row is added in foreign table, i want SOLR to index it automatically so users can do fulltext search in real (or near real) time. Does Teiid have some functionality i could use? I read docs but i'm not sure if SOLR connector is what i need here. I do not need to access SOLR documents via Teiid, i just need to update SOLR documents on create/update/delete in tables integrated by Teiid.

Thank you.

[shawkins]

> Does Teiid have some functionality i could use?

As long as the insert/update/delete is processed through Teiid you have a couple of options.  If you need row level handling you could instead direct the update through a view and use instead of triggers.

create view tbl as select * from physical.tbl;

create trigger on tbl instead of insert as

for each row

begin

--do the solr update, then the actual insert

insert into tbl values (new.col1, new.col2 ...);

end;

Note this can impact the performance of updates that otherwise could get fully pushed to a source - but it will be optimized out completely for selects.

Another other option be log an enhancement for exposing data events at the row or table level to a Java api that you can register listeners with.

If you need to update the search index based upon updates made outside of Teiid you'd have to look into CDC options, such as Debizium.

[lukyer]

Thank you for options you provided. I'm interested mostly in CDC option. However if i understand it correctly one have to setup plugin in remote database which allow Debizium to work. Do you know about some solution which can work without remote database cooperation (plugins, settings, ...)? Ideally something universal as JDBC, not Postgres specific. If it doesn't exist - is my best option to regularly index remote database by CRON or something?

Thank you.

[shawkins]

> However if i understand it correctly one have to setup plugin in remote database which allow Debizium to work.

That is correct for a Postgresql backend.  Other sources Debezium supports require some configuration but are less invasive.

> Do you know about some solution which can work without remote database cooperation (plugins, settings, ...)?

Most will require some db settings, processing of transaction logs, configuration of where to publish events, etc.  Otherwise you'd have to have some kind of protocol gateway or require clients to use specialized drivers, either way there's a lot logic that would need to go into understanding the statements and transaction boundaries - so it ends up being much easier to do CDC with some level of backend integration.