提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
一、hive数据类型1、数值型数据类型2、字符型数据类型3、日期型数据类型4、其他数据类型5、复合数据类型
二、hive数据类型转换1、隐式转换的规律2、同数据类型之间的转换2、不同数据类型之间的转换
三、实际应用的注意事项1、字符串string与bigint类型的坑
一、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
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 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 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
精彩内容
发表评论