How to use Quest DB for time-series analysis in Java

QuestDB is a fast and easy way to work with time-series data. It supports various protocols and formats for data ingestion and query, such as InfluxDB Line Protocol, PostgreSQL wire protocol, HTTP REST API, and SQL. QuestDB also provides a web console that allows you to import data, run queries, chart results, and export data.

In this tutorial, we will learn how to use QuestDB in Java projects. We will use the InfluxDB Line Protocol as the primary ingestion method and the PostgreSQL wire protocol as the query method. We will also use the QuestDB web console to visualize our data.

Prerequisites

To follow this tutorial, you will need:

Java 8 or higher
Maven
Docker
A QuestDB instance running on your machine or the cloud

You can install QuestDB using Docker by running the following command:

docker run -p 9000:9000 \
  -p 9009:9009 \
  -p 8812:8812 \
  -p 9003:9003 \
  -v "$(pwd):/var/lib/questdb" \
  questdb/questdb:7.1.1

This will expose the following ports:

9000: REST API and Web Console
9009: InfluxDB Line Protocol
8812: PostgreSQL wire protocol
9003: Health monitoring endpoint

You can check if QuestDB is running by visiting http://localhost:9000 or http://localhost:9003.

Creating a Maven project

We will create a simple Maven project to demonstrate how to use QuestDB in Java. You can use your favorite IDE or editor to create the project, or use the following command:

mvn archetype:generate \
  -DgroupId=com.example \
  -DartifactId=questdb-demo \
  -DarchetypeArtifactId=maven-archetype-quickstart \
  -DinteractiveMode=false

This will create a project structure like this:

questdb-demo
├── pom.xml
└── src
    ├── main
    │   └── java
    │       └── com
    │           └── example
    │               └── App.java
    └── test
        └── java
            └── com
                └── example
                    └── AppTest.java

We will edit the pom.xml file to add the dependencies for QuestDB client libraries:

<dependencies>
  <dependency>
    <groupId>org.questdb</groupId>
    <artifactId>questdb</artifactId>
    <version>7.1.1</version>
  </dependency>
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.1</version>
  </dependency>
</dependencies>

We will also add a plugin to enable Java 8 features:

<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>3.8.1</version>
      <configuration>
        <source>8</source>
        <target>8</target>
      </configuration>
    </plugin>
  </plugins>
</build>

Sending data to QuestDB

We will use the InfluxDB Line Protocol to send data to QuestDB. This is a text format that consists of measurements, tags, fields, and timestamps for each data point.

We will use the Sender class from the questdb library to send data using this protocol. The Sender is closeable and implements AutoCloseable, so we can use it inside a try-with-resources block.

We will create a Sender object by providing the address of the QuestDB instance and the database name:

try (Sender sender = Sender.builder()
        .address("localhost:9009")
        .database("mydb")
        .build()) {
    // send data here
}

We can then use the send() method of the Sender object to send data points. The send() method takes a LineProtoSender type as a parameter, which is a functional interface that allows us to write data points using a LineProtoSenderContext object.

For example, we can send some temperature readings from different sensors using this code:

try (Sender sender = Sender.builder()
        .address("localhost:9009")
        .database("mydb")
        .build()) {
    sender.send(context -> {
        context.metric("temperature")
                .tag("sensor", "A")
                .field("value", 25.3)
                .timestamp(Instant.now());
        context.metric("temperature")
                .tag("sensor", "B")
                .field("value", 24.7)
                .timestamp(Instant.now());
        context.metric("temperature")
                .tag("sensor", "C")
                .field("value", 26.1)
                .timestamp(Instant.now());
    });
}

This will create a table called temperature in QuestDB with four columns: sensor, value, timestamp, and series. The series column is an internal column that QuestDB uses to store the tag set of each data point.

We can also use the sendBatch() method of the Sender object to send multiple batches of data points in one call. The sendBatch() method takes a List of LineProtoSender types as a parameter.

For example, we can send three batches of data points using this code:

try (Sender sender = Sender.builder()
        .address("localhost:9009")
        .database("mydb")
        .build()) {
    List<LineProtoSender> batches = new ArrayList<>();
    batches.add(context -> {
        context.metric("temperature")
                .tag("sensor", "A")
                .field("value", 25.3)
                .timestamp(Instant.now());
        context.metric("temperature")
                .tag("sensor", "B")
                .field("value", 24.7)
                .timestamp(Instant.now());
    });
    batches.add(context -> {
        context.metric("temperature")
                .tag("sensor", "C")
                .field("value", 26.1)
                .timestamp(Instant.now());
        context.metric("temperature")
                .tag("sensor", "D")
                .field("value", 25.9)
                .timestamp(Instant.now());
    });
    batches.add(context -> {
        context.metric("temperature")
                .tag("sensor", "E")
                .field("value", 24.5)
                .timestamp(Instant.now());
        context.metric("temperature")
                .tag("sensor", "F")
                .field("value", 25.7)
                .timestamp(Instant.now());
    });
    sender.sendBatch(batches);
}

This will append more data points to the temperature table in QuestDB.

Querying data from QuestDB

We will use the PostgreSQL wire protocol to query data from QuestDB. This is a standard protocol that allows us to use any PostgreSQL-compatible driver or tool to connect to QuestDB and run SQL queries.

We will use the PostgreSQL JDBC driver as an example. We will add the dependency to our pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.1</version>
</dependency>

We will then create a Connection object by providing the URL, username, and password of the QuestDB instance:

String url = "jdbc:postgresql://localhost:8812/mydb?user=admin&password=quest";
try (Connection conn = DriverManager.getConnection(url)) {
    // query data here
}

We can then use a Statement object to execute SQL queries and get a ResultSet object with the query results:

try (Connection conn = DriverManager.getConnection(url);
     Statement stmt = conn.createStatement()) {
    // execute a query and get a result set
    ResultSet rs = stmt.executeQuery("SELECT * FROM temperature");
    // iterate over the result set and print the data
    while (rs.next()) {
        String sensor = rs.getString("sensor");
        double value = rs.getDouble("value");
        Timestamp ts = rs.getTimestamp("timestamp");
        System.out.println(sensor + " " + value + " " + ts);
    }
}

This will print all the data points from the temperature table.

We can also use a PreparedStatement object to execute parameterized SQL queries and get a ResultSet object with the query results:

try (Connection conn = DriverManager.getConnection(url);
     PreparedStatement pstmt = conn.prepareStatement(
             "SELECT * FROM temperature WHERE sensor = ?")) {
    // set the parameter value
    pstmt.setString(1, "A");
    // execute the query and get a result set
    ResultSet rs = pstmt.executeQuery();
    // iterate over the result set and print the data
    while (rs.next()) {
        String sensor = rs.getString("sensor");
        double value = rs.getDouble("value");
        Timestamp ts = rs.getTimestamp("timestamp");
        System.out.println(sensor + " " + value + " " + ts);
    }
}

This will print only the data points from sensor A.

Visualizing data with the QuestDB web console

We can also use the QuestDB web console to visualize our data. The web console is accessible at http://localhost:9000 and provides a graphical interface to interact with QuestDB.

The web console has four tabs: Import, SQL, Console, and Export.

The Import tab allows us to import data from CSV files by dragging and dropping them into the web console. We can also specify the table name, column names, column types, and designated timestamps for the imported data.
The SQL tab allows us to run SQL queries and see the results in a table or a chart. We can also save our queries for later use or share them with others.
The Console tab allows us to see the server logs and status, such as memory usage, CPU usage, disk space, and open files.
The Export tab allows us to export data from QuestDB to CSV files. We can specify the table name, column names, column types, and designated timestamps for the exported data.

For example, we can run this SQL query in the SQL tab to see the average temperature by sensor:

SELECT sensor, avg(value) FROM temperature GROUP BY sensor;

We can then see the results in a table or a chart:

We can also customize the chart type, title, axis labels, colors, and legend.

Conclusion

In this tutorial, we learned how to use QuestDB in Java projects. We used the InfluxDB Line Protocol to send data to QuestDB and the PostgreSQL wire protocol to query data from QuestDB. We also used the QuestDB web console to visualize our data.

QuestDB is a fast and easy way to work with time-series data. It offers high-performance ingestion and query capabilities, developer-friendly features and integrations, and scalable and reliable deployment options. If you are looking for a database that can handle your time-series analysis needs, give QuestDB a try.

You can start building with QuestDB cloud or check out their GitHub repo for more information. You can also join their developer community on Slack or follow them on Twitter for updates and news.

How to use Quest DB for time-series analysis in Java

Prerequisites

Creating a Maven project

Sending data to QuestDB

Querying data from QuestDB

Visualizing data with the QuestDB web console

For tutorial you can check:

For Demo You can check:

For Slack community you can check:

Conclusion

Subscribe to my newsletter

Sumit Kumar

Sumit Kumar