How to fix Hive query issue with double and float type while checking condition, (having trouble with casting)

0
votes

I'm trying to run a basic hive query that selects for values above a threshold. The column type named "corr_coeff" is type float however the hive query language through hiveview2.0 or through zepelin (via JDBC Drive) always passes my equality check as a double and I get errors.

FilterPredicate column: corr_coeff's declared type (java.lang.Double) does not match the schema found in file metadata. Column corr_coeff is of type: FLOAT

I tried setting a new variable for the threshold and casting it however this doesn't work either.

set PROB_THRESH=(cast 0.70 as float);
select corr_coeff FROM trends where trends.corr_coeff>PROB_THRESH;

Then I get the error:

org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:54 Invalid table alias or column reference 'PROB_THRESH': (possible column names are: index, filename, start_trnd, end_trnd, start_trnd_time, end_trnd_time, feature1, feature2, corr_coeff)

Edit: update 8/21

So I found out that it will work if I cast the entire column originally a float as a double but this is a waste of resources.

set PROB_THRESH=0.70;
select corr_coeff FROM trends WHERE cast(trends.corr_coeff as double) >${hiveconf:PROB_THRESH};

What I want instead but always fails is to cast the threshold value in the comparison as a float because Hive initializes the variable as a double

use fca_analytics;
set PROB_THRESH=0.70;
select corr_coeff FROM trends WHERE trends.corr_coeff > cast(${hiveconf:PROB_THRESH} as float);

This fails:

FilterPredicate column: corr_coeff's declared type (java.lang.Double) does not match the schema found in file metadata. Column corr_coeff is of type: FLOAT Valid types for this column are: [class java.lang.Float]

1
hivehiveql

1 Answers

0
votes

PROB_THRESH is a Hive variable.

Hive variables are referred using the following syntax, ${hiveconf:myvariable}

So, your code must be modified to,

set PROB_THRESH=0.70;
select corr_coeff FROM trends where trends.corr_coeff > ${hiveconf:PROB_THRESH};

You can read more about hive variables on https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VariableSubstitution