Read Me First!

Before reading this document, it is recommended that Managing Adaptors is read as it contains information about the entire process of configuring and deploying an Adaptor, whereas this document deals specifically with the off-the-shelf YOUnite Temporal Adaptor.

Introduction

The YOUnite Temporal Adaptor is an off-the-shelf adaptor that accepts data events from YOUnite and stores them in a database, indexed by timestamp. It also accepts requests from YOUnite to retrieve the state of a data record at a particular timestamp.

Supported Data Sources

The YOUnite Temporal Adaptor supports many popular databases using the open source QueryDSL project to assist with translations to the various SQL dialects of different databases. See the JDBC Configuration section below for a list of databases that are supported.

Even if a database is not in the list of supported databases, it may still be compatible with the YOUnite Temporal Adaptor if:

  1. It has a JDBC driver and can process normal SELECT, INSERT and UPDATE operations.

  2. If it meets these criteria, the database type that most closely matches may be used. See the property template.name in the JDBC Adaptor section.

  3. If the above does not work, a custom Java class can be developed to implement the proper translations - contact YOUnite for additional information.

In addition to JDBC data sources, the YOUnite Temporal Adaptor supports MongoDB.

Configuration

Adaptor Configuration

Before starting on this section, read Managing Adaptors, which discusses how to create and retrieve the adaptor credentials. Once the adaptor credentials have been retrieved, the following properties must be configured either as environment variables or included in the adaptor.properties file.

Required properties:

Property Environment Variable Description Example Value

adaptor.uuid

ADAPTOR_UUID

UUID of the adaptor

3dfcc03d-e5d4-4d57-9e9b-5c5d2db32f9a

client.id

CLIENT_ID

Username used to connect to message bus

8c9167a6-bb83-4f77-bdfc-1947a946f77b

client.secret

CLIENT_SECRET

Password used to connect to message bus

de02e3fa-4b23-46cb-aed6-5665a16e73d3

message.bus.url

MESSAGE_BUS_URL

Message Bus URL

tcp://message-bus-uri:61617

auth.server.url

AUTH_SERVER_URL

OAuth Server to validate adaptor access credentials. The YOUnite Server runs an embedded OAuth server that your implementation may be using. By default it runs on port 8080 so, in this case the value would be http://ip-address-of-the-YOUnite-Server:8080

http://oauth-server-uri

Optional properties:

Property Environment Variable Description Default Value

hostname

HOSTNAME

Name of the host for the health check endpoint. Note that Kubernetes sets this environment variable by default.

localhost

port

PORT

HTTP Port to expose for health checks. The health check endpoint at /health, ie GET host:8001/health

8001

concurrency

CONCURRENCY

Concurrency for incoming data messages, ie, number of threads that will handle incoming messages. Note that this could lead to messages being processed out of order, though they are guaranteed to be processed in order based on their message grouping (the default is unique ID of the record, ie the DR UUID).

1

ops.concurrency

OPS_CONCURRENCY

Concurrency for incoming ops messages, ie, number of threads that will handle incoming messages. These are messages with adaptor status information and metadata, request for stats, shutdown requests. Expressed as a range, ie "1-5" or a single value indicating the maximum (with an implied minimum of 1).

1

assembly.concurrency

ASSEMBLY_CONCURRENCY

Concurrency for incoming assembly messages, ie, number of threads that will handle incoming messages. These are federated GET requests. Expressed as a range, ie "1-5" or a single value indicating the maximum (with an implied minimum of 1).

1

session.cache.size

SESSION_CACHE_SIZE

JMS session cache size.

10

enable.metrics

ENABLE_METRICS

Enable / disable displaying metrics at a given interval.

true

metrics.log.interval

METRIC_LOGS_INTERVAL

Interval in milliseconds to display metrics.

3600000 (one hour)

n/a

LOG_LEVEL

Log level for YOUnite logback messages. Applies to younite-db-adaptor implementations, but not the Adaptor SDK. Must come from an environment variable.

INFO

n/a

ROOT_LOG_LEVEL

Log level for non-YOUnite logback messages. Applies to younite-db-adaptor implementations, but not the Adaptor SDK. Must come from an environment variable.

INFO

Example:

# Configuration

# Adaptor UUID
adaptor.uuid = 3dfcc03d-e5d4-4d57-9e9b-5c5d2db32f9a

# ClientID and Secret to be used by JMS to connect to the message bus
client.id = 8c9167a6-bb83-4f77-bdfc-1947a946f77b
client.secret = de02e3fa-4b23-46cb-aed6-5665a16e73d3

# Message Bus URL
message.bus.url = tcp://192.2.200.25:61616

# Optional configuration
concurrency = 4
ops.concurrency = 1-4
assembly.concurrency = 2-2
session.cache.size = 10

Health Check Endpoint

The adaptor includes a HTTP endpoint to check the health of the adaptor at /health. The default port is 8001.

If the adaptor is healthy, it will return a 200 OK response when querying the endpoint. If it’s not healthy, it will return a 503 Service Unavailable. For example:

GET http://adaptor-host-name:8001/health

{
  "status": "UP"
}

In addition, it will return some information about the adaptor such as the status of each service and the uptime. For example, if the employee v1 domain handler is not working, the adaptor might return something like this:

GET http://adaptor-host-name:8001/health

{
  "status": "DOWN",
  "adaptorService": "UP",
  "upTimeMilliseconds": 100000,
  "customer:1": "UP",
  "employee:1": "DOWN"
}

Adaptor Metadata Configuration

The Adaptor Metadata Configuration contains the rest of the configuration for the YOUnite Temporal Adaptor. This configuration is stored in JSON format in the metadata of an adaptor. See Managing Adaptors, which discusses how to assign metadata to an adaptor, which can be done when the adaptor is created or later by updating the adaptor metadata in the UI or via the API.

You can jump ahead to a full metadata example or, read the specifications below:

Metadata Schema Specification

The top level of the metadata schema contains a list of databases and tables.

{
    "databases": { "string" : {DatabaseConnection}, ... },
    "tables": [ {Table}, ... ]
}
Property Description Type Default Value Required

databases

Key = data source name, value = DatabaseConnection.

Object of String → DatabaseConnection

none

yes

tables

List of table specifications.

List of Table

none

yes
DatabaseConnection

A DatabaseConnection contains connection information for a database referenced by a Table:

{
    "type": "string",
    "properties": { "string": object, ... }
}
Property Description Type Default Value Required

type

Database type. Supported values are JDBC or MONGO.

String

none

yes

properties

List of properties for the connection.

Object of String → Object

none

yes

Notes:

  1. Properties are specific to the database type. See JDBC Configuration and MongoDB Configuration for information about what the properties mean.

Table

A Table is the specification of an entity that contains data:

{
    "domain": "string",
    "version": integer,
    "dataSource": {DataSource},
    "dataColumns": { "string": {Column}, ... },
    "drUuidColumnName": "string",
    "sourceAdaptorColumnName": "string",
    "timestampColumnName": "string",
    "timestampColumnType": "string",
    "actionColumn": "string",
    "autoLinkDrs": boolean
}
Property Description Type Default Value Required

domain

Data domain name defined in YOUnite that this table maps to.

String

none

yes

version

Data domain version defined in YOUnite that this table maps to.

Integer

none

yes

dataSource

Data source for this table.

DataSource

none

yes

dataColumns

Column names and specifications of the data columns in the table that will map to a YOUnite domain version's schema. domain schema.

Object of String → Column

none

yes

drUuidColumnName

Column name in the database to store the DR UUID associated with the data event - must be able to store 36 characters, ie varchar(36).

String

_dr_uuid

no

sourceAdaptorColumnName

Column name in the database to store the UUID of the Source Adaptor associated with the data event - must be able to store 36 characters, ie varchar(36).

String

_source_adaptor

no

timestampColumnName

Column name in the database to store the timestamp associated with the data event. Must be a timestamp / date column type.

String

_source_adaptor

no

timestampColumnType

Type of column storing the timestamp. Either DATE or TIMESTAMP. DATE should be compatible with all column types, even timestamp, so it’s only recommended that this value be changed if there’s a problem.

String

DATE

no

actionColumn

Column name in the database to store the action (ie POST, PUT or DELETE) associated with the data event. Must be able to store 6 characters, ie varchar(6).

String

_action

no

autoLinkDrs

When true (which is the default), a message will be sent back to YOUnite when a new record is received, linking it in YOUnite.

Boolean

true

no

Notes:

  1. The domain name and version reference the domain / version defined in YOUnite.

  2. The key of dataColumns is the name of the column in the table. The Column specification defines what the name of the property is in the data domain version schema.

  3. dataColumns must ONLY include columns that are defined in the specified data domain version in YOUnite.

  4. In the database, allcolumns, other than the metadata columns must be NOT NULL as a DELETE event will assign nulls to all data columns.

DataSource

A DataSource is a reference to a DatabaseConnection with specific database, schema and table information:

{
    "name": "string",
    "databaseName": "string",
    "schema": "string",
    "tableName": "string"
}
Property Description Type Default Value Required

name

Name of a DatabaseConnection listed in databases.

String

none

yes

databaseName

Database name.

String

none

no* (except for Mongo)

schema

Schema name.

String

none

no

tableName

Table name.

String

none

yes* (except for Mongo)

Notes:

  1. Type is case sensitive and must be JDBC, MONGO or KAFKA.

  2. Required / optional parameters may vary depending on the database type. See JDBC Configuration and MongoDB Configuration for more information on these database connection types.

Column

A Column is the specification of a column in a table (or property in a stream):

{
    "source": "string",
    "columnType": "string"
}
Property Description Type Default Value Required

source

Name of the column in the domain/version specification in YOUnite.

String

See "Notes" below

no

columnType

Column type. Must be one of the following: STRING, LONG, DOUBLE, DATE, LOCAL_DATE, LOCAL_TIME, TIMESTAMP.

String

STRING

no

Notes:

  1. If source is not specified, it will default to the name of the column in the database.

  2. The columnType corresponds to the Java data type (for TIMESTAMP, the java.sql.timestamp data type).

  3. What’s with all the date types?

    1. DATE = an instant in time with millisecond precision relative to a time zone.

    2. LOCAL_DATE = a date (no time) with no time zone information.

    3. LOCAL_TIME = a time (no date) with no time zone information.

    4. TIMESTAMP = a higher precision date with up to nanosecond precision.

Full Adaptor Metadata Example

PostgreSQL

The following example is an adaptor for the PostgreSQL table customer in the temporal_data database.

{
    "databases": {
        "postgres": {
            "type": "JDBC",
            "properties": {
                "jdbcUrl": "jdbc:postgresql://postgres-db:5432/temporal_data",
                "dataSource.password": "mysecretpassword",
                "dataSource.user": "postgres",
                "maximumPoolSize": "10"
            }
        }
    },
    "tables": [
        {
            "domain": "customer",
            "version": 1,
            "dataSource": {
                "name": "postgres",
                "schema": "public",
                "tableName": "customer"
            },
            "dataColumns": {
                "customer_id": {
                    "source": "customerId",
                    "columnType": "STRING",
                    "updatable": false
                },
                "first_name": {
                    "source": "firstName",
                    "columnType": "STRING"
                },
                "last_name": {
                    "source": "lastName",
                    "columnType": "STRING"
                },
                "email": {
                    "source": "email",
                    "columnType": "STRING"
                },
                "gender": {
                    "source": "gender",
                    "columnType": "STRING"
                },
                "birth_date": {
                    "source": "birthDate",
                    "columnType": "LOCAL_DATE"
                },
                "phone": {
                    "source": "phone",
                    "columnType": "STRING"
                },
                "address": {
                    "source": "address",
                    "columnType": "STRING"
                },
                "city": {
                    "source": "city",
                    "columnType": "STRING"
                },
                "state": {
                    "source": "state",
                    "columnType": "STRING"
                },
                "zip": {
                    "source": "zip",
                    "columnType": "STRING"
                },
                "service_rep": {
                    "source": "serviceRep",
                    "columnType": "STRING"
                },
                "last_visit": {
                    "source": "lastVisit",
                    "columnType": "LOCAL_DATE"
                },
                "account_balance": {
                    "source": "accountBalance",
                    "columnType": "DOUBLE"
                }
            },
            "drUuidColumnName": "dr_uuid",
            "sourceAdaptorColumnName": "source_adaptor",
            "timestampColumnName": "timestamp",
            "timestampColumnType": "DATE",
            "actionColumn": "action",
            "autoLinkDrs": true
        }
    ]
}
MongoDB

The following example is an adaptor for the MongoDB collection customer in the temporal-data database.

{
    "databases": {
        "mongo": {
            "type": "MONGO",
            "properties": {
                "connectionString": "mongodb://admin:admin@mongo:27017/admin"
            }
        }
    },
    "tables": [
        {
            "domain": "customer",
            "version": 1,
            "dataSource": {
                "name": "mongo",
                "databaseName": "temporal-data"
            },
            "dataColumns": {
                "customer_id": {
                    "source": "customerId",
                    "columnType": "STRING",
                    "updatable": false
                },
                "first_name": {
                    "source": "firstName",
                    "columnType": "STRING"
                },
                "last_name": {
                    "source": "lastName",
                    "columnType": "STRING"
                },
                "email": {
                    "source": "email",
                    "columnType": "STRING"
                },
                "gender": {
                    "source": "gender",
                    "columnType": "STRING"
                },
                "birth_date": {
                    "source": "birthDate",
                    "columnType": "LOCAL_DATE"
                },
                "phone": {
                    "source": "phone",
                    "columnType": "STRING"
                },
                "address": {
                    "source": "address",
                    "columnType": "STRING"
                },
                "city": {
                    "source": "city",
                    "columnType": "STRING"
                },
                "state": {
                    "source": "state",
                    "columnType": "STRING"
                },
                "zip": {
                    "source": "zip",
                    "columnType": "STRING"
                },
                "service_rep": {
                    "source": "serviceRep",
                    "columnType": "STRING"
                },
                "last_visit": {
                    "source": "lastVisit",
                    "columnType": "LOCAL_DATE"
                },
                "account_balance": {
                    "source": "accountBalance",
                    "columnType": "DOUBLE"
                }
            },
            "drUuidColumnName": "dr_uuid",
            "sourceAdaptorColumnName": "source_adaptor",
            "timestampColumnName": "timestamp",
            "timestampColumnType": "DATE",
            "actionColumn": "action",
            "autoLinkDrs": true
        }
    ]
}

Service Configuration

JDBC Configuration

When configuring a DatabaseConnection that is of type JDBC, the following are some required and common options to use in properties:

Property Description Required

jdbcUrl

JDBC Url for the data source, ie jdbc:postgresql://localhost:5432/customers

yes

dataSource.user

Username to connect to the database as

usually

dataSource.password

Password to use

usually

maximumPoolSize

Connection pool size

no

template.name

Database template name. Leave blank to auto-detect. See Template Names below.

no

template.printschema

Print schema name when running queries? (ie SELECT …​ FROM public.customer vs SELECT …​ FROM customer)

no

More details on options:

  1. HikariCP is used to pool JDBC connections and all of the properties are passed to it with the exception of properties that start with "template." which are used to set certain options in the QueryDSL template used.

  2. HikariCP uses properties that start with "dataSource." to configure the JDBC driver. All other properties (except "template." properties) are used to configure HikariCP itself. So in the example above (see Full Adaptor Metadata Example - "databases.postgres") "dataSource.user" and "dataSource.password" are passed to the JDBC driver to connect to the database whereas "maximumPoolSize" is a configuration property of HikariCP.

  3. These are the configuration options for templates:

    1. template.printschema: true or false to specify whether the schema should be used by SQL queries before table names.

    2. template.quote: true or false indicating whether names should be quoted.

    3. template.newlinetosinglespace: specifies newlines should be converted to spaces

    4. template.escape: specifies a single character that should be used as the escape character.

HikariCP documentation can be found here: https://github.com/brettwooldridge/HikariCP

Template Names

QueryDSL templates handle translations for the various SQL dialects. The template.name property can be used to explicitly use a template (otherwise, if it’s a supported database it should be auto-detected). The following templates are available:

  • CUBRID

  • DB2

  • Derby

  • Firebird

  • HSQLDB

  • H2

  • MySQL

  • Oracle

  • PostgreSQL

  • SQLite

  • SQLServer2005

  • SQLServer2008

  • SQLServer2012

  • Teradata

Note
 If none of these templates matches the data source, the closest one can be chosen and may work.

In addition, custom templates may be generated for sources not listed above. Contact YOUnite for more information.

MongoDB Configuration

When configuring a DatabaseConnection that is of type MONGO, there is one value to configure in in properties:

Property Description Required

connectionString

MongoDB Connection String. See https://docs.mongodb.com/manual/reference/connection-string/

yes

Data Record Assembly

The YOUnite Data Virtualization Service’s API endpoints can take advantage of temporal features of this type of adaptor. See Temporal Adaptor Assembly