converting values from string to int in hive

Question

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

It seems you have a comma as a part of the data. I would suggest to take care of it while loading the data into the table. Once the comma is removed from the string, the CAST will work correctly. — visakh
– visakh, Commented Jul 14, 2014 at 11:27
First make sure the delimiter you are using is correct w.r.t your csv file. Second check your int/double/float values in csv file, they should not have "" in the file. In your case it seems the problem of delimiters as @visakh pointed out. — Mukesh S
– Mukesh S, Commented Jul 14, 2014 at 12:22

Community · Accepted Answer · 2017-05-23 12:22:53Z

2

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

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Aug 18, 2016 at 9:33

Balkrishan Aggarwal

6131 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting values from string to int in hive

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related