Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive ignoring data type declarations in create table statement

Solved Go to solution

Hive ignoring data type declarations in create table statement

Contributor

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

Accepted Solutions
Highlighted

Re: Hive ignoring data type declarations in create table statement

Contributor

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

View solution in original post

3 REPLIES 3
Highlighted

Re: Hive ignoring data type declarations in create table statement

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.

Highlighted

Re: Hive ignoring data type declarations in create table statement

Contributor

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

Highlighted

Re: Hive ignoring data type declarations in create table statement

Contributor

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

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here