presto – Make Me Engineer

What is the equivalent of Presto UNNEST function in Hive

June 3, 2023 by Tarik

Use lateral view [outer] explode. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias. This is example from Presto migration from Hive docs: SELECT student, score FROM tests LATERAL VIEW explode(scores) … Read more

Getting all Buildings in range of 5 miles from specified coordinates

July 12, 2022 by Tarik

Why are you storing x,y in separated columns? I strongly suggest you to store them as geometry or geography to avoid unnecessary casting overhead in query time. That being said, you can compute and check distances in miles using ST_DWithin or ST_Distance: (Test data) CREATE TABLE building (name text, long numeric, lat numeric); INSERT INTO … Read more