Saqib Ali Saqib Ali - 2 months ago 21
MySQL Question

Importing multi-valued field into Solr from mySQL using Solr Data Import Handler

We have the following two tables in our mySQL:

mysql> describe comment;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| blogpost_id | int(11) | YES | | NULL | |
| comment_text | varchar(256) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+

mysql> describe comment_tags;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| comment_id | int(11) | YES | | NULL | |
| tag | varchar(80) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+


Where each comment can have multiple tags. We can import the entire comment into Solr using the Data Import Handler. However I am not sure how to import the tags for each comment into a multivalued field defined the schema.xml for each comment document.

Please advise. Thanks

Answer

Try something like this:

<dataConfig>
    <!-- dataSource is just an example. Included just for completeness. -->
    <dataSource batchSize="500" type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/my-database" user="root" password="somethinglong1283"/>
<document>
    <entity name="comment" pk="id" query="SELECT * FROM comment">
        <field column="blogpost_id" name="blogpost_id"/>
        <field column="comment_text" name="comment_text" />
        <entity name="comment_tags" pk="comment_id" query="SELECT * FROM comment_tags WHERE comment_id='${comment.id}'">
            <field column="tag" name="tag" />
        </entity>
    </entity>
</document>