Confluent Platform 7.0 と Cluster Linking でクラウドへのリアルタイムブリッジを構築 | ブログを読む

Announcing ksqlDB 0.20.0

We’re pleased to announce ksqlDB 0.20.0!

The 0.20 ksqlDB release includes support for the DATE and TIME data types, along with functionality for working with these types. The DATE type represents a calendar date, and the TIME type represents a time of day in millisecond precision. They are useful for representing less specific time values, such as birthdays or the time of a daily alarm that would not make as much sense as a TIMESTAMP value. The following looks at how these types would be used.

DATE and TIME data types ksqlDB

Consider a movie theater company named Franz Theaters that uses ksqlDB to manage movie ticket sales data. They have an Apache Kafka® topic streaming Avro data with the following format:

{
   "name": "ticket_sales",
   "type": "record",
   "fields": [
      {"name": "purchaser_name", "type": "string"},
      {"name": "movie_title", "type": "string"},
      {"name": "ticket_date", "type": "int", "logicalType": "date"},
      {"name": "showtime", "type": "int", "logicalType": "time-millis"},
      {"name": "purchase_timestamp", "type": "long", "logicalType": "timestamp-millis"},
      {"name": "location", "type": "string"}
   ]
}

Prior to 0.20, the date and time types were interpreted as INTEGER data, but now ksqlDB recognizes the logical types and interprets them as DATE or TIME. Franz Theaters uses the following stream to work with their ticket sales data in ksqlDB:

CREATE STREAM ticket_sales (
  purchaser_name VARCHAR,
  movie_title VARCHAR,
  ticket_date DATE,
  showtime TIME,
  purchase_timestamp TIMESTAMP,
  location VARCHAR
) WITH (
  kafka_topic=’ticket_sales’,
  value_format=’avro’
);

Oh no! Franz Theaters has just been informed that their Mountain View location will have to close on October 11, 2021 from 11 a.m. to 3 p.m. due to an extremely loud road upgrade right outside the theater. To find a list of customers to contact, they use the following query:

> SELECT * FROM ticket_sales
WHERE location = ‘Mountain View’
AND ticket_date = ‘2021-10-11’
AND showtime BETWEEN ‘11:00’ AND ‘15:00’
EMIT CHANGES;
-----------------------------------------------------------------------------------------------------------------
 purchaser_name | movie_title             | ticket_date    | showtime | purchase_timestamp       | location
-----------------------------------------------------------------------------------------------------------------
 Jessica Riley  | Horse And Dog           | 2021-10-11     | 11:00    | 2021-06-31T12:18:39.446  | Mountain View
 Jeremy Gonzales| Hidden In The Mines     | 2021-10-11     | 12:30    | 2021-07-04T13:27:02.210  | Mountain View
 Justin Evans   | Harold                  | 2021-10-11     | 13:00    | 2021-07-04T13:29:45.447  | Mountain View

They plan to use the results of this query to send an automated email to the customers, but they would like to represent the ticket date in a less ambiguous format. To do so, they use the FORMAT_DATE function:

> SELECT
  purchaser_name,
  movie_title,
  FORMAT_DATE(ticket_date, ‘MMMM dd, YYYY’) AS formatted_ticket_date,
  showtime,
  purchase_timestamp,
  location
FROM ticket_sales
WHERE location = ‘Mountain View’
AND ticket_date = ‘2021-10-11’
AND showtime BETWEEN ‘11:00’ AND ‘15:00’;
------------------------------------------------------------------------------------------------------------------------
 purchaser_name | movie_title             | formatted_ticket_date | showtime | purchase_timestamp       | location
------------------------------------------------------------------------------------------------------------------------
 Jessica Riley  | Horse And Dog           | October 11, 2021      | 11:00    | 2021-06-31T12:18:39.446  | Mountain View
 Jeremy Gonzales| Hidden In The Mines     | October 11, 2021      | 12:30    | 2021-07-04T13:27:02.210  | Mountain View
 Justin Evans   | Harold                  | October 11, 2021      | 13:00    | 2021-07-04T13:29:45.447  | Mountain View

After reaching out to all the affected customers, everyone agreed that they would be happy to have their ticket transferred to the same time the following week. To update all of the ticket dates in the stream, they used the following query:

INSERT INTO ticket_sales
SELECT
  purchaser_name,
  movie_title,
  DATEADD(DAYS, 7, ticket_date) AS ticket_date,
  showtime,
  purchase_timestamp,
  location
FROM ticket_sales
WHERE location = ‘Mountain View’
AND ticket_date = ‘2021-10-11’
AND showtime BETWEEN ‘14:00’ AND ‘17:00’;

And on October 18, 2021, everyone happily saw the movie with no loud, distracting road work—all thanks to ksqlDB!

For more information on the DATE and TIME data types, check out the ksqlDB documentation.

Get started with ksqlDB

Get started with ksqlDB today, via the standalone distribution or with Confluent, and join the community to ask a question and find new resources.

Get Started

Chittaranjan Prasad is an engineer on the ksqlDB team at Confluent. He joined in 2020 after completing his bachelor’s degree in computer science and economics at the University of California, Berkeley.

Did you like this blog post? Share it now

Subscribe to the Confluent blog

More Articles Like This

Getting Started with Apache Kafka in Python

Welcome Pythonistas to the streaming data world centered around Apache Kafka®! If you’re using Python and ready to get hands-on with Kafka, then you’re in the right place. This blog

Introducing 18 New, Fully Managed Connectors in Confluent Cloud

In our February 2020 blog post Celebrating Over 100 Supported Apache Kafka® Connectors, we announced support for more than 100 connectors on Confluent Platform. Since then, we have been focused

A Guide to Stream Processing and ksqlDB Fundamentals

Event streaming applications are a powerful way to react to events as they happen and to take advantage of data while it is fresh. However, they can be a challenge