使用Solr索引查询Mysql数据

系统环境：Centos7
Solr版本：5.5.4
mysql：5.7.18
数据表规模：60万

1.创建表，导入数据

其中，ROW_ID为主键

2.启动solr

bin/solr start

3.创建collection

[root@zk01 solr-5.5.4]# bin/solr create -c awreportsCopying configuration to new core instance directory:/soft/solr-5.5.4/server/solr/awreportsCreating new core 'awreports' using command:http://localhost:8983/solr/admin/cores?action=CREATE&name=awreports&instanceDir=awreports{  "responseHeader":{    "status":0,    "QTime":1664},  "core":"awreports"}

4.修改/solr-5.5.4/server/solr/awreports/conf/managed-schema文件，修改旧的field【id】和uniqueKey为row_id

<field name="row_id" type="string" indexed="true" stored="true" required="true" multiValued="false" /><field name="accountId" type="string" indexed="true" stored="true" required="true" /><field name="day" type="date" indexed="true" stored="true" /><field name="impressions" type="int" indexed="true"/><defaultSearchField>accountId</defaultSearchField>  <uniqueKey>row_id</uniqueKey>

5.修改conf/solrconfig.xml文件，添加数据库数据的xml

<requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">       <lst name="defaults">          <str name="config">data-config.xml</str>       </lst></requestHandler>

6.创建data-config.xml文件，内部主要配置了访问数据库的用户名、密码，以及对应的数据库的字段

<dataConfig>    <dataSource type="JdbcDataSource"    driver="com.mysql.jdbc.Driver"    url="jdbc:mysql://127.0.0.1:3306/AWReports"    user="root"    password="123456"/>    <document>        <entity name="awReportAccount"            query="SELECT AW_ReportAccount.ROW_ID,AW_ReportAccount.ACCOUNT_ID,AW_ReportAccount.`DAY`,AW_ReportAccount.IMPRESSIONS from AW_ReportAccount">            <field column="ACCOUNT_ID" name="accountId" />            <field column="ROW_ID" name="row_id"/>            <field column="DAY" name="day"/>        </entity>    </document></dataConfig>

7.将solr-dataimporthandler-5.5.4.jar从solr-5.5.4/dist/文件夹下copy到solr-5.5.4/server/solr-webapp/webapp/WEB-INF/lib当中，此java包是导入数据用的;下载mysql-connector-java-5.1.31-bin.jar包，将它copy到solr-5.5.4/server/lib下;

8.复制solr-5.5.4/example/example-DIH/solr/solr/conf下的admin-extra.html、admin-extra.menu-bottom.html、admin-extra.menu-top.html到/solr-5.5.4/server/solr/awreports/conf下;

9.重启solr，完成全量数据导入，在每次全量数据导入执行的时候，原有索引会被删除，如果不想删除原有索引，可以选择clean为false

[root@zk01 solr-5.5.4]# bin/solr restartSending stop command to Solr running on port 8983 ... waiting 5 seconds to allow Jetty process 12735 to stop gracefully.Waiting up to 30 seconds to see Solr running on port 8983 [|]  Started Solr server on port 8983 (pid=21925). Happy searching!

10.web界面查询