10 The purpose of the **s3 select** engine is to create an efficient pipe between
11 user client and storage nodes (the engine should be close as possible to
12 storage). It enables the selection of a restricted subset of (structured) data
13 stored in an S3 object using an SQL-like syntax. It also enables for higher
14 level analytic-applications (such as SPARK-SQL), using that feature to improve
15 their latency and throughput.
17 For example, an s3-object of several GB (CSV file), a user needs to extract a
18 single column filtered by another column. As the following query: ``select
19 customer-id from s3Object where age>30 and age<65;``
21 Currently the whole s3-object must be retrieved from OSD via RGW before
22 filtering and extracting data. By "pushing down" the query into radosgw, it's
23 possible to save a lot of network and CPU(serialization / deserialization).
25 **The bigger the object, and the more accurate the query, the better the
31 S3-select query is sent to RGW via `AWS-CLI
32 <https://docs.aws.amazon.com/cli/latest/reference/s3api/select-object-content.html>`_
34 It passes the authentication and permission process as an incoming message
35 (POST). **RGWSelectObj_ObjStore_S3::send_response_data** is the “entry point”,
36 it handles each fetched chunk according to input object-key.
37 **send_response_data** is first handling the input query, it extracts the query
38 and other CLI parameters.
40 Per each new fetched chunk (~4m), RGW executes an s3-select query on it. The
41 current implementation supports CSV objects and since chunks are randomly
42 “cutting” the CSV rows in the middle, those broken-lines (first or last per
43 chunk) are skipped while processing the query. Those “broken” lines are
44 stored and later merged with the next broken-line (belong to the next chunk),
45 and finally processed.
47 Per each processed chunk an output message is formatted according to `AWS
49 <https://docs.aws.amazon.com/AmazonS3/latest/API/archive-RESTObjectSELECTContent.html#archive-RESTObjectSELECTContent-responses>`_
50 and sent back to the client. RGW supports the following response:
51 ``{:event-type,records} {:content-type,application/octet-stream}
52 {:message-type,event}``. For aggregation queries the last chunk should be
53 identified as the end of input, following that the s3-select-engine initiates
54 end-of-process and produces an aggregated result.
60 **S3select** has a definite set of functionalities compliant with AWS.
62 The implemented software architecture supports basic arithmetic expressions,
63 logical and compare expressions, including nested function calls and casting
64 operators, which enables the user great flexibility.
66 review the below s3-select-feature-table_.
72 Upon an error being detected, RGW returns 400-Bad-Request and a specific error message sends back to the client.
73 Currently, there are 2 main types of error.
75 **Syntax error**: the s3select parser rejects user requests that are not aligned with parser syntax definitions, as
76 described in this documentation.
77 Upon Syntax Error, the engine creates an error message that points to the location of the error.
78 RGW sends back the error message in a specific error response.
80 **Processing Time error**: the runtime engine may detect errors that occur only on processing time, for that type of
81 error, a different error message would describe that.
82 RGW sends back the error message in a specific error response.
84 .. _s3-select-feature-table:
89 Currently only part of `AWS select command
90 <https://docs.aws.amazon.com/AmazonS3/latest/dev/s3-glacier-select-sql-reference-select.html>`_
91 is implemented, table below describes what is currently supported.
93 The following table describes the current implementation for s3-select
96 +---------------------------------+-----------------+-----------------------------------------------------------------------+
97 | Feature | Detailed | Example / Description |
98 +=================================+=================+=======================================================================+
99 | Arithmetic operators | ^ * % / + - ( ) | select (int(_1)+int(_2))*int(_9) from s3object; |
100 +---------------------------------+-----------------+-----------------------------------------------------------------------+
101 | | ``%`` modulo | select count(*) from s3object where cast(_1 as int)%2 = 0; |
102 +---------------------------------+-----------------+-----------------------------------------------------------------------+
103 | | ``^`` power-of | select cast(2^10 as int) from s3object; |
104 +---------------------------------+-----------------+-----------------------------------------------------------------------+
105 | Compare operators | > < >= <= = != | select _1,_2 from s3object where (int(_1)+int(_3))>int(_5); |
106 +---------------------------------+-----------------+-----------------------------------------------------------------------+
107 | logical operator | AND OR NOT | select count(*) from s3object where not (int(_1)>123 and int(_5)<200);|
108 +---------------------------------+-----------------+-----------------------------------------------------------------------+
109 | logical operator | is null | return true/false for null indication in expression |
110 +---------------------------------+-----------------+-----------------------------------------------------------------------+
111 | logical operator | is not null | return true/false for null indication in expression |
112 +---------------------------------+-----------------+-----------------------------------------------------------------------+
113 | logical operator and NULL | unknown state | review null-handle_ observe how logical operator result with null. |
114 | | | the following query return **0**. |
116 | | | select count(*) from s3object where null and (3>2); |
117 +---------------------------------+-----------------+-----------------------------------------------------------------------+
118 | Arithmetic operator with NULL | unknown state | review null-handle_ observe the results of binary operations with NULL|
119 | | | the following query return **0**. |
121 | | | select count(*) from s3object where (null+1) and (3>2); |
122 +---------------------------------+-----------------+-----------------------------------------------------------------------+
123 | compare with NULL | unknown state | review null-handle_ observe results of compare operations with NULL |
124 | | | the following query return **0**. |
126 | | | select count(*) from s3object where (null*1.5) != 3; |
127 +---------------------------------+-----------------+-----------------------------------------------------------------------+
128 | missing column | unknown state | select count(*) from s3object where _1 is null; |
129 +---------------------------------+-----------------+-----------------------------------------------------------------------+
130 | query is filtering rows where predicate | select count(*) from s3object where (_1 > 12 and _2 = 0) is not null; |
131 | is returning non null results. | |
132 | this predicate will return null | |
133 | upon _1 or _2 is null | |
134 +---------------------------------+-----------------+-----------------------------------------------------------------------+
135 | projection column | similar to | select case |
136 | | switch/case | cast(_1 as int) + 1 |
137 | | default | when 2 then "a" |
138 | | | when 3 then "b" |
139 | | | else "c" end from s3object; |
141 +---------------------------------+-----------------+-----------------------------------------------------------------------+
142 | projection column | similar to | select case |
143 | | if/then/else | when (1+1=(2+1)*3) then 'case_1' |
144 | | | when ((4*3)=(12)) then 'case_2' |
145 | | | else 'case_else' end, |
146 | | | age*2 from s3object; |
147 +---------------------------------+-----------------+-----------------------------------------------------------------------+
148 | logical operator | ``coalesce {expression,expression ...} :: return first non-null argument`` |
150 | | select coalesce(nullif(5,5),nullif(1,1.0),age+12) from s3object; |
151 +---------------------------------+-----------------+-----------------------------------------------------------------------+
152 | logical operator | ``nullif {expr1,expr2} ::return null in case both arguments are equal,`` |
153 | | ``or else the first one`` |
155 | | select nullif(cast(_1 as int),cast(_2 as int)) from s3object; |
156 +---------------------------------+-----------------+-----------------------------------------------------------------------+
157 | logical operator | ``{expression} in ( .. {expression} ..)`` |
159 | | select count(*) from s3object |
160 | | where 'ben' in (trim(_5),substring(_1,char_length(_1)-3,3),last_name); |
161 +---------------------------------+-----------------+-----------------------------------------------------------------------+
162 | logical operator | ``{expression} between {expression} and {expression}`` |
164 | | select count(*) from s3object |
165 | | where substring(_3,char_length(_3),1) between "x" and trim(_1) |
166 | | and substring(_3,char_length(_3)-1,1) = ":"; |
167 +---------------------------------+-----------------+-----------------------------------------------------------------------+
168 | logical operator | ``{expression} like {match-pattern}`` |
170 | | select count(*) from s3object where first_name like '%de_'; |
172 | | select count(*) from s3object where _1 like \"%a[r-s]\; |
173 +---------------------------------+-----------------+-----------------------------------------------------------------------+
174 | | ``{expression} like {match-pattern} escape {char}`` |
176 | logical operator | select count(*) from s3object where "jok_ai" like "%#_ai" escape "#"; |
177 +---------------------------------+-----------------+-----------------------------------------------------------------------+
178 | true / false | select (cast(_1 as int)>123 = true) from s3object |
179 | predicate as a projection | where address like '%new-york%'; |
180 +---------------------------------+-----------------+-----------------------------------------------------------------------+
181 | an alias to | select (_1 like "_3_") as *likealias*,_1 from s3object |
182 | predicate as a projection | where *likealias* = true and cast(_1 as int) between 800 and 900; |
183 +---------------------------------+-----------------+-----------------------------------------------------------------------+
184 | casting operator | select cast(123 as int)%2 from s3object; |
185 +---------------------------------+-----------------+-----------------------------------------------------------------------+
186 | casting operator | select cast(123.456 as float)%2 from s3object; |
187 +---------------------------------+-----------------+-----------------------------------------------------------------------+
188 | casting operator | select cast('ABC0-9' as string),cast(substr('ab12cd',3,2) as int)*4 from s3object; |
189 +---------------------------------+-----------------+-----------------------------------------------------------------------+
190 | casting operator | select cast(5 as bool) from s3object; |
191 +---------------------------------+-----------------+-----------------------------------------------------------------------+
192 | casting operator | select cast(substring('publish on 2007-01-01',12,10) as timestamp) from s3object; |
193 +---------------------------------+-----------------+-----------------------------------------------------------------------+
194 | non AWS casting operator | select int(_1),int( 1.2 + 3.4) from s3object; |
195 +---------------------------------+-----------------+-----------------------------------------------------------------------+
196 | non AWS casting operator | select float(1.2) from s3object; |
197 +---------------------------------+-----------------+-----------------------------------------------------------------------+
198 | not AWS casting operator | select to_timestamp('1999-10-10T12:23:44Z') from s3object; |
199 +---------------------------------+-----------------+-----------------------------------------------------------------------+
200 | Aggregation Function | sum | select sum(int(_1)) from s3object; |
201 +---------------------------------+-----------------+-----------------------------------------------------------------------+
202 | Aggregation Function | avg | select avg(cast(_1 a float) + cast(_2 as int)) from s3object; |
203 +---------------------------------+-----------------+-----------------------------------------------------------------------+
204 | Aggregation Function | min | select min( int(_1) * int(_5) ) from s3object; |
205 +---------------------------------+-----------------+-----------------------------------------------------------------------+
206 | Aggregation Function | max | select max(float(_1)),min(int(_5)) from s3object; |
207 +---------------------------------+-----------------+-----------------------------------------------------------------------+
208 | Aggregation Function | count | select count(*) from s3object where (int(_1)+int(_3))>int(_5); |
209 +---------------------------------+-----------------+-----------------------------------------------------------------------+
210 | Timestamp Functions | extract | select count(*) from s3object where |
211 | | | extract(year from to_timestamp(_2)) > 1950 |
212 | | | and extract(year from to_timestamp(_1)) < 1960; |
213 +---------------------------------+-----------------+-----------------------------------------------------------------------+
214 | Timestamp Functions | date_add | select count(0) from s3object where |
215 | | | date_diff(year,to_timestamp(_1),date_add(day,366, |
216 | | | to_timestamp(_1))) = 1; |
217 +---------------------------------+-----------------+-----------------------------------------------------------------------+
218 | Timestamp Functions | date_diff | select count(0) from s3object where |
219 | | | date_diff(month,to_timestamp(_1),to_timestamp(_2))) = 2; |
220 +---------------------------------+-----------------+-----------------------------------------------------------------------+
221 | Timestamp Functions | utcnow | select count(0) from s3object where |
222 | | | date_diff(hours,utcnow(),date_add(day,1,utcnow())) = 24; |
223 +---------------------------------+-----------------+-----------------------------------------------------------------------+
224 | Timestamp Functions | to_string | select to_string( |
225 | | | to_timestamp("2009-09-17T17:56:06.234567Z"), |
226 | | | "yyyyMMdd-H:m:s") from s3object; |
228 | | | ``result: "20090917-17:56:6"`` |
229 +---------------------------------+-----------------+-----------------------------------------------------------------------+
230 | String Functions | substring | select count(0) from s3object where |
231 | | | int(substring(_1,1,4))>1950 and int(substring(_1,1,4))<1960; |
232 +---------------------------------+-----------------+-----------------------------------------------------------------------+
233 | substring with ``from`` negative number is valid | select substring("123456789" from -4) from s3object; |
234 | considered as first | |
235 +---------------------------------+-----------------+-----------------------------------------------------------------------+
236 | substring with ``from`` zero ``for`` out-of-bound | select substring("123456789" from 0 for 100) from s3object; |
237 | number is valid just as (first,last) | |
238 +---------------------------------+-----------------+-----------------------------------------------------------------------+
239 | String Functions | trim | select trim(' foobar ') from s3object; |
240 +---------------------------------+-----------------+-----------------------------------------------------------------------+
241 | String Functions | trim | select trim(trailing from ' foobar ') from s3object; |
242 +---------------------------------+-----------------+-----------------------------------------------------------------------+
243 | String Functions | trim | select trim(leading from ' foobar ') from s3object; |
244 +---------------------------------+-----------------+-----------------------------------------------------------------------+
245 | String Functions | trim | select trim(both '12' from '1112211foobar22211122') from s3objects; |
246 +---------------------------------+-----------------+-----------------------------------------------------------------------+
247 | String Functions | lower/upper | select lower('ABcD12#$e') from s3object; |
248 +---------------------------------+-----------------+-----------------------------------------------------------------------+
249 | String Functions | char_length | select count(*) from s3object where char_length(_3)=3; |
250 | | character_length| |
251 +---------------------------------+-----------------+-----------------------------------------------------------------------+
252 | Complex queries | select sum(cast(_1 as int)), |
253 | | max(cast(_3 as int)), |
254 | | substring('abcdefghijklm',(2-1)*3+sum(cast(_1 as int))/sum(cast(_1 as int))+1, |
255 | | (count() + count(0))/count(0)) from s3object; |
256 +---------------------------------+-----------------+-----------------------------------------------------------------------+
257 | alias support | | select int(_1) as a1, int(_2) as a2 , (a1+a2) as a3 |
258 | | | from s3object where a3>100 and a3<300; |
259 +---------------------------------+-----------------+-----------------------------------------------------------------------+
265 NULL is a legit value in ceph-s3select systems similar to other DB systems, i.e. systems needs to handle the case where a value is NULL.
267 The definition of NULL in our context, is missing/unknown, in that sense **NULL can not produce a value on ANY arithmetic operations** ( a + NULL will produce NULL value).
269 The Same is with arithmetic comparison, **any comparison to NULL is NULL**, i.e. unknown.
270 Below is a truth table contains the NULL use-case.
272 +---------------------------------+-----------------------------+
273 | A is NULL | Result (NULL=UNKNOWN) |
274 +=================================+=============================+
276 +---------------------------------+-----------------------------+
277 | A OR False | NULL |
278 +---------------------------------+-----------------------------+
280 +---------------------------------+-----------------------------+
282 +---------------------------------+-----------------------------+
283 | A AND False | False |
284 +---------------------------------+-----------------------------+
285 | A AND True | NULL |
286 +---------------------------------+-----------------------------+
288 +---------------------------------+-----------------------------+
290 S3-select Function Interfaces
291 -----------------------------
295 The timestamp functionalities as described in `AWS-specs
296 <https://docs.aws.amazon.com/AmazonS3/latest/dev/s3-glacier-select-sql-reference-date.html>`_
297 is fully implemented.
299 ``to_timestamp( string )`` : The casting operator converts string to timestamp
300 basic type. to_timestamp operator is able to convert the following
301 ``YYYY-MM-DDTHH:mm:ss.SSSSSS+/-HH:mm`` , ``YYYY-MM-DDTHH:mm:ss.SSSSSSZ`` ,
302 ``YYYY-MM-DDTHH:mm:ss+/-HH:mm`` , ``YYYY-MM-DDTHH:mm:ssZ`` ,
303 ``YYYY-MM-DDTHH:mm+/-HH:mm`` , ``YYYY-MM-DDTHH:mmZ`` , ``YYYY-MM-DDT`` or
304 ``YYYYT`` string formats into timestamp. Where time (or part of it) is
305 missing in the string format, zero's are replacing the missing parts. And for
306 missing month and day, 1 is default value for them. Timezone part is in
307 format ``+/-HH:mm`` or ``Z`` , where the letter "Z" indicates Coordinated
308 Universal Time (UTC). Value of timezone can range between -12:00 and +14:00.
310 ``extract(date-part from timestamp)`` : The function extracts date-part from
311 input timestamp and returns it as integer. Supported date-part : year, month,
312 week, day, hour, minute, second, timezone_hour, timezone_minute.
314 ``date_add(date-part, quantity, timestamp)`` : The function adds quantity
315 (integer) to date-part of timestamp and returns result as timestamp. It also
316 includes timezone in calculation. Supported data-part : year, month, day,
317 hour, minute, second.
319 ``date_diff(date-part, timestamp, timestamp)`` : The function returns an
320 integer, a calculated result for difference between 2 timestamps according to
321 date-part. It includes timezone in calculation. supported date-part : year,
322 month, day, hour, minute, second.
324 ``utcnow()`` : return timestamp of current time.
326 ``to_string(timestamp, format_pattern)`` : returns a string representation of
327 the input timestamp in the given input string format.
332 +--------------+-----------------+-----------------------------------------------------------------------------------+
333 | Format | Example | Description |
334 +==============+=================+===================================================================================+
335 | yy | 69 | 2-digit year |
336 +--------------+-----------------+-----------------------------------------------------------------------------------+
337 | y | 1969 | 4-digit year |
338 +--------------+-----------------+-----------------------------------------------------------------------------------+
339 | yyyy | 1969 | Zero-padded 4-digit year |
340 +--------------+-----------------+-----------------------------------------------------------------------------------+
341 | M | 1 | Month of year |
342 +--------------+-----------------+-----------------------------------------------------------------------------------+
343 | MM | 01 | Zero-padded month of year |
344 +--------------+-----------------+-----------------------------------------------------------------------------------+
345 | MMM | Jan | Abbreviated month year name |
346 +--------------+-----------------+-----------------------------------------------------------------------------------+
347 | MMMM | January | Full month of year name |
348 +--------------+-----------------+-----------------------------------------------------------------------------------+
349 | MMMMM | J | Month of year first letter (NOTE: not valid for use with to_timestamp function) |
350 +--------------+-----------------+-----------------------------------------------------------------------------------+
351 | d | 2 | Day of month (1-31) |
352 +--------------+-----------------+-----------------------------------------------------------------------------------+
353 | dd | 02 | Zero-padded day of month (01-31) |
354 +--------------+-----------------+-----------------------------------------------------------------------------------+
355 | a | AM | AM or PM of day |
356 +--------------+-----------------+-----------------------------------------------------------------------------------+
357 | h | 3 | Hour of day (1-12) |
358 +--------------+-----------------+-----------------------------------------------------------------------------------+
359 | hh | 03 | Zero-padded hour of day (01-12) |
360 +--------------+-----------------+-----------------------------------------------------------------------------------+
361 | H | 3 | Hour of day (0-23) |
362 +--------------+-----------------+-----------------------------------------------------------------------------------+
363 | HH | 03 | Zero-padded hour of day (00-23) |
364 +--------------+-----------------+-----------------------------------------------------------------------------------+
365 | m | 4 | Minute of hour (0-59) |
366 +--------------+-----------------+-----------------------------------------------------------------------------------+
367 | mm | 04 | Zero-padded minute of hour (00-59) |
368 +--------------+-----------------+-----------------------------------------------------------------------------------+
369 | s | 5 | Second of minute (0-59) |
370 +--------------+-----------------+-----------------------------------------------------------------------------------+
371 | ss | 05 | Zero-padded second of minute (00-59) |
372 +--------------+-----------------+-----------------------------------------------------------------------------------+
373 | S | 0 | Fraction of second (precision: 0.1, range: 0.0-0.9) |
374 +--------------+-----------------+-----------------------------------------------------------------------------------+
375 | SS | 6 | Fraction of second (precision: 0.01, range: 0.0-0.99) |
376 +--------------+-----------------+-----------------------------------------------------------------------------------+
377 | SSS | 60 | Fraction of second (precision: 0.001, range: 0.0-0.999) |
378 +--------------+-----------------+-----------------------------------------------------------------------------------+
379 | SSSSSS | 60000000 | Fraction of second (maximum precision: 1 nanosecond, range: 0.0-0999999999) |
380 +--------------+-----------------+-----------------------------------------------------------------------------------+
381 | n | 60000000 | Nano of second |
382 +--------------+-----------------+-----------------------------------------------------------------------------------+
383 | X | +07 or Z | Offset in hours or "Z" if the offset is 0 |
384 +--------------+-----------------+-----------------------------------------------------------------------------------+
385 | XX or XXXX| +0700 or Z | Offset in hours and minutes or "Z" if the offset is 0 |
386 +--------------+-----------------+-----------------------------------------------------------------------------------+
387 | XXX or XXXXX | +07:00 or Z | Offset in hours and minutes or "Z" if the offset is 0 |
388 +--------------+-----------------+-----------------------------------------------------------------------------------+
389 | X | 7 | Offset in hours |
390 +--------------+-----------------+-----------------------------------------------------------------------------------+
391 | xx or xxxx | 700 | Offset in hours and minutes |
392 +--------------+-----------------+-----------------------------------------------------------------------------------+
393 | xxx or xxxxx | +07:00 | Offset in hours and minutes |
394 +--------------+-----------------+-----------------------------------------------------------------------------------+
397 Aggregation Functions
398 ~~~~~~~~~~~~~~~~~~~~~
400 ``count()`` : return integer according to number of rows matching condition(if such exist).
402 ``sum(expression)`` : return a summary of expression per all rows matching condition(if such exist).
404 ``avg(expression)`` : return a average of expression per all rows matching condition(if such exist).
406 ``max(expression)`` : return the maximal result for all expressions matching condition(if such exist).
408 ``min(expression)`` : return the minimal result for all expressions matching condition(if such exist).
413 ``substring(string,from,to)`` : substring( string ``from`` start [ ``for`` length ] )
414 return a string extract from input string according to from,to inputs.
415 ``substring(string from )``
416 ``substring(string from for)``
418 ``char_length`` : return a number of characters in string (``character_length`` does the same).
420 ``trim`` : trim ( [[``leading`` | ``trailing`` | ``both`` remove_chars] ``from``] string )
421 trims leading/trailing(or both) characters from target string, the default is blank character.
423 ``upper\lower`` : converts characters into lowercase/uppercase.
428 The SQL LIMIT operator is used to limit the number of rows processed by the query.
429 Upon reaching the limit set by the user, the RGW stops fetching additional chunks.
430 TODO : add examples, for aggregation and non-aggregation queries.
434 **Alias** programming-construct is an essential part of s3-select language, it enables much better programming especially with objects containing many columns or in the case of complex queries.
436 Upon parsing the statement containing alias construct, it replaces alias with reference to correct projection column, on query execution time the reference is evaluated as any other expression.
438 There is a risk that self(or cyclic) reference may occur causing stack-overflow(endless-loop), for that concern upon evaluating an alias, it is validated for cyclic reference.
440 Alias also maintains a result cache, meaning that successive uses of a given alias do not evaluate the expression again. The result is instead returned from the cache.
442 With each new row the cache is invalidated as the results may then differ.
447 ``s3select`` contains several testing frameworks which provide a large coverage for its functionalities.
449 (1) Tests comparison against a trusted engine, meaning, C/C++ compiler is a trusted expression evaluator,
450 since the syntax for arithmetical and logical expressions are identical (s3select compare to C)
451 the framework runs equal expressions and validates their results.
452 A dedicated expression generator produces different sets of expressions per each new test session.
454 (2) Compares results of queries whose syntax is different but which are semantically equivalent.
455 This kind of test validates that different runtime flows produce an identical result
456 on each run with a different, random dataset.
458 For example, on a dataset which contains a random numbers(1-1000)
459 the following queries will produce identical results.
460 ``select count(*) from s3object where char_length(_3)=3;``
461 ``select count(*) from s3object where cast(_3 as int)>99 and cast(_3 as int)<1000;``
463 (3) Constant dataset, the conventional way of testing. A query is processing a constant dataset, its result is validated against constant results.
465 Additional Syntax Support
466 ~~~~~~~~~~~~~~~~~~~~~~~~~
468 S3select syntax supports table-alias ``select s._1 from s3object s where s._2 = ‘4’;``
470 S3select syntax supports case insensitive ``Select SUM(Cast(_1 as int)) FROM S3Object;``
472 S3select syntax supports statements without closing semicolon ``select count(*) from s3object``
478 Any HTTP client can send an ``s3-select`` request to RGW, which must be compliant with `AWS Request syntax <https://docs.aws.amazon.com/AmazonS3/latest/API/API_SelectObjectContent.html#API_SelectObjectContent_RequestSyntax>`_.
482 When sending an ``s3-select`` request to RGW using AWS CLI, clients must follow `AWS command reference <https://docs.aws.amazon.com/cli/latest/reference/s3api/select-object-content.html>`_.
487 aws --endpoint-url http://localhost:8000 s3api select-object-content
488 --bucket {BUCKET-NAME}
489 --expression-type 'SQL'
490 --scan-range '{"Start" : 1000, "End" : 1000000}'
491 --input-serialization
492 '{"CSV": {"FieldDelimiter": "," , "QuoteCharacter": "\"" , "RecordDelimiter" : "\n" , "QuoteEscapeCharacter" : "\\" , "FileHeaderInfo": "USE" }, "CompressionType": "NONE"}'
493 --output-serialization '{"CSV": {"FieldDelimiter": ":", "RecordDelimiter":"\t", "QuoteFields": "ALWAYS"}}'
495 --request-progress '{"Enabled": True}'
496 --expression "select count(0) from s3object where int(_1)<10;" output.csv
501 **FileHeaderInfo** -> (string)
502 Describes the first line of input. Valid values are:
504 **NONE** : The first line is not a header.
505 **IGNORE** : The first line is a header, but you can't use the header values to indicate the column in an expression.
506 it's possible to use column position (such as _1, _2, …) to indicate the column (``SELECT s._1 FROM S3OBJECT s``).
507 **USE** : First line is a header, and you can use the header value to identify a column in an expression (``SELECT column_name FROM S3OBJECT``).
509 **QuoteEscapeCharacter** -> (string)
510 A single character used for escaping the quotation mark character inside an already escaped value.
512 **RecordDelimiter** -> (string)
513 A single character is used to separate individual records in the input. Instead of the default value, you can specify an arbitrary delimiter.
515 **FieldDelimiter** -> (string)
516 A single character is used to separate individual fields in a record. You can specify an arbitrary delimiter.
523 aws s3api select-object-content \
524 --bucket "mybucket" \
526 --expression "SELECT * FROM s3object s" \
527 --expression-type 'SQL' \
528 --request-progress '{"Enabled": false}' \
529 --input-serialization '{"CSV": {"FieldDelimiter": ","}, "CompressionType": "NONE"}' \
530 --output-serialization '{"CSV": {"FieldDelimiter": ":", "RecordDelimiter":"\\t", "QuoteFields": "ALWAYS"}}' /dev/stdout
532 **QuoteFields** -> (string)
533 Indicates whether to use quotation marks around output fields.
534 **ALWAYS**: Always use quotation marks for output fields.
535 **ASNEEDED** (not implemented): Use quotation marks for output fields when needed.
537 **RecordDelimiter** -> (string)
538 A single character is used to separate individual records in the output. Instead of the default value, you can specify an
541 **FieldDelimiter** -> (string)
542 The value used to separate individual fields in a record. You can specify an arbitrary delimiter.
547 The scan range option to AWS-CLI enables the client to scan and process only a selected part of the object.
548 This option reduces input/output operations and bandwidth by skipping parts of the object that are not of interest.
549 TODO : different data-sources (CSV, JSON, Parquet)
554 The ``s3-select`` engine contains a CSV parser, which parses s3-objects as follows.
555 - Each row ends with ``row-delimiter``.
556 - ``field-separator`` separates adjacent columns, successive instances of ``field separator`` define a NULL column.
557 - ``quote-character`` overrides ``field separator``, meaning that ``field separator`` is treated like any character between quotes.
558 - ``escape character`` disables interpretation of special characters, except for ``row delimiter``.
560 Below are examples of CSV parsing rules.
562 +---------------------------------+-----------------+-----------------------------------------------------------------------+
563 | Feature | Description | input ==> tokens |
564 +=================================+=================+=======================================================================+
565 | NULL | successive | ,,1,,2, ==> {null}{null}{1}{null}{2}{null} |
566 | | field delimiter | |
567 +---------------------------------+-----------------+-----------------------------------------------------------------------+
568 | QUOTE | quote character | 11,22,"a,b,c,d",last ==> {11}{22}{"a,b,c,d"}{last} |
570 | | field delimiter | |
571 +---------------------------------+-----------------+-----------------------------------------------------------------------+
572 | Escape | escape char | 11,22,str=\\"abcd\\"\\,str2=\\"123\\",last |
573 | | overrides | ==> {11}{22}{str="abcd",str2="123"}{last} |
574 | | meta-character. | |
575 | | escape removed | |
576 +---------------------------------+-----------------+-----------------------------------------------------------------------+
577 | row delimiter | no close quote, | 11,22,a="str,44,55,66 |
578 | | row delimiter is| ==> {11}{22}{a="str,44,55,66} |
580 +---------------------------------+-----------------+-----------------------------------------------------------------------+
581 | csv header info | FileHeaderInfo | "**USE**" value means each token on first line is column-name, |
582 | | tag | "**IGNORE**" value means to skip the first line |
583 +---------------------------------+-----------------+-----------------------------------------------------------------------+
588 A JSON reader has been integrated with the ``s3select-engine``, which allows the client to use SQL statements to scan and extract information from JSON documents.
589 It should be noted that the data readers and parsers for CSV, Parquet, and JSON documents are separated from the SQL engine itself, so all of these readers use the same SQL engine.
591 It's important to note that values in a JSON document can be nested in various ways, such as within objects or arrays.
592 These objects and arrays can be nested within each other without any limitations.
593 When using SQL to query a specific value in a JSON document, the client must specify the location of the value
594 via a path in the SELECT statement.
596 The SQL engine processes the SELECT statement in a row-based fashion.
597 It uses the columns specified in the statement to perform its projection calculation, and each row contains values for these columns.
598 In other words, the SQL engine processes each row one at a time (and aggregates results), using the values in the columns to perform SQL calculations.
599 However, the generic structure of a JSON document does not have a row-and-column structure like CSV or Parquet.
600 Instead, it is the SQL statement itself that defines the rows and columns when querying a JSON document.
602 When querying JSON documents using SQL, the FROM clause in the SELECT statement defines the row boundaries.
603 A row in a JSON document should be similar to how the row delimiter is used to define rows when querying CSV objects, and how row groups are used to define rows when querying Parquet objects.
604 The statement "SELECT ... FROM s3object[*].aaa.bb.cc" instructs the reader to search for the path "aaa.bb.cc" and defines the row boundaries based on the occurrence of this path.
605 A row begins when the reader encounters the path, and it ends when the reader exits the innermost part of the path, which in this case is the object "cc".
607 NOTE : The semantics of querying JSON document may change and may not be the same as the current methodology described.
609 TODO : relevant example for object and array values.
618 "lastName": "Jackson",
622 "streetAddress": "101",
627 "firstName": "Joe_2",
628 "lastName": "Jackson_2",
632 "streetAddress": "101",
638 { "type": "home1", "number": "734928_1","addr": 11 },
639 { "type": "home2", "number": "734928_2","addr": 22 },
640 { "type": "home3", "number": "734928_3","addr": 33 },
641 { "type": "home4", "number": "734928_4","addr": 44 },
642 { "type": "home5", "number": "734928_5","addr": 55 },
643 { "type": "home6", "number": "734928_6","addr": 66 },
644 { "type": "home7", "number": "734928_7","addr": 77 },
645 { "type": "home8", "number": "734928_8","addr": 88 },
646 { "type": "home9", "number": "734928_9","addr": 99 },
647 { "type": "home10", "number": "734928_10","addr": 100 }
650 "key_after_array": "XXX",
653 "main_desc" : "value_1",
654 "second_desc" : "value_2"
658 # the from-clause define a single row.
659 # _1 points to root object level.
660 # _1.age appears twice in Documnet-row, the last value is used for the operation.
661 query = "select _1.firstname,_1.key_after_array,_1.age+4,_1.description.main_desc,_1.description.second_desc from s3object[*];";
662 expected_result = Joe_2,XXX,25,value_1,value_2
665 # the from-clause points the phonenumbers array (it defines the _1)
666 # each element in phoneNumbers array define a row.
667 # in this case each element is an object contains 3 keys/values.
668 # the query "can not access" values outside phonenumbers array, the query can access only values appears on _1.phonenumbers path.
669 query = "select cast(substring(_1.number,1,6) as int) *10 from s3object[*].phonenumbers where _1.type='home2';";
670 expected_result = 7349280
676 using BOTO3 is "natural" and easy due to AWS-cli support.
682 def run_s3select(bucket,key,query,column_delim=",",row_delim="\n",quot_char='"',esc_char='\\',csv_header_info="NONE"):
684 s3 = boto3.client('s3',
685 endpoint_url=endpoint,
686 aws_access_key_id=access_key,
687 region_name=region_name,
688 aws_secret_access_key=secret_key)
692 r = s3.select_object_content(
695 ExpressionType='SQL',
696 InputSerialization = {"CSV": {"RecordDelimiter" : row_delim, "FieldDelimiter" : column_delim,"QuoteEscapeCharacter": esc_char, "QuoteCharacter": quot_char, "FileHeaderInfo": csv_header_info}, "CompressionType": "NONE"},
697 OutputSerialization = {"CSV": {}},
699 RequestProgress = {"Enabled": progress})
701 except ClientError as c:
705 for event in r['Payload']:
706 if 'Records' in event:
708 records = event['Records']['Payload'].decode('utf-8')
710 if 'Progress' in event:
712 pprint.pprint(event['Progress'],width=1)
715 pprint.pprint(event['Stats'],width=1)
718 pprint.pprint(event['End'],width=1)
728 "select int(_1) as a1, int(_2) as a2 , (a1+a2) as a3 from s3object where a3>100 and a3<300;")
739 <?xml version="1.0" encoding="UTF-8"?>
741 <Code>NoSuchKey</Code>
742 <Message>The resource you requested does not exist</Message>
743 <Resource>/mybucket/myfoto.jpg</Resource>
744 <RequestId>4442587FB7D0A2F9</RequestId>
752 <?xml version="1.0" encoding="UTF-8"?>
755 <Payload>blob</Payload>
759 <BytesProcessed>long</BytesProcessed>
760 <BytesReturned>long</BytesReturned>
761 <BytesScanned>long</BytesScanned>
766 <BytesProcessed>long</BytesProcessed>
767 <BytesReturned>long</BytesReturned>
768 <BytesScanned>long</BytesScanned>
780 For CEPH S3 Select, responses can be messages of the following types:
782 **Records message**: Can contain a single record, partial records, or multiple records. Depending on the size of the result, a response can contain one or more of these messages.
784 **Error message**: Upon an error being detected, RGW returns 400 Bad Request, and a specific error message sends back to the client, according to its type.
786 **Continuation message**: Ceph S3 periodically sends this message to keep the TCP connection open.
787 These messages appear in responses at random. The client must detect the message type and process it accordingly.
789 **Progress message**: Ceph S3 periodically sends this message if requested. It contains information about the progress of a query that has started but has not yet been completed.
791 **Stats message**: Ceph S3 sends this message at the end of the request. It contains statistics about the query.
793 **End message**: Indicates that the request is complete, and no more messages will be sent. You should not assume that request is complete until the client receives an End message.