CODEWITHSUNDEEP
sqlhiveimpala
user295944
user295944

Reputation: 373

Impala analysis exception from views

I have a database which has a view created off other views which are created off other views (a data engineer built the views not me)

In Hive I can do this but its slow, so I want to use Impala

select * from table limit 5;

In Impala I get an error, have tried invalidate metadata and refresh with no luck. 

"ERROR: AnalysisException: No matching function with signature: lower(BIGINT)."

what reason could this happen? Never seen this type of error before. Is there a way to do this recursively?

show create table;

Upvotes: 0

Views: 4601

Answers (1)

Samson Scharfrichter
Samson Scharfrichter

Reputation: 9067

To begin with, be aware that Hive and Impala are distinct solutions, with distinct SQL parsers, supporting a distinct set of functions and features. A syntax that is valid in Hive may not be valid in Impala. Some table formats defined with Hive may not be supported by Impala (e.g. ORC, or Parquet with a BINARY column).

In this specific case, the Hive documentation appears to match the Impala documentation for function lower() (caveat: check what versions you are using).

But there's a big catch: lower() takes a String and produces a String. It is not a number function. That smells like a gross mistake such as confusing lower() -- convert some text to lowercase -- and floor() -- get the integer value that is equal or less to a decimal value.

Check with your so-called Data Engineer what he/she was trying to do, and make sure the views were properly tested (or are properly tested after the correction is made). Hive clearly applies some implicit type conversions that enable queries to run, even though it makes no sense and produces goofy results.

Upvotes: 0

Related Questions