Apache Hive Logo

Hive實戰－通過指定經緯度點找出周圍的POI列表

需求描述：

有60多個店鋪,按經緯度圈出周圍1公里內所有POI列表

輸入：

店鋪1：116.123324,39.343313

店鋪2：116.34232423,40.3423423

店鋪3：116.4231,40.2343

店鋪4：116.2342,39.3433

......

店鋪65：.......

輸出結果：

excel表,表頭包含：店鋪id、POI名稱、一級分類、二級分類、經緯度

數據描述：

aoipoi表

主要字段：name, category_id, lng, lat

name：aoi或poi名稱

type：name的類型(aoi或poi)

category_id：分類id.存儲格式為“一級分類id|二級分類id,一級分類id|二級分類id,一級分類id|二級分類id”,可為空字符

lng：經度

lat：緯度

mst_dict表：

主要字段：id, name

id：分類的id

name：分類的名稱

思路：

分別得到每個店鋪周圍1公里內的數據,然后合并在一起,再把分類id轉化為中文存入HDFS文件.

(把id轉化放在后面可以減少需要轉化的id)

Hive SQL：

# 添加臨時函數：指定點經緯度1km內的數據(下面的UDF在以后講UDF/UDAF/UDTF時一并貼出)

add jar /home/zyl/hive-udf-1.0-SNAPSHOT.jar;

create temporary function dis_lnglat as 'com.zyl.udf.CalculatedLnglatDistance';

# 查詢：店鋪ID、POI名稱、一級分類名稱、二級分類名稱、經度、緯度

with q1 as

(

select name, category_id, lng, lat from zyl.aoipoi a where a.type='poi'

)

insert overwrite directory '/user/zyl/tmp_cpy_tab/20171017/lnglat'

select /*+mapjoin(b)*/concat_ws("\t", a.id, poiname, one_cate, coalesce(name, ''), lng, lat) line from (

select /*+mapjoin(b)*/a.id, poiname, coalesce(name, '') one_cate, two_cate, lng, lat from (

select name poiname, coalesce(split(category_id2,'\\|')[0], '') one_cate, coalesce(split(category_id2,'\\|')[1], '') two_cate, lng, lat, id from (

select name, category_id, lng, lat, '1' id from q1 where dis_lnglat(lng,lat,116.123324,39.343313)<=1000

union all

select name, category_id, lng, lat, '2' id from q1 where dis_lnglat(lng,lat,116.34232423,40.3423423)<=1000

union all

select name, category_id, lng, lat, '3' id from q1 where dis_lnglat(lng,lat,116.4231,40.2343)<=1000

union all

select name, category_id, lng, lat, '4' id from q1 where dis_lnglat(lng,lat,116.2342,39.3433)<=1000

) a LATERAL VIEW explode(split(category_id,'\\,')) myTable1 AS category_id2

) a left join zyl.mst_dict b on (a.one_cate = b.id)

) a left join zyl.mst_dict b on (a.two_cate = b.id)

group by a.id, poiname, one_cate, name, lng, lat

Hive SQL 脫敏后截圖

結果前10條脫敏后示例

講解

函數：

coalesce()

split()

explode()

非空查找函數: COALESCE

語法: COALESCE(T v1, T v2,…)

返回值: T

說明: 返回參數中的第一個非空值；如果所有值都為NULL,那么返回NULL

舉例：

select COALESCE(null,'is null','not null');

is null

select COALESCE(null, 0, 1);

0

select COALESCE('abc', 0, 1);

abc

分割字符串函數: SPLIT

語法: split(string str, stringpat)

返回值: array

說明:按照pat字符串分割str,會返回分割后的字符串數組

舉例：

select split('abtcdtef','t');

["ab","cd","ef"]

select split('ab,cd,ef','\\,');

["ab","cd","ef"]

行拆列函數：EXPLODE

explode(ARRAY) 列表中的每個元素生成一行.在本篇文章中用到了split之后為數組(ARRAY)交給explode處理.

explode(MAP) map中每個key-value對,生成一行,key為一列,value為一列

限制：

1、No other expressions are allowed in SELECT

SELECT pageid, explode(adid_list) AS myCol... is not supported

2、UDTF's can't be nested

SELECT explode(explode(adid_list)) AS myCol... is not supported

3、GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported

SELECT explode(adid_list) AS myCol ... GROUP BY myCol is not supported

下面語法／功能在以后再講：

with q1 as table

union all

insert overwrite directory

LATERAL VIEW ... myTable AS ... alias

mapjoin

left join

轉義(\\|、\\,)

注冊函數