Hive ignoring data type declarations in create table statement

[#1]

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?

[#2]

@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.

[#3]

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

[#4 · Accepted solution]

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