Skip to main content

Generic JDBC

Generic JDBC is a connector allowing to connect DirectQuery to any database, as long as it provides a JDBC driver.

For DirectQuery to generate the proper SQL queries, some customization is still required, and can be done by creating an instance of the class com.activeviam.database.jdbc.api.SqlDialect.

DirectQuery supports some databases through a SQL dialect implementation :

<dependency>
    <groupId>com.activeviam.database.jdbc.dialect.dremio</groupId>
    <artifactId>dremio-dialect</artifactId>
    <version>${atoti.server.version}</version>
</dependency>

Sql Dialect​

The dialect implementation is split across a handful of interfaces, each with a default implementation based on standard SQL. Each interface covers a different aspect of the SQL dialect, such as keywords, types, functions, ...

However, every database can have slightly different features, and would require the overriding of some methods in these interfaces.

It's then possible to create a new instance of com.activeviam.database.jdbc.api.SqlDialect via its builder:

return SqlDialect.builder()
    .sqlFormatter(SQL_FORMATTER)
    .keywords(new TransactSqlSqlDialectForKeywords())
    .types(new TransactSqlSqlDialectForTypes())
    .functions(new TransactSqlSqlDialectForFunctions())
    .aggregations(new TransactSqlSqlDialectForAggregations())
    .build();

It is recommended to read the documentation of these interfaces, along with the implementation of the various methods. They would be a good starting point for overriding any of them.

com.activeviam.database.jdbc.api.ISqlDialectForKeywords​

This interface defines standard keywords, such as "SELECT", "FROM", ... It is possible to override one of these methods if necessary, but a large majority of databases shouldn't require it.

com.activeviam.database.jdbc.api.ISqlDialectForTypes​

This interface defines standard SQL types, as well as how to serialize literals.

com.activeviam.database.jdbc.api.ISqlDialectForFunctions​

This interface defines SQL functions, i.e. functions transforming an expression, such as abs, nullif, ... They are taking one (or more) SqlPartPlaceholder as argument, and return a list of ISqlPart.

ISqlPart is a sealed interface with 2 implementations: SqlPartPlaceholder and SqlPartString. The first one represents the input, while the second one is just a string.

So as an example, the implementation of abs would be:

List<ISqlPart> absFunction(final SqlPartPlaceholder column) {
    return List.of(
        new SqlPartString("abs("),
        column,
        new SqlPartString(")"));
  }

DirectQuery can replace the placeholder with the required expression and generate the actual query to be run.

com.activeviam.database.jdbc.api.ISqlDialectForAggregationFunctions​

This interface defines SQL aggregation functions, i.e. functions aggregating rows, such as sum, min, ... Their implementation is similar to the SQL functions, explained above.

com.activeviam.database.jdbc.api.ISqlDialectForTableManagement​

Finally, DirectQuery also helps with generating complex DDL queries (e.g. for aggregate tables), and requires the definition of methods to return keywords related to DDL statements such as CREATE TABLE or keywords related to data types.

SQL formatter​

Optionally, it's also possible to add a SQL formatter to the dialect. This formatter is used when printing queries into the logs, and might make the queries easier to read.

Time-travel support​

Time-travel is not supported by Generic JDBC. This means that updating applications based on this Database are subject to data desynchronization in case of changes in the Database.
Read this page to learn more about the visible effects of this desynchronization.

Vectors support​

Vector support is a feature heavily dependent on the actual database.

Most databases should be able to handle emulated vectors (multi-rows and multi-columns). Native vectors are unlikely to be supported, especially in regard to aggregation functions.

Gotchas​

Nullable fields​

Some databases can define fields with nullable types. DirectQuery is capable of detecting this and defines its internal model accordingly.
While this is not a problem in itself, it can conflict with the rule in Atoti cubes that all levels must be based on non-nullable values. This does not create an issue of any sort as the local model is updated behind the scene, assigning a default value based on the type.
It has a side effect on the query performance, as DirectQuery must convert on-the-fly null values to their assigned default values, as well as adding extra conditions during joins to handle null values on both sides.

Testing your dialect implementation​

To test your dialect implementation, you can use the com.activeviam.database.jdbctck.api.ATestGenericJdbcUponTck abstract class. You need to implement a few simple methods, to indicate the names of the catalog and schema, the naming standard of your database (are they lower or upper case), the actual dialect to test as well as the JDBC properties used to connect to the testing database.

Ask your favorite data squad for the CSV files containing the testing data.