Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Varchar column in an External Table is treated as Char by Hive

Highlighted

Varchar column in an External Table is treated as Char by Hive

Explorer

I created an external table whose definition contains a varchar column.

Create table test1_dat ( SUBNO bigint , AFFILCODE varchar(5) , EFFDT string )) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'            STORED AS TEXTFILE            LOCATION '/data/testdb/test1_dat';


The following Hql statement returns no records.

select * from jaffilm0_dat where rtrim(AFFILCODE)='LTC' limit 10 ;


The following Hql statements return correct records.

select * from jaffilm0_dat where rtrim(AFFILCODE)='LTC' limit 10 ;
select * from jaffilm0_dat where AFFILCODE='LTC  ' limit 10 ;  -- adding two trailing spaces


Basically, AFFILCODE is treated as char(5) by Hive. Is this supposed to happen? Is there any issue with my table definition script?

1 REPLY 1

Re: Varchar column in an External Table is treated as Char by Hive

Explorer

I just found out that the flat file behind the table has extra spaces in that AFFILCODE column. So rtrim has to be used all the time when querying against that column. Sorry for the confusion.