CODEWITHSUNDEEP
castinghive
user2876812
user2876812

Reputation: 336

converting values from string to int in hive

I am creating a table in hive;

create table patients(
  patient_id INT,
  age_group STRING,
  gender STRING,
  income_range STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';

load data local inpath '/mnt/patients.csv' into table patients;

Now when I am using the command:

hive>select * from patients limit 5;

I am getting the output:

NULL    75-84,  F,      32000-47999
NULL    75-84,  M,      16000-23999
NULL    85+,    M,      <16000
NULL    65-74,  F,      32000-47999
NULL    <65,    M,      <16000

But when I am using assigning patient_id as string its showing:

 910997967,      75-84,  F,      32000-47999
 506013497,      75-84,  M,      16000-23999
 432041392,      85+,    M,      <16000
 633048699,      65-74,  F,      32000-47999

I tried to use :

 hive>select CAST(patient_id AS int) from patients;

But its not changing the values to int and only showing 

NULL
NULL
...

How could the values of patient_id can be converted to int values?

Thanks

Upvotes: 3

Views: 16547

Answers (1)

Balkrishan Aggarwal
Balkrishan Aggarwal

Reputation: 613

As @visakh pointed out that there is a comma(,) in your 1st column: patient_id.

You need to remove this.

You may use

    CAST(regexp_replace(patient_id, ',' , '') AS INT)

This is similar to Hive function to replace comma in column value

Upvotes: 2

Related Questions