Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of importing data from mysql to solr, which involves things, learned from theoretical knowledge, there are many books, literature for your reference, in practical sense, accumulated years of practical experience can be shared with you.

1 mysql Import data to solr

Method 1: create a project, query the data, and send an add to the solr; (not recommended)

Method 2: copy data to solr by configuration

The above has completed the deployment of solr in the local window8, added a custom coredemo for solr, and introduced the ik word splitter.

So how do you import local mysql data into solr?

1.1 preparation 1.1.1 prepare the data source

Mysql data source: the user table (7 pieces of data) in the test library, where this update_time field is the basis for solr to update database data, and must have this field in the table.

SETFOREIGN_KEY_CHECKS=0

-

-- Table structurefor user

-

DROP TABLE IFEXISTS `user`

CREATE TABLE`user` (

`id`int (10) NOT NULL AUTO_INCREMENT

`name` varchar (30) DEFAULT NULL

`sex` int (1) DEFAULT NULL

`title`varchar (200) DEFAULT NULL

`insert_ time`datetime DEFAULT NULL

`update_ time`datetime DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDBAUTO_INCREMENT=8 DEFAULT CHARSET=utf8

-

-- Records of user

-

INSERT INTO `user`VALUES ('1Qing,' Hang3', '1bike,' computer', '2017-05-02 15-15-40-14-19,' 2017-05-03 15-40-40-22')

INSERT INTO `user`VALUES ('2', 'Lisan','1','if there is a computer, life is a game. Work hard', '2017-05-02 15-40-14-19,' 2017-05-03-15-40-40-22')

INSERT INTO `user`values ('3percent,' Li Si', '0percent,' you can configure and operate after the preparatory work is completed.' , '2017-05-02 1540 purl 1900,' 2017-05-03 1540 purl 22')

INSERT INTO `user`values ('44th,' Wang Wu', '0upload,' this demo is mainly used when uploading multiple images and can carry dynamic loading parameters.' , '2017-05-02 15-40-40-19),' 2017-05-0315-40-40-22')

INSERT INTO `user`VALUES (also available in '5users,' Zhao Liu', '1users,' demo) with multiple file uploads and previews. You can also tune UBG, '2017-05-25 15-15-43-43-03,' 2017-05-25 15-43-03-7')

INSERT INTO `user`VALUES ('6mm,' Sunday', '01VALUES,' this demo is mainly used when uploading multiple images', '2017-05-02 1515-43VALUES', '2017-05-11 1515-43VALUES')

INSERT INTO `user`Values ('74th,' Li Bai', '1Qing,' Poet', '2017-05-02 15 4314 23,' 2017-05-02 15 140 4325')

1.1.2 prepare the required jar

Link to mysql driver jar:mysql-connector-java-5.1.14.jar (this is basically available)

And D:\ solr-4.10.2\ dist\ solr-dataimporthandler-4.10.2.jar

1.1.3 prepare for configuration

From solr's extracted files directory, find the db-data-config.xml file under "D:\ solr-4.10.2\ example\ example-DIH\ solr\ db\ conf" to your core (for example:

D:\ solr-4.10.2\ example\ demo-solr\ demo\ conf) conf directory, and rename it to data-config.xml.

The detailed configuration is as follows:

DeltaImportQuery: SQL used when getting incremental data (used for incremental import)

DeltaQuery: get competitive SQL (used for incremental import)

Notice how the query condition is written: ${.}, as in this example:

${dataimporter.last_index_time} Index Last Import time

The id in ${dataimporter.delta.id} entity user query results, which can also be represented by ${user.id}

For a detailed explanation of data-config.xml, refer to the blog post:

Http://www.jianshu.com/p/91a5a0c35475

Http://blog.csdn.net/boolbo/article/details/50352331

1.2 configuration implementation

Note: if solr is started with tomcat, it's just that the relevant jar and configuration files are placed in a different location. The configuration is consistent.

1. Copy mysql-connector-java-5.1.14.jar and solr-dataimporthandler-4.10.2.jar to the solr's D:\ solr-4.10.2\ example\ solr-webapp\ webapp\ WEB-INF\ lib directory

2. In the D:\ solr-4.10.2\ example\ demo-solr\ demo\ conf directory, change the solrconfig.xml configuration file. Add the following:

Data-config.xml

3. In the D:\ solr-4.10.2\ example\ demo-solr\ demo\ conf directory, change the schema.xml configuration and configure the relevant fields:

Id

4. Restart the solr application to view the original data:

It's the same old data.

5. Import mysql data into solr:

Select Dataimport for the import operation.

Query:

Prove that the data has been imported and deleted the original data!

2 delete all data

Documents runs / update, specifying type as xml. Query first and then delete:

The statement is:

*: *

Submit, perform deletion!

Of course, the above is manual deletion, if there is a lot of data to be deleted, what should I do if I want to delete the specified data automatically the next time I automatically synchronize the data?

2.1 conditional deletion of solr data (deletion during incremental import of data)

Scenario: a batch of store data to be auctioned is stored in solr, and the auction data has an auction end time (endTime). When the auction ending time is up, the auction data stored in solr is meaningless. You want to delete this data stored in solr.

It is not impossible to delete it manually according to the above, but this manual operation is too crazy, and it is time-consuming and labor-consuming to manually delete this piece of data in solr, and it is not very realistic.

The solution is:

Add to the entity of data-config.xml:

DeletedPkQuery= "select id from shops where endTime < NOW ()"

This sentence, which is at the same level as entiy's query, aims to query the id of all stores whose end time is less than the current time in the store data.

The store id performs deltaImportQuery operations along with the id collected in deltaQuery, except that one is to add data and the other is to delete data.

Note: this configuration has no effect after successfully importing data. It only works during the import process!

3 performance optimization

1. Set the stored of all field that are only used for search and do not need as a result (especially some larger field) to false

2. Set the indexed of the field that does not need to be used for search but is returned as a result to false

3. Delete all unnecessary copyField statements for minimization of index fields and search efficiency

4. Set the index of all text fields to false, then use copyField to copy them all to a total text field, and then search.

Read the above introduction of mysql to import data into solr method, hope to bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.