SqlCommitter

Commit documents to an SQL table.

committer/sqlJavadoc: SqlCommitter

Notes

Commits crawled documents to a relational database table using JDBC. Supports dynamic table and field creation, field mappings, field name and value sanitization, and multi-value field joining.

Examples

Writes documents into a PostgreSQL table, creating it on first use and sanitizing field names that SQL will not accept.

YAML

committer:
  class: SqlCommitter
  connectionUrl: jdbc:postgresql://localhost:5432/crawl
  driverClass: org.postgresql.Driver
  credentials:
    username: crawler
    password: changeme
  tableName: documents
  primaryKey: id
  createTableSQL: CREATE TABLE documents (id VARCHAR(2048) NOT NULL PRIMARY KEY, content CLOB)
  createFieldSQL: ALTER TABLE documents ADD {{field}} VARCHAR(8000)
  fixFieldNames: true

JSON

{
  "committer": {
    "class": "SqlCommitter",
    "connectionUrl": "jdbc:postgresql://localhost:5432/crawl",
    "driverClass": "org.postgresql.Driver",
    "credentials": {
      "username": "crawler",
      "password": "changeme"
    },
    "tableName": "documents",
    "primaryKey": "id",
    "createTableSQL": "CREATE TABLE documents (id VARCHAR(2048) NOT NULL PRIMARY KEY, content CLOB)",
    "createFieldSQL": "ALTER TABLE documents ADD {{field}} VARCHAR(8000)",
    "fixFieldNames": true
  }
}

XML

<committer class="SqlCommitter">
  <connectionUrl>jdbc:postgresql://localhost:5432/crawl</connectionUrl>
  <driverClass>org.postgresql.Driver</driverClass>
  <credentials>
    <username>crawler</username>
    <password>changeme</password>
  </credentials>
  <tableName>documents</tableName>
  <primaryKey>id</primaryKey>
  <createTableSQL>CREATE TABLE documents (id VARCHAR(2048) NOT NULL PRIMARY KEY, content CLOB)</createTableSQL>
  <createFieldSQL>ALTER TABLE documents ADD {{field}} VARCHAR(8000)</createFieldSQL>
  <fixFieldNames>true</fixFieldNames>
</committer>

Usage

Full configuration skeleton, with every option and its default

YAML

class: SqlCommitter
connectionUrl: string
createFieldSQL: string
createTableSQL: string
credentials:
  password: string
  passwordKey:
    size: 0
    source: KEY
    value: string
  username: string
driverClass: string
driverPath: string
fieldMappings: {}
fixFieldNames: false
fixFieldValues: false
multiValuesJoiner: string
primaryKey: string
properties:
  caseInsensitiveKeys: false
queue:
  class: FsQueue
  batchSize: 0
  commitLeftoversOnInit: false
  maxPerFolder: 0
  onCommitFailure:
    ignoreErrors: false
    maxRetries: 0
    retryDelay: 0
    splitBatch: OFF
restrictions:
  - fieldMatcher: {}
    valueMatcher: {}
tableName: string
targetContentField: string

JSON

{
  "class": "SqlCommitter",
  "connectionUrl": "string",
  "createFieldSQL": "string",
  "createTableSQL": "string",
  "credentials": {
    "password": "string",
    "passwordKey": {
      "size": 0,
      "source": "KEY",
      "value": "string"
    },
    "username": "string"
  },
  "driverClass": "string",
  "driverPath": "string",
  "fieldMappings": {},
  "fixFieldNames": false,
  "fixFieldValues": false,
  "multiValuesJoiner": "string",
  "primaryKey": "string",
  "properties": {
    "caseInsensitiveKeys": false
  },
  "queue": {
    "class": "FsQueue",
    "batchSize": 0,
    "commitLeftoversOnInit": false,
    "maxPerFolder": 0,
    "onCommitFailure": {
      "ignoreErrors": false,
      "maxRetries": 0,
      "retryDelay": 0,
      "splitBatch": "OFF"
    }
  },
  "restrictions": [
    {
      "fieldMatcher": {},
      "valueMatcher": {}
    }
  ],
  "tableName": "string",
  "targetContentField": "string"
}

XML

<sqlCommitter>
  <class>SqlCommitter</class>
  <connectionUrl>string</connectionUrl>
  <createFieldSQL>string</createFieldSQL>
  <createTableSQL>string</createTableSQL>
  <credentials>
    <password>string</password>
    <passwordKey>
      <size>0</size>
      <source>KEY</source>
      <value>string</value>
    </passwordKey>
    <username>string</username>
  </credentials>
  <driverClass>string</driverClass>
  <driverPath>string</driverPath>
  <fieldMappings/>
  <fixFieldNames>false</fixFieldNames>
  <fixFieldValues>false</fixFieldValues>
  <multiValuesJoiner>string</multiValuesJoiner>
  <primaryKey>string</primaryKey>
  <properties>
    <caseInsensitiveKeys>false</caseInsensitiveKeys>
  </properties>
  <queue>
    <class>FsQueue</class>
    <batchSize>0</batchSize>
    <commitLeftoversOnInit>false</commitLeftoversOnInit>
    <maxPerFolder>0</maxPerFolder>
    <onCommitFailure/>
  </queue>
  <restrictions>
    <restriction>
      <fieldMatcher/>
      <valueMatcher/>
    </restriction>
  </restrictions>
  <tableName>string</tableName>
  <targetContentField>string</targetContentField>
</sqlCommitter>

Properties

Property	Type	Required	Default
connectionUrl	string	No	-
createFieldSQL	string	No	-
createTableSQL	string	No	-
credentials	Credentials	No	Credentials
driverClass	string	No	-
driverPath	string	No	-
fieldMappings	Record<string, string>	No	-
fixFieldNames	boolean	No	false
fixFieldValues	boolean	No	false
multiValuesJoiner	string	No	|
primaryKey	string	No	-
properties	Record<string, string[]>	No	-
queue	CommitterQueue	No	FsQueue
restrictions	PropertyMatcher[]	No	-
tableName	string	No	-
targetContentField	string	No	content

Property Details

connectionUrl

JDBC connection URL.

createFieldSQL

The ALTER statement used to create missing table fields. Use {tableName} to represent the table name and {fieldName} to represent newly encountered field names.

createTableSQL

The CREATE statement used to create a table if it does not already exist. If you need fields of specific data types, specify them here. Use {tableName} and {primaryKey} to represent the table name and primary key, respectively.

credentials

Username and password to a protected database.

driverClass

Class name of the JDBC driver to use.

driverPath

Path to JDBC driver .jar file. Not required if already in classpath (i.e., crawler's lib folder).

fieldMappings

Mapping of source fields to target ones (table field names).

fixFieldNames

Attempt to prevent insertion errors due to unsupported characters in field names by converting them to underscores. Convert any first character that is not alphanumeric and any following characters that are not underscore or alphanumeric.

fixFieldValues

Attempt to prevent insertion errors by truncating values that are larger than their defined maximum field length.

multiValuesJoiner

Characters used to join a multi-valued field into the single value stored in its column. Default is |. Choose something that cannot appear in your values if you intend to split them apart again later.

primaryKey

The name of the table primary key field where to store the document reference value. The document reference from the source document is used unless the source document has a field matching this name. Then the matching source field will be used to populate the primary key. If for some reason it is not possible to define a table field as the primary key, at a minimum, the target field should be "unique" and ideally "indexed".

properties

Additional connection properties as per database vendor specifications.

queue

Configures the committer queue used to batch documents before sending them to the target system. Adjust queue settings to tune throughput and memory usage.

restrictions

Defines filter conditions that restrict which documents this committer processes. Documents not matching the restrictions are ignored by this committer.

tableName

The target database table name where documents will be committed.

targetContentField

Table field name where to store the document content. Leave blank if you do not want to store the document content. Since document content can sometimes be quite large, a CLOB field (or equivalent) is usually advised. If the source document contains a field of the same name, that field value will take precedence over the document content and the document content is ignored. Default is "content".