hive 建表-复杂数据类型

目录

• ​​hive outline​​
• ​​map格式数据​​
• ​​array 格式数据​​
• ​​\001格式数据​​
• ​​多字节分隔符​​
• ​​json格式数据​​

• ​​简单json建表​​
• ​​复杂json建表（array、struct）​​

hive outline

​​链接​​

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之间的分隔符

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 ','; -- 数组元素之间分隔符

\001格式数据

使用的是ASCii码

需求：有以下格式的数据，分析数据，并建表

​​分析：​​这种格式是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]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)"
        );

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;

复杂json建表（array、struct）

​​struct​​：就好比python中的元组，可以存放不同的数据类型

原始数据（有n多条json，只拿出来了一条，格式化后内容如下）

{
    "common": {
        "ar": "230000",
        "ba": "iPhone",
        "ch": "Appstore",
        "is_new": "1",
        "md": "iPhone 8",
        "mid": "YXfhjAYH6As2z9Iq",
        "os": "iOS 13.2.9",
        "uid": "485",
        "vc": "v2.1.134"
    },
    "actions": [
        {
            "action_id": "favor_add",
            "item": "3",
            "item_type": "sku_id",
            "ts": 1585744376605
        }
    ],
    "displays": [
        {
            "displayType": "query",
            "item": "3",
            "item_type": "sku_id",
            "order": 1,
            "pos_id": 2
        },
        {
            "displayType": "query ",
            "item": "6",
            "item_type": "sku_id",
            "order": 5,
            "pos_id": 1
        }
    ],
    "page": {
        "during_time": 7648,
        "item": "3",
        "item_type": "sku_id",
        "last_page_id": "login",
        "page_id": "good_detail",
        "sourceType": "promotion"
    },
    "err": {
        "error_code": "1234",
        "msg": "***********"
    },
    "ts": 1585744374423
}

建表语句参考

create external table ods_log_inc
(
    `common`   struct<ar :string,ba :string,ch :string,is_new :string,md :string,mid :string,os :string,uid :string,vc
                      :string> comment '公共信息',
    `page`     struct<during_time :string,item :string,item_type :string,last_page_id :string,page_id
                      :string,source_type :string> comment '页面信息',
    `actions`  array<struct<action_id:string,item:string,item_type:string,ts:bigint>> comment '动作信息',
    `displays` array<struct<display_type :string,item :string,item_type :string,`order` :string,pos_id
                            :string>> comment '曝光信息',
    `err`      struct<error_code:bigint,msg:string> comment '错误信息',
    `ts`       bigint comment '时间戳'
) comment '活动信息表'
    row format serde 'org.apache.hadoop.hive.serde2.jsonserde';