Useful SQL query examples

Example SQL queries which may be helpful:

This works in IMPALA SQL to convert a unix epoch time to 30min intervals time for example time 19:15, 19:25 will show as 19:00 and 19:31, 19:50 will show as 19:30 etc.

SELECT from_timestamp (cast((epochtime div 1800000)*1800 as timestamp) + interval (epochtime % 1000) milliseconds, ‘yyyy-MM-dd-HH:mm’) as timeat30mininterval,
from_timestamp (cast(epochtime div 1000 as timestamp) + interval (epochtime % 1000) milliseconds, ‘yyyy-MM-dd-HH:mm’) as originaltime
FROM mydb.mytable
order by 2 desc;

This SQL query parses a JSON string in a column to extract the values of each field:

select b.b1, c.c1,c.c2,d.d1,d.d2,json_column
from db1.json_table1 a
lateral view json_tuple(a.json_column, ‘field1′,’field2’, ‘field3’) b as b1,b2,b3
lateral view json_tuple(b.b2,’field2.1′,’field2.2′) c as c1,c2
lateral view json_tuple(b.b3,’field3.1′,’field3.2′) d as d1,d2
;

db1.json_table1

json_column, col_x,col_y

———————————————————————————————————————————————————————-

{‘field1′:’val1’, ‘field2’:{‘field2.1′:’val2.1′,’field2.2′:val2.2’}, ‘field3’: {‘field3.1′:’val3.1′,’field3.2′:val3.2’}}, val_x, val_y

RESULT:

val1 val2.1 val2.2 val3.1 val3.2 val_x val_y

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.