Hive string to int data type conversion with text file format

[#1]

I am trying to cast string to int ( cast(id as int) ) with external text file format and it is giving null values . Below are the results .

Example :

select id from test limit 5;

123023342

005957304

null

002191996

null

select cast(id as int ) from test limit 5;

NULL

NULL

NULL

NULL

NULL

[#2]

@Nelson KA Rajendran

Can you please share the format of the table using command 'show create table test;'?

Thanks and Regards,

Sindhu

[#3]

Below is the show create table result.

CREATE TABLE `test`( `id` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://path' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='true', 'numFiles'='1', 'numRows'='1', 'rawDataSize'='10', 'totalSize'='11', 'transient_lastDdlTime'='1468612262') ;

[#4]

@Nelson KA Rajendran,

Do you mind posting a sample from input file ?How many rows did you have in the input file?