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

分类:热门推荐日期:2024-01-19浏览:1评论:0

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

文章目录

  • 一、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.14159
          DOUBLE8byte双精度浮点数范围：-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、日期型数据类型

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

          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

相关推荐

关于我们常见问题广告服务免责声明联系我们
Powered By Z-BlogPHP 1.7.3 Theme By 优美主题
Copyright Your WebSite.Some Rights Reserved. 浙ICP备2023018783号