/atsd-jdbc

JDBC Type 4 Driver for Axibase Time Series Database

Primary LanguageJavaApache License 2.0Apache-2.0

Build Status Codacy Badge License Maven Central Known Vulnerabilities

JDBC driver

The ATSD JDBC driver enables Java applications to read and write time-series data from the Axibase Time Series Database using SQL.

Reading Data

To retrieve records from the database, execute SELECT statements following the query syntax and examples provided in the ATSD SQL documentation.

To read data from the database, the user must be granted:

Writing Data

To write data into ATSD, execute INSERT or UPDATE statements which are parsed by the driver and transformed into series commands sent into the database.

To write data into the database, the user must be granted:

Driver Class

com.axibase.tsd.driver.jdbc.AtsdDriver

JDBC URL

The ATSD JDBC driver prefix is jdbc:atsd:, followed by the ATSD hostname (IP address) and port, optional catalog, and driver properties.

jdbc:atsd://atsd_hostname:port[/catalog][;property_name=property_value]
jdbc:atsd://192.0.2.1:8443

Properties can be appended to the JDBC URL using a semicolon as a separator:

jdbc:atsd://192.0.2.1:8443;tables=infla%;expandTags=true

For example, add secure=false property when connecting to the database via the HTTP protocol:

jdbc:atsd://192.0.2.1:8088;secure=false

Connection Management

When pooling connections for performance purposes, use the following lightweight connection query for stale checking and invalidation:

SELECT 1

License

The driver is released under the Apache 2.0 License.

Compatibility

The table below specifies a range of compatible driver versions for a given database revision number displayed on the Settings > System Information page.

For example, database revision number 16200 supports driver versions between 1.2.20 (inclusive) and 1.3.0 (exclusive).

Database Version Driver Version
12400 1.2.1
12500 1.2.6
14049 1.2.8
14126 1.2.10
14220 1.2.12
14451 1.2.15
14540 1.2.16
16130 1.2.20
16620 1.3.0
16643 1.3.2
16855 1.3.3
17285 1.4.0
20443 1.4.7

JDBC Connection Properties Supported by Driver

Name Type Supported drivers Default Description
trust boolean 1.3.1+ true Skip SSL certificate validation.
secure boolean 1.3.1+ true Use HTTPS protocol to communicate with ATSD.
connectTimeout number 1.2.7+ 5 Connection timeout, in seconds.
readTimeout number 1.2.7+ 0 Read I/O timeout, in seconds.
strategy file, memory, stream 1.0+ stream Result set processing strategy.
tables comma-separated list 1.2.21+ % List of metric names or metric expressions returned as tables by the DatabaseMetaData#getTables method.
expandTags boolean 1.2.21+ false Return series tags as separate columns in the DatabaseMetaData#getColumns method.
metaColumns boolean 1.2.21+ false Add metric.tags, entity.tags, and entity.groups columns to the list of columns returned by the DatabaseMetaData#getColumns method.
assignColumnNames boolean 1.3.0+ false Force ResultSetMetaData.getColumnName(index) method to return column names.
If disabled, method returns column labels.
timestamptz boolean 1.3.2+ true Instantiate Timestamp fields with the time zone stored in the database (UTC). If timestamptz is set to false, the Timestamp fields are created based on the client's local time zone.
missingMetric error, warning, none 1.3.2+ warning Control behavior when the referenced metric does not exist. If 'error' is specified, the driver raises an AtsdMetricNotFoundException. If warning is specified, an SQLWarning is created. If none is specified, no error or warning is created.
compatibility odbc2 1.3.2+ not set Simulate behavior of ODBC2.0 drivers: substitute bigint datatype with double, return 11 as timestamp type code
disableUnderscoreInColumns boolean 1.4.4+ true Treat underscore as literal character in DatabaseMetaData.getColumns queries

Result Set Processing Strategy

Choose the appropriate strategy based on available Java heap memory, disk space, and expected row count produced by a typical query.

Name Description
stream Reads data received from the database in batches when triggered by the ResultSet.next() invocation. This command keeps the connection open until all rows are processed by the client.
file Buffers data received from the database to a temporary file on the local file system and reads rows from the file on the ResultSet.next() invocation.
memory Buffers data received from the database into the application memory and returns rows on the ResultSet.next() invocation directly from a memory structure.
  • The memory strategy is more efficient than the file strategy but requires more memory. The memory strategy is optimal for queries returning some amount of rows on the order of one hundred thousand or less, whereas the file strategy can process millions of rows during operation, provided enough disk space is available.
  • The stream strategy is faster than both alternatives, at the expense of keeping the database connection open. It is not recommended if row processing can last a significant time on a slow client.

Requirements

  • Java 1.7 and later

Downloads

  • Compiled drivers are listed on the Releases page.
  • atsd-jdbc-*.jar files are built without dependencies.
  • atsd-jdbc-*-DEPS.jar files contain dependencies.
  • The latest jar file with dependencies is atsd-jdbc-1.4.10-DEPS.jar.

Debugging

  • Download a *-DEBUG-DEPS.jar driver file that contains tracing code such as atsd-jdbc-1.4.7-DEBUG-DEPS.jar.
  • Replace the current atsd-jdbc-*.jar file in the ./lib directory with the downloaded JAR file.
  • Locate the logging properties file, for example log4j2.properties in case of Apache Log4j 2.
  • Define a new logger for com.axibase classes at the DEBUG level, for example:
<Logger name="com.axibase" level="DEBUG" additivity="false">
  <AppenderRef ref="atsdJdbcLog"/>
</Logger>

<RollingFile name="atsdJdbcLog" fileName="${basedir}/atsd-jdbc.log" filePattern="${basedir}/atsd-jdbc.%i.log">
  <Policies>
    <SizeBasedTriggeringPolicy size="20MB"/>
  </Policies>
  <DefaultRolloverStrategy max="10" fileIndex="min"/>
  <PatternLayout pattern="${standardLogPattern}"/>
</RollingFile>
  • Restart the application or service to apply the settings.
  • Review the atsd-jdbc.log file for driver.jdbc.AtsdDriver messages.
DEBUG 2018-09-20T09:19:58,525+0000 [adm, #0, #14] driver.jdbc.AtsdDriver: [createDriverVersion] 1.4.7-DEBUG
DEBUG 2018-09-20T09:19:58,525+0000 [adm, #0, #14] driver.jdbc.AtsdDriver: [acceptsURL] jdbc:atsd://atsd_hostname:8443
DEBUG 2018-09-20T09:19:58,525+0000 [adm, #0, #14] driver.jdbc.AtsdDriver: [connect] jdbc:atsd://atsd_hostname:8443
DEBUG 2018-09-20T09:19:58,650+0000 [adm, #0, #14] driver.jdbc.AtsdDriver: [createMeta] d6c9793b-63c6-4794-af4b-bee8414d947d
DEBUG 2018-09-20T09:19:58,963+0000 [adm, #0, #14] jdbc.ext.AtsdDatabaseMetaData: #getResultSetHoldability(): entered
DEBUG 2018-09-20T09:19:58,978+0000 [adm, #0, #14] jdbc.ext.AtsdDatabaseMetaData: #getResultSetHoldability(): 1 in 4.64ms

Integration

Classpath

Download the jar file with dependencies and add it to the classpath of your application.

  • Unix:
java -cp "atsd-jdbc-1.4.10-DEPS.jar:lib/*" your.package.MainClass
  • Windows:
java -cp "atsd-jdbc-1.4.10-DEPS.jar;lib/*" your.package.MainClass

Apache Maven

Add atsd-jdbc dependency to pom.xml in your project.

<dependency>
    <groupId>com.axibase</groupId>
    <artifactId>atsd-jdbc</artifactId>
    <version>1.4.10</version>
</dependency>

The ATSD JDBC driver is published in Maven Central/Sonatype repositories and is imported automatically.

Alternatively, build the project from sources:

$ mvn clean package -DskipTests=true

Database Tools

Download the jar file with dependencies and import it into your database client tool.

Follow the instructions to create a custom JDBC driver based on the ATSD jar file.

Reporting Tools

Supported Data Types

TYPE NAME DATA TYPE PRECISION
BOOLEAN 16 1
DECIMAL 3 0
DOUBLE 8 15
FLOAT 7 7
INTEGER 4 10
BIGINT -5 19
SMALLINT 5 5
VARCHAR 12 131072
TIMESTAMP 93 23
JAVA_OBJECT 2000 2147483647

Capabilities

Usage

To execute a query, load the driver class, open a connection, create an SQL statement, execute the query, and process the result set:

Class.forName("com.axibase.tsd.driver.jdbc.AtsdDriver");
Connection connection = DriverManager.getConnection("jdbc:atsd://192.0.2.1:8443", "username", "password");
String query = "SELECT value, datetime FROM \"mpstat.cpu_busy\" WHERE entity = 'nurswgvml007' LIMIT 1";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

Prepared Statements

Initialize a prepared statement, set placeholder parameters, and execute the query:

String query = "SELECT value, datetime FROM \"mpstat.cpu_busy\" WHERE entity = ? LIMIT 1";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, "nurswgvml007");
ResultSet resultSet = prepareStatement.executeQuery();

ATSD Extensions

Extensions implement additional methods for the standard JDBC java.sql.Statement and java.sql.PreparedStatement interfaces.

  • Class com.axibase.tsd.driver.jdbc.ext.AtsdStatement extends java.sql.Statement.
  • Class com.axibase.tsd.driver.jdbc.ext.AtsdPreparedStatement extends java.sql.PreparedStatement.
  • Class com.axibase.tsd.driver.jdbc.ext.AtsdResultSet extends java.sql.ResultSet.

To access additional methods you need to cast the standard JDBC classes to ATSD classes:

Statement stmt = //get statement;
AtsdStatement astmt = (AtsdStatement)stmt;
PreparedStatement pstmt = //prepare statement;
AtsdPreparedStatement apstmt = (AtsdPreparedStatement)stmt;

Note that the AtsdPreparedStatement class is not a subclass of AtsdStatement and therefore cannot be cast from AtsdStatement.

  • void setTagsEncoding(boolean enc) - supported by AtsdStatement and AtsdPreparedStatement.
  • Map<String, String> getTags() - supported by AtsdResultSet.
  • void setTags(Map<String, String> tagMap) - supported by AtsdPreparedStatement.
  • void setTimeExpression(String string) - supported by AtsdPreparedStatement.

Calendar Expressions

Supported in 1.2.9+.

To set a calendar expression as a parameter in a prepared statement, cast the statement to AtsdPreparedStatement and invoke the setTimeExpression method.

String query = "SELECT * FROM \"df.disk_used\" WHERE datetime > ? LIMIT 1";
PreparedStatement preparedStatement = connection.prepareStatement(query);
AtsdPreparedStatement axibaseStatement = (AtsdPreparedStatement)preparedStatement;
axibaseStatement.setTimeExpression(1, "current_day - 1 * week + 2 * day");

Tag Columns

Supported in 1.4.0+

Use the setTags and getTags methods to encode and decode tag columns (series tags, metric tags, entity tags) into a java.util.Map instance.

Map<String, String> seriesTags = new HashMap<String, String>();
seriesTags.put("surface", "Outer");
seriesTags.put("status", "Initial");

String insertQuery = "INSERT INTO temperature (entity, tags, time, value) VALUES (?, ?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(insertQuery);
AtsdPreparedStatement aps = (AtsdPreparedStatement)statement;
aps.setString(1, "sensor-01");
aps.setTags(2, seriesTags);
aps.setTimestamp(3, System.currentTimeMillis());
aps.setDouble(4, 24.5);
aps.execute();

When retrieving records from the database, ensure that tag encoding is enabled before the query is executed.

AtsdStatement atsdStatement = (AtsdStatement) statement;
atsdStatement.setTagsEncoding(true);

String query = "SELECT datetime, value, tags, entity.tags FROM temperature WHERE entity = 'sensor-01' LIMIT 1";
AtsdResultSet rs = (AtsdResultSet)atsdStatement.executeQuery(query);
while (rs.next()) {
    Timestamp ts = rs.getTimestamp(1);
    double value = rs.getDouble(2);
    Map<String, String> seriesTags = rs.getTags(3);
    Map<String, String> entityTags = rs.getTags(4);
}

SQL Warnings

The database can return SQL warnings, as opposed to raising a non-recoverable error, in case of an unknown tag or tag value.

To retrieve SQL warnings, invoke the resultSet.getWarnings() method:

SQLWarning rsWarning = resultSet.getWarnings();
if (rsWarning != null) {
    System.err.println(rsWarning.getMessage());
}

Database Metadata

The list of tables and columns can be retrieved using DatabaseMetaData#getTables and DatabaseMetaData#getColumns methods. Use % and _ wildcards when matching tables and columns by name.

// Match tables disk_used, disk_used_percent
ResultSet rs = dbMetadata.getTables(null, null, "_isk_%", null);

The list of tables visible to these methods can be filtered with the tables={expression} connection property.

Basic Example

import java.sql.*;


public class TestQuery {

    public static void main(String[] args) throws Exception {

        Class.forName("com.axibase.tsd.driver.jdbc.AtsdDriver");  
        String host = args[0];
        String port = args[1];
        String username = args[2];
        String password = args[3];

        String jdbcUrl = "jdbc:atsd://" + host + ":" port;

        String query = "SELECT * FROM \"mpstat.cpu_busy\" WHERE datetime > now - 1 * HOUR LIMIT 5";
        Connection connection = null;
        try {
            System.out.println("Connecting to " + jdbcUrl);
            connection = DriverManager.getConnection(jdbcUrl, username, password);
            System.out.println("Connection established to " + jdbcUrl);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);
            System.out.println("Query complete.");
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            int rowNumber = 1;
            while (resultSet.next()) {
                System.out.println("= row " + rowNumber++);
                for (int colIndex = 1; colIndex <= columnCount; colIndex++) {
                    System.out.println("  " + metaData.getColumnLabel(colIndex) + " = " + resultSet.getString(colIndex));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           try {
               connection.close();
           } catch(Exception e){}
        }
    }
}

Additional Examples

Class.forName("com.axibase.tsd.driver.jdbc.AtsdDriver");

String host = args[0];
String port = args[1];
String username = args[2];
String password = args[3];
String jdbcUrl = "jdbc:atsd://" + host + ":" port;

String query = "SELECT entity, datetime, value, tags.mount_point, tags.file_system "
        + "FROM \"df.disk_used_percent\" WHERE entity = 'NURSWGHBS001' AND datetime > now - 1 * HOUR LIMIT 10";

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
     Statement statement = conn.createStatement();
     ResultSet resultSet = statement.executeQuery(query)) {

    int rowNumber = 1;
    while (resultSet.next()) {
        System.out.print(rowNumber++);
        System.out.print("\tentity = " + resultSet.getString("entity"));
        System.out.print("\tdatetime = " + resultSet.getTimestamp("datetime").toString());
        System.out.print("\tvalue = " + resultSet.getString("value"));
        System.out.print("\ttags.mount_point = " + resultSet.getString("tags.mount_point"));
        System.out.println("\ttags.file_system = " + resultSet.getString("tags.file_system"));
    }

    final SQLWarning warnings = resultSet.getWarnings();
    if (warnings != null) {
        warnings.printStackTrace();
    }
} catch (Exception e){
    e.printStackTrace();
}

Results:

1  entity = nurswghbs001 datetime = 2016-08-22 12:52:03.0 value = 28.8116 tags.mount_point = / tags.file_system = /dev/md2
2  entity = nurswghbs001 datetime = 2016-08-22 12:52:04.0 value = 28.8116 tags.mount_point = / tags.file_system = /dev/md2
3  entity = nurswghbs001 datetime = 2016-08-22 12:52:18.0 value = 28.8116 tags.mount_point = / tags.file_system = /dev/md2
4  entity = nurswghbs001 datetime = 2016-08-22 12:52:19.0 value = 28.8116 tags.mount_point = / tags.file_system = /dev/md2
5  entity = nurswghbs001 datetime = 2016-08-22 12:52:33.0 value = 28.8117 tags.mount_point = / tags.file_system = /dev/md2
6  entity = nurswghbs001 datetime = 2016-08-22 12:52:34.0 value = 28.8117 tags.mount_point = / tags.file_system = /dev/md2
7  entity = nurswghbs001 datetime = 2016-08-22 12:52:48.0 value = 28.8117 tags.mount_point = / tags.file_system = /dev/md2
8  entity = nurswghbs001 datetime = 2016-08-22 12:52:49.0 value = 28.8117 tags.mount_point = / tags.file_system = /dev/md2
9  entity = nurswghbs001 datetime = 2016-08-22 12:53:03.0 value = 28.8117 tags.mount_point = / tags.file_system = /dev/md2
10 entity = nurswghbs001 datetime = 2016-08-22 12:53:04.0 value = 28.8117 tags.mount_point = / tags.file_system = /dev/md2

The following example shows how to extract metadata from the database:

Class.forName("com.axibase.tsd.driver.jdbc.AtsdDriver");

String host = args[0];
String port = args[1];
String username = args[2];
String password = args[3];

String jdbcUrl = "jdbc:atsd://" + host + ":" port;

try (Connection connection = DriverManager.getConnection(jdbcUrl, userName, password)) {

    DatabaseMetaData metaData = connection.getMetaData();
    String databaseProductName = metaData.getDatabaseProductName();
    String databaseProductVersion = metaData.getDatabaseProductVersion();
    String driverName = metaData.getDriverName();
    String driverVersion = metaData.getDriverVersion();
    System.out.println("Product Name:   \t" + databaseProductName);
    System.out.println("Product Version:\t" + databaseProductVersion);
    System.out.println("Driver Name:    \t" + driverName);
    System.out.println("Driver Version: \t" + driverVersion);
    System.out.println("\nTypeInfo:");

    ResultSet rs = metaData.getTypeInfo();
    while (rs.next()) {
        String name = rs.getString("TYPE_NAME");
        int type = rs.getInt("DATA_TYPE");
        int precision = rs.getInt("PRECISION");
        boolean isCS = rs.getBoolean("CASE_SENSITIVE");
        System.out.println(String.format(
                "\tName:%s \tCS: %s \tType: %s \tPrecision: %s", name, isCS, type, precision));
    }
    System.out.println("\nTableTypes:");

    rs = metaData.getTableTypes();
    while (rs.next()) {
        String type = rs.getString(1);
        System.out.println('\t' + type);
    }
    rs = metaData.getCatalogs();

    while (rs.next()) {
        String catalog = rs.getString(1);
        System.out.println("\nCatalog: \t" + catalog);
        ResultSet rs1 = metaData.getSchemas(catalog, null);
        while (rs1.next()) {
            String schema = rs1.getString(1);
            System.out.println("Schema: \t" + schema);
        }
    }
}

Results:

Product Name:       Axibase
Product Version:    Axibase Time Series Database, <ATSD_EDITION>, Revision: <ATSD_REVISION_NUMBER>
Driver Name:        ATSD JDBC driver
Driver Version:     1.4.10

TypeInfo:
    Name:bigint         CS: false     Type: -5    Precision: 19
    Name:boolean        CS: false     Type: 16    Precision: 1
    Name:decimal        CS: false     Type: 3     Precision: 0
    Name:double         CS: false     Type: 8     Precision: 15
    Name:float          CS: false     Type: 7     Precision: 7
    Name:integer        CS: false     Type: 4     Precision: 10
    Name:java_object    CS: false     Type: 2000  Precision: 2147483647
    Name:smallint       CS: false     Type: 5     Precision: 5
    Name:varchar        CS: true      Type: 12    Precision: 131072
    Name:timestamp      CS: false     Type: 93    Precision: 23

TableTypes:
    TABLE
    VIEW
    SYSTEM

Catalog: null

Spring Framework Integration

We recommend the Spring Data JDBC library to integrate ATSD JDBC driver with Spring.

See an example here.

config file gist:

@Configuration
public class AtsdRepositoryConfig {

    @Bean
    public SqlGenerator sqlGenerator() {
        return new AtsdSqlGenerator();
    }

    @Bean
    public DataSource dataSource() {
        final HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(login);
        dataSource.setPassword(password);
        dataSource.setReadOnly(true);
        return dataSource;
    }

    @Bean
    public EntityValueFloatRepository entityRepository() {
        return new EntityValueFloatRepository(table);
    }

}

repository file gist:

@Repository
public class EntityValueFloatRepository extends JdbcRepository<EntityValueFloat, Float> {

    public EntityValueFloatRepository(String table) {
        super(ROW_MAPPER, new MissingRowUnmapper<EntityValueFloat>(), table);
    }

    public static final RowMapper<EntityValueFloat> ROW_MAPPER = new RowMapper<EntityValueFloat>() {
        @Override
        public EntityValueFloat mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new EntityValueFloat(rs.getString("entity"), rs.getLong("time"), rs.getFloat("value"));
        }
    };

}

Usage example with Spring Boot:

@Resource
private EntityValueFloatRepository entityRepository;

@Override
public void run(String... args) throws Exception {
    PageRequest page = new PageRequest(0, 1000, Direction.DESC, "time", "value");
    Page<EntityValueFloat> result = entityRepository.findAll(page);
    List<EntityValueFloat> list = result.getContent();
    assert list != null && !list.isEmpty();
}