Configuring Solr 4 Data Import Handler with JDBC

Preparation

You should have set-up Solr on Tomcat along with Tika's extracting request handler as shown in the previous two guides:
   http://amac4.blogspot.co.uk/2013/07/setting-up-solr-with-apache-tomcat-be.html       http://amac4.blogspot.co.uk/2013/07/setting-up-tika-extracting-request.html    

Set-Up

• Firstly we need the libraries that are required to use Data Import Handler. Create a folder and name it dih (preferably in your $SOLR_HOME), and place solr-dataimporthandler-4.0.0.jar and solr-dataimporthandler-extras-4.0.0.jar from $SOLR/dist directory in the dih folder. 
  
  Add this to the solrconfig.xml file:

<lib dir="../../dih" regex=".*\.jar" />

• Now we modify the solrconfig.xml file. Add the following :

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

• Create a db-data-config.xml. This is for the Data Import Handler configuration. It should look similar to:

<dataConfig>
<dataSource driver="org.postgresql.Driver"
url="jdbc:postgresql://localhost:1234/users" user="users"
password="secret" />
<document>
<entity name="user" query="SELECT id, name from users">
<field column="id" name="id" />
<field column="name" name="name" />
<entity name="birthday" query="select birthday from table where id=${user.id}">
<field column="description" name="description" />
</entity>
</entity>
</document>
</dataConfig></dataConfig>

• For other database engines enter the specific driver, url, user and password attributes.

• We now need to modify the fields section of the schema.xml file to something like the following snippet:

<field name="id" type="string" indexed="true" stored="true"
required="true"/>
<field name="name" type="text" indexed="true" stored="true" />
<field name="birthday" type="text" indexed="true" stored="true"/>
<field name="description" type="" indexed="true"
stored="true"/>

Solr may not like type="text", if so then change it to text_general

One more thing before the indexing – you should copy an appropriate JDBC driver to the $SOLR_HOME/lib directory of your Solr installation or the dih directory we created before. You can get the libraries from the databases websites

To index, run the following query: http://localhost:8080/solr/dataimport?command=full-import .The HTTP protocol is asynchronous so you won't be updated on the status of the indexing process. To check the status of the indexing process, you can run the command once again.

Setting Up Highlighting For Solr 4

The large search engines like Google and Bing show you a small snippet of text that often contains one or more of the keywords that have been searched for. To set this up on Solr is also very straightforward and this is a short guide on how to set it up.

Schema.xml

Always ensure that your schema.xml file for Nutch and for Solr are identical otherwise you will encounter problems so theses changes must be applied to both.

Ensure that the content field (or whatever fields you wish to highlight) are set to stored.

  <field name="content" type="text_general" stored="true" indexed="true"/>

NOTE: For pre-solr 4.0.0 "text_general" is called "text"

You also need to ensure that these two lines are present:

  <field name="id" type="string" indexed="true" stored="true" required="true" />
  <field name="name" type="text_general" indexed="true" stored="true" />

Solrconfig.xml

There do not need to be any major changes to this file but it contains a lot of features that you are able to change allowing you to tweak the higlighting functionality to your own liking.

  <!-- Highlighting defaults -->
     <str name="hl">on</str>
     <str name="hl.fl">content</str>
     <str name="hl.encoder">html</str>
     <str name="hl.simple.pre">&lt;b&gt;</str>
     <str name="hl.simple.post">&lt;/b&gt;</str>
     <str name="f.title.hl.fragsize">0</str>
     <str name="f.title.hl.alternateField">title</str>
     <str name="f.name.hl.fragsize">0</str>
     <str name="f.name.hl.alternateField">name</str>
     <str name="f.content.hl.snippets">3</str>
     <str name="f.content.hl.fragsize">200</str>
     <str name="f.content.hl.alternateField">content</str>
     <str name="f.content.hl.maxAlternateFieldLength">750</str>

Querying

When you query the Solr Server and have highlighting enabled it will return to you a extra tag named highlighting. The next name tag will match up with the id of the documents and can be easily matched using software like xPath.

  <lst name="highlighting">
    <lst name="file:/C:/Users/alamil/Documents/TextFiles/a.doc">
       <arr name="content">
        <str>Budget and Council Tax  POLICY AND RESOURCES COMMITTEE  BUDGET<em>STRATEGY</em></str>
      </arr>
    </lst>
    <lst name="file:/C:/Users/alamil/Documents/TextFilesb.doc">
      <arr name="content">
        <str>CONTENTS Introduction Customer Care Standards <em>Strategy</em></str>
      </arr>
    </lst>
  </lst>

The most common highlighting parameters available to the user are:
hl=true: If you want highlighting this must ALWAYS be true. Any blank, missing or "false" value disables highlighting feature.
hl.fl=content: Enables highlighting in that field, by default you are probably going to want to use content but other field can also be selected.
hl.snippets=5: It accepts a number as value, the specified numeric value decides the number of highlighted snipets to be returned in a query respense. The default value is 1.
hl.requireFieldMatch: It accept a true or false value as parameter, the highlighted response is returned only if the keyword is found in requied field.
The default value is "false".
hl.maxAnalyzedChars: It decides, how many characters into a document should be considered for highlighting.The default value is "51200". 

Solr 4 Deduplication On Windows

Setting Up Deduplication

I followed the Solr wiki to try and set-up deduplication and all it did was cause my Solr server to break, these settings worked for me and should also work for you too.  

Any changes made to the schema.xml file for Solr should also be reflected in the schema.xml file in Nutch.

schema.xml

You need a separate field to store the signature:

 <field name="signature" type="string" stored="true" indexed="true" multiValued="false" />

solrconfig.xml

The SignatureUpdateProcessorFactory has to be registered in the solrconfig.xml as part of the UpdateRequest Chain:

<updateRequestProcessorChain name="dedupe">
    <processor class="org.apache.solr.update.processor.SignatureUpdateProcessorFactory">
      <bool name="enabled">true</bool>
      <bool name="overwriteDupes">true</bool>
      <str name="signatureField">signature</str>
      <str name="fields">id</str>
      <str name="signatureClass">org.apache.solr.update.processor.Lookup3Signature</str>
    </processor>
    <processor class="solr.LogUpdateProcessorFactory" />
    <processor class="solr.RunUpdateProcessorFactory" />
  </updateRequestProcessorChain>

Also be sure to change your update handlers to use the defined chain:

 <requestHandler name="/update" class="solr.XmlUpdateRequestHandler" >
    <lst name="defaults">
      <str name="update.chain">dedupe</str>
    </lst>
  </requestHandler>

The update processor can also be specified per request with a parameter of update.chain=dedupe.

Note that for pre-Solr3.2 you need to use update.processor instead

Deleting Dead URLS & Files That No Longer Exist Nutch

Nutch allows you to crawl the web or a filesystem in order to build up index store of all its content. If your objective is to simply crawl the content once, it is fairly easy. But if you want to continuously monitor a site and crawl updates, it can be harder. Harder because the Nutch documentation does not have many details about that.

When you recrawl your source there can be a previously active URLs or Files that now no longer exist and you would like Nutch to remove them from its Indexes.  Nutch would update any changes made to documents it had indexed in the past but any files that had been deleted still remained in the indexes.

If you wish to skip straight to the solution then just go to the end of the post, but if you wish to understand what is happening then read on.

Nutch stores a record of all the files/urls it has encountered whilst doing its crawl and is called the crawldb.  Initially this is built from the list of urls/files provided by the user using the inject command which will be normally be taken from your seed.txt file.

Nutch uses a generate/fetch/update process:
generate:  This command looks at the crawldb for all the urls/files that are due for fetching and regroups them in a segment. A url/file is due for fetch if if it is new or the time has expired for that url/file and is now due for recrawling (default is 30 days).

fetch:  This command will go an fetch all the urls/files specified in the segment.

update:  This command will add the results of the crawling, which have been stored in the segment, into the crawldb and each url/file will be updated to indicate the time it wad fetched and when its next scheduled fetch is.  If any urls/files have been discovered, they will be added and marked as not fetched.

How does Nutch can detect if a page has changed or not? Each time a page is fetched, Nutch computes a signature for the page. At the next fetch, if the signature is the same (or if a 304 is returned by the web server because of the If-Modified-Since header), Nutch can tell if the page was modified or not (It is not just the content, if the http headers or metatags have changed it will be marked as modified).  If a document no longer exists it returns a 404 and will be marked DB_GONE. During the update cycle Nutch has the ability to purge all the those urls/files that have been marked DB_GONE.

The linkdb stores the finalised indexes that Nutch has generated from the crawl and this is the data that Nutch passes to the Solr Server during the solrindex process.

To tell Nutch that you would like all the urls/files that have been deleted you need to add the following code to your nutch-site.xml:

<property>
  <name>db.update.purge.404</name>
  <value>true</value>
  <description>If true, updatedb will add purge records with status DB_GONE
  from the CrawlDB.
  </description>
</property>

I hope this is of some help to you. 

Solr XSLT alternative with live links

The XSLT files that Solr has standard are pretty good and cover a wide variety of different transforms however they didnt give me exactly what I wanted.  I use Nutch as a filesystem crawler and therefore wanted the XSLT transform to make all the ur'ls(uri's) to the crawled files to be hyperlinks, so that when I clicked on them it would either download that file or allow me to view it in my browser.  I therefore decided that I would make my own XSLT file even though I had no previous experience using XSLT before so I have adapted the example.xsl file that comes standard with Solr and this is the final version:

<?xml version='1.0' encoding='UTF-8'?>
 
<xsl:stylesheet version='1.0'
    xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
>
 
  <xsl:output media-type="text/html" encoding="UTF-8"/> 
 
  <xsl:variable name="title" select="concat('Solr search results (',response/result/@numFound,' documents)')"/>
 
  <xsl:template match='/'>
    <html>
      <head>
        <title><xsl:value-of select="$title"/></title>
        <xsl:call-template name="css"/>
      </head>
      <body>
        <h1><xsl:value-of select="$title"/></h1>
        <div class="note">
          ******* Author: Allan Macmillan ******** Blog:amac4.blogspot.com ********* allan2.xls
        </div>
        <xsl:apply-templates select="response/result/doc"/>
      </body>
    </html>
  </xsl:template>
 
  <xsl:template match="doc">
    <xsl:variable name="pos" select="position()"/>
    <div class="doc">
      <table width="100%">
        <xsl:apply-templates>
          <xsl:with-param name="pos"><xsl:value-of select="$pos"/></xsl:with-param>
        </xsl:apply-templates>
      </table>
    </div>
  </xsl:template>
 
  <xsl:template match="doc/*[@name='score']" priority="100">
    <xsl:param name="pos"></xsl:param>
    <tr>
      <td class="name">
        <xsl:value-of select="@name"/>
      </td>
      <td class="value">
        <xsl:value-of select="."/>
 
        <xsl:if test="boolean(//lst[@name='explain'])">
          <xsl:element name="a">
            <!-- can't allow whitespace here -->
            <xsl:attribute name="href">javascript:toggle("<xsl:value-of select="concat('exp-',$pos)" />");</xsl:attribute>?</xsl:element>
          <br/>
          <xsl:element name="div">
            <xsl:attribute name="class">exp</xsl:attribute>
            <xsl:attribute name="id">
              <xsl:value-of select="concat('exp-',$pos)" />
            </xsl:attribute>
            <xsl:value-of select="//lst[@name='explain']/str[position()=$pos]"/>
          </xsl:element>
        </xsl:if>
      </td>
    </tr>
  </xsl:template>
 
  <xsl:template match="doc/arr" priority="100">
    <tr>
      <td class="name">
        <xsl:value-of select="@name"/>
      </td>
      <td class="value">
        <ul>
        <xsl:for-each select="*">
          <li><xsl:value-of select="."/></li>
        </xsl:for-each>
        </ul>
      </td>
    </tr>
  </xsl:template>
 
 
  <xsl:template match="doc/*">
    <tr>
      <td class="name">
        <xsl:value-of select="@name"/>
      </td>
      <td class="value">
      <xsl:variable name="var" select="."/>
      <xsl:choose>
         <xsl:when test="starts-with($var,'file:////')">
      <a>
            <xsl:attribute name="href">
               <xsl:value-of select="$var"/>
            </xsl:attribute>
     <xsl:value-of select="$var"/></a>
         </xsl:when>
  <xsl:otherwise>
             <xsl:value-of select="$var"/>
         </xsl:otherwise>
      </xsl:choose>
      </td>
    </tr>
  </xsl:template>
 
  <xsl:template match="*"/>
 
  <xsl:template name="css">
    <script>
      function toggle(id) {
        var obj = document.getElementById(id);
        obj.style.display = (obj.style.display != 'block') ? 'block' : 'none';
      }
    </script>
    <style type="text/css">
      body { font-family: "Lucida Grande", sans-serif }
      td.name { font-style: italic; font-size:80%; }
      td { vertical-align: top; }
      ul { margin: 0px; margin-left: 1em; padding: 0px; }
      .note { font-size:80%; }
      .doc { margin-top: 1em; border-top: solid grey 1px; }
      .exp { display: none; font-family: monospace; white-space: pre; }
    </style>
  </xsl:template>
 
</xsl:stylesheet>

XSLT Error For Solr - HTTP Status 500 - {msg=getTransformer fails in getContentType...

It is common to apply an XSLT stylesheet to an xml file to give it a cleaner look and Solr is no different coming with 4 pre-written xslt files for you to choose from.  

The error message XSLT Error For Solr - HTTP Status 500 - {msg=getTransformer fails in getContentType... is a common one and in most cases is easily fixed.  There are a few reasons that the error message appears:

1. You have not specified a stylesheet to use
          e.g http://localhost:8080/solr/select?q=*&wt=xslt
   Make sure you have specified a stylesheet and that the stylesheet you specified actually exists
          e.g http://localhost:8080/solr/select?q=*&wt=xslt&tr=example.xsl
   
2. A common mistake is to think that a stylesheet has a .xslt file extension - THEY DONT! The file extension is .xsl so be sure when you save your file and when you specify it in your query that you use .xsl
   
3. There are errors in you XSL file - Be careful if you are working in an environment that does not do syntax checking (notepad) - It is easy to forget to close a tag or make a spelling mistake.

Stack Trace:

 {msg=getTransformer fails in getContentType,trace=java.lang.RuntimeException: getTransformer fails in getContentType at org.apache.solr.response.XSLTResponseWriter.getContentType(XSLTResponseWriter.java:74) at org.apache.solr.servlet.SolrDispatchFilter.writeResponse(SolrDispatchFilter.java:623) at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:372) at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:155) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1852) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.io.IOException: 'tr' request parameter is required to use the XSLTResponseWriter at org.apache.solr.response.XSLTResponseWriter.getTransformer(XSLTResponseWriter.java:1

Setting Up Tika & Extracting Request Handler

Setting Up Tika's Extracting Request Handler

Some of this is covered in the set-up of Solr

Sometimes indexing prepared text files (such as XML, CSV, JSON, etc) is not enough. There are numerous situations where you need to extract data from binary files. For example, indexing PDF files – actually their contents. To do that we can use Apache Tika which comes built in with Apache Solr by using its ExtractingRequestHandler.

Preparation

You should have worked through the set-up for Solr prior to this point and can be found at:

http://amac4.blogspot.co.uk/2013/07/setting-up-solr-with-apache-tomcat-be.html  

If you wish to have a fully functioning file or web crawler using Nutch that Indexes to Solr then follow the next steps of the guide at:   

http://amac4.blogspot.co.uk/2013/07/configuring-nutch-to-crawl-urls.html   

http://amac4.blogspot.co.uk/2013/07/setting-up-nutch-to-crawl-filesystem.html   

http://amac4.blogspot.co.uk/2013/07/web-service-to-query-solr-rest.html  

Set-Up Guide

• In the $SOLR_HOME/collection1/conf/solrconfig.xml file there will be a section with heading - Solr Cell Update Request Handler. The code there should be updated or replaced to say:

<requestHandler name="/update/extract" class="solr.extraction.ExtractingRequestHandler">
  <lst name="defaults">
    <str name="fmap.content">text</str>
   <str name="lowernames">true</str>
   <str name="uprefix">attr_</str>
   <str name="captureAttr">true</str>
 </lst>
</requestHandler>

• Create an "extract" folder anywhere in the system, one option would be putting it in the solr_home folder. Then place the solr-cell-4.3.0.jar file in it from the $SOLR/dist. Then copy the contents of the $SOLR/contrib/extraction/lib/ folder into your extract folder.

• In the solrconfig.xml file add code for the directory you have chosen:

<lib dir="$SOLR_HOME/extract" regex=".*\.jar" />

• In the schema.xml file the <field name="text"…..> line needs edited to say

<field name="text" type="text_general" indexed="true" stored="true" multiValued="true"/>

• To test that it works open command prompt and navigate to any directory containing a pdf file and execute the following code replacing the filename with the file to be used:

curl "http://localhost:8080/solr/update/extract?literal.id=1&commit=true" -F "myfile=@FILENAME.pdf"

• If all has worked correctly then the following output should be displayed

<?xml version="1.0" encoding="UTF-8"?>
<response>
  <lst name="responseHeader">
    <int name="status">0</int>
    <int name="QTime">578</int>
  </lst>
</response>

Next Steps

You now have Solr configured properly and ready to use Tika to extract the data that you need.  The next step is configure Nutch, an open source web crawler that will crawl the web to find pages to index:  

http://amac4.blogspot.co.uk/2013/07/configuring-nutch-to-crawl-urls.html 

How It Works

Binary file parsing is implemented using the Apache Tika framework. Tika is a toolkit for detecting and extracting metadata and structured text from various types of documents, not only binary files but also HTML and XML files. To add a handler that uses Apache Tika, we need to add a handler based on the solr.extraction.ExtractingRequestHandler class to our solrconfig.xml file as shown in the example. In addition to the handler definition, we need to specify where Solr should look for the additional libraries we placed in the extract directory that we created. The dir attribute of the lib tag should be pointing to the path of the created directory. The regex attribute is the regular expression telling Solr which files to load. Let's now discuss the default configuration parameters. The fmap.content parameter tells Solr what field content of the parsed document should be extracted. In our case, the parsed content will go to the field named text. The next parameter lowernames is set to true; this tells Solr to lower all names that come from Tika and have them lowercased. The next parameter, uprefix, is very important. It tells Solr how to handle fields that are not defined in the schema.xml file. The name of the field returned from Tika will be added to the value of the parameter and sent to Solr. For example, if Tika returned a field named creator, and we don't have such a field in our index, then Solr would try to index it under a field named attrcreator which is a dynamic field. The last parameter tells Solr to index Tika XHTML elements into separate fields named after those elements. Next we have a command that sends a PDF file to Solr. We are sending a file to the /update/ extract handler with two parameters. First we define a unique identifier. It's useful to be able to do that during document sending because most of the binary document won't have an identifier in its contents. To pass the identifier we use the literal.id parameter. The second parameter we send to Solr is the information to perform the commit right after document processing.

Source Code

If you are unsure of anything then pop me an email and i can send you sample schema.xml and solrconfig.xml for you to use.