目录
hive outline
hive map格式数据
需求:有以下数据,分析数据格式并建表
1,孙悟空,53,西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688
2,鲁班七号,54,木偶奇遇记:288-福禄兄弟:288-黑桃队长:60-电玩小子:2288-星空梦想:0
3,后裔,53,精灵王:288-阿尔法小队:588-辉光之辰:888-黄金射手座:1688-如梦令:1314
4,铠,52,龙域领主:288-曙光守护者:1776
5,韩信,52,飞衡:1788-逐梦之影:888-白龙吟:1188-教廷特使:0-街头霸王:888
字段含义:
字段:id、name(英雄名称)、win_rate(胜率)、skin_price(皮肤及价格)
分析:
前3个字段原生数据类型、最后一个字段复杂类型map。需要指定字段之间分隔符、集合元素之间分隔符、map kv之间分隔符
create table if not exists t_hot_hero_skin_price(
id int,
name string,
win_rate int,
skin_price map<string,int>
)
row format delimited
fields terminated by ',' -- 指定字段之间的分割符
collection items terminated by '-' -- 指定集合元素之间的分隔符
map keys terminated by ':'; -- 指定map元素kv之间的分隔符
hive array 格式数据
需求:有以下数据,分析数据格式并建表
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
字段含义:
字段:move(电影名称)、category(电影所需类别)
分析:
最后一个字段复杂类型array。需要指定字段之间分隔符、数组元素之间分隔符
create table movie_info
(
name string,
category array<string>
) row format delimited
fields terminated by '\t' -- 字段之间分隔符
collection items terminated by ','; -- 数组元素之间分隔符
hive \001格式数据
使用的是ASCii码
需求:有以下格式的数据,分析数据,并建表
分析:
这种格式是hive默认的格式,不用指定任何的分割符
hive 多字节分隔符
hive 中默认使用单字节分隔符
来加载文本数据,例如逗号、制表符、空格等等,默认的分隔符为\001。根据不同文件的不同分隔符,我们可以通过在创建表时使用 row format delimited fields terminated by ‘单字节分隔符’ 来指定文件中的分割符,但
在实际工作中,我们遇到的数据往往不是非常规范化的数据,例如我们会遇到以下的两种情况
- 情况一:每一行数据的分隔符是多字节分隔符,例如:”||”、“–”等
- 情况二:数据的字段中包含了分隔符
针对上述场景,有以下3种解决方案
(1)替换分隔符(不推荐)
面对情况一,如果数据中的分隔符是多字节分隔符,可以使用程序(在ETL阶段编写一个MapReduce程序)提前将数据中的多字节分隔符替换为单字节分隔符,然后使用Hive就可以正确加载对应的数据了
(2)自定义InputFormat(不推荐)
Hive中也允许使用自定义InputFormat来解决以上问题,通过在自定义InputFormat,来自定义解析逻辑实现读取每一行的数据
(3)RegexSerDe正则加载(推荐)
面对情况一和情况二的问题,Hive中提供了一种特殊的方式来解决,Hive提供了一种特殊的Serde
来加载特殊数据的问题,使用正则匹配来加载数据,匹配每一列的数据
- 什么是SerDe?
SerDe是英文Serialize和Deserilize的组合缩写,Hive的SerDe类提供了序列化和反序列化两个功能,用于实现将Hive中的对象进行序列化和将数据进行反序列化
- SerDe类型有哪些?
Hive中默认提供了多种SerDe用于解析和加载不同类型的数据文件,常用的有ORCSerde 、RegexSerde、JsonSerDe等
- 实操解决上述情景1
- 分析数据,写正则表达式
1. 原始数据格式
01||周杰伦||中国||台湾||男||七里香
2. 写正则表达式
([0-9]*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)
3. 搜索一个在线网站,看看是否匹配
- 基于正则表达式,使用RegexSerde建表
create table singer
(
id string,--歌手id
name string,--歌手名称
country string,--国家
province string,--省份
gender string,--性别
works string--作品
)
--指定使用RegexSerde加载数据
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
--指定正则表达式
WITH SERDEPROPERTIES (
"input.regex" = "([0-9]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)"
);
hive json格式数据
简单json建表
原始数据
{"device":"device_30","deviceType":"kafka","signal":98.0,"time":1616817201390}
{"device":"device_32","deviceType":"kafka","signal":65.0,"time":1616817207131}
{"device":"device_32","deviceType":"kafka","signal":95.0,"time":1616817207714}
{"device":"device_71","deviceType":"bigdata","signal":45.0,"time":1616817207907}
建表语句参考
create table tb_json_test2 (
device string,
deviceType string,
signal double,
`time` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' -- 指定使用专门解析json的类JsonSerDe
STORED AS TEXTFILE;
device | devicetype | signal | time |
---|---|---|---|
device_30 | kafka | 98 | 1616817201390 |
device_32 | kafka | 65 | 1616817207131 |
device_32 | kafka | 95 | 1616817207714 |
device_71 | bigdata | 45 | 1616817207907 |
复杂json建表(array、struct)
struct
:就好比python中的元组,可以存放不同的数据类型
原始数据(有n多条json,只拿出来了一条,格式化后内容如下)
{
"sid":123,
"name":"zs",
"hobby":[
"爬山",
"拍照"
],
"friends_list":[
{
"nickname":"ww",
"sex":1
},
{
"nickname":"ls",
"sex":0
}
],
"address":{
"country":"china",
"province":"henan",
"area":"xc",
"email":50078
}
}
建表语句参考
create table tbl_struct_arryay
(
id bigint,
name string,
hobby array<string>,
friends_list array<struct<nickname:string,sex:int>> comment '朋友列表',
address struct<country:string,province:string, area:string, email:bigint>
) comment '测试表'
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe';
-- 装置数据 data_tbl_struct_arryay.txt
-- {"sid": 123,"name": "zs","hobby": ["爬山","拍照"],"friends_list": [{"nickname":"ww","sex":1},{"nickname":"ls","sex":0}],"address":{"country":"china","province":"henan","area":"xc","email":50078}}
load data local inpath '/opt/module/input/hive/data_tbl_struct_arryay.txt' into table practice_hive.tbl_struct_arryay;
select *
from tbl_struct_arryay;
取数据
-- 取数据
select name,
hobby[0],
friends_list.nickname,
address.country
from practice_hive.tbl_struct_arryay
hive 建表指定分区、分桶、存储、压缩、位置
create table dwd_trade_order_detail_inc
(
`id` string comment '订单明细id',
`order_id` string comment '订单id',
`sku_id` string comment 'sku_id',
`sku_name` string comment 'sku_name',
`user_id` string comment '用户id',
`sku_num` string comment '购买个数',
`region_id` string comment '区域id',
`source_id` string comment '来源编号',
`source_type_code` string comment '来源类型编码',
`source_type_name` STRING comment '来源类型名称',
`order_price` decimal(16, 2) comment '下单时sku价格',
`split_total_amount` decimal(16, 2) comment '分摊总金额',
`split_activity_amount` decimal(16, 2) comment '分摊活动减免金额',
`split_coupon_amount` decimal(16, 2) comment '分摊优惠券减免金额',
`create_time` string comment '创建时间'
) COMMENT '交易域下单明细事务事实表'
partitioned by (dt string )
clustered by (id) into 8 buckets
row format delimited fields terminated by '\t'
stored as orc
location '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
tblproperties ('orc.compress' = 'snappy');