Overview

The YOUnite JDBC driver allows access to YOUnite APIs via JDBC:

Most API endpoints are queryable via SQL either as a SQL table or SQL a table-valued function. See YOUnite API Documentation for details of the available API endpoints.

Additionally, Federated Data Records may be queried via the Data Virtualization Service. See Accessing Data Records for more information about assembling Federated Data Records.

Driver Configuration

JDBC URL

The JDBC URL starts with jdbc:younite: and ends with the URL to the api endpoint of the YOUnite server.

jdbc:younite:{http|https}://{host}:{port}{path to API endpoint, ie /api}

Notes:

  1. If http:// or https:// is excluded, http:// will be used.

  2. If port is excluded, the default port is used (80 or 443).

  3. If path is excluded, /api will be used, which is the default path to the API endpoint.

  4. If path is /, the root of the host is assumed to be the path to the API endpoint.

Examples:

JDBC URL Actual API URL used

jdbc:younite:yn.host.com

http://yn.host.com:80/api

jdbc:younite:yn.host.com:8080

http://yn.host.com:8080/api

jdbc:younite:https://yn.host.com

https://yn.host.com:443/api

jdbc:younite:https://yn.host.com:8443

https://yn.host.com:8443/api

jdbc:younite:http://yn.host.com/

http://yn.host.com:80/

jdbc:younite:http://yn.host.com:8080/younite

http://yn.host.com:8080/younite

Authentication

The JDBC driver requires a valid access token to passed in the password property.

This access token can be retrieved in the YOUnite UI by clicking the icon in the upper right corner and choosing Access Token.

Access Token
Note
 Access tokens typically expire, so a new access token may need to be retrieved each time.

Optional properties

  • default.zone.uuid = The UUID of the zone to use for data requests by default.If not supplied, the user’s default zone UUID is used, if available, or the root zone if the user doesn’t have a default zone.

  • expands.refs = Expand and return cross-references with assembled data.

  • source.adaptor.uuid = UUID of an adaptor to run all data queries against by default.

  • default.fetch.size = Default number of records to read at a time.If not specified, 50 is used.

  • async.prefetch = Pre-fetch the next page of data automatically in the background. Default is true.

See Accessing Data Records for more information about how these parameters affect Federated Data Records Assembly

Schemas

Two schemas exist: data and younite. The data schema is used for retrieving Federated Data Records via the Data Virtualization Service’s assembler. The younite schema is for all other YOUnite API endpoints, such as zones, domains, etc.

Data Schema

The data schema contains one table and one table-valued function per domain version. The name of the table is {domain}_{version}, ie customer_1. The name of the function is fn_{domain}_{version} ie fn_customer_1.

The table and table-valued function both allow retrieving the same data, however, the function allows for more control over what is returned. Depending on the type of domain (FEDERATED or YOUNITE_DATA_STORE), the function parameter differs.

FEDERATED domains (Data Virtualization Service)

Functions for FEDERATED tables takes a single parameter, which is a JSON-encoded string with the same values you would pass to get POST /drs/assembler endpoint. See POST /drs/assembler for a reference of what information can be passed.

Examples:

  1. Assemble the first 10 customers by customerId

    select * from customer_1 order by customerId limit 10

  2. Assemble the first 10 customers by customerId from a specific adaptor:

    select * from fn_customer_1('{"adaptors": ["2b6848cb-789f-43da-a3d6-a21e00d7b4b0"]}') order by customerId limit 10

YOUNITE_DATA_STORE domains

Functions for YOUNITE_DATA_STORE tables take a single parameter either "true" or "false", indicating whether to expand cross-references. For example select * from fn_country_1('true') will include cross-referenced data for all country records.

Younite Schema

The younite schema contains tables and table-valued functions which correspond to API endpoints that retrieve data from YOUnite.

The tables return data for endpoints that require no parameters, such as zones, domains, domains_versions, etc. Example: select * from zones.

The functions return data for endpoints that require one or more parameters such as zones_adaptors or zones_users. Example: select * from fn_zones_adaptors('44c41dbc-dbb8-481a-ae01-cca2487fbefb'). This is equivalent to GET /zones/44c41dbc-dbb8-481a-ae01-cca2487fbefb/adaptors.

Supported Expressions and Limitations

Limitations:

  1. SELECT is the only supported statement.

  2. Only a single table / function can be referenced in the FROM clause (joins are not supported).

  3. ORDER BY support for data requests is limited to DR Key properties.

  4. ORDER BY support varies for other requests and may be ignored.

  5. The ORDER BY clause may only include columns names

  6. DISTINCT, GROUP BY, etc are not supported.

SELECT syntax:

SELECT [expressions ... ] FROM [table or function] [WHERE expressions... ] [ORDER BY [column [DESCENDING], ...]] [OFFSET x] [LIMIT x]

Supported expressions:

The SELECT and WHERE clause support the following SQL expressions and functions:

  1. Constant strings and numbers. Boolean constants are not supported directly, but a string can be cast to a boolean, ie CAST('true' as BOOLEAN).

  2. Mathematical operations: * / + - %

  3. String concatenation: ||

  4. Parenthesis: ( )

  5. Type casting: CAST([expression] as [type]) - see CAST data types below.

  6. CASE statement: CASE [expression] WHEN [value] THEN [expression], …​ [ELSE expression] END or CASE WHEN [condition] THEN [expression], …​ [ELSE expression] END

  7. Boolean logic: AND OR

CAST data types:

The data types allowed correspond to JSON data types:

  1. STRING

  2. NUMBER (floating point)

  3. INTEGER (non-floating point)

  4. BOOLEAN

  5. OBJECT

IMPORTANT: FPerformance considerations of the WHERE clause for Data Requests

YOUnite stores only the DR Key values for data records, not the entire data record itself. Therefore, expressions in the WHERE clause that reference properties that are not DR Keys are filtered by the JDBC Driver as data arrives. Depending on the number of data records this could be a very slow and costly operation. For example, if there are one million records in a customer domain, a WHERE clause that does not filter on DR Key properties would need to assemble all one million records and then filter the results as they come in.

Http Client Advanced Configuration

The Apache HTTP Client is used to communicate with YOUnite. The following properties can be passed to the JDBC driver (see https://www.javadoc.io/doc/org.apache.httpcomponents/httpclient/4.4/org/apache/http/impl/client/HttpClientBuilder.html):

  • ssl.TrustManagerFactory.algorithm

  • javax.net.ssl.trustStoreType

  • javax.net.ssl.trustStore

  • javax.net.ssl.trustStoreProvider

  • javax.net.ssl.trustStorePassword

  • ssl.KeyManagerFactory.algorithm

  • javax.net.ssl.keyStoreType

  • javax.net.ssl.keyStore

  • javax.net.ssl.keyStoreProvider

  • javax.net.ssl.keyStorePassword

  • https.protocols

  • https.cipherSuites

  • http.proxyHost

  • http.proxyPort

  • https.proxyHost

  • https.proxyPort

  • http.nonProxyHosts

  • http.keepAlive

  • http.maxConnections

  • http.agent