CODEWITHSUNDEEP
hivehdfs
CleanSock
CleanSock

Reputation: 383

Casting data from string in csv file to bigint in Hive table

I have a csv file in which the amount field is a string. While loading into the table, I want to convert it into Bigint. I know that I can do cast(column_name as BIGINT) but at which stage should I do that?

I tried creating the table with the data type of amount as Bigint and loaded the file. When I query it, I get null as there is a data type mismatch.

If somebody can show me how to convert it and at what stage, it would be really appreciated.

Thanks!

Upvotes: 1

Views: 2369

Answers (2)

Mukesh S
Mukesh S

Reputation: 2876

If csv has amount field as string that means you data will be inside double quotes "12345". So in this case you can't load your data as int/bigint. Because when you run the LOAD command you cannot make any modifications in the data types of the columns.

There are two ways of doing this:

1.) Make your data(amount field) in csv as normal value instead of keeping it as string field.
    Then load your data into the table.

2.) First load your data as string field, then create another table with amount field as int/bigint then do:

insert into new_table select col1,col2..cast(coln as bigint)..colm from old_table

Hope this helps...!!!

Upvotes: 2

Jayanth
Jayanth

Reputation: 329

Try using a cast to an Unsigned Integer such as

CAST(col_name AS UNSIGNED INTEGER)

Upvotes: 0

Related Questions