Data Tool¶

accessControl¶

The accessControl element is optional. If it is present, the HTTP basic authentication will be used to restrict access the Data Tool URLs.

It accepts any number of child user elements. Each must have name and password attributes.

DataSources¶

A DataSource describes how the Data Tool should connect to the database. You can use a javax.sql.DataSource or java.sql.Driver.

<dataSource name="dataSource"
  jar="drivers/mysql-connector-java-5.1.10-bin.jar"
  class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
  <url>jdbc:mysql://localhost/test</url>
  <user>test</user>
  <password>test</password>
</dataSource>

<driver name="dataSource"
  jar="drivers/mysql-connector-java-5.1.10-bin.jar"
  class="com.mysql.jdbc.Driver">
  <url>jdbc:mysql://localhost/test</url>
  <username>test</username>
  <password>test</password>
</driver>

<dataSource name="dataSource"
  jar="drivers/jtds-1.2.5.jar"
  class="net.sourceforge.jtds.jdbcx.JtdsDataSource">
  <!-- 1: sqlserver, 2: sybase -->
  <serverType>1</serverType>
  <serverName>localhost</serverName>
  <databaseName>test</databaseName>
  <user>test</user>
  <password>test</password>
</dataSource>

<driver name="dataSource"
  jar="drivers/jtds-1.2.5.jar"
  class="net.sourceforge.jtds.jdbc.Driver">
  <url>jdbc:jtds:sqlserver://localhost/test</url>
  <username>test</username>
  <password>test</password>
</driver>

Profiles¶

Profiles describe how the state about changeset pulls should be stored.

Profiles are expected to exist before use. The Discovery Data Tool does not automatically create them unless you provide the SQL for this in a createSql element.

create table changeset_profile (
  name varchar(20) not null unique primary key,
  last_run timestamp
)

<sqlProfile name="profile" dataSource="dataSource">
  <!--
    createSql is optional. By including it we enable automatic profile creation.
  -->
  <createSql>
    insert into changeset_profile (name) values (:name)
  </createSql>
  <retrieveSql startColumn="startTime" endColumn="endTime">
    select last_run as startTime, CURRENT_TIMESTAMP as endTime
    from changeset_profile
    where name = :name
  </retrieveSql>
  <updateSql>
    update changeset_profile
    set last_run = :lastRun
    where name = :name
  </updateSql>
</sqlProfile>

Publishers¶

A publisher is a named entry point for pulling changesets. Each publisher is accessible via a different URL based on its name.

http://localhost:8089/ws/publisher/production
http://localhost:8089/ws/publisher/stage
http://localhost:8089/ws/publisher/test

<sqlPublisher name="production"
  dataSource="dataSource" profile="profile">
  ...
</sqlPublisher>

<delta>
  <set-item idColumn="id">
    <query><![CDATA[
    select * from items where last_updated >= :start and last_updated < :end
  ]]></query>
  </set-item>
</delta>

<delta>
  <remove-item idColumn="id">
    <query><![CDATA[
    select id from deleted_items where last_updated >= :start and last_updated < :end
  ]]></query>
  </remove-item>
</delta>

<delta>
  <remove-item idColumn="id">
    <query><![CDATA[
    select id from deleted_items where last_updated >= :start and last_updated < :end
  ]]></query>
  </remove-item>
</delta>

<snapshot>
  <set-item idColumn="id">
    <query><![CDATA[
    select * from items
  ]]></query>
  </set-item>
</snapshot>

<snapshot>
  <set-item idColumn="id">
    <query><![CDATA[
    select * from items
  ]]></query>
  </set-item>
  <remove-item idColumn="id">
    <query><![CDATA[
    select id from deleted_items
  ]]></query>
  </remove-item>
</snapshot>

<delta>
  <set-item idColumn="id">
    <query><![CDATA[
    call get_updated_items(:start, :end)
  ]]></query>
  </set-item>
</delta>

<changeset>
  <set-item id="007">
    <properties>
      <struct>
        <entry name="ratings">
          <array>
            <element><int>1</int></element>
            <element><int>5</int></element>
          </array>
        </entry>
      </struct>
    </properties>
  </set-item>
</changeset>

create table vertical_master_items (
  id varchar(255) primary key not null,
  lastUpdated timestamp not null default current_timestamp);

create table vertical_data (
  id varchar(255) not null references vertical_master_items(id),
  name varchar(255),
  value varchar(255));

<sqlPublisher name="test-simple" dataSource="dataSource">
  <delta>
    <set-item idColumn="id">
      <query><![CDATA[
        SELECT data.id, data.name, data.value
        FROM vertical_master_items master
        JOIN vertical_data data on data.id = master.id
        WHERE lastUpdated >= :start and lastUpdated < :end
        ORDER BY data.id
    ]]></query>
      <merge-columns keyColumn="name" valueColumn="value"/>
    </set-item>
  </delta>
</sqlPublisher>

<changeset>
  <set-item id="1">
    <properties>
      <struct>
        <entry name="id">
          <string>1</string>
        </entry>
        <entry name="color">
          <string>red</string>
        </entry>
      </struct>
    </properties>
  </set-item>
  <set-item id="2">
    <properties>
      <struct>
        <entry name="id">
          <string>2</string>
        </entry>
        <entry name="color">
          <array>
            <element>
              <string>orange</string>
            </element>
            <element>
              <string>yellow</string>
            </element>
          </array>
        </entry>
      </struct>
    </properties>
  </set-item>
</changeset>

<subquery><![CDATA[
  select name as color from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery type="delimited" delimiter="|"><![CDATA[
  select name as color_delim from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-default"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-array" type="array"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-delimited" type="delimited"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-delimited" type="delimited" delimiter="|"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="address" discriminator="addr_type"><![CDATA[
  select addr_type, street, city, state, zipcode
  from SubqueryAddress
  where parent_id=:id
  order by id
]]></subquery>
<subquery propertyPrefix="product"><![CDATA[
  select id, price, description from order_lines where order_id=:id order by id
]]></subquery>

Create the RSA key, Certificate and Java Key Store¶

The full process for creating and RSA key, X.509 certificate and Java Key Store is well documented on the Internet. These minimal steps will get you started with a self-signed certificate. You can substitute the name datatool with anything you want. Note that you need to have openssl and Java 1.5 or newer installed.

# Generate RSA key.
# Note that the Common Name must match the host name that will be in the public
# feed URL for the Data Tool
openssl genrsa -des3 -out datatool.key

# Create x509 certificate from the key
openssl req -new -x509 -key datatool.key -out datatool.crt

# Combine the key(s) and the certificate into the a single pkcs12 file
openssl pkcs12 -inkey datatool.key -in datatool.crt -export -out datatool.pkcs12

# create a Java key store and import the key and certificate
keytool -importkeystore -srckeystore datatool.pkcs12 -srcstoretype PKCS12 \
    -destkeystore datatool.jks

You will need to place the keystore file datatool.jks in a location where the Data Tool will be able to read it when it starts up.

Configuration options in datatool.properties¶

The Discovery Data Tool is configured with a datatool.properties file which is read by the discovery_datatool shell script and used to launch the application.

The following properties may be specified (none are required).

http_port

Port on which to listen for HTTP. Defaults to 8089.

bind_address

TCP address to bind to. Listens on all interfaces if not specified.

https_port

Port on which to listen for HTTPS. If not specified, then Discovery Data Tool will not serve HTTPS.

keystore_file

Path the a Java Key Store file containing the server certificate and private key. Required for HTTPS.

keystore_pass

Password for the Java Key Store file containing the server certificate and private key. Required for HTTPS.

key_pass

Password for the server private key. Required if your key has a password.

truststore_file

Path to a Java Key Store file containing trusted client certificates. Required if you want to verify SSL client certificates.

truststore_pass

Password for the trust store. Required if using a truststore_file.

jvm.memory

Maximum heap to allocate in megabytes. Uses JVM default if not specified.

jvm.logging

Override for specifying logging configuration. Example -Djava.util.logging.config.file=/special_location/logging.properties. If unspecified and a logging.properties file exists in the same directory as the datatool.properties, then that file is used, otherwise the logging.properties from the Discovery Data Tool release directory is used.

validate_sql

Whether the Discovery Data Tool should validate its sqlPublishers’ SQL statements against the database. While no results are retrieved, this option can slow application start-up. Defaults to true. Set to false to disable validation.

Design the Data Integration¶

Start by determining what your key business entities are. How are they related? What do you plan to search and display. Remember that the Discovery Engine cannot perform joins, so each item that you include in your changeset must include all the data that you want to use for search or display - including any data from related entities. Based on your proposed searches, go ahead and create a dimensions.xml that you’ll use to validate your initial changeset. It’s an iterative process, so this is just a starting point.

If you want to support processing incremental changes in the Discovery Engine, then your entities will need a last-modified timestamp and you will need some mechanism for tracking when items are deleted. Database triggers can be useful to ensure that last-modified timestamps are updated appropriately - especially if you choose to cascade updates on dependent objects to your main items (e.g. updating a broker updates the last-modified timestamp on all of the broker’s listings).

Tracking deletes¶

For tracking deleted items, two strategies are soft deletion and a deleted items table. To implement soft deletion, rows are not actually deleted, and a status flag is updated instead. All queries include the status flag so that you don’t inadvertandly include deleted items. If you want to implement a hard delete, where your items are actually deleted, a database trigger can add a row to a deleted items table each time an item is deleted. The deleted items table contains, at a minimum, the item identifier and the timestamp of when the item was deleted. With either of these mechanism, it is trivial to create SQL queries that return the list of item identifiers that were deleted during a specific timeframe.

A note about identifiers¶

Item identifiers in the Discovery Engine must be unique. In an SQL databae, it is common for each table to have its own primary key sequence. In the engine, all items share a global identifier namespace (as if they were all in one “table”), so you cannot simply use the database primary key as your item id. You can, however, trivially modify your database identifier by appending or prepending a type discriminator (e.g. “broker_1” instead of just “1”).

Let’s write some SQL¶

Once you’ve considered all the above points, you’re ready to build and test the SQL that you will use to export your entities in a denormalized form for your changesets, and the SQL that you will use to export the current deleted items list. Pay particular attention to the scalability of your queries as the size of your data may increase over time. You can test these initial SQL queries out in the tool of your choice.

Remeber that there are two kinds of changesets that you can choose to support: complete changesets and incremental changesets.

Complete changesets (snapshot, bulk, full), contain all the data for all the items that should be live. As such, there is no need to include a remove-item query - unless you need to remove items that you just created (and this is not permitted in bulk changesets). Complete changesets have an :end timestamp, but do not have a :start timestamp.

Incremental changests (detla), only contain information about items that were modified or deleted since the last incremental changeset. Use the :start and :end bound parameters in your SQL. Note that the general approach is WHERE lastUpdated >= :start and lastUpdated < :end.

Testing with the Discovery Data Tool and the Discovery Engine¶

After you write your basic SQL, create a discovery_dataool.xml with the building blocks described in this document. If you want to create incremental changesets, then you must define a sqlProfile. Create one or more sqlPublisher s and wire everything together so that you can test your SQL in the Data Tool and test the changesets it generates with your engine. Afterall, what good is a changeset if you cannot search or retrieve the data like you planned.

For testing, you can use curl on the command line, redirecting the output to a file. You can test the data tool without parameters to get a complete set of data, and test using a profile. I often use the dryRun=true parameter when testing a profile so that the profile will not be updated in the database. This way you can repeatedly produce delta changesets with the same :start and :end to test your SQL changes against the same subset of items.

If you have access to modify the database, then you can also delete or update the test profiles that you used to test incremental feeds. If you only have read-only db access, then you can still test the delta queries even without a profile by using the startDate and endDate parameters. It should now be clear that many of these parameters exist solely to enable testing.

To test a complete changeset (e.g. full, snapshot, or a delta with all data);

$ curl  'http://localhost:8089/ws/publisher/{publisherName}' > cs.xml

To test a delta with a profile:

$ curl  'http://localhost:8089/ws/publisher/{publisherName}?profile=testProfile&dryRun=true' > cs.xml

When you use curl -qi, the HTTP response headers are included in the output. This is useful for confirming that the changeset type is as expected (delta, full, etc) by looking at the X-t11e-type header. If you use curl -qi, you must need to edit the output to remove the headers in order to use the changeset (or it will not be valid XML).

Query string parameters useful for testing are:

startDate

Test delta with :start SQL bound parameter in lieu of using a profile

endDate

Test delta with :end SQL bound parameter in lieu of using a profile

profile

Refers to the profile stored in the database. Depending on the datatool configuration, profiles may be auto-created

dryRun=true

Do not update the profile database row. This way you can repeatedly test the same delta timespan window

forceSnapshot=true

Generate a complete feed. Corresponds to the “Restart with complete feed now” engine feed UI checkbox.

Note that startDate and endDate use the date time format yyyy-MM-dd'T'HH:mm:ss

Some things that we recommend testing¶

1. That the sql doesn’t have any runtime errors.

   The datatool will validate the sql at startup using the database/driver to parse it, and fail to start if invalid. To fully validate the sql you also need to pull data through it. If you have subqueries, then you’ll want to test scenarios where your subqueries return 0, 1, and >1 rows. This is just basic branch coverage of your queries. If your sql has conditions (e.g. case, if) you’ll want to test all the permutations.

2. The changeset contains all the expected data in the structure that you expect (e.g. if you’re using subqueries, did you want parallel arrays of primitives but got an array of dictionaries)

3. Deltas using a profile only contain the modified and deleted items that you expect

4. All the publishers and sql are used. If you have different SQL for a <full> and <delta> in a publisher, then you need to request and generate both kinds of changesets in order to test the SQL for both.

5. The XML is valid by running it though xmllint

6. The changeset can be processed by the engine as expected. You can manually upload the XML via the admin UI. This sometimes uncovers errors that you missed. For example, if you didn’t notice that the XML was invalid due to an exception.

   Upload a dimensions file, and verify that the data in the changeset can be indexed and queried as expected. The data might be in the changeset, but not in a manner than can be indexed, or maybe there’s a mismatch in property names between the dimension and the changeset.

7. Incremental feeds to the engine work as expected. Configure a recurring feed for the engine via the admin UI using your data tool, make data changes, and verify that the changes reach your engine.

If you request a changeset and the data tool encounters a runtime error in the middle (e.g. sql that failed in a subquery part of the way through), then the resulting changeset will be invalid with the exception trace included in the generated output.

Logging¶

You will see most Data Tool errors in the log/datatool-0.log file, especially errors validating the SQL at startup. The datatool will not start if the SQL fails to validate. When posting a changeset to your Discovery Engine, you may see some errors processing the changeset immediately in the Discovery Engine HTTP response, but some errors will only appear in the Discovery Engine log file log/discovery-0.log.