Importing/Indexing Database (MySQL or SQL Server) in Solr Using Data Import Handler

Step 1: Install Solr

Download and install Solr from Solr Website. Access Solr admin via http://localhost:8983/solr/.

Step 2: Set Up Connectors

• MySQL: Download JDBC driver and place mysql-connector-java-*.jar in contrib/dataimporthandler/lib.
• SQL Server: Download Microsoft JDBC driver and copy sqljdbc4.jar to the same directory.

Step 3: Create New Collection

Create a new collection folder like /solr/myproducts/conf. Copy solrconfig.xml and modify to include:

<lib dir="../../contrib/dataimporthandler/lib" regex=".*\.jar" />
<requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">
    <lst name="defaults">
        <str name="config">data-config.xml</str>
    </lst>
</requestHandler>

Step 4: Create data-config.xml

• For MySQL:

<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/mydb1" user="root" password=""/>
<entity name="product" query="select id, name from products" deltaImportQuery="..." deltaQuery="...">

For SQL Server:

<dataSource type="JdbcDataSource" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://servername..." user="sa" password="mypass"/>

Step 5: Edit schema.xml

Add field mappings based on your DB structure:

<field name="id" type="string" indexed="true" stored="true" required="true"/>
<field name="name" type="text_general" indexed="true" stored="true"/>

Step 6: Perform Data Import

• Full import: http://localhost:8983/solr/myproducts/dataimport?command=full-import
• Delta import: http://localhost:8983/solr/myproducts/dataimport?command=delta-import

References

• Solr DataImportHandler Wiki