Import MySQL database with Apache Solr - Delta Import

Step 1: Create table structure in MySQL Database

---

MySQL Table Structure:

CREATE TABLE `apache_solr_delta_import`.`item` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '    ',
`NAME` varchar(450) DEFAULT NULL,
`MANU` varchar(450) DEFAULT NULL,
`WEIGHT` float DEFAULT NULL,
`PRICE` float DEFAULT NULL,
`POPULARITY` int(11) DEFAULT NULL,
`INSTOCK` tinyint(4) DEFAULT NULL,
`INCLUDES` varchar(450) DEFAULT NULL,
`last_modified` TIMESTAMP,
PRIMARY KEY (`ID`)
);

CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
`last_modified` TIMESTAMP,
PRIMARY KEY (`id`)
);

CREATE TABLE `feature` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`last_modified` TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_feature_1` (`item_id`),
CONSTRAINT `fk_feature_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `item_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CATEGORY_ID` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`last_modified` TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_item_category_1` (`CATEGORY_ID`),
KEY `fk_item_category_2` (`item_id`),
CONSTRAINT `fk_item_category_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_item_category_2` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Sample Data in MySQL:

insert into item values(id,'item 1','item 1 manu','1.2','100.23','1',1,'includes item 1',CURRENT_TIMESTAMP);
insert into feature values(id,'feature item 1',1,CURRENT_TIMESTAMP);
insert into category values(id,'music',CURRENT_TIMESTAMP);
insert into item_category values(id,1,1,CURRENT_TIMESTAMP);

Step 2: Register the Handler in solrconfig.xml

---

Path: "mySolr/solr/example/solr/conf/solrconfig.xml"

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

<lst name="defaults">

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

      </lst>
</requestHandler>

Step 3: Create data-config.xml and add the following content

---

Path: "mySolr/solr/example/solr/conf/"

<dataConfig>
    <dataSource driver="com.mysql.jdbc.Driver" type="JdbcDataSource"
       url="jdbc:mysql://localhost:3306/apache_solr_delta_import" user="root" password="root" />
    <document name="products">
       <entity name="item" pk="ID" query="select * from item"
               deltaImportQuery="select * from item where ID = '${dataimporter.delta.id}'"
               deltaQuery="select id from item where last_modified &gt; '${dataimporter.last_index_time}'">
               <entity name="feature" pk="ITEM_ID"
                   query="select DESCRIPTION as features from feature where ITEM_ID = '${item.ID}'"
                   deltaQuery="select ITEM_ID from feature where last_modified > '${dataimporter.last_index_time}'"
                   parentDeltaQuery="select ID from item where ID = ${feature.ITEM_ID}"/>


           <entity name="item_category" pk="ITEM_ID, CATEGORY_ID"
                   query="select CATEGORY_ID from item_category where ITEM_ID = '${item.ID}'"
                   deltaQuery="select ITEM_ID, CATEGORY_ID from item_category where last_modified > '${dataimporter.last_index_time}'"
                   parentDeltaQuery="select ID from item where ID=${item_category.ITEM_ID}">
               <entity name="category" pk="ID"
                       query="select DESCRIPTION as cat from category where ID = '${item_category.CATEGORY_ID}'"
                       deltaQuery="select ID from category where last_modified &gt; '${dataimporter.last_index_time}'"
                       parentDeltaQuery="select ITEM_ID, CATEGORY_ID from item_category where CATEGORY_ID=${category.ID}"/>
           </entity>
       </entity>
    </document>
</dataConfig>

Step 4: Add the required JDBC Connector jar file

---

Path: "mySolr/solr/example/lib/"

In our case we used MySQL so we added : mysql-connector-java-5.1.20.jar

Step 5: Run Commands

---

Path: "mySolr/solr/example/"

• Run the Apache Solr server from example directory:

    java -jar start.jar

• Loading data:
• DIH Admin Console :

http://localhost:8983/solr/admin/dataimport.jsp?handler=/dataimport

• To check the data added in Solr:

http://localhost:8983/solr/select/?q=*%3A*&version=2.2&start=0&rows=10&indent=on

• Add New Data now in all table and check again

        insert into item values(id,'item 2','item 2 manu','1.2','100.23','1',1,'includes item 2',CURRENT_TIMESTAMP);

insert into feature values(id,'feature item 2',2,CURRENT_TIMESTAMP);

insert into category values(id,'electronics',CURRENT_TIMESTAMP);

insert into item_category values(id,2,2,CURRENT_TIMESTAMP);

http://localhost:8983/solr/select/?q=*%3A*&version=2.2&start=0&rows=10&indent=on