I have about 40 tables in database and we are planning to use Solr to search. So, when users enter a search they can choose some or all tables to search for data. These 40 tables are not related to each other. I tried search on single table and it worked great but I am not clear on how to approach the above scenario in Solr. Any ideas are greatly appreciated.
2 Answers
You can add the table name to the solr schema.
For example add a field table:
<field name="table" type="string" indexed="true" stored="stored" multiValued="false"/>
Now you can choose to facet on tables:
&fq=table:(users OR log)
You need to make the value stored, to know from which table something comes and decide your process strategy for the results.
I think this is a reasonable approach.
9 Comments
tigger tigger
Thanks for the response! Are you saying I should put all data from different tables into one document, with extra column to identify the table? But the columns are different across the tables, and also primary keys on some tables are GUIDs and some are integers. How does it work?
Edgar Klerks
Solr has only one schema (at least when you work with one core), so the suggestion from the developers of solr is to flatten your data into a single index: flatten data. But to do this, you must initially designed it this way. So if you have primary keys which are sometimes guids and sometimes integers, it will not work very well. What you can try is to add a dynamic field for those. dynamic_fields
Edgar Klerks
I work at a rather big project, which tries to make all their magazines searchable, we import all the data from various databases into one mongo database, so we have control over how the data is organized. That helps somewhat. But I think in your case this is not an option.
Edgar Klerks
I meant store it the primary keys as String not as dynamic field, which is something entirely different. This is not great for performance, but works for different typed fields.
tigger tigger
Thank you for the response! If I store the primary keys as string, should I save them in the same field in the document? Also, shouldn't the schema needs a unique key? If I save them in the same field, there might be more than one record with the same primary key. How does that work? Also, how does the full import query and delta query looks like? Do I need to use union SQL statements? Sorry, too many questions, but I am at loss here. Any help is greatly appreciated.
|
You forgot to explain how to store the table name into the created "table"-field.
This is, what I was looking for:
TemplateTransformer: Can be used to overwrite or modify any existing Solr field ... The value assigned to the field is based on a static template string ... Solr Wiki
data-config.xml:
<entity name="Books" transformer="TemplateTransformer">
<!-- other fields -->
<field column="Table" template="Books"/>
</entity>
<entity name="Movies" transformer="TemplateTransformer">
<!-- other fields -->
<field column="Table" template="Movies"/>
</entity>
schema.xml:
<field name="Table" type="string" indexed="true" stored="true" multiValued="false"/>
