Hive函数使用之URL解析函数及侧视图
实际工作需求
业务需求中，我们经常需要对用户的访问、用户的来源进行分析，用于支持运营和决策。例如我们经常对用户访问的页面进行统计分析，分析热门受访页面的Top10，观察大部分用户最喜欢的访问最多的页面等：
又或者我们需要分析不同搜索平台的用户来源分析，统计不同搜索平台中进入网站的用户个数，根据数据进行精准的引导和精准的广告投放等：
要想实现上面的受访分析、来源分析等业务，必须在实际处理数据的过程中，对用户访问的URL和用户的来源URL进行解析处理，获取用户的访问域名、访问页面、用户数据参数、来源域名、来源路径等信息。
URL的基本组成
在对URL进行解析时，我们要先了解URL的基本组成部分，再根据实际的需求从URL中获取对应的部分，例如一条URL由以下几个部分组成：
- PROTOCOL：协议类型
通信协议类型，一般也叫作Schema，常见的有http、https等；
- HOST：域名
一般为服务器的域名主机名或ip地址
- PATH：访问路径
访问路径目录，由“/”隔开的字符串，表示的是主机上的目录或文件地址
- QUERY：参数数据
查询参数，此项为可选项，可以给动态网页传递参数，用“&”隔开，每个参数的名和值用“=”隔开
Hive中的URL解析函数
数据准备
Hive中为了实现对URL的解析，专门提供了解析URL的函数 parse_url 和 parse_url_tuple ，在 show functions 中可以看到对应函数
为了更好地学习这两个函数的使用，下面我们在Hive中创建一张表，加载url数据来进行测试。
- 准备数据
vim /export/data/url.txt |
添加以下数据内容
1 2 3 4 |
- 创建数据库
/*创建数据库*/ |
- 创建表
/*创建数据表*/ |
- 加载数据
/*加载数据*/ |
- 查看数据
/*查询数据*/ |
需求
基于当前的数据，实现对URL进行分析，从URL中获取每个ID对应HOST、PATH以及QUERY，最终实现效果如下：
parse_url
- 功能
parse_url函数是Hive中提供的最基本的url解析函数，可以根据指定的参数，从URL解析出对应的参数值进行返回，函数为普通的一对一函数类型。
- 语法
parse_url(url, partToExtract[, key ]) – extracts a part from a URL |
parse_url在使用时需要指定两个参数
第一个参数：url：指定要解析的URL
第二个参数：key：指定要解析的内容
- 示例
SELECT parse_url( ‘#39; , ‘HOST’ ) FROM src LIMIT 1 ; SELECT parse_url( ‘#39; , ‘QUERY’ ) FROM src LIMIT 1 ; SELECT parse_url( ‘#39; , ‘QUERY’ , ‘query’ ) FROM src LIMIT 1 ; |
- 测试
- 查询tb_url中每个url的HOST
select id,url,parse_url(url,”HOST”) as host from tb_url; |
- 查询tb_url中每个url的PATH
select id,url,parse_url(url,”PATH”) as path from tb_url; |
- 查询tb_url中每个url的QUERY
select id,url,parse_url(url,”QUERY”) as query from tb_url; |
- 实现需求
select |
- 问题
使用parse_url函数每次只能解析一个参数，导致需要经过多个函数调用才能构建多列，开发角度较为麻烦，实现过程性能也相对较差，需要对同一列做多次计算处理，我们希望能实现调用一次函数，就可以将多个参数进行解析，得到多列结果。
parse_url_tuple
- 功能
parse_url_tuple函数是Hive中提供的基于parse_url的url解析函数，可以通过一次指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型。
- 语法
parse_url_tuple(url, partname1, partname2, …, partnameN) – extracts N (N>= 1 ) parts from a URL. |
parse_url在使用时可以指定多个参数
第一个参数：url：指定要解析的URL
第二个参数：key1：指定要解析的内容1
……
第N个参数：keyN：指定要解析的内容N
- 示例
SELECT b. * FROM src LATERAL VIEW parse_url_tuple(fullurl, ‘HOST’ , ‘PATH’ , ‘QUERY’ , ‘QUERY:id’ ) b as host , path , query , query_id LIMIT 1 ; |
- 测试
- 查询tb_url中每个url的HOST、PATH
select parse_url_tuple(url,”HOST”,”PATH”) as ( host , path ) from tb_url; |
- 查询tb_url中每个url的PROTOCOL、HOST、QUERY
select parse_url_tuple(url,”PROTOCOL”,”HOST”,”PATH”) as (protocol, host , path ) from tb_url; |
- 实现需求
select parse_url_tuple(url,”HOST”,”PATH”,”QUERY”) as ( host , path , query ) from tb_url; |
- 问题
当前实现的过程中，通过parse_url_tuple实现了通过调用一个函数，就可以从URL中解析得到多个参数的值，但是当我们将原表的字段放在一起查询时，会出现以下问题：
select id, parse_url_tuple(url,”HOST”,”PATH”,”QUERY”) as ( host , path , query ) from tb_url; 0: jdbc:hive2://node1:10000> select . . . . . . . . . . . . . .> id, . . . . . . . . . . . . . .> parse_url_tuple(url,”HOST”,”PATH”,”QUERY”) as (host,path,query) . . . . . . . . . . . . . .> from tb_url; Error: Error while compiling statement: FAILED: SemanticException 3:52 AS clause has an invalid number of aliases. Error encountered near token ‘path’ (state=42000,code=40000) |
UDTF函数的问题
Hive中的一对多的UDTF函数可以实现高效的数据转换，但是也存在着一些使用中的问题，UDTF函数对于很多场景下有使用限制，例如： select时不能包含其他字段、不能嵌套调用、不能与group by等放在一起调用 等等。
UDTF函数的调用方式，主要有以下两种方式：
方式一：直接在select后单独使用
方式二：与Lateral View放在一起使用
Lateral View侧视图
功能
Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。
侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。
一般只要使用UDTF，就会固定搭配lateral view使用。
官方链接：
语法
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias ( ‘,’ columnAlias)* |
基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3…… |
测试
- 单个lateral view调用 ，实现上述需求中的应用
select |
- 多lateral view调用
select |
- Outer Lateral View
如果UDTF不产生数据时，这时侧视图与原表关联的结果将为空，如下图所示：
select |
如果加上outer关键字以后，就会保留原表数据，类似于outer join
select |
