S3 select content from an object

Arithmetic operators

^ * % / + - ( )

select (int(_1)+int(_2))*int(_9) from s3object;

Arithmetic operators

% modulo

select count(*) from s3object where cast(_1 as int)%2 = 0;

Arithmetic operators

^ power-of

select cast(2^10 as int) from s3object;

Compare operators

> < >= <= = !=

select _1,_2 from s3object where (int(_1)+int(_3))>int(_5);

logical operator

AND OR NOT

select count(*) from s3object where not (int(1)>123 and int(_5)<200);

logical operator

is null

Returns true/false for null indication in expression

logical operator and NULL

is not null

Returns true/false for null indication in expression

logical operator and NULL

unknown state

Review null-handle and observe the results of logical operations with NULL. The query returns 0.

select count(*) from s3object where null and (3>2);

Arithmetic operator with NULL

unknown state

Review null-handle and observe the results of binary operations with NULL. The query returns 0.

select count(*) from s3object where (null+1) and (3>2);

Compare with NULL

unknown state

Review null-handle and observe results of compare operations with NULL. The query returns 0.

select count(*) from s3object where (null*1.5) != 3;

missing column

unknown state

select count(*) from s3object where _1 is null;

projection column

Similar to if or then or else

select case when (1+1=(2+1)*3) then ‘case_1’ when ((4*3)=(12)) then ‘case_2’ else ‘case_else’ end, age*2 from s3object;

projection column

similar to switch/case default

logical operator

coalesce returns first non-null argument

select coalesce(nullif(5,5),nullif(1,1.0),age+12) from s3object;

logical operator

nullif returns null in case both arguments are equal, or else the first one,nullif(1,1)=NULL nullif(null,1)=NULL nullif(2,1)=2

select nullif(cast(_1 as int),cast(_2 as int)) from s3object;

logical operator

{expression} in ( .. {expression} ..)

select count(*) from s3object where ‘ben’ in (trim(_5),substring(_1,char_length(_1)-3,3),last_name);

logical operator

{expression} between {expression} and {expression}

select _1 from s3object where cast(_1 as int) between 800 and 900;
select count(*) from stdin where substring(_3,char_length(_3),1) between “x” and trim(_1) and substring(_3,char_length(_3)-1,1) = “:”;

logical operator

{expression} like {match-pattern}

select count(*) from s3object where first_name like ‘%de_’; select count(*) from s3object where _1 like "%a[r-s]";

logical operator

casting operator

select cast(123 as int)%2 from s3object;

casting operator

select cast(123.456 as float)%2 from s3object;

casting operator

select cast(‘ABC0-9’ as string),cast(substr(‘ab12cd’,3,2) as int)*4 from s3object;

casting operator

select cast(substring(‘publish on 2007-01-01’,12,10) as timestamp) from s3object;

non AWS casting operator

select int(_1),int( 1.2 + 3.4) from s3object;

non AWS casting operator

select float(1.2) from s3object;

non AWS casting operator

select to_timestamp('1999-10-10T12:23:44Z') from s3object;

Aggregation Function

sum

select sum(int(_1)) from s3object;

Aggregation Function

avg

select avg(cast(_1 as float) + cast(_2 as int)) from s3object;

Aggregation Function

min

select min( int(_1) * int(_5) ) from s3object;

Aggregation Function

max

select max(float(_1)),min(int(_5)) from s3object;

Aggregation Function

count

select count(*) from s3object where (int(1)+int(_3))>int(_5);

Timestamp Functions

extract

select count(*) from s3object where extract(year from to_timestamp(_2)) > 1950 and extract(year from to_timestamp(_1)) < 1960;

Timestamp Functions

dateadd

select count(0) from s3object where date_diff(year,to_timestamp(_1),date_add(day,366,to_timestamp(_1))) = 1;

Timestamp Functions

datediff

select count(0) from s3object where date_diff(month,to_timestamp(_1),to_timestamp(_2)) = 2;

Timestamp Functions

utcnow

select count(0) from s3object where date_diff(hour,utcnow(),date_add(day,1,utcnow())) = 24

Timestamp Functions

to_string

String Functions

substring

select count(0) from s3object where int(substring(_1,1,4))>1950 and int(substring(_1,1,4))<1960;

String Functions

trim

select trim(‘ foobar ‘) from s3object;

String Functions

trim

select trim(trailing from ‘ foobar ‘) from s3object;

String Functions

trim

select trim(leading from ‘ foobar ‘) from s3object;

String Functions

trim

select trim(both ‘12’ from ‘1112211foobar22211122’) from s3object;

String Functions

lower or upper

select lower(‘ABcD12#$e’) from s3object;

String Functions

char_length, character_length

select count(*) from s3object where char_length(_3)=3;

Complex queries

select sum(cast(_1 as int)),max(cast(_3 as int)), substring(‘abcdefghijklm’, (2-1)*3+sum(cast(_1 as int))/sum(cast(_1 as int))+1, (count() + count(0))/count(0)) from s3object;

alias support

select int(_1) as a1, int(_2) as a2 , (a1+a2) as a3 from s3object where a3>100 and a3<300;