SQL and Scan Queries | Ignite Documentation

Ignite Summit 2025 – Call for Speakers Now Open – Learn more 

Apache Ignite logo
Edit

SQL and Scan Queries

Operation codes

Upon a successful handshake with an Ignite server node, a client can start performing various SQL and scan queries by sending a request (see request/response structure below) with a specific operation code:

Operation OP_CODE

OP_QUERY_SQL

2002

OP_QUERY_SQL_CURSOR_GET_PAGE

2003

OP_QUERY_SQL_FIELDS

2004

OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE

2005

OP_QUERY_SCAN

2000

OP_QUERY_SCAN_CURSOR_GET_PAGE

2001

OP_RESOURCE_CLOSE

0

Note that the above mentioned op_codes are part of the request header, as explained here.

Note

Customs Methods Used in Sample Code Snippets Implementation

Some of the code snippets below use readDataObject(…​) introduced in this section and little-endian versions of methods for reading and writing multiple-byte values that are covered in this example.

OP_QUERY_SQL

Executes an SQL query over data stored in the cluster. The query returns the whole record (key and value).

Request Type Description

Header

Request header.

int

Cache ID: Java-style hash code of the cache name

byte

Use 0. This field is deprecated and will be removed in the future.

String

Name of a type or SQL table.

String

SQL query string.

int

Query argument count.

Data Object

Query argument.

Repeat for as many times as the query argument count that is passed in the previous parameter.

bool

Distributed joins.

bool

Local query.

bool

Replicated only - Whether query contains only replicated tables or not.

int

Cursor page size.

long

Timeout (miliseconds).

Timeout value should be non-negative. Zero value disables timeout.

Response includes the first page of the result.

Response Type Description

Header

Response header.

long

Cursor id. Can be closed with OP_RESOURSE_CLOSE.

int

Row count for the first page.

Key Data Object + Value Data Object

Records in the form of key-value pairs.

Repeat for as many times as the row count obtained in the previous parameter.

bool

Indicates whether more results are available to be fetched with OP_QUERY_SQL_CURSOR_GET_PAGE. When true, query cursor is closed automatically.

 
String entityName = "Person";
int entityNameLength = getStrLen(entityName); // UTF-8 bytes

String sql = "Select * from Person";
int sqlLength = getStrLen(sql);

DataOutputStream out = new DataOutputStream(socket.getOutputStream());

// Request header
writeRequestHeader(34 + entityNameLength + sqlLength, OP_QUERY_SQL, 1, out);

// Cache id
String queryCacheName = "personCache";
writeIntLittleEndian(queryCacheName.hashCode(), out);

// Flag = none
writeByteLittleEndian(0, out);

// Query Entity
writeString(entityName, out);

// SQL query
writeString(sql, out);

// Argument count
writeIntLittleEndian(0, out);

// Joins
out.writeBoolean(false);

// Local query
out.writeBoolean(false);

// Replicated
out.writeBoolean(false);

// cursor page size
writeIntLittleEndian(1, out);

// Timeout
writeLongLittleEndian(5000, out);
// Read result
DataInputStream in = new DataInputStream(socket.getInputStream());

// Response header
readResponseHeader(in);

long cursorId = readLongLittleEndian(in);

int rowCount = readIntLittleEndian(in);

// Read entries (as user objects)
for (int i = 0; i < rowCount; i++) {
  Object key = readDataObject(in);
  Object val = readDataObject(in);

  System.out.println("CacheEntry: " + key + ", " + val);
}

boolean moreResults = readBooleanLittleEndian(in);

OP_QUERY_SQL_CURSOR_GET_PAGE

Retrieves the next SQL query cursor page by cursor id from OP_QUERY_SQL.

Request Type Description

Header

Request header.

long

Cursor id.

Response format looks as follows:

Response Type Description

Header

Response header.

long

Cursor id.

int

Row count.

Key Data Object + Value Data Object

Records in the form of key-value pairs.

Repeat for as many times as the row count obtained in the previous parameter.

bool

Indicates whether more results are available to be fetched with OP_QUERY_SQL_CURSOR_GET_PAGE. When true, query cursor is closed automatically.

 
DataOutputStream out = new DataOutputStream(socket.getOutputStream());

// Request header
writeRequestHeader(8, OP_QUERY_SQL_CURSOR_GET_PAGE, 1, out);

// Cursor Id (received from Sql query operation)
writeLongLittleEndian(cursorId, out);
// Read result
DataInputStream in = new DataInputStream(socket.getInputStream());

// Response header
readResponseHeader(in);

int rowCount = readIntLittleEndian(in);

// Read entries (as user objects)
for (int i = 0; i < rowCount; i++){
  Object key = readDataObject(in);
  Object val = readDataObject(in);

  System.out.println("CacheEntry: " + key + ", " + val);
}

boolean moreResults = readBooleanLittleEndian(in);

OP_QUERY_SQL_FIELDS

Performs SQL fields query.

Request Type Description

Header

Request header.

int

Cache ID: Java-style hash code of the cache name.

byte

Use 0. This field is deprecated and will be removed in the future.

String

Schema for the query; can be null, in which case default PUBLIC schema will be used.

int

Query cursor page size.

int

Max rows.

String

SQL

int

Argument count.

Data Object

Query argument.

Repeat for as many times as the query argument count that is passed in the previous parameter.

byte

Statement type.

ANY = 0

SELECT = 1

UPDATE = 2

bool

Distributed joins

bool

Local query.

bool

Replicated only - Whether query contains only replicated tables or not.

bool

Enforce join order.

bool

Collocated - Whether your data is co-located or not.

bool

Lazy query execution.

long

Timeout (milliseconds).

bool

Include field names.
Response Type Description

Header

Response header.

long

Cursor id. Can be closed with OP_RESOURCE_CLOSE.

int

Field (column) count.

String (optional)

Needed only when IncludeFieldNames is true in the request.

Column name.

Repeat for as many times as the field count that is retrieved in the previous parameter.

int

First page row count. Data Object Column (field) value. Repeat for as many times as the field count.

Repeat for as many times as the row count that is retrieved in the previous parameter.

bool

Indicates whether more results are available to be retrieved with OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE.

 
String sql = "Select id, salary from Person";
int sqlLength = sql.getBytes("UTF-8").length;

String sqlSchema = "PUBLIC";
int sqlSchemaLength = sqlSchema.getBytes("UTF-8").length;

DataOutputStream out = new DataOutputStream(socket.getOutputStream());

// Request header
writeRequestHeader(43 + sqlLength + sqlSchemaLength, OP_QUERY_SQL_FIELDS, 1, out);

// Cache id
String queryCacheName = "personCache";
int cacheId = queryCacheName.hashCode();
writeIntLittleEndian(cacheId, out);

// Flag = none
writeByteLittleEndian(0, out);

// Schema
writeByteLittleEndian(9, out);
writeIntLittleEndian(sqlSchemaLength, out);
out.writeBytes(sqlSchema); //sqlSchemaLength

// cursor page size
writeIntLittleEndian(2, out);

// Max Rows
writeIntLittleEndian(5, out);

// SQL query
writeByteLittleEndian(9, out);
writeIntLittleEndian(sqlLength, out);
out.writeBytes(sql);//sqlLength

// Argument count
writeIntLittleEndian(0, out);

// Statement type
writeByteLittleEndian(1, out);

// Joins
out.writeBoolean(false);

// Local query
out.writeBoolean(false);

// Replicated
out.writeBoolean(false);

// Enforce join order
out.writeBoolean(false);

// collocated
out.writeBoolean(false);

// Lazy
out.writeBoolean(false);

// Timeout
writeLongLittleEndian(5000, out);

// Replicated
out.writeBoolean(false);
// Read result
DataInputStream in = new DataInputStream(socket.getInputStream());

// Response header
readResponseHeader(in);

long cursorId = readLongLittleEndian(in);

int colCount = readIntLittleEndian(in);

int rowCount = readIntLittleEndian(in);

// Read entries
for (int i = 0; i < rowCount; i++) {
  long id = (long) readDataObject(in);
  int salary = (int) readDataObject(in);

  System.out.println("Person id: " + id + "; Person Salary: " + salary);
}

boolean moreResults = readBooleanLittleEndian(in);

OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE

Retrieves the next query result page by cursor id from OP_QUERY_SQL_FIELDS .

Request Type Description

Header

Request header.

long

Cursor id received from OP_QUERY_SQL_FIELDS
Response Type Description

Header

Response header.

int

Row count.

Data Object

Column (field) value. Repeat for as many times as the field count.

Repeat for as many times as the row count that is retrieved in the previous parameter.

bool

Indicates whether more results are available to be retrieved with OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE

 
DataOutputStream out = new DataOutputStream(socket.getOutputStream());

// Request header
writeRequestHeader(8, QUERY_SQL_FIELDS_CURSOR_GET_PAGE, 1, out);

// Cursor Id
writeLongLittleEndian(1, out);
// Read result
DataInputStream in = new DataInputStream(socket.getInputStream());

// Response header
readResponseHeader(in);

int rowCount = readIntLittleEndian(in);

// Read entries (as user objects)
for (int i = 0; i < rowCount; i++){
   // read data objects * column count.
}

boolean moreResults = readBooleanLittleEndian(in);

OP_QUERY_SCAN

Performs scan query.

Request Type Description

Header

Request header.

int

Cache ID: Java-style hash code of the cache name.

byte

Flag. Pass 0 for default, or 1 to keep the value in binary form.

Data Object

Filter object. Can be null if you are not going to filter data on the cluster. The filter class has to be added to the classpath of the server nodes.

byte

Filter platform:

JAVA = 1

DOTNET = 2

CPP = 3

Pass this parameter only if filter object is not null.

int

Cursor page size.

int

Number of partitions to query (negative to query entire cache).

bool

Local flag - whether this query should be executed on local node only.
Response Type Description

Header

Response header.

long

Cursor id.

int

Row count.

Key Data Object + Value Data Object

Records in the form of key-value pairs.

Repeat for as many times as the row count obtained in the previous parameter.

bool

Indicates whether more results are available to be fetched with OP_QUERY_SCAN_CURSOR_GET_PAGE. When true, query cursor is closed automatically.

 
DataOutputStream out = new DataOutputStream(socket.getOutputStream());

// Request header
writeRequestHeader(15, OP_QUERY_SCAN, 1, out);

// Cache id
String queryCacheName = "personCache";
writeIntLittleEndian(queryCacheName.hashCode(), out);

// flags
writeByteLittleEndian(0, out);

// Filter Object
writeByteLittleEndian(101, out); // null

// Cursor page size
writeIntLittleEndian(1, out);

// Partition to query
writeIntLittleEndian(-1, out);

// local flag
out.writeBoolean(false);
// Read result
DataInputStream in = new DataInputStream(socket.getInputStream());

//Response header
readResponseHeader(in);

// Cursor id
long cursorId = readLongLittleEndian(in);

int rowCount = readIntLittleEndian(in);

// Read entries (as user objects)
for (int i = 0; i < rowCount; i++) {
  Object key = readDataObject(in);
  Object val = readDataObject(in);

  System.out.println("CacheEntry: " + key + ", " + val);
}

boolean moreResults = readBooleanLittleEndian(in);

OP_QUERY_SCAN_CURSOR_GET_PAGE

Fetches the next SQL query cursor page by cursor id that is obtained from OP_QUERY_SCAN.

Request Type Description

Header

Request header.

long

Cursor id.
Response Type Description

Header

Response header.

long

Cursor id.

long

Row count.

Key Data Object + Value Data Object

Records in the form of key-value pairs.

Repeat for as many times as the row count obtained in the previous parameter.

bool

Indicates whether more results are available to be fetched with OP_QUERY_SCAN_CURSOR_GET_PAGE. When true, query cursor is closed automatically.

OP_RESOURCE_CLOSE

Closes a resource, such as query cursor.

Request Type Description

Header

Request header.

long

Resource id.
Response Type Description

Header

Response header

 
DataOutputStream out = new DataOutputStream(socket.getOutputStream());

// Request header
writeRequestHeader(8, OP_RESOURCE_CLOSE, 1, out);

// Resource id
long cursorId = 1;
writeLongLittleEndian(cursorId, out);
// Read result
DataInputStream in = new DataInputStream(socket.getInputStream());

// Response header
readResponseHeader(in);
© 2024 The Apache Software Foundation.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.