SQL Data Store

Overview

The Relevance Module in Bloomreach Content requires a SQL database to store visitor data, request log data, and statistics data.

Supported SQL Database Engines

The SQL data store must use a database engine that supports SQL MERGE (UPSERT) statements to ensure high performance and reliable storage for large data volumes.

The following SQL database engines are supported for production:

SQL Database Engine
MySQL (including Amazon RDS)
Oracle (including Amazon RDS)
PostgreSQL

Info: For specific supported versions, see the System Requirements.

For development, the embedded H2 database engine is used, as with the repository.

You can use the same SQL database server for both the Relevance SQL database and the repository if the server runs one of the supported engines. If not, set up a separate SQL database server for Relevance data.

Use a separate database (schema) for Relevance data. Do not store Relevance data in the same database (schema) as repository data, because their performance and storage requirements differ significantly.

Configuration

The Relevance SQL Store connects to its database using a JNDI data source, which must be defined at the application container level (for example, in Apache Tomcat).

Configure a separate JNDI data source for the Relevance SQL data store, similar to the JNDI data source configuration for the CMS repository database.

Below is an example of a MySQL-specific JNDI data source definition to add to Tomcat's conf/context.xml:

<Resource name="jdbc/targetingDS" auth="Container" type="javax.sql.DataSource"
          maxTotal="100" maxIdle="10" initialSize="10" maxWaitMillis="10000"
          testWhileIdle="true" testOnBorrow="false" validationQuery="SELECT 1"
          timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000"
          username="DBUSER" password="DBPASSWORD"
          driverClassName="com.mysql.cj.jdbc.Driver"
          url="jdbc:mysql://DBHOST:DBPORT/DBNAME?characterEncoding=utf8"/>
copy

Replace DBUSER, DBPASSWORD, DBHOST, DBPORT, and DBNAME with your specific database values.

Ensure that the database JDBC driver JAR is available in the Tomcat common (shared) classloader.

The Relevance-specific JNDI data source, for example jdbc/targetingDS, is referenced in the SQL Store configuration.

Configure the JNDI data source name for each Relevance SQL store (targetingdata, requestlog, statistics) as shown in the default configuration:

/targeting:targeting/targeting:datastores/targeting:targetingdata:
  dataSource: jdbc/targetingDS
  targeting:storefactoryclass: com.onehippo.cms.targeting.storage.sql.DelegatingSqlStoreFactory
/targeting:targeting/targeting:datastores/targeting:requestlog:
  dataSource: jdbc/targetingDS
  targeting:storefactoryclass: com.onehippo.cms.targeting.storage.sql.DelegatingSqlStoreFactory
/targeting:targeting/targeting:datastores/targeting:statistics:
  dataSource: jdbc/targetingDS
  targeting:storefactoryclass: com.onehippo.cms.targeting.storage.sql.DelegatingSqlStoreFactory
copy

The configured targeting:storefactoryclass (com.onehippo.cms.targeting.storage.sql.DelegatingSqlStoreFactory) looks up the JNDI data source and determines the database engine from the connection. It then delegates to the appropriate engine-specific SQLStoreFactory implementation.

On first access, the SQL Stores automatically create the required tables: visitors, requestlog, and personastatistics.

You can optionally prefix these table names by specifying the tablePrefix property. For example:

/targeting:targeting/targeting:datastores/targeting:targetingdata:
  dataSource: jdbc/targetingDS
  tablePrefix: hippo_
  targeting:storefactoryclass: com.onehippo.cms.targeting.storage.sql.DelegatingSqlStoreFactory
copy

This configuration results in the table name hippo_visitors.

As long as you do not change the JNDI data source names or table prefixes, the configuration remains valid across development, test, acceptance, and production environments. The actual database and settings are determined by the application container's JNDI data source configuration.

Configuration Properties

The following table lists all available configuration properties:

Property	Type	Default Value	Description
dataSource	String	n/a	JNDI data source as configured at container level.
tablePrefix	String	empty string	Optional prefix for table names.
targeting:storefactoryclass	String	n/a	Fully qualified name of the store factory Java class.
maxAgeDays	long	35 for requestlogs, 60 for targetingdata	Records older than this value (in days) are deleted. Set to 0 to retain all records.
cleanupJobCronTrigger¹	String	n/a	Cron expression for scheduling cleanup jobs. Cleanup jobs run only if maxAgeDays > 0. If not set, jobs run every hour.

¹ Available since version 13.4.0.

You can configure operational processing parameters (such as the number of synchronous retrieve or asynchronous store threads and retrieve timeout) in the Visitor Service configuration.

Example Development Configuration

For initial development using the embedded H2 database, use the following JNDI data source configuration in your implementation project's conf/context.xml:

<Resource name="jdbc/targetingDS" auth="Container" type="javax.sql.DataSource"
          maxTotal="100" maxIdle="10" initialSize="10" maxWaitMillis="10000"
          testWhileIdle="true" testOnBorrow="false" validationQuery="SELECT 1"
          timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000"
          username="sa" password=""
          driverClassName="org.h2.Driver"
          url="jdbc:h2:${repo.path}/targeting/targeting"/>
copy

This configuration stores the targeting H2 database as a sibling of the repository database in the same storage folder (${repo.path}).

The H2 database JAR is included by default and does not require additional deployment steps.

If you use another supported database for local development, configure the Maven Cargo Plugin in your project root pom.xml (within the cargo.run profile) to deploy the database driver to the Cargo "extra" classpath (Tomcat common/lib folder). For example, to add the MySQL driver:

...
<profile>
  <id>cargo.run</id>
  <dependencies>
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>${mysql-connector.version}</version>
      <scope>provided</scope>
    </dependency>
  </dependencies>
  ...
  <build>
    <plugins>
      ...
      <plugin>
        <groupId>org.codehaus.cargo</groupId>
        <artifactId>cargo-maven3-plugin</artifactId>
        <configuration>
          ...
          <container>
            <dependencies combine.children="append">
              <dependency>
                <groupId>com.mysql</groupId>
                <artifactId>mysql-connector-j</artifactId>
                <classpath>extra</classpath>
              </dependency>
              ...
copy