Hive null values in external table using OpenCSVSerde

Question

I have an input file with an optional column (it may be missing). On top of the file an external hive table was created using OpenCSVSerde. My column(TAUSER) is at the 10th position considering the separator "/" and TAUSERID at 9th position.

The problem is that the following query doesn't return any row:

select * from mbb_cics_source where tauser ='' and substr(tauserid,1,3)="W99";

Normally it returns the last line. So how does hive handle missing values? I tried also "tauser is null", but it doesn't work.

Any Help? Thanks

Extract from the input file:

20160311/18:00:10.293278/18:00:10.309334/00:00:00.000234/00:00:00.016055 00:00:00.015815/00:00:00.000239/1/C563314//IXB1/3321//TPWPEA/OS0E
20160311/18:00:10.393249/18:00:10.395713/00:00:00.001179/00:00:00.002463/00:00:00.001233/00:00:00.001224/65/C66T0JC//LTBB/20087//TPAPEA/OS0E
20160311/18:00:10.416986/18:00:10.417419/00:00:00.000321/00:00:00.000432/00:00:00.000102/00:00:00.000329/0/C44P398//LTBE/72064//TPAPEB/OS0E
20160311/18:00:11.461089/18:00:11.482755/00:00:00.006002/00:00:00.021665/00:00:00.013864/00:00:00.007795/92/W990003//LTEF/33758//TPAPEC/OS0E

DDL of the external table:

use test;
drop table if exists mbb_cics_source;

CREATE external TABLE mbb_cics_source(TASTRDTS string, 
                           timep string, 
                           timep1 string,
                           ndcpu string, 
                           ndrep string,
                           ndwait string,
                           nddisp string,
                           TAIOCT string,
                           TAUSERID string,
                           TAUSER string,
                           TAPTRAN string,
                           TATASKID string,
                           TAABNCDE string,
                           TAVTMID string,
                           TASMFSID string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = "/",
   "serialization.null.format" = ""
)  
STORED AS TEXTFILE
location '/test/mbb_cics';

Hive null values in external table using OpenCSVSerde

0 Answers