Solr导入数据库数据的相关配置（包含全量索引和增量索引）

     前段时间突然遇到了在不用数据库中导入数据的问题，于是查阅了资料，注意每个数据库的查询返回的数据结构要一致，不然会出现数据丢失或者部分数据无法导入的情况.配置如下：

<dataConfig>
    <dataSource name="ds-1" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/xx" user="root" password="123"/>
	<dataSource name="ds-2" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/xxx" user="root" password="123"/>
    <document>
        <entity name="music" dataSource="ds-1"
               query="select CONCAT(m.musicID,'-1') as id,trim(m.name) as ItemTitle,'' as Weight,CONCAT_WS('-',m.musicID,'MUSIC',trim(s.`name`)) as 'DESC',
						(
							select group_concat(CountryCode separator ',')  
							from music_coin mc
							where mc.musicID=m.musicID and mc.status=1
						) as CountryCode
						from music m,music_singer ms,singer s
						where m.musicID=ms.musicID and ms.singerID=s.singerID and m.`status`=1"
    deltaImportQuery="select CONCAT(m.musicID,'-1') as id,trim(m.name) as ItemTitle,'' as Weight,CONCAT_WS('-',m.musicID,'MUSIC',trim(s.`name`)) as 'DESC',
						(
							select group_concat(CountryCode separator ',')  
							from music_coin mc
							where mc.musicID=m.musicID and mc.status=1
						) as CountryCode
						from music m,music_singer ms,singer s
						where m.musicID=ms.musicID and ms.singerID=s.singerID and m.status=1 and m.musicID = '${dataimporter.delta.id}'"
		  deltaQuery="select CONCAT(m.musicID,'-1') as id,trim(m.name) as ItemTitle,'' as Weight,CONCAT_WS('-',m.musicID,'MUSIC',trim(s.`name`)) as 'DESC',
						(
							select group_concat(CountryCode separator ',')  
							from music_coin mc
							where mc.musicID=m.musicID and mc.status=1
						) as CountryCode
						from music m,music_singer ms,singer s
						where m.musicID=ms.musicID and ms.singerID=s.singerID and m.status=1 and m.updateTime > '${dataimporter.last_index_time}'">
                <field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
        </entity>
		<entity name="singer" dataSource="ds-1"
               query="select CONCAT(c.colID,'-2') as id,trim(s.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colID,'ARTIST') as 'DESC','' as ContryCode from col c,singer s where c.extend=s.singerID and c.colType=2 and c.status=1"
	deltaImportQuery="select CONCAT(c.colID,'-2') as id,trim(s.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colID,'ARTIST') as 'DESC','' as ContryCode from col c,singer s where c.extend=s.singerID and c.colType=2 and c.status=1 and c.colID = '${dataimporter.delta.id}"
		  deltaQuery="select CONCAT(c.colID,'-2') as id,trim(s.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colID,'ARTIST') as 'DESC','' as ContryCode from col c,singer s where c.extend=s.singerID and c.colType=2 and c.status=1 and c.updateDate > '${dataimporter.last_index_time}'">
				<field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
        </entity>
		<entity name="col" dataSource="ds-1"
                query="select CONCAT(c.colId,'-3') as id,trim(c.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colId,'ALBUM') as 'DESC',
						(select GROUP_CONCAT(CountryCode separator ',')
						from col_coin cc WHERE  cc.colID = c.colID and cc.status=1
						)	as CountryCode 
						from col c,singer s where c.singerID=s.singerID and c.colType=5 and c.albumType!='SINGLE' and c.status=1"
	 deltaImportQuery="select CONCAT(c.colId,'-3') as id,trim(c.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colId,'ALBUM') as 'DESC',
						(select GROUP_CONCAT(CountryCode separator ',')
						from col_coin cc WHERE  cc.colID = c.colID and cc.status=1
						)	as CountryCode 
						from col c,singer s where c.singerID=s.singerID and c.colType=5 and c.albumType!='SINGLE' and c.status=1 and c.colID = '${dataimporter.delta.id}'"				
		   deltaQuery="select CONCAT(c.colId,'-3') as id,trim(c.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colId,'ALBUM') as 'DESC',
						(select GROUP_CONCAT(CountryCode separator ',')
						from col_coin cc WHERE  cc.colID = c.colID and cc.status=1
						)	as CountryCode 
						from col c,singer s where c.singerID=s.singerID and c.colType=5 and c.albumType!='SINGLE' and c.status=1 and c.updateDate > '${dataimporter.last_index_time}'">
                <field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
        </entity>
		<entity name="video" dataSource="ds-1"
                query="select  CONCAT(v.videoID,'-4') as id,trim(v.name) as ItemTitle,'' as Weight,CONCAT_WS('-',v.videoID,'VIDEO') as 'DESC',
						(select GROUP_CONCAT(CountryCode separator ',')
						from video_coin vc WHERE  vc.videoID = v.videoID and v.status=1
						)	as CountryCode 
						from video v left join video_singer vs on v.videoID=vs.videoID
						left join singer s on vs.singerID=s.singerID
						where  v.status=1"
	 deltaImportQuery="select  CONCAT(v.videoID,'-4') as id,trim(v.name) as ItemTitle,'' as Weight,CONCAT_WS('-',v.videoID,'VIDEO') as 'DESC',
						(select GROUP_CONCAT(CountryCode separator ',')
						from video_coin vc WHERE  vc.videoID = v.videoID and v.status=1
						)	as CountryCode 
						from video v left join video_singer vs on v.videoID=vs.videoID
						left join singer s on vs.singerID=s.singerID
						where  v.status=1 and v.videoID = '${dataimporter.delta.id}'"				
		   deltaQuery="select  CONCAT(v.videoID,'-4') as id,trim(v.name) as ItemTitle,'' as Weight,CONCAT_WS('-',v.videoID,'VIDEO') as 'DESC',
						(select GROUP_CONCAT(CountryCode separator ',')
						from video_coin vc WHERE  vc.videoID = v.videoID and v.status=1
						)	as CountryCode 
						from video v left join video_singer vs on v.videoID=vs.videoID
						left join singer s on vs.singerID=s.singerID
						where  v.status=1 and v.updateTime > '${dataimporter.last_index_time}'">
                <field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
        </entity>
		<entity name="playlist" dataSource="ds-1"
                query="select CONCAT(c.colID,'-5') as id,trim(c.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colID,'PLAYLIST') as 'DESC','' as ContryCode
						from col c left join boom_vip b
						on c.extend=b.afid
						where c.colType=1 and c.status=1"
	 deltaImportQuery="select CONCAT(c.colID,'-5') as id,trim(c.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colID,'PLAYLIST') as 'DESC','' as ContryCode
						from col c left join boom_vip b
						on c.extend=b.afid
						where c.colType=1 and c.status=1 and c.colID = '${dataimporter.delta.id}'"				
		   deltaQuery="select CONCAT(c.colID,'-5') as id,trim(c.name) as ItemTitle,'' as Weight,CONCAT_WS('-',c.colID,'PLAYLIST') as 'DESC','' as ContryCode
						from col c left join boom_vip b
						on c.extend=b.afid
						where c.colType=1 and c.status=1 and c.updateDate > '${dataimporter.last_index_time}'">
                <field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
        </entity>
		 <entity name="userName" dataSource="ds-2" 
			    query="select CONCAT(afid,'-6') as id,trim(`name`) as ItemTitle,'' as Weight,CONCAT_WS('-',afid,'USERNAME',afid) as 'DESC','' as CountryCode
						from user_statics"
	 deltaImportQuery="select CONCAT(afid,'-6') as id,trim(`name`) as ItemTitle,'' as Weight,CONCAT_WS('-',afid,'USERNAME',afid) as 'DESC','' as CountryCode
						from user_statics where afid='${dataimporter.delta.id}'"				
	       deltaQuery="select CONCAT(afid,'-6') as id,trim(`name`) as ItemTitle,'' as Weight,CONCAT_WS('-',afid,'USERNAME',afid) as 'DESC','' as CountryCode
						from user_statics where updateTime > '${dataimporter.last_index_time}'">
				<field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
		</entity>
		 <entity name="userID" dataSource="ds-2" 
				query="select CONCAT(afid,'-7') as id,trim(`afid`) as ItemTitle,'' as Weight, CONCAT_WS('-',afid,'USERID',trim(name)) as 'DESC','' as CountryCode
						from user_statics"
	 deltaImportQuery="select CONCAT(afid,'-7') as id,trim(`afid`) as ItemTitle,'' as Weight, CONCAT_WS('-',afid,'USERID',trim(name)) as 'DESC','' as CountryCode
						from user_statics where afid='${dataimporter.delta.id}'"			
		   deltaQuery="select CONCAT(afid,'-7') as id,trim(`afid`) as ItemTitle,'' as Weight, CONCAT_WS('-',afid,'USERID',trim(name)) as 'DESC','' as CountryCode
						from user_statics  where updateTime > '${dataimporter.last_index_time}'">
				<field column="id" name="id" />
				<field column="ItemTitle" name="ItemTitle" />
				<field column="CountryCode" name="CountryCode" />	
				<field column="Weight" name="Weight" />	
				<field column="DESC" name="DESC" />	
		</entity>
    </document>
</dataConfig>

 

解释一下：

query 是全量导入时，把你的数据中查到的数据全部导入

deltaImportQuery 和 deltaQuery 是增量导入数据所需要的两个查询语句。deltaImportQuery 后面有个过滤条件是数据库中的唯一标识id = '${dataimporter.delta.id}'  后面这个id 是solr索引库中的id ,固定不变的。deltaQuery 的话后面只需要加个数据库中更新时间的字段 例如updateDate > '${dataimporter.last_index_time}' 即可   ，我们每次导入数据的时候会在索引库的conf 目录下的dataimport.properties文件中记录时间点  也就是last_index_time=日期，就是为了方便做增量索引。只要你的数据库中有更新了某些字段或者添加了新的条数（前提是表中必须有个任意更新字段就要更新updateDate 这个字段），我们只要做个定时的增量索引，就可以做到每次增量时，都可保证solr查询到的数据都是最新的。