Solr 连接sql server 作为数据源

1. 下载jdbc 驱动

Download Microsoft JDBC Driver 4.0 for SQL Server from: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774


copy file 'sqljdbc4.jar' to 'contrib/dataimporthandler/lib'

2. 创建 core pdms

create core and below  dir is ready
[plcm@pdmstest pdms]$ ll
total 4
drwxrwxr-x 3 plcm plcm 143 Oct 18 18:39 conf
-rw-rw-r-- 1 plcm plcm  74 Oct 18 17:58 core.properties
drwxrwxr-x 5 plcm plcm  56 Oct 18 17:58 data
[plcm@pdmstest pdms]$ pwd
/home/plcm/Downloads/solr-7.1.0/server/solr/pdms
[plcm@pdmstest pdms]$ 

3. 拷贝lib

在 /path-to/solr-6.X.X/contrib/dataimporthandler/下创建lib文件夹，拷贝lib到目录下

cp /path-to/

    Progress/DataDirect/Connect_for_JDBC_51/lib/sqlserver.jar

    /path-to/ solr-

    6.6.0/contrib/dataimporthandler/lib/sqlserver.jar

4.  配置lib   solrconfig xml

[plcm@pdmstest conf]$ vi solrconfig.xml 
[plcm@pdmstest conf]$ pwd
/home/plcm/Downloads/solr-7.1.0/server/solr/configsets/_default/conf


edit solrconfig.xml by adding:


<lib dir="../../contrib/dataimporthandler/lib" regex=".*\.jar" />
<lib dir="../../dist/" regex="solr-dataimporthandler-.*\.jar" />

配置handler

Add the following configuration lines to solrconfig.xml which will instruct Solr to register the Data Import handler.

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

    <lst name="defaults">

    <str name="config">/path-to/solr-6.6.0/server/solr/your_corename/conf/data-config.xml</str>

    </lst>

    </requestHandler>

6.  data-config.xml

create a new file data-config.xml. Add the following configuration to the file which has the details on how to connect to SQL Server using Progress DataDirect JDBC driver, which entity to get the data from specified by the query and fields that you are expecting in the query resultset. 

<dataConfig>  <dataSource type="JdbcDataSource"               driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"               url="jdbc:sqlserver://172.21.104.67:1433;database=epmdb"                 user="plcmuser"               password="Polycom123"/>  <document>    <entity name="resourcefile"        pk="resourceFileId"      query="select resourceFileId,fileName from t1.resourcefile">       <field column="resourceFileId" name="resourceFileId"/>       <field column="fileName" name="fileName"/>           </entity>  </document></dataConfig>

7.managed-schema.xml

Go to the path /path-to/solr-6.6.0/server/solr//conf and open the file managed-schema.xml.
Find the uniqueKey tag and change it to the primary key of the entity that you are querying on. In this case I haveresourceFileId as my primary key for the tableresorce file  I am trying to fetch, so I changed the value of the tag as below.

<uniqueKey>resourceFileId</uniqueKey>

8  add field to managed-schema.xml

<field name="resourceFileId" type="string" indexed="true" stored="true" required="true" multiValued="false"/>  <field name="fileName" type="string" indexed="true" stored="true" multiValued="false"/>

增加了一些基本类型定义

<fieldType name="int" class="solr.TrieIntField" docValues="true" precisionStep="0" positionIncrementGap="0"/> <fieldType name="float" class="solr.TrieFloatField" docValues="true" precisionStep="0" positionIncrementGap="0"/> <fieldType name="long" class="solr.TrieLongField" docValues="true" precisionStep="0" positionIncrementGap="0"/> <fieldType name="double" class="solr.TrieDoubleField" docValues="true" precisionStep="0" positionIncrementGap="0"/>

附件为测试成功的 配置文件，三个文件均位于conf目录下

https://github.com/hanruikai/solr