Spring Boot and Time Series Data in ScyllaDB

The following tutorial walks you through how to use Spring Boot apps with ScyllaDB for time series data, taking advantage of shard-aware drivers and prepared statements. It’s a shorter version of a ScyllaDB University (self-paced free training) lab. 

About This Spring Boot and Time Series Data Tutorial

This tutorial provides a step-by-step demonstration of how to use the popular Spring Boot framework to build a sample stock application. ScyllaDB is used to store the stock price (time series data). The application has several APIs that support the create, get, and delete operations on stocks with the date and price. Additionally, an HTTP API tool, Postman, is used to interact with our application and to test the API functionality. By the end of the tutorial, you’ll have a running Spring Boot app that serves as an HTTP API server with ScyllaDB as the underlying data storage. You’ll be able to create stocks with prices and dates and get a list of stock items. And you’ll learn how ScyllaDB can be used to store time series data. Note that ScyllaDB University offers a number of videos with additional background that are helpful for this lesson. For example:

• A close look at the data model used in this application
• How to connect Spring to the ScyllaDB Cluster
• How to test the application via Postman
• Options for creating a UI
• What should be done differently for a real app in production

There’s also a video where you can watch a ScyllaDB engineer “live coding” with Spring Boot.

A Quick Introduction To Spring Boot

"Spring Boot is an open-source micro framework maintained by a company called Pivotal. It provides Java developers with a platform to get started with an auto-configurable production-grade Spring application. With it, developers can get started quickly without wasting time preparing and configuring their Spring application. Spring Boot is built on top of the Spring framework and has many dependencies that can be plugged into the Spring application. Some examples are Spring Kafka, Spring LDAP, Spring Web Services, and Spring Security. However, developers have to configure each building brick using many XML configuration files or annotations." (Source: What Is Spring Boot?)

A Quick Introduction to Postman

Postman is an API tool for building and using APIs. It simplifies each step of the API lifecycle and streamlines collaboration so you can create better APIs. It enables you to easily explore, debug, and test your APIs while also enabling you to define complex API requests for HTTP, REST, SOAP, GraphQL, and WebSockets. The API client automatically detects the language of the response, links, and format text inside the Body to make inspection easy. The client also includes built-in support for authentication protocols like OAuth 1.2/2.0, AWS Signature, Hawk, and many more. Through the API client, you can organize requests into Postman Collections to help you organize your requests for reuse so you don’t waste time building everything from scratch. Your collections can also contain JavaScript code to tie requests together or automate common workflows, and you can use scripting to visualize your API responses as charts and graphs.

Setup a ScyllaDB Cluster

You can run this lab with a cluster created using ScyllaDB Cloud or Docker. The following steps use Docker. If you choose to run the cluster on ScyllaDB Cloud, skip the Docker Compose section part below. Get the sample project code from the GitHub repo and start a three-node ScyllaDB cluster using Docker Compose:

git clone https://github.com/scylladb/scylla-code-samples
cd scylla-code-samples/spring/springdemo-custom
docker-compose -f docker-compose-spring.yml up -d

Wait one minute or so and check that the cluster is up and running:

docker exec -it scylla-node1 nodetool status

Time Series Data Model

You can learn more about compaction strategies here. Once the cluster status is ready, create the data model using cqlsh for the stock application. You can also see the data schema in the sample repo.

docker exec -it scylla-node1 cqlsh

CREATE KEYSPACE IF NOT EXISTS springdemo WITH replication = {'class':'NetworkTopologyStrategy', 'replication_factor':3} AND durable_writes = false;
CREATE TABLE IF NOT EXISTS springdemo.stocks (symbol text, date timestamp, value decimal, PRIMARY KEY (symbol, date)) WITH CLUSTERING ORDER BY (date DESC);
ALTER TABLE springdemo.stocks WITH compaction = { 'class' : 'TimeWindowCompactionStrategy', 'compaction_window_unit' : 'DAYS', 'compaction_window_size' : 31 };
ALTER TABLE springdemo.stocks WITH default_time_to_live = 94608000;
Exit

The above creates a keyspace called springdemo and sets the replication to 3, equal to the cluster size. The table stocks contains the columns symbol, date, and value to store the time series stock data. It also sets the table stocks compaction policy to TWCS with 31 days as the compaction window. Also, since it is assumed you are only interested in the last three years of quotes, the TTL is set (in seconds) for the table. Time-Window Compaction Strategy compacts SSTables within each time window using the Size-tiered Compaction Strategy (STCS). SSTables from different time windows are never compacted together. You set the TimeWindowCompactionStrategy parameters when you create a table using a CQL command.

Time-Window Compaction Strategy (TWCS)

It works as follows:

• A time window is configured. The window is determined by the compaction window size compaction_window_size and the time unit (compaction_window_unit).
• SSTables created within the time window are compacted using Size-tiered Compaction Strategy (STCS).
• Once a time window ends, take all SSTables created during the time window and compact the data into one SSTable.
• The final resulting SSTable is never compacted with other time-windows SSTables.

If the time window were for one day, at the end of the day, the SSTables accumulated for that day only would be compacted into one SSTable.

Build the App With the Example Code

The spring boot application has a default configuration file in the /spring/springdemo-custom/src/main/resources/ directory named application.yml. In it, you can add custom config settings such as the ScyllaDB nodes IP address and port, credentials, and the name of your data center (DC1 by default). Edit application.yml with your system settings. Then, build the application:

./gradlew build

Launch the Spring Boot application:

java -jar ./build/libs/springdemo-custom-0.0.1-SNAPSHOT.jar

The application runs as an HTTP server and, by default, listens to port 8082.

Test the Application’s API

Download the Postman app and install it to your dev environment. You can import an existing API definition into Postman. API definitions can be imported from a local file or directory, a URL, raw text, a code repository, or an API gateway. 

This is the API definition file named /spring/springdemo-custom/Springdemo-custom.postman_collection.json: 

{
	"info": {
		"_postman_id": "2e4045bc-4b53-4773-88f4-a22c984d87bb",
		"name": "Springdemo-custom",
		"schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json"
	},
	"item": [
		{
			"name": "create stock AAPL",
			"request": {
				"method": "POST",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"symbol\": \"AAPL\",\r\n    \"date\":\"202111181200\",\r\n    \"value\": \"26\"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": {
					"raw": "localhost:8082/api/v1/stocks",
					"host": [
						"localhost"
					],
					"port": "8082",
					"path": [
						"api",
						"v1",
						"stocks"
					]
				}
			},
			"response": []
		},
		{
			"name": "create stock MSFT",
			"request": {
				"method": "POST",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"symbol\": \"MSFT\",\r\n    \"date\":\"202111121200\",\r\n    \"value\": \"12\"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": {
					"raw": "localhost:8082/api/v1/stocks",
					"host": [
						"localhost"
					],
					"port": "8082",
					"path": [
						"api",
						"v1",
						"stocks"
					]
				}
			},
			"response": []
		},
		{
			"name": "delete stock MSFT",
			"request": {
				"method": "POST",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"symbol\": \"MSFT\",\r\n    \"date\":\"202111121200\",\r\n    \"value\": \"12\"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": {
					"raw": "localhost:8082/api/v1/stocks",
					"host": [
						"localhost"
					],
					"port": "8082",
					"path": [
						"api",
						"v1",
						"stocks"
					]
				}
			},
			"response": []
		},
		{
			"name": "get stocks",
			"request": {
				"method": "GET",
				"header": [],
				"url": {
					"raw": "localhost:8082/api/v1/stocks",
					"host": [
						"localhost"
					],
					"port": "8082",
					"path": [
						"api",
						"v1",
						"stocks"
					]
				}
			},
			"response": []
		}
	]
}

Launch the app and import it by clicking Collections, Import, File, and Upload Files. This file contains the stock application API definition.

After importing the JSON file, you will see a list of collections used to interact with the stock application’s API with predefined data requests as an example. The first two are used to send HTTP POST requests to create the AAPL and MSFT stocks with a stock value and date. After creating the stocks, you can then use the list API to get a list of stocks. All the stock data is stored in the created ScyllaDB table stocks.

Click Body to see the Post data to be sent through the HTTP API to the Java sample application. You can see that the JSON includes the symbol, date, and value items. Note that if your Java sample application isn’t running in localhost, you need to replace localhost with the actual IP on which your application is running. Click Send to use Postman to send the HTTP POST request to the application as below.

This is the response after you send the request to create the APPL stock:

Next, select create stock MSFT and click the Send button to create the stock.

Summary

In this lab, you built an application with the Spring Boot framework that stores time series stock data to ScyllaDB. It uses the TimeWindowCompactionStrategy. Check out the full lesson on ScyllaDB University; it’s more detailed, offers additional resources, and contains an extra part where you can see the data by querying the ScyllaDB cluster.