Configuring the Solr DataImportHandler

First, download the Postgres JDBC. JDBCs interface databases and Java. Install Solr first too. Go to that directory.

$ bin/solr create_core -c lasflores

This will create a new Solr Core for named "lasflores." Cores have their own indices and configuration files. It's an atomic project. The core is stored in the Solr download folder ./server/solr/lasflores. Inside there there's a conf directory and a data directory. data holds the index, conf the configurations. The main file to edit is the solrconfig.xml in conf. First, though, go to http://localhost:8983/solr/#/. You'll notice under the cores section that lasflores is now a core.

Now open up the solrconfig.xml in a text editor. Add these two lines where you see all the <lib... /> tags:

<lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-\d.*\.jar" />
<lib dir="${solr.install.dir:../../../..}/dist/" regex="postgresql-\d.*\.jar" />

The first retrieves the jar for the DataImportHandler. That allows imports from sources like SQL databases, email servers and HTTP requests. The second is the aforementioned JDBC. Proceed to put that into the right folder:

$ cd /path/to/solr/download/folder/server/solr/dist
$ mv ~/Downloads/postgresql-42.2.2.jar .

Now everything's in the right place. Refresh the Solr core (in the webpage) to make sure it's still okay.

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

This creates the dataimport API function. Notice the db-data-config.xml file. It doesn't exist yet. Create it in the conf directory. Put this in it:

<dataConfig>
  <dataSource driver="org.postgresql.Driver"
    url="jdbc:postgresql://localhost:5432/lasflores"
    user="michael"
    password="postgres" />
  <document></document>
</dataConfig>

Fill it out with your credentials. The empty document section is for SQL stuff. Since we haven't completely filled out the database yet, it's hard to say exactly what'll go in here.

Refresh the core and make sure it hasn't fallen apart yet.

As I mentioned in the Examining the Database article, the document area of the config can be altered to take this form:

  <document name="construction">
    <entity name="construccion" query="select * from construccion">
      <field column="id" name="id" />
      <field column="geom" name="geometry" />
      <field column="OBJECTID" name="objectID" />
      <field column="TERRENO_CODIGO" name="terrenoCodigo" />
      <field column="TIPO_CONSTRUCCION" name="tipoConstruccion" />
      <field column="TIPO_DOMINIO" name="tipoDominio" />
      <field column="NUMERO_PISOS" name="numeroPisos" />
      <field column="NUMERO_SOTANOS" name="numeroSotanos" />
      <field column="NUMERO_MEZANINES" name="numeroMezanines" />
      <field column="NUMERO_SEMISOTANOS" name="numeroSemisotanos" />
      <field column="ETIQUETA" name="etiqueta" />
      <field column="IDENTIFICADOR" name="identificador" />
      <field column="CODIGO_EDIFICACION" name="codigoEdificacion" />
      <field column="CODIGO_ANTERIOR" name="codigoAnterior" />
      <field column="SHAPE.AREA" name="shapeArea" />
      <field column="SHAPE.LEN" name="shapeLen" />
      <field column="wkt" name="wellKnownText" />
    </entity>
  </document>

This just imports the full construccion table.

N.B.: this is only a full import and is not suitable for continued use. We should be only using a delta import after the first full import.

One problem I ran into was that the conf directory couldn't be read by the solrconfig.xml parser, so the data importer couldn't read the db-data-config.xml file, causing a failure. You can fix this easily using $ chmod -R a+w conf/ (Credit: this post).

That's it! Now crack this open in the browser: http://localhost:8983/solr/lasflores/dataimport?command=full-import. You'll get a response that looks like this:

{
  "responseHeader":{
    "status":0,
    "QTime":2},
  "initArgs":[
    "defaults",[
      "config","db-data-config.xml"]],
  "command":"full-import",
  "status":"idle",
  "importResponse":"",
  "statusMessages":{
    "Total Requests made to DataSource":"1",
    "Total Rows Fetched":"2388",
    "Total Documents Processed":"2388",
    "Total Documents Skipped":"0",
    "Full Dump Started":"2018-04-09 03:39:06",
    "":"Indexing completed. Added/Updated: 2388 documents. Deleted 0 documents.",
    "Committed":"2018-04-09 03:39:06",
    "Time taken":"0:0:0.270"}}

And it only took a quarter of a second. Not bad for 2400 documents.