How to select all column exclude columns ‘date’ and ‘cst_id’ in hive? Answer Try below – For single session – set hive.support.quoted.identifiers=none; refer quotedIdentifiers for more information on hive select using regular expressions
Tag: hive
How to get combined values from a table in hive
Have a table in Hive with a following structure: col1 col2 col3 col4 col5 col6 —————————– AA NM ER NER NER NER AA NM NER ERR NER NER AA NM NER NER TER …
Easy table transpose in hive
I need to transpose my table. Now i have that type of table: But i want to get the next result How should i transpose my table for achieving this result? Answer Use case statements with min() or max() aggregation:
Casting the Bigint number Returns NULL
I need o convert a integer value to the highest data type in hive as my value is of 25 digits NULL value will be returned for the above select stmnt; I am very well aware that the supplied number is greater than the largest number of Bigint. But we are getting such values upon which i have to calculate
How to add Conditions on SQL Percentile Window function?
I want to do a special query to evaluate team members. Each member will have a score, and those with score that is greater than 80th percentile of that team’s score will get a bonus. However, I want …
Hive: less than operator error in subquery
I want the latest records from HIVE table using the following query- WITH lot as (select * from to_burn_in as a where a.rel_lot=’${Rel_Lot}’) select a.* from lot AS a where not exists (select 1 from …
In SQL How to convert time into UNIX timestamp
In hive there is some data I have. Now I want to convert the start_timestamp into unix_timestamp in second. How to do that? Because the start_timestamp has two formats: First format: 2018-03-22 …
Hive Case Resulting Duplicate Row
I have a table containing contact numbers and another reference table containing a “length” variable and a number column. What I need is to find the prefix name where the prefix of the number matches …
Hive like does not work when the string is from other table
I meet a strange problem in hive The table a: The table b When I use: It works well But when I use I got return null. The b.domain is ‘%taobao\.com%’ or ‘%tmall\.com%’. What’s the different using them direct in SQL query? What makes the second query failed? Answer Without ‘\’ in the template it works fine: Result: And the
“Hive” max column value from multiple columns
Hi: I have a situation where I need to find the max value on 3 calculated fields and store it in another field, is it possible to do it in one SQL query? Below is the example One way I tried is to calculate Personal_Income, Share_Income, Job_Income in the first pass and in the second pass I used but this