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

sodaloveer

于 2023-05-10 09:04:32 发布

阅读量8.2k

点赞数 2

分类专栏： hive 文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/sodaloveer/article/details/130555485

版权

hive 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

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

文章目录

一、hive数据类型
二、hive数据类型转换
三、实际应用的注意事项
- 1、字符串string与bigint类型的坑

一、hive数据类型

1、数值型数据类型

类型	支持范围	说明
TINYINT	1byte有符号整数范围：-128~127	范围太小，基本不用
SMALLINT	2byte有符号整数范围：-32,768 to 32,767	基本不用
INT/INTEGER	4byte有符号整数范围： -2,147,483,648 to 2,147,483,647	INTERGER是hive2.20中才开始有的，一般不用
BIGINT	8byte有符号整数范围：-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807，精度为19位，为int的补充使用
FLOAT	4byte单精度浮点数范围：-3.4028235E38 to 3.4028235E387，精度为7位。	3.14159
DOUBLE	8byte双精度浮点数范围：-1.7976E+308 to 1.797693E+308，精度为15或16位，比单精度浮点数float存储更大。	3.114159
DECIMAL	最长可以存储长度为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、日期型数据类型

类型	说明
TIMESTAMP	1.始于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 
select 1,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 
select 3,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 FALSE
BINARY	字节数组，用于存储变长的二进制数据。

5、复合数据类型

类型	说明
STRUCT	字段集合,类型可以不同
MAP	MAP是键值对元组合（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  select 150970594253582621;


> 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