Exploring Hazelcast SQL Data Types

Overview

Hazelcast has in-memory data platform, it offers distributed data structures and stream data processing capabilities. One of its important features is the ability to perform SQL queries over distributed data. Hazelcast SQL makes it easier to interact with distributed data structures, such as maps, in a manner similar to relational databases.

Understanding Hazelcast SQL data types will help you to write efficient queries and managing Hazelcast data correctly.

In this blog, we’ll dive into the key data types supported by Hazelcast SQL and their mapping to Java types.

1. Numeric Data Types

Numerical operations are fundamental in SQL queries. Hazelcast SQL supports the following numeric types:

• TINYINT: Represents a 1-byte integer. It holds values ranging from -128 to 127.
• SMALLINT: A 2-byte integer, storing values between -32,768 to 32,767.
• INTEGER: A commonly used 4-byte integer, ideal for most integer values (-2^31 to 2^31-1).
• BIGINT: For larger integer values, this 8-byte integer type can store from -2^63 to 2^63-1.
• DECIMAL: Arbitrary precision fixed-point numbers, often used for precise calculations like financial data.
• DOUBLE: An 8-byte floating-point number, useful for representing real numbers.

These types allow you to handle a wide range of numeric data, from small integers to large decimal numbers, catering to various application needs.

2. Boolean Data Type

• BOOLEAN: Represents logical TRUE or FALSE values. It is essential when filtering data based on logical conditions, for instance in WHERE clauses:

Example usage:

SELECT * FROM employees WHERE active = TRUE;

3. String Data Types

Hazelcast provides flexible string Hazelcast SQL Data Types for handling textual data:

• VARCHAR: A variable-length character string. It is the most common type for storing text data, such as names, addresses, or descriptions. You can store any length of string up to the maximum allowed by the system.

Example usage:

SELECT name FROM customers WHERE name LIKE 'Akash%';

4. Date and Time Data Types

Handling dates and times is essential for time-based operations, and Hazelcast SQL supports several types to cover different needs:

• DATE: Stores only the date (year, month, day).
• TIME: Stores only the time (hour, minute, second).
• TIMESTAMP: Combines both date and time.
• TIMESTAMP WITH TIME ZONE: Extends TIMESTAMP by including time zone information for better time tracking across regions.

These data types are useful when working with event logs, historical records, or other time-based data. You can filter or perform time-based queries effectively:

SELECT event_date, event_time FROM logs WHERE event_date = '2024-09-01';

5. Binary Data Type

Sometimes, you may need to store raw binary data, such as files or byte arrays:

• VARBINARY: A variable-length binary data type used for storing byte arrays. It is ideal when dealing with media files, encrypted data, or other binary formats.

Example:

SELECT document FROM files WHERE file_id = 101;

6. Special Data Types

• NULL: Like any other SQL engine, Hazelcast supports the NULL value to represent the absence of a value in a field. It’s important to handle NULL values correctly, especially in queries that involve arithmetic or string operations.
• OBJECT: This is a Hazelcast-specific data type used for storing complex objects, such as Java objects that don’t directly map to any SQL types.

Mapping Hazelcast SQL Data Types to Java Types

When working with Hazelcast from a Java application, each Hazelcast SQL data type maps to a corresponding Java type. Here’s how they align:

• TINYINT → byte
• SMALLINT → short
• INTEGER → int
• BIGINT → long
• DECIMAL → BigDecimal
• DOUBLE → double
• BOOLEAN → boolean
• VARCHAR → String
• DATE → java.sql.Date
• TIME → java.sql.Time
• TIMESTAMP → java.sql.Timestamp
• VARBINARY → byte[]

These mappings ensure seamless integration between Hazelcast SQL and Java-based applications, making data access and manipulation straightforward.

Conclusion

Hazelcast SQL provides multiple set of data types that make it easy to understand and work with Hazelcast distributed data in a familiar SQL-like manner. From numeric types to handling complex objects, it covers a wide range of user use cases. Whether you’re developing a distributed microservices, and handling real-time data processing, or querying large datasets, Hazelcast SQL Data Types gives you the flexibility as user need.

Understanding Hazelcast SQL Data Types will help you write efficient queries and build distributed applications on top of Hazelcast.

How to use these data types in Hazelcast SQL? explore it.