apache-ignite

1
// Licensed to the Apache Software Foundation (ASF) under one or more
2
// contributor license agreements.  See the NOTICE file distributed with
3
// this work for additional information regarding copyright ownership.
4
// The ASF licenses this file to You under the Apache License, Version 2.0
5
// (the "License"); you may not use this file except in compliance with
6
// the License.  You may obtain a copy of the License at
7
//
8
// http://www.apache.org/licenses/LICENSE-2.0
9
//
10
// Unless required by applicable law or agreed to in writing, software
11
// distributed under the License is distributed on an "AS IS" BASIS,
12
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
// See the License for the specific language governing permissions and
14
// limitations under the License.
15
= SQL and Scan Queries
16

17
== Operation codes
18

19
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:
20

21

22
[cols="2,1",opts="header"]
23
|===
24
|Operation |   OP_CODE
25
|OP_QUERY_SQL|    2002
26
|OP_QUERY_SQL_CURSOR_GET_PAGE|    2003
27
|OP_QUERY_SQL_FIELDS| 2004
28
|OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE| 2005
29
|OP_QUERY_SCAN|   2000
30
|OP_QUERY_SCAN_CURSOR_GET_PAGE|   2001
31
|OP_RESOURCE_CLOSE|   0
32
|===
33

34

35
Note that the above mentioned op_codes are part of the request header, as explained link:binary-client-protocol/binary-client-protocol#standard-message-header[here].
36

37
[NOTE]
38
====
39
[discrete]
40
=== Customs Methods Used in Sample Code Snippets Implementation
41

42
Some of the code snippets below use `readDataObject(...)` introduced in link:binary-client-protocol/binary-client-protocol#data-objects[this section] and little-endian versions of methods for reading and writing multiple-byte values that are covered in link:binary-client-protocol/binary-client-protocol#data-objects[this example].
43
====
44

45

46
== OP_QUERY_SQL
47

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

50

51
[cols="1,2",opts="header"]
52
|===
53
|Request Type |    Description
54
|Header|  Request header.
55
|int| Cache ID: Java-style hash code of the cache name
56
|byte|    Use 0. This field is deprecated and will be removed in the future.
57
|String|  Name of a type or SQL table.
58
|String|  SQL query string.
59
|int| Query argument count.
60
|Data Object| Query argument.
61

62
Repeat for as many times as the query argument count that is passed in the previous parameter.
63
|bool|    Distributed joins.
64
|bool|    Local query.
65
|bool|    Replicated only - Whether query contains only replicated tables or not.
66
|int| Cursor page size.
67
|long|    Timeout (miliseconds).
68

69
Timeout value should be non-negative. Zero value disables timeout.
70
|===
71

72

73
Response includes the first page of the result.
74

75
[cols="1,2",opts="header"]
76
|===
77
|Response Type |   Description
78
|Header|  Response header.
79
|long|    Cursor id. Can be closed with OP_RESOURSE_CLOSE.
80
|int| Row count for the first page.
81
|Key Data Object + Value Data Object| Records in the form of key-value pairs.
82

83
Repeat for as many times as the row count obtained in the previous parameter.
84
|bool|    Indicates whether more results are available to be fetched with OP_QUERY_SQL_CURSOR_GET_PAGE.
85
When true, query cursor is closed automatically.
86
|===
87

88

89
[tabs]
90
--
91
tab:Request[]
92

93
[source, java]
94
----
95
String entityName = "Person";
96
int entityNameLength = getStrLen(entityName); // UTF-8 bytes
97

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

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

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

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

110
// Flag = none
111
writeByteLittleEndian(0, out);
112

113
// Query Entity
114
writeString(entityName, out);
115

116
// SQL query
117
writeString(sql, out);
118

119
// Argument count
120
writeIntLittleEndian(0, out);
121

122
// Joins
123
out.writeBoolean(false);
124

125
// Local query
126
out.writeBoolean(false);
127

128
// Replicated
129
out.writeBoolean(false);
130

131
// cursor page size
132
writeIntLittleEndian(1, out);
133

134
// Timeout
135
writeLongLittleEndian(5000, out);
136
----
137

138
tab:Response[]
139

140
[source, java]
141
----
142
// Read result
143
DataInputStream in = new DataInputStream(socket.getInputStream());
144

145
// Response header
146
readResponseHeader(in);
147

148
long cursorId = readLongLittleEndian(in);
149

150
int rowCount = readIntLittleEndian(in);
151

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

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

160
boolean moreResults = readBooleanLittleEndian(in);
161

162
----
163

164
--
165

166

167

168
== OP_QUERY_SQL_CURSOR_GET_PAGE
169

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

172
[cols="1,2",opts="header"]
173
|===
174
|Request Type |    Description
175
|Header|  Request header.
176
|long|    Cursor id.
177
|===
178

179

180
Response format looks as follows:
181

182
[cols="1,2",opts="header"]
183
|===
184
|Response Type |   Description
185
|Header|  Response header.
186
|long|    Cursor id.
187
|int| Row count.
188
|Key Data Object + Value Data Object| Records in the form of key-value pairs.
189

190
Repeat for as many times as the row count obtained in the previous parameter.
191
|bool|    Indicates whether more results are available to be fetched with OP_QUERY_SQL_CURSOR_GET_PAGE.
192
When true, query cursor is closed automatically.
193

194
|===
195

196
[tabs]
197
--
198
tab:Request[]
199

200
[source, java]
201
----
202
DataOutputStream out = new DataOutputStream(socket.getOutputStream());
203

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

207
// Cursor Id (received from Sql query operation)
208
writeLongLittleEndian(cursorId, out);
209
----
210

211
tab:Response[]
212

213
[source, java]
214
----
215
// Read result
216
DataInputStream in = new DataInputStream(socket.getInputStream());
217

218
// Response header
219
readResponseHeader(in);
220

221
int rowCount = readIntLittleEndian(in);
222

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

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

231
boolean moreResults = readBooleanLittleEndian(in);
232

233
----
234

235
--
236

237

238
== OP_QUERY_SQL_FIELDS
239

240
Performs SQL fields query.
241

242
[cols="1,2",opts="header"]
243
|===
244
|Request Type |    Description
245
|Header|  Request header.
246
|int| Cache ID: Java-style hash code of the cache name.
247
|byte|    Use 0. This field is deprecated and will be removed in the future.
248
|String|  Schema for the query; can be null, in which case default PUBLIC schema will be used.
249
|int| Query cursor page size.
250
|int| Max rows.
251
|String|  SQL
252
|int| Argument count.
253
|Data Object| Query argument.
254

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

257
|byte|    Statement type.
258

259
ANY = 0
260

261
SELECT = 1
262

263
UPDATE = 2
264

265
|bool|    Distributed joins
266
|bool|    Local query.
267
|bool|    Replicated only - Whether query contains only replicated tables or not.
268
|bool|    Enforce join order.
269
|bool|    Collocated - Whether your data is co-located or not.
270
|bool|    Lazy query execution.
271
|long|    Timeout (milliseconds).
272
|bool|    Include field names.
273
|===
274

275

276
[cols="1,2",opts="header"]
277
|===
278
|Response Type |   Description
279
|Header|  Response header.
280
|long|    Cursor id. Can be closed with OP_RESOURCE_CLOSE.
281
|int| Field (column) count.
282
|String (optional)|   Needed only when IncludeFieldNames is true in the request.
283

284
Column name.
285

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

288
|int| First page row count.
289
Data Object Column (field) value. Repeat for as many times as the field count.
290

291
Repeat for as many times as the row count that is retrieved in the previous parameter.
292
|bool|    Indicates whether more results are available to be retrieved with OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE.
293
|===
294

295
[tabs]
296
--
297
tab:Request[]
298

299
[source, java]
300
----
301
String sql = "Select id, salary from Person";
302
int sqlLength = sql.getBytes("UTF-8").length;
303

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

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

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

312
// Cache id
313
String queryCacheName = "personCache";
314
int cacheId = queryCacheName.hashCode();
315
writeIntLittleEndian(cacheId, out);
316

317
// Flag = none
318
writeByteLittleEndian(0, out);
319

320
// Schema
321
writeByteLittleEndian(9, out);
322
writeIntLittleEndian(sqlSchemaLength, out);
323
out.writeBytes(sqlSchema); //sqlSchemaLength
324

325
// cursor page size
326
writeIntLittleEndian(2, out);
327

328
// Max Rows
329
writeIntLittleEndian(5, out);
330

331
// SQL query
332
writeByteLittleEndian(9, out);
333
writeIntLittleEndian(sqlLength, out);
334
out.writeBytes(sql);//sqlLength
335

336
// Argument count
337
writeIntLittleEndian(0, out);
338

339
// Statement type
340
writeByteLittleEndian(1, out);
341

342
// Joins
343
out.writeBoolean(false);
344

345
// Local query
346
out.writeBoolean(false);
347

348
// Replicated
349
out.writeBoolean(false);
350

351
// Enforce join order
352
out.writeBoolean(false);
353

354
// collocated
355
out.writeBoolean(false);
356

357
// Lazy
358
out.writeBoolean(false);
359

360
// Timeout
361
writeLongLittleEndian(5000, out);
362

363
// Replicated
364
out.writeBoolean(false);
365
----
366

367
tab:Response[]
368

369
[source, java]
370
----
371
// Read result
372
DataInputStream in = new DataInputStream(socket.getInputStream());
373

374
// Response header
375
readResponseHeader(in);
376

377
long cursorId = readLongLittleEndian(in);
378

379
int colCount = readIntLittleEndian(in);
380

381
int rowCount = readIntLittleEndian(in);
382

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

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

391
boolean moreResults = readBooleanLittleEndian(in);
392

393
----
394

395
--
396

397

398
== OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE
399

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

402
[cols="1,2",opts="header"]
403
|===
404
|Request Type|    Description
405
|Header|  Request header.
406
|long|    Cursor id received from OP_QUERY_SQL_FIELDS
407
|===
408

409

410
[cols="1,2",opts="header"]
411
|===
412
|Response Type |   Description
413
|Header|  Response header.
414
|int| Row count.
415
|Data Object| Column (field) value. Repeat for as many times as the field count.
416

417
Repeat for as many times as the row count that is retrieved in the previous parameter.
418
|bool|    Indicates whether more results are available to be retrieved with OP_QUERY_SQL_FIELDS_CURSOR_GET_PAGE
419
|===
420

421
[tabs]
422
--
423
tab:Request[]
424

425
[source, java]
426
----
427
DataOutputStream out = new DataOutputStream(socket.getOutputStream());
428

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

432
// Cursor Id
433
writeLongLittleEndian(1, out);
434
----
435

436
tab:Response[]
437

438
[source, java]
439
----
440
// Read result
441
DataInputStream in = new DataInputStream(socket.getInputStream());
442

443
// Response header
444
readResponseHeader(in);
445

446
int rowCount = readIntLittleEndian(in);
447

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

453
boolean moreResults = readBooleanLittleEndian(in);
454

455
----
456

457
--
458

459

460
== OP_QUERY_SCAN
461

462
Performs scan query.
463

464
[cols="1,2",opts="header"]
465
|===
466
|Request Type |    Description
467
|Header|  Request header.
468
|int| Cache ID: Java-style hash code of the cache name.
469
|byte|    Flag. Pass 0 for default, or 1 to keep the value in binary form.
470
|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.
471
|byte|    Filter platform:
472

473
JAVA = 1
474

475
DOTNET = 2
476

477
CPP = 3
478

479
Pass this parameter only if filter object is not null.
480
|int| Cursor page size.
481
|int| Number of partitions to query (negative to query entire cache).
482
|bool|    Local flag - whether this query should be executed on local node only.
483
|===
484

485

486
[cols="1,2",opts="header"]
487
|===
488
|Response Type |   Description
489
|Header|  Response header.
490
|long|    Cursor id.
491
|int| Row count.
492
|Key Data Object + Value Data Object| Records in the form of key-value pairs.
493

494
Repeat for as many times as the row count obtained in the previous parameter.
495
|bool|    Indicates whether more results are available to be fetched with OP_QUERY_SCAN_CURSOR_GET_PAGE.
496
When true, query cursor is closed automatically.
497
|===
498

499
[tabs]
500
--
501
tab:Request[]
502

503
[source, java]
504
----
505
DataOutputStream out = new DataOutputStream(socket.getOutputStream());
506

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

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

514
// flags
515
writeByteLittleEndian(0, out);
516

517
// Filter Object
518
writeByteLittleEndian(101, out); // null
519

520
// Cursor page size
521
writeIntLittleEndian(1, out);
522

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

526
// local flag
527
out.writeBoolean(false);
528
----
529

530
tab:Response[]
531

532
[source, java]
533
----
534
// Read result
535
DataInputStream in = new DataInputStream(socket.getInputStream());
536

537
//Response header
538
readResponseHeader(in);
539

540
// Cursor id
541
long cursorId = readLongLittleEndian(in);
542

543
int rowCount = readIntLittleEndian(in);
544

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

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

553
boolean moreResults = readBooleanLittleEndian(in);
554

555
----
556

557
--
558

559

560
== OP_QUERY_SCAN_CURSOR_GET_PAGE
561

562

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

565
[cols="1,2",opts="header"]
566
|===
567
|Request Type |    Description
568
|Header|  Request header.
569
|long|    Cursor id.
570
|===
571

572

573
[cols="1,2",opts="header"]
574
|===
575
|Response Type |   Description
576
|Header|  Response header.
577
|long|    Cursor id.
578
|long|    Row count.
579
|Key Data Object + Value Data Object | Records in the form of key-value pairs.
580

581
Repeat for as many times as the row count obtained in the previous parameter.
582
|bool|    Indicates whether more results are available to be fetched with OP_QUERY_SCAN_CURSOR_GET_PAGE.
583
When true, query cursor is closed automatically.
584
|===
585

586

587
== OP_RESOURCE_CLOSE
588

589
Closes a resource, such as query cursor.
590

591
[cols="1,2",opts="header"]
592
|===
593
|Request Type |    Description
594
|Header|  Request header.
595
|long|    Resource id.
596
|===
597

598

599
[cols="1,2",opts="header"]
600
|===
601
|Response Type |   Description
602
|Header|  Response header
603
|===
604

605
[tabs]
606
--
607
tab:Request[]
608

609
[source, java]
610
----
611
DataOutputStream out = new DataOutputStream(socket.getOutputStream());
612

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

616
// Resource id
617
long cursorId = 1;
618
writeLongLittleEndian(cursorId, out);
619
----
620

621
tab:Response[]
622

623
[source, java]
624
----
625
// Read result
626
DataInputStream in = new DataInputStream(socket.getInputStream());
627

628
// Response header
629
readResponseHeader(in);
630

631
----
632

633
--
634

635