CODEWITHSUNDEEP
sqlviewhive
Craig
Craig

Reputation: 1985

Creating a Hive view

I have a Hive UDF named find_distance which calculates the coordinate distance between a pair of lat-long coordinates. I also have a table containing a list of city names and their respective lat-long coordinates. So currently if I need to find the distance between two cities, say Denver and San Jose, I need to perform a self join:

Select find_Distance(cityA.latitude, cityA.longitude, cityB.latitude, cityB.longitude) from
(select latitude, longitude from city_data.map_info where city = 'Denver') cityA
join
(select latitude, longitude from city_data.map_info where city = 'San Jose') cityB;

How would I go about building a view that would accept just the city names as parameters? So in effect I could just use 

SELECT distance from city_distance where cityA = 'Denver' and cityB = 'San Jose'

Upvotes: 1

Views: 335

Answers (1)

Sergey Medintsev
Sergey Medintsev

Reputation: 55

Try this VIEW:

CREATE VIEW city_distance AS
SELECT
  cityA.city as city_from,
  cityA.city as city_to,
  find_Distance(cityA.latitude, cityA.longitude, cityB.latitude, cityB.longitude) as distance
FROM
  (SELECT city, latitude, longitude FROM city_data.map_info) cityA
  CROSS JOIN
  (SELECT city, latitude, longitude FROM city_data.map_info) cityB;

Upvotes: 1

Related Questions