hive 建表-复杂数据类型

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 ‘单字节分隔符’ 来指定文件中的分割符，但在实际工作中，我们遇到的数据往往不是非常规范化的数据，例如我们会遇到以下的两种情况

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

2. 情况二：数据的字段中包含了分隔符

针对上述场景，有以下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. 分析数据，写正则表达式

1. 原始数据格式
01||周杰伦||中国||台湾||男||七里香
2. 写正则表达式
([0-9]*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)
3. 搜索一个在线网站，看看是否匹配

2. 基于正则表达式，使用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');