hive 字段不同的两张表如何合并 hive合并两个字段

转载

云端筑梦者 2023-12-25 22:20:36

文章标签 hive 字段不同的两张表如何合并 sql hive 数据仓库大数据 文章分类 Hive 大数据

1 多字节分隔符

应用场景:

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

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

解决方案:

方案一:在ETL阶段通过一个MapReduce程序，将数据中的“||”替换为单字节的分隔符“|”
该方式实现较为简单,只需替换字符串即可,但,无法满足情况二的需求
方案二:1)正则加载数据建表时使用指定SERDE类:ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.RegexSerDe’指定正则表达式WITH SERDEPROPERTIES (“input.regex” = “正则表达式”);

hive 字段不同的两张表如何合并 hive合并两个字段_大数据

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

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

hive 字段不同的两张表如何合并 hive合并两个字段_大数据_02

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

2 URL解析

hive 字段不同的两张表如何合并 hive合并两个字段_大数据_03

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】

hive 字段不同的两张表如何合并 hive合并两个字段_hive 字段不同的两张表如何合并_04

解析方式一:使用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