impala SQL插入报错

最新推荐文章于 2023-04-27 21:34:55 发布

原创最新推荐文章于 2023-04-27 21:34:55 发布 · 3.5k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#impala #SQL插入报错

数据库专栏收录该内容

15 篇文章

订阅专栏

本文介绍在使用Impala进行数据插入时遇到的精度损失错误及其解决方案。通过具体案例，展示了如何将字符串类型数据转换为指定长度的VARCHAR类型，以避免分析异常错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

impala插入数据的时候，出现报错：

[hs-170:21000] > insert into test_abc(fund_account, client_name, client_sex) values('1234567890123456789001', 'name1', '0');
Query: insert into test_abc(fund_account, client_name, client_sex) values('1234567890123456789001', 'name1', '0')
ERROR: AnalysisException: Possible loss of precision for target table 'wt_tmp.test_abc'.
Expression ''1234567890123456789001'' (type: STRING) would need to be cast to VARCHAR(32) for column 'fund_account'

出现这类错误的时候，你就看报错，它说把某个字段转成什么就把某个字段转成什么。上述报错中提示：

 (type: STRING) would need to be cast to VARCHAR(32) for column 'fund_account'

意思是：这个输入格式为STRING的字段fund_account'字段需转换成格式VARCHAR(32)。

来看下表test_abc的结构：

[hs-170:21000] > show create table test_abc;
Query: show create table test_abc
+-------------------------------------------------------------------------------+
| result                                                                        |
+-------------------------------------------------------------------------------+
| CREATE TABLE wt_tmp.test_abc (                                                |
|   fund_account VARCHAR(32) COMMENT 'fund_account',                            |
|   client_name VARCHAR(64) COMMENT 'client_name',                              |
|   client_sex VARCHAR(32) COMMENT 'client_sex'                                 |
| )                                                                             |
| STORED AS TEXTFILE                                                            |
| LOCATION 'hdfs://hs-171:xxx/user/xxx/wt_tmp.db/test_abc' |
| TBLPROPERTIES ('transient_lastDdlTime'='1542874679')                          |
+-------------------------------------------------------------------------------+

接下来执行改造过的插入语句：

insert into test_abc(fund_account, client_name, client_sex) values(cast(001 as VARCHAR(32)), cast('name1' as varchar(64)), cast(0 as varchar(32)));
insert into test_abc(fund_account, client_name, client_sex) values(cast(002 as VARCHAR(32)), cast('name2' as varchar(64)), cast(0 as varchar(32)));
insert into test_abc(fund_account, client_name, client_sex) values(cast(003 as VARCHAR(32)), cast('name3' as varchar(64)), cast(1 as varchar(32)));
insert into test_abc(fund_account, client_name, client_sex) values(cast(004 as VARCHAR(32)), cast('name4' as varchar(64)), cast(1 as varchar(32)));
insert into test_abc(fund_account, client_name, client_sex) values(cast(005 as VARCHAR(32)), cast('name5' as varchar(64)), cast(0 as varchar(32)));
insert into test_abc(fund_account, client_name, client_sex) values(cast(006 as VARCHAR(32)), cast('name6' as varchar(64)), cast(2 as varchar(32)));

查看数据：

[hs-170:21000] > select * from test_abc;
Query: select * from test_abc
+--------------+-------------+------------+
| fund_account | client_name | client_sex |
+--------------+-------------+------------+
| 2            | name2       | 0          |
| 1            | name1       | 0          |
| 6            | name6       | 2          |
| 5            | name5       | 0          |
| 4            | name4       | 1          |
| 3            | name3       | 1          |
+--------------+-------------+------------+
Fetched 6 row(s) in 1.29s

插入成功。
上述SQL为单条插入，也可以一次插入多条（与mysql插入方式一致）：

insert into test_abc(fund_account, client_name, client_sex) values(cast(007 as VARCHAR(32)), cast('name7' as varchar(64)), cast(2 as varchar(32))),(cast(008 as VARCHAR(32)), cast('name8' as varchar(64)), cast(0 as varchar(32)));