0
0

【hive】hive数据类型及数据类型转换的注意事项

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

一、hive数据类型

1、数值型数据类型

类型支持范围说明TINYINT1byte有符号整数范围：-128~127范围太小，基本不用SMALLINT2byte有符号整数范围：-32,768 to 32,767基本不用INT/INTEGER4byte有符号整数范围： -2,147,483,648 to 2,147,483,647INTERGER是hive2.20中才开始有的，一般不用BIGINT8byte有符号整数范围：-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807，精度为19位，为int的补充使用FLOAT4byte单精度浮点数范围：-3.4028235E38 to 3.4028235E387，精度为7位。3.14159DOUBLE8byte双精度浮点数范围：-1.7976E+308 to 1.797693E+308，精度为15或16位，比单精度浮点数float存储更大。3.114159DECIMAL最长可以存储长度为38位的小数
DECIMAL 数值类型说明

1、decimal的使用decimal(precision, scale),前面是整数部分，后面是小数部分。
整数部分如果没有定义，则默认长度为10，小数部分如果没有指定，则默认为0.
如果超出长度则会截取，如下小数部位默认长度为0，被四舍五入截取了，整数部位进1。

>select CAST(12345.523456 AS DECIMAL);  
+--------+
|  _c0   |
+--------+
|12346|
+--------+

2.如果需要转换的数据长度超过decimal指定长度，则结果不会截取，直接为null值，尽管decimal最长可以存储长度为38位的小数。 如下默认整数部分长度为10，现在需要转化的数据整数长度超过10，直接整个结果为NULL

>select CAST(12345678910.523456 AS DECIMAL);
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+

3.使用decimal将其他类型的数据转换成decimal，指定长度，超出部分四舍五入。

>select CAST(123456789.1234567 AS DECIMAL(20,5)); 
+------------------+
|       _c0        |
+------------------+
|123456789.12346|
+------------------+

2、字符型数据类型

类型说明STRING长字符串，能用string类型的尽量使用string类型。VARCHAR固定长度，使用时要指定长度.所以转换的时候有可能超出指定长度后，数据丢失CHAR固定长度，使用需要指定长度，只是char的长度比varchar小多了
VARCHAR 数值类型说明

1.varchar固定长度，使用时要指定长度。所以转换的时候有可能超出指定长度后，数据丢失。

>select CAST("ABCDEFGHICD" AS VARCHAR(10));
+-------------+
|     _c0     |
+-------------+
| ABCDEFGHIC  |
+-------------+

2.varchar建表时，需要指定长度，否则报错。长度如果指定小了，数据插入会直接被截取长度。

> create table test_varchar(id varchar(10));> insert overwrite table test_varchar  values ('123456789122');>select * from test_varchar;
+------------------+
| test_varchar.id  |
+------------------+
|1234567891|
+------------------+

3、日期型数据类型

类型说明TIMESTAMP1.始于Hive 0.8.0。用来表示UTC时间(时间标准时间)。提供了用于时区转换的便捷UDFs（to_utc_timestamp，from_utc_timestamp）。2. 所有现有的日期时间UDFs（月，日，年，小时等）都使用TIMESTAMP数据类型。其次TIMESTAP支持整型，浮点型，字符串型的数据。具体使用后面介绍实际开发中使用的也不是特别多。DATE始于Hive 0.12.0 DATE值描述特定的年/月/日，格式为YYYY-MM-DD。 例如，DATE’2013-01-01’。 日期类型没有时间组件。 Date类型支持的值范围是0000-01-01到9999-12-31，这取决于Java Date类型的原始支持。 Date types只能在Date, Timestamp, or String types之间转换。INTERVAL始于Hive 1.2.0,实际开发中不怎么用。
TIMESTAMP 数值类型说明

1.使用timestamp创建日期类型的字段，可以存储浮点，整型，字符串类型的时间数据

> create table test_timestamp(
a int,
b bigint,
c timestamp
);> insert overwrite table test_timestamp 
select1,2,12334324 from test_timestamp  limit 2;>select * from test_timestamp;
+-------------------+-------------------+--------------------------+
| test_timestamp.a  | test_timestamp.b  |     test_timestamp.c     |
+-------------------+-------------------+--------------------------+
|3|4|1970-01-01 03:25:34.324  ||3|4|1970-01-01 03:25:34.324  |
+-------------------+-------------------+--------------------------+

> insert overwrite table test_timestamp 
select3,4,"2019-05-22 21:23:34" from test_timestamp  limit 2;>select * from test_timestamp;
+-------------------+-------------------+------------------------+
| test_timestamp.a  | test_timestamp.b  |    test_timestamp.c    |
+-------------------+-------------------+------------------------+
|3|4|2019-05-22 21:23:34.0  ||3|4|2019-05-22 21:23:34.0  |
+-------------------+-------------------+------------------------+

4、其他数据类型

类型说明BOOLEAN布尔类型：TRUE or FALSEBINARY字节数组，用于存储变长的二进制数据。

5、复合数据类型

类型说明STRUCT字段集合,类型可以不同MAPMAP是键值对元组合（key-value），key必须为原始类型，value可以任意类型ARRAY数组是一组具有相同类型和名称的变量的集合。

二、hive数据类型转换

1、隐式转换的规律

  • hive转换时也包括隐式转换（implicit conversion）和显式转换（explicitly conversions）。
  • 比如我们对两个不同数据类型的数字进行比较，假如一个数据类型是INT型，另一个SMALLINT类型，那么SMALLINT类型的数据将会被隐式转换成INT类型；但是我们不能隐式地将一个 INT类型的数据转换成SMALLINT或TINYINT类型的数据，这将会返回错误，除非你使用了cast操作。
  • 任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式地转换成DOUBLE。
  • BOOLEAN类型不能转换为其他任何数据类型！如果强转换的话会返回NULL。

数据类型转换表
在这里插入图片描述

2、同数据类型之间的转换

同数据类型是指同是数值型数据类型，日期型数据类型等等。
同类型数据进行转换遵循“向上转型”规则，即低类型的数据在和高类型的数据（范围更大的类型）进行逻辑运算时，会隐式的自动转换成高类型的数据类型。然后再进行计算。
比如1和1.23比较，会自动将1转换成1.0进行计算比较。

2、不同数据类型之间的转换

强转换函数cast()
cast函数使用：cast(value as type)，value是需要被转换的数据， AS是固定关键字，type是需要转换的类型

>select 
cast("1223" as double),
cast("456.23" as int),
cast("1.99" as int),
cast("abc" as int) ,
cast(456.23 as decimal(9,2));
+---------+------+------+-------+---------+
|   _c0   | _c1  | _c2  |  _c3  |   _c4   |
+---------+------+------+-------+---------+
|1223.0|456|1| NULL  |456.23|
+---------+------+------+-------+---------+

使用cast()函数注意事项

  • cast转换要符合转换条件才能转换，转换条件可参考数据类型转换表，否则结果就是NULL。比如“abc"转换成double，很明显不是数值，所以转换失败。
  • 如果使用cast将高类型的数据转换成低类型的数据，cast函数会直接截取，损失数据精确度甚至得到错误结果。比如，将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现。因此，将浮点型456.23转换成int，显示结果会直接截取掉小数部分，保留整数部分。如果要保留数值的精度，可以将浮点型456.23转换成decimal(9,2)，不过必须定义decimal的精度，精度是总的数字位数 ，包括小数点左边和右边位数的总和。
  • 日期型数据类型转换说明：对于日期型数据类型，只能在Date、Timestamp以及String之间进行转换。
    有效的转换结果cast(date as date)返回date类型cast(timestamp as date)timestamp中的年/月/日的值是依赖与当地的时区，结果返回date类型cast(string as date)如果string是YYYY-MM-DD格式的，则相应的年/月/日的date类型的数据将会返回；但如果string不是YYYY-MM-DD格式的，结果则会返回NULL。cast(date as timestamp)基于当地的时区，生成一个对应date的年/月/日的时间戳值cast(date as string)date所代表的年/月/日时间将会转换成YYYY-MM-DD的字符串
    ```

    cast(timestamp as date)# 显示当前时间戳 current_timestamp()# 显示当前日期 current_date()>select current_timestamp();

+--------------------------+
| _c0 |
+--------------------------+
|2023-05-09 11:24:12.067 |
+--------------------------+

select current_date();
+-------------+
| _c0 |
+-------------+
|2023-05-09 |
+-------------+

cast(timestamp as date)>select cast(current_timestamp() as date);

+-------------+
| _c0 |
+-------------+
|2023-05-09 |
+-------------+

cast(date as timestamp)>select cast(current_date() as timestamp);

+------------------------+
| _c0 |
+------------------------+
|2023-05-09 00:00:00.0 |
+------------------------+


---

## 三、实际应用的注意事项

## 1、字符串string与bigint类型的坑

**坑一：**
**错误**：如果 table 1 中含有字段 a1 是string格式，比如”420001053411844“，”000001053411844“等等，另一个 table 2 含有字段 a2 是bigint格式的，需要将 table 1 中 a1 与t able 2 的 a2 进行关联。如果直接将cast(a1 as bigint)与a2，因为如果a1是字段”000001053411844“会变成”1053411844“，导致本应该不连接字段匹配成功。

desc test_table1;
+-----------+------------+----------+
| col_name | data_type | comment |
+-----------+------------+----------+
| a1 | string ||
+-----------+------------+----------+
select * from test_table1;
+------------------+
| test_table1.a1 |
+------------------+
|420001053411844|| 000001053411844 |
+------------------+

desc test_table2;
+-----------+------------+----------+
| col_name | data_type | comment |
+-----------+------------+----------+
| a2 | bigint ||
+-----------+------------+----------+
select * from test_table2;
+------------------+
| test_table2.a2 |
+------------------+
|420001053411844||1053411844|
+------------------+

SELECT
a.a1 a1,
b.a2 a2
from test_table1 a
join test_table2 b
on cast(a.a1 as bigint)= b.a2;
+------------------+------------------+
| a1 | a2 |
+------------------+------------------+
|420001053411844|420001053411844|| 000001053411844 |1053411844|
+------------------+------------------+


**原因及注意**：不可以直接将cast(a1 as bigint)与a2，因为如果a1是字段”000001053411844“会变成”1053411844“，导致匹配出现错误。

select cast("000001053411844" as int);
+-------------+
| _c0 |
+-------------+
|1053411844|
+-------------+


**解决方案**：将bigint类型转成string格式再关联。

SELECT
a.a1 a1,
b.a2 a2
from test_table1 a
join test_table2 b
on a.a1 = cast(b.a2 as string);
+------------------+------------------+
| a1 | a2 |
+------------------+------------------+
|420001053411844|420001053411844|
+------------------+------------------+


**题外话**：只要表字段是bigint类型，好像就无法存储 000001053411844，插入都会变成1053411844 。

create table test_table2 (a2 bigint);> insert overwrite table test_table2 select * from test_table1 ;> load data local inpath '/apps/wqf/cdc_model/data/data_20230509.txt' overwrite into table wqf.test_table2;> insert overwrite table test_table2 values (420001053411844),(000001053411844);> insert overwrite table test_table2 values ("420001053411844"),("000001053411844");> insert overwrite table test_table2 select * from test_table1;>select * from test_table2 ;
+------------------+
| test_table2.a2 |
+------------------+
|420001053411844||1053411844|
+------------------+


**坑二：**
**错误**：如果 table 1 中含有字段 a1 是string格式，比如"150970594253582620"等等，另一个 table 2 含有字段 a2 是bigint格式的，比如"150970594253582621"等等，需要将 table 1 中 a1 与table 2 的 a2 进行关联。按理说两条数据是不会进行连接，结果却跟想象的不同，两条数据匹配上了。

create table test_table1 (a1 string);> insert overwrite table test_table1 select"150970594253582620";> create table test_table2 (a2 bigint);> insert overwrite table test_table2 select150970594253582621;> SELECT
a.a1 a1,
b.a2 a2
from test_table1 a
join test_table2 b
on a.a1= b.a2;
+---------------------+---------------------+
| a1 | a2 |
+---------------------+---------------------+
|150970594253582620|150970594253582621|
+---------------------+---------------------+

```

原因及注意：table1的a1和table2的a2，一个是string，一个是bigint。在string和bigint连接时，都会隐式转换成double。​其中bigint的精度为19位，大小是8byte，范围是-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807。其中double的精度为15或16位，大小是8byte，范围是-1.7976E+308 ~ 1.797693E+308。由此，两者都会损失精度，造成两者相等的情况。

解决方案：a1与a2 同时 指定两者的类型。

字符串string与bigint类型坑的总结：
1、在比较bigint和string的时候显示的指定两者的类型，除了注意数据类型能否转换以外，还需要关注两点，一是实际转换字段对应的是什么内容，特别是string数据类型转成整数型数值类型，要注意原字段开头是否有一部分数据带有0，或者结尾是否是带小数点，如坑一；二是进行关联或者转换字段实际数值精度，是否会精度损失的情况。

参考文章：
https://juejin.cn/post/7039162114157756430

标签： hive hadoop 数据仓库
本文转载自: https://blog.csdn.net/sodaloveer/article/details/130555485
版权归原作者 sodaloveer 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【hive】hive数据类型及数据类型转换的注意事项”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航