Unable to Insert data into VARCHAR data type in Impala

Unable to Insert data into VARCHAR data type in Impala

In this blog post, I will talk about an issue that Impala user is not able to directly insert into a table that has VARCHAR column type. To re-produce, see below test case:
CREATE TABLE test (a varchar(20));
INSERT INTO test SELECT 'a';
ERROR: AnalysisException: Possible loss of precision for target table 'default.test'.
Expression ''a'' (type: STRING) would need to be cast to VARCHAR(20) for column 'a'
I would like to advise that this is working as expected currently, as Impala was designed to force data integrity by stopping user from converting STRING data type into VARCHAR behind the scene and user is unaware of data loss in this scenario. This has been reported in the upstream JIRA: IMPALA-1333. And the plan is to allow such implicit casting between STRING and VARCHAR, so that any thirdparty software can handle this case easier. This probably will be implement using a flag to turn ON and OFF at session level when needed, rather than just allow implicit conversion blindly, as it will have the risk of losing data or cause data corruption unexpected. For now, if you know that you have data in STRING that will fit into VARCHAR’s length, then, as mentioned in the JIRA, you can run below query to overcome the issue:
INSERT INTO test SELECT CAST('a' AS varchar(20));

Leave a Reply

Your email address will not be published.

My new Snowflake Blog is now live. I will not be updating this blog anymore but will continue with new contents in the Snowflake world!