[ Hive ] 函数应用 : 多字节分隔符.url,json解析.拉链表

1 多字节分隔符

应用场景:

情况一:每一行数据的分隔符是多字节分隔符，例如：”||”、“–”等

情况二:数据的字段中包含了分隔符

解决方案:

1. 方案一:在ETL阶段通过一个MapReduce程序，将数据中的“||”替换为单字节的分隔符“|”

   该方式实现较为简单,只需替换字符串即可,但,无法满足情况二的需求

2. 方案二:

   1)正则加载数据

   建表时使用指定SERDE类:

   ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.RegexSerDe’

   指定正则表达式

   WITH SERDEPROPERTIES (“input.regex” = “正则表达式”);

   

   2)正则解决数据中包含分隔符(同上,修改对应正则表达式)

   时间字段不再被分割为两个字段，整体作为一个字段被加载

   

3. 方案三:

   自定义InputFormat:

   自定义InputFormat继承自TextInputFormat，读取数据时将每条数据中的”||”全部替换成“|”–>将开发好的InputFormat打成jar包，放入Hive的lib目录中–>在Hive中，将jar包临时添加到环境变量中(add jar /export/server/hive-3.1.2-bin/lib/HiveUserInputFormat.jar)–>创建表,指定自定义的InputFormat读取数据

2 URL解析

1)parse_url函数:

根据指定的参数，从URL解析出对应的参数值进行返回，函数为普通的一对一函数类型UDF。

-- 语法
parse_url(url, partToExtract[, key]) - extracts a part from a URL
  Parts: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO key 
  
  
-- 示例
SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') FROM src LIMIT 1;
'facebook.com'

SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') FROM src LIMIT 1;
'query=1'

SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY', 'query') FROM src LIMIT 1;
'1'  

2)parse_url_tuple函数:

通过一次指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型。与explode不同,该函数返回的时多列而不是多行

-- 语法
parse_url_tuple(url, partname1, partname2, ..., partnameN) - extracts N (N>=1) parts from a URL.
It takes a URL and one or multiple partnames, and returns a tuple. All the input parameters and 
output column types are string.
Partname: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY_NAME>

-- 示例
-- 方式一：与Lateral View放在一起使用可解除使用限制(其他字段,嵌套,group by)
SELECT b.* FROM src LATERAL VIEW parse_url_tuple(fullurl, 'HOST', 'PATH', 'QUERY', 'QUERY:id') 
b as host, path, query, query_id LIMIT 1;

-- 方式二：直接在select后单独使用
SELECT parse_url_tuple(a.fullurl, 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'FILE',
  'AUTHORITY', 'USERINFO', 'QUERY:k1') as (ho, pa, qu, re, pr, fi, au, us, qk1) from src a; 

3 JSON解析

每条数据都以JSON形式存在，每条数据中都包含4个字段，分别为设备名称【device】、设备类型【deviceType】、信号强度【signal】和信号发送时间【time】

解析方式一:使用JSON函数进行处理

解析方式二:使用Hive内置的JSON Serde加载数据

1)get_json_object函数:

select
       --获取设备名称
       get_json_object(json,"$.device") as device,
       --获取设备类型
         get_json_object(json,"$.deviceType") as deviceType,
       --获取设备信号强度
       get_json_object(json,"$.signal") as signal,
       --获取时间
       get_json_object(json,"$.time") as stime
from tb_json_test1;

2)json_tuple函数:

-- 方式一:直接在select后单独使用
select
       --解析所有字段
       json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
from tb_json_test1;

-- 方式二:搭配侧视图
select
       json,device,deviceType,signal,stime
from tb_json_test1
lateral view json_tuple(json,"device","deviceType","signal","time") b
as device,deviceType,signal,stime;

3)JSONSerde

在创建表时，只要指定使用JSONSerde解析表的文件，就会自动将JSON文件中的每一列进行解析。如果每一行数据就是一个JSON数据，那么建议直接使用JSONSerde来实现处理最为方便

ROW FORMAT SERDE ‘org.apache.hive.hcatalog.data.JsonSerDe’
STORED AS TEXTFILE

4 拉链表

拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题，将更新的数据进行状态记录

整体实现过程一般分为三步:

step1：增量采集变化数据，放入增量表中(ods层)

step2：构建临时表，将Hive中的拉链表与临时表的数据进行合并(union all+left join)

step3：将临时表的数据覆盖写入拉链表中(insert overwrite)