Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive ignoring data type declarations in create table statement

avatar
Rising Star

I am creating an external table with some fields that are numeric decimals. I have tried declaring them as floats, doubles, and decimal formats, but hive is casting them as strings regardless.

Here is a sample of the query:

create external table sapbw.copa_ods(currency_type string, cost_for_fi decimal, gross_margin_fi decimal, sales_for_fi decimal)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

stored as textfile

location '/data_LZ/BW/COPA_ODS';

Why is this happening?

1 ACCEPTED SOLUTION

avatar
Rising Star

Found the culprit, it's the CSVSerde, it casts all fields as strings.

View solution in original post

3 REPLIES 3

avatar
Super Guru
@Josh Persinger

May be someone else can confirm but I think you are storing the data as text format so it is storing it as a string. When loading the data in memory it will be loaded as decimal. The on disk representation varies based on file format.

Try creating data in Avro or ORC format and see if it retains the data type as Decimal.

avatar
Rising Star

I just tried following the method shown here: https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/moving_data_from...

I created the ORC table and it still stores everything as a string

avatar
Rising Star

Found the culprit, it's the CSVSerde, it casts all fields as strings.