Solr Indexing - MariaDB Table Data into Apache Solr

In one of my assignments I was asked to indexed MariaDB data inside Apache Solr. That was very interesting to integrate MariaDB to Apache Solr. Here I will explain you step by step to achieve the target.

First of we need to download and install below artifacts.

• Solr5.0.0
• MariaDB-10.1.9
• MariaDB connector

First we will create database named ContactDB inside MariaDB and inside this Database will create table and then put some raw data to do the same.

MariaDB [(none)]> create database contactdb;

MariaDB [(none)]> use contactdb;

MariaDB [contactdb]> create table EMPLOYEE (first_name varchar (30), last_name     varchar (30), company varchar (30), gender varchar (10), contact varchar (10));

MariaDB [contactdb]> insert into EMPLOYEE(first_name, last_name, company, gender

, contact) values('Iqubal M', 'Kaki', 'TechM', 'M', '839090000');

MariaDB [contactdb]> select * from employee;

Download Solr from apache SOLR5.0.0 Extracts solr5.0.0.zip folder into your preferred directory. Here I am referring D:/ApacheSolr/solr-5.0.0 as $SOLAR_HOME

Place MariaDB connector mariadb-java-client-1.2.0.jar files into

"$SOLR_HOME/contrib/dataimporthandler/lib". Create sub directory "lib" if it is not present there.

Now start solr by executing below command from command prompt.

>> cd solr5.0.0/bin;

>> solr start

It will start solr on 8983 port, verify by using the URL http://localhost:8983/solr 

Direct your Web browser to http://localhost:8983/solr  to visit the Solr Admin UI.

1. Then Create Core into SOLR by executing below commands from SOLR_HOME/bin.

# bin/solr create_core -c employees -d basic_configs

2. Create db-data-config.xml file inside solr5.0.0/server/solr/employees/conf folder.

And now put your MariaDB related information here. From this file solr knows what to show on the search.

<dataConfig> <dataSource type="JdbcDataSource" driver="org.mariadb.jdbc.Driver" url="jdbc:mysql://localhost:3306/contactdb" user="root" password="root"/> <document>             <entity name="id" query="select first_name as 'id' , first_name, last_name, company, gender, contact from EMPLOYEE"/> </document> </dataConfig>

3. Now made below changes into the solr5.0.0/server/solr/employees/conf/solrconfig.xml and save the file.

And add following line within config tag.

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

4. Now open schema.xml in the same directory and add following lines. Here I am configuring solr what to index from MariaDB table. i.e. solr5.0.0/server/solr/employees/schema.xml

<dynamicField name="*_name" type="text_general" multiValued="false" indexed="true" stored="true" /> <dynamicField name="*company" type="text_general" multiValued="false" indexed="true" stored="true" /> <dynamicField name="*gender" type="text_general" multiValued="false" indexed="true" stored="true" /> <dynamicField name="*contact" type="text_general" multiValued="false" indexed="true" stored="true" />

5. Now stop and start the solr server.

>> cd solr5.0.0/bin;

>> solr stop -all

>> solr start

Now indexed the MariaDB table data into Apache Solr by hitting the below URL. It will show how many documents indexed. The first URL indexed all of the table data whether there is changes happened on DB or not, however second URL picked only the later Database changes.

http://localhost:8983/solr/employees/dataimport?command=full-import

http://localhost:8983/solr/employees/dataimport?command=delta-import

Solr Full Import

Solr Delta Import

Now you can see the results details onto Solr Admin Console

Now you can query on solr by using below URL.

http://localhost:8983/solr/employees/select?q=*&wt=json&qf=first_name%20last_name&defType=edismax&indent=true

Hope you have enjoyed the article.

Author: Iqubal Mustafa Kaki, Technical Specialist.

Want to connect with me
If you want to connect with me, please connect through my email - iqubal.kaki@gmail.com