Integrating Solr 6 with My Sql

Hi Readers,

Good Day, I was doing some couple of tasks for identifying Integration of solr with mysql, the strange part is that solr has changed its schema structure when compared to the older version of solr .I am Writing this tutorial for solr 6 , the output of this is to make the readers to connect to mysql and index and query for the Results .

 Pre-requisites

      

     MySql - any version

     MySql – JDBC Connector

     Sample DatBase Containing any data

Step:1

Create a Core in Solr called Refrence you can see my blog here.

Step:2

Once you create the Core then the Following folder will be Created in the Server

Ie <Solr Installed Dir>solr-6.4.2\server

Navigate to  <Solr Installed Dir>solr-6.4.2\server\solr\refrence

There will be two folders called conf and data created Default, if not create it.

Conf – this folder is used for the Defining the Configs for solr , example what to index and what should take as part of the query.

Data- this folder is used for the Indexing stuff.

Step:3

Edit the File solrconfig.xml  and if this file is not avalaible in the Conf Directory copy from some other examples of solr and paste it .

Add the Following handler in the request handler

  <requestHandler name="/dataimport" class="solr.DataImportHandler">

    <lst name="defaults">

      <str name="config">db-data-config.xml</str>

    </lst>

  </requestHandler>

By Defaut solr does not comes with the dataimport handler , you need to add it externally.

You also need to add the dependency jars in that xml.

<lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-.*\.jar" />

<lib dir="="${solr.install.dir:../../../..}/contrib/dataimporthandler/lib" regex=".*\.jar" />

Make sure you copy the mysql jdbc jar in that path eg(cotrib /lib) , otherwise you can create a lib folder inside the refrence and paste the jar.

Step:4

As defined in the conig , you need to have the following file db-data-config.xml  in the conf directory.

Create a file called db-data-config.xml and edit with the below contents.

<?xml version="1.0" encoding="UTF-8" ?>

<dataConfig>

<dataSource type="JdbcDataSource"  driver="com.mysql.jdbc.Driver"  url="jdbc:mysql://localhost:3306/classicmodels"

            user="root"

            password="root"/>

<document name="classicmodels">

   <entity name="products" query="select * from products">

     <field column="productCode" name="id"/>

     <field column="productName" name="name"/>

     <field column="productDescription" name="description"/>      

  </entity>

</document>

</dataConfig>

This config file has the cofigurations for the Database I hope you are familiar with the Database Connections  for mysql

Document name is the DataBase Name .

Entity name is the Table Name.

Query is how to fetch the Data From the Table.

Name is the identification in the Solr.

Once you create mapping and changes done , save it and you need to define this fields , this is the place where the mapping of the database names is done with the field names .

This Steps Differs from the Previous versions of the Solr.

Step:5

You have to declare these Fields to the solr , how you can do it ?

Navigate to managed-schema   File in <SOLR-INSTALLED_DIR>\solr-6.4.2\server\solr\refrence\conf

Check for the field name tag and declare your defined fields also here.

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

    <field name="name" type="string" indexed="true" stored="true" required="true" multiValued="false" />

    

    <field name="description" type="string" indexed="true" stored="true" required="true" multiValued="false" />

Once you declare here, then solr understands your Fields .

then you need to copy your fields or link your fields as like below


    <copyField source="id" dest="refrence"/>
    <copyField source="name" dest="refrence"/>
    <copyField source="description" dest="refrence"/> 

where refrence can be defined as below 

    <field name="refrence" type="text_general" indexed="true" stored="false" multiValued="true"/>

it is of type "text_general" you can use this filed type for copying of the fields 

Step:6

Now we have declared the Fields, we have to define the default Fields to be searched .

We can achieve by two ways, one by defining in the solrconfig and other is by passing through the query.

We can see how to define in the solrconfig.xml

Navigate to solrconfig.xml in conf directory and search for the request handler select and add the following fields to it like below

<requestHandler name="/select" class="solr.SearchHandler">

     <lst name="defaults">

       <str name="echoParams">explicit</str>

       <int name="rows">10</int>

                   <str name="df">refrence</str>

     </lst>

</ requestHandler>

Once if you define like this, it will be treated as the search fields and no need to define in the URL.

Step:7 you need to import and index the Data.

Restart the solr server after the changes in the above Files .

Step:8

Navigate to http://localhost:8983/solr/refrence/dataimport?command=full-import

Once if you do this , it will import and index it.

You can see this by the below

http://localhost:8983/solr/refrence/dataimport

this gives the details about the processed skipped etc

Step:9

Now this Is the time to query for the Data you have Fetched and Indexed.

http://localhost:8983/solr/refrence/select?q=harley&wt=json

this will give the below response

Once if you see the below response, congragulations you have connected your database and indexed it as per your requirement .

Happy Learning . Stay Tuned for more solr Tutorials .