Hive Spark 数据类型总结

一. 基本类型

• 示例

CREATE TABLE IF NOT EXISTS test.type_example
(
    id_deleted     boolean,
    order_num      tinyint,
    scan_num       smallint,
    click_num      int,
    show_num       bigint,
    salary         float,
    balance        decimal(20, 4),
    user_id        int,
    username       string,
    address        varchar(255),
    gender         char(1),
    create_at      timestamp,
    update_at      date
);

INSERT INTO test.type_example(id_deleted,
                              order_num,
                              scan_num,
                              click_num,
                              show_num,
                              salary,
                              balance,
                              user_id,
                              username,
                              address,
                              gender,
                              create_at,
                              update_at)
VALUES (FALSE,
        2,
        1209,
        1390,
        2340,
        DOUBLE(6500.2),
        123456.2345,
        202105,
        "zhang",
        "北京回龙观",
        "F",
        CURRENT_TIMESTAMP,
        CURRENT_DATE);

1.1 decimal

• HIVE: decimal(m,n) 表示数字总长度为 m 位，小数位为 n 位，那么整数位就只有 m-n 位了，在 hive 中默认值为 decimal(10,0)
• MySQL:decimal(m,n)表示整数位长度为 m 位，小数位为 n 位

二. 复杂类型

• 示例

CREATE TABLE IF NOT EXISTS test.type_example_complex
(
    username     string,
    teacher_list ARRAY<string>,
    class_info   MAP<string, string>,
    score_info   STRUCT<math:int, english:int, chinese:int>
);

INSERT INTO test.type_example_complex
    (username, teacher_list, class_info, score_info)
VALUES ("zhang",
        ARRAY('zhang', "wang", '李'),
        MAP('leader', 'xie', 'student_num', '55'),
        named_struct('math', 90, 'english', 91, 'chinese', 92));
        

2.1 array 结构

SELECT username,teacher_list[1] from type_example_complex  -- 获取第二个字符
-- zhang,wang

SELECT username,teacher_list[10] from type_example_complex -- 获取超范围的字符
-- zhang,NULL

SELECT array_contains(teacher_list, 'zhang') from type_example_complex -- 是否包含某个元素
-- true

2.2 map 结构

SELECT class_info['student_num'] FROM type_example_complex -- 获取某个key
-- 55

SELECT class_info['not_exists'] FROM type_example_complex --获取不存在的key
-- NULL

-- map_keys(Map)函数：可得map中所有的key; 返回值类型: array
SELECT map_keys(class_info) FROM type_example_complex
-- ["leader","student_num"]

select str_to_map('name:zhang,age:12,address:beijing gu gong') -- 字符串转换成map
-- {"name":"zhang","age":"12","address":"beijing gu gong"}

2.3 struct 结构

struct 相对于map 来说，它有固定的字段及类型。map可以存放任何数量的key-value，而struct只能存放提前定义好的字段，类似其他编程语言的对象的。

SELECT score_info.math,score_info.english,score_info.chinese FROM type_example_complex
-- 90,91,92

SELECT score_info.not_exists FROM type_example_complex
-- 会报错字段不存在

select named_struct('math', 90, 'english', 91, 'chinese', 92) -- 创建一个struct
-- {"math":90,"english":91,"chinese":92}

2.4 复杂类型初始化

参考资料

• https://www.docs4dev.com/docs/zh/apache-hive/3.1.1/reference/LanguageManual_Types.html
• https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types