{"id":2583,"date":"2015-05-17T14:39:05","date_gmt":"2015-05-17T12:39:05","guid":{"rendered":"http:\/\/www.extradrm.com\/?p=2583"},"modified":"2015-05-17T15:39:08","modified_gmt":"2015-05-17T13:39:08","slug":"import-mysql-database-with-apache-solr-delta-import","status":"publish","type":"post","link":"https:\/\/www.extradrm.com\/?p=2583","title":{"rendered":"Loading data from Mysql to Solr with a Data Import Handler"},"content":{"rendered":"

Solr is used not as a primary data store because it is a Search Platform whose primary purpose is giving the ability to do complex searches with blazing performance. This means that you usually have your data in a primary data store, like mysql Server and you need to move data to a Solr server to power up your searches.<\/p>\n

I am using SolR 4.9.1 under windows vista with wampserver for this tutorial with a single core collection1 ….<\/p>\n

In our case we used MySQL so we added in the contrib \/ dataimporthandler \/ lib<\/strong> :
\nmysql-connector-java-5.1.32-bin.jar<\/p>\n

\"mysql-jar\"<\/a><\/p>\n

 <\/p>\n

Nota Bene : If you were using SQL Serve<\/strong>r then you will need to\u00a0 add sqljdbc4.jar<\/strong> that contains classes needed to connect to a SqlServer database from java jdbc.<\/p>\n

First step is to use the sample mysql database used by Apache SolR tutorials (let us start wampserver and create a database called test with root user an without password :<\/p>\n

CREATE TABLE `item` (\r\n`ID` int(11) NOT NULL AUTO_INCREMENT,\r\n`NAME` varchar(450) DEFAULT NULL,\r\n`MANU` varchar(450) DEFAULT NULL,\r\n`WEIGHT` float DEFAULT NULL,\r\n`PRICE` float DEFAULT NULL,\r\n`POPULARITY` int(11) DEFAULT NULL,\r\n`INSTOCK` tinyint(4) DEFAULT NULL,\r\n`INCLUDES` varchar(450) DEFAULT NULL,\r\n`last_modified` TIMESTAMP,\r\nPRIMARY KEY (`ID`)\r\n);\r\n\r\nCREATE TABLE `category` (\r\n`id` int(11) NOT NULL AUTO_INCREMENT,\r\n`description` varchar(450) DEFAULT NULL,\r\n`last_modified` TIMESTAMP,\r\nPRIMARY KEY (`id`)\r\n);\r\n\r\nCREATE TABLE `feature` (\r\n`id` int(11) NOT NULL AUTO_INCREMENT,\r\n`description` varchar(450) DEFAULT NULL,\r\n`item_id` int(11) DEFAULT NULL,\r\n`last_modified` TIMESTAMP,\r\nPRIMARY KEY (`id`),\r\nKEY `fk_feature_1` (`item_id`),\r\nCONSTRAINT `fk_feature_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION\r\n);\r\n\r\nCREATE TABLE `item_category` (\r\n`id` int(11) NOT NULL AUTO_INCREMENT,\r\n`CATEGORY_ID` int(11) DEFAULT NULL,\r\n`item_id` int(11) DEFAULT NULL,\r\n`last_modified` TIMESTAMP,\r\nPRIMARY KEY (`id`),\r\nKEY `fk_item_category_1` (`CATEGORY_ID`),\r\nKEY `fk_item_category_2` (`item_id`),\r\nCONSTRAINT `fk_item_category_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,\r\nCONSTRAINT `fk_item_category_2` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION\r\n);<\/pre>\n
Adding some Sample Data in MySQL:<\/p>\n
insert into item values(id,'item 1','item 1 manu','1.2','100.23','1',1,'includes item 1',CURRENT_TIMESTAMP);\r\n\r\ninsert into feature values(id,'feature item 1',1,CURRENT_TIMESTAMP);\r\ninsert into category values(id,'music',CURRENT_TIMESTAMP);\r\ninsert into item_category values(id,1,1,CURRENT_TIMESTAMP);<\/pre>\n
After that we have to prepare collection xml config file :
\nin solrconfig.xml add (windows)<\/b><\/p>\n
..\r\n\r\n<lib dir=\"C:\\solr\\contrib\\dataimporthandler\\lib\\\" regex=\".*\\.jar\" \/>\r\n<lib dir=\"C:\\solr\\dist\\\" regex=\"solr-dataimporthandler-\\d.*\\.jar\" \/>\r\n\r\n<requestHandler name=\"\/dataimport\" class=\"org.apache.solr.handler.dataimport.DataImportHandler\">\r\n    <lst name=\"defaults\">\r\n          <str name=\"config\">db-data-config.xml<\/str>\r\n      <\/lst>\r\n<\/requestHandler>    \r\n...<\/pre>\n
Now create in config collection1 folder : db-data-config.xml<\/strong> (From Solr version 4.4 and +++). This Data Import configuration file will specify how you want to import data inside your Solr.<\/p>\n
<dataConfig>\r\n    <dataSource driver=\"com.mysql.jdbc.Driver\" type=\"JdbcDataSource\"\r\n       url=\"jdbc:mysql:\/\/localhost:3306\/test\" user=\"root\" password=\"\" \/>\r\n<document>\r\n<!-- this entity is the 'root' entity. -->\r\n    <entity name=\"item\" query=\"select * from item\" deltaQuery=\"select id from item where last_modified > '${dataimporter.last_index_time}'\">\r\n\t<field column=\"NAME\" name=\"name\" \/>\r\n\t<!-- This entity is nested and reflects the one-to-many relationship between an item and its multiple features. Note the use of variables; ${item.ID} is the value of the column 'ID' for the\r\n\tcurrent item ('item' referring to the entity name) -->\r\n\t<entity name=\"feature\"\r\n\t\tquery=\"select DESCRIPTION from FEATURE where ITEM_ID='${item.ID}'\" deltaQuery=\"select ITEM_ID from FEATURE where last_modified > '${dataimporter.last_index_time}'\"\r\n\t\tparentDeltaQuery=\"select ID from item where ID=${feature.ITEM_ID}\">\r\n\t<field name=\"features\" column=\"DESCRIPTION\" \/>\r\n\t<\/entity>\r\n\r\n\t<entity name=\"item_category\"\r\n\t\tquery=\"select CATEGORY_ID from item_category where ITEM_ID='${item.ID}'\"\r\n\t\tdeltaQuery=\"select ITEM_ID, CATEGORY_ID from item_category where last_modified > '${dataimporter.last_index_time}'\"\r\n\t\tparentDeltaQuery=\"select ID from item where ID=${item_category.ITEM_ID}\">\r\n\t\t<entity name=\"category\" query=\"select DESCRIPTION from category where ID = '${item_category.CATEGORY_ID}'\"\r\n\t\t\tdeltaQuery=\"select ID from category where last_modified > '${dataimporter.last_index_time}'\"\r\n\t\t\tparentDeltaQuery=\"select ITEM_ID, CATEGORY_ID from item_category where CATEGORY_ID=${category.ID}\">\r\n\t\t\t<field column=\"description\" name=\"cat\" \/>\r\n\t\t<\/entity>\r\n\t<\/entity>\r\n    <\/entity>\r\n<\/document>\r\n<\/dataConfig><\/pre>\n
In case it was an sql server, I like to show a more simple sample DIH handler :<\/p>\n
\r\n<dataConfig>  \r\n    <dataSource type=\"JdbcDataSource\"\r\n            driver=\"com.microsoft.sqlserver.jdbc.SQLServerDriver\"\r\n            url=\"jdbc:sqlserver:\/\/10.0.10.104;databaseName=thesaurus;\"\r\n            user=\"sa\"\r\n            password=\"zzzzzz\"\r\n            batchSize=\"5\" \/> \r\n             \r\n    <document name=\"TestDocument\">  \r\n        <entity name=\"TestEntity\" query=\"SELECT * FROM tag\">  \r\n            <field column=\"Id\" name=\"id\" \/>\r\n            <field column=\"Term\" name=\"term\" \/>  \r\n            <field column=\"Name\" name=\"name\" \/>  \r\n        <\/entity>  \r\n    <\/document>  \r\n<\/dataConfig> \r\n<\/pre>\n
All the tests after insert or update will be queried under Admin SolR UI \/ collection1 …<\/p>\n
Before we continue, Here are some useful URLs for Import \/ indexing MySQL database with Apache Solr – Delta Import to make the tests more quicker :<\/p>\n
Clear or delete Solr index: http:\/\/localhost:8983\/solr\/update?stream.body=<delete><query>*:*<\/query><\/delete>&commit=true\r\nRetrieve all: http:\/\/localhost:8983\/solr\/select?q=*:*&omitHeader=true\r\nIndex db: http:\/\/localhost:8983\/solr\/collection1\/dataimport?command=full-import\r\n\r\nhttp:\/\/localhost:8983\/solr\/collection1\/dataimport?command=full-import&clean=false\r\nReload core: http:\/\/localhost:8983\/solr\/admin\/cores?action=RELOAD&core=collection1\r\nQuery for word Sample: http:\/\/localhost:8983\/solr\/select?q=Sample&wt=json&qf=first_name%20last_name&defType=edismax<\/pre>\n
For indexing the whole database use :<\/p>\n
http:\/\/localhost:8983\/solr\/collection1\/dataimport?command=full-import<\/pre>\n
Now let us test SolR delta import by updating a row in items mysql table :<\/p>\n
update item set `last_modified` = CURRENT_TIMESTAMP , price=91 where `ID` =1;\r\nupdate users set `last_modified` = CURRENT_TIMESTAMP , user_name='Haddad' where `user_id` =1;\r\n<\/pre>\n
and let us do solr delta import with our dih :<\/p>\n
http:\/\/localhost:8983\/solr\/collection1\/dataimport?command=delta-import&optimize=false<\/pre>\n
It is important to note that some problems may occur with delta import if the server time is not set properly or on ID in DIH if the proper version of Solr is not the good one
\nDIH file for version lucene 1.4 is slightly different from 4.4 +++ versions<\/p>\n
Import data with standard Admin UI solr web interface<\/em><\/strong><\/span>
\n\"solr_admin_ui\"<\/a><\/p>\n
Cheers<\/p>\n","protected":false},"excerpt":{"rendered":"
Solr is used not as a primary data store because it is a Search Platform whose primary purpose is giving the ability to do complex searches with blazing performance. This means that you usually...<\/p>\n","protected":false},"author":1,"featured_media":2847,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[340],"tags":[341],"youtube_video":null,"_links":{"self":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/2583"}],"collection":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2583"}],"version-history":[{"count":0,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/2583\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/media\/2847"}],"wp:attachment":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}