Skip to content
Advertisement

Tag: hiveql

“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

How to find previous date in HIVE

I want to find previous date in Hive. For example : I have Date as 20180821 (yyyyMMdd) format, I want to find previous date of this in Hive. I tried date_sub() function, seems it works only with yyyy-MM-dd format. How to solve my issue. Please help Answer In addition to already provided solution with unix_timestamp conversion, please consider also this.

Remove nulls from an array in SQL

Want to remove nulls from an array in hive/sql for example : array is [‘1’,null] after converting to string values it should be ‘1’ only. to split the array I am using below: concat_ws( “,”, …

Initcap of word

I’m having a table x it contain the column resource_name in this column I’m having data like NASRI(SRI). I’m applying initcap on this column it’s giving output Nasri(sri). But my expected output is Nasri(Sri). How I can achieve the desired result? Thank you Answer One possible solution is to use split() with concat_ws(). If value does not contain ‘()’, then

Deleting Part of a string in HIVE

I am trying to delete a part of a string in HIVE. I want to delete the last eleven characters for all records in a column. The data looks like: 1018492743|0001-01-01 I want it to look like: 1018492743 The code I have tried looks like: Select right(a.ord_id, len(a.ord_id)-ll) It isn’t working because len isnt a function in HIVE Another issue

Time difference between two date fields

I have a date field names “dts” in string format. I want to find out all the records based on their time differences (in hours). The run event time should be greater than or equal to eat event. The output should be: Answer Convert timestamps to seconds, then subtract, divide result by 3600 to get hours, use case+count to count

Concat multiple rows with a delimiter in Hive

I need to concat string values row wise with ‘~’ as delimiter. I have the following data: I need to concat ‘Comment’ column for each ‘id’ in the ascending order of ‘row_id’ with ‘~’ as delimiter. Expected output is as below: GROUP_CONCAT is not an option since its not recognized in my Hive version. I can use collect_set or collect_list,

Advertisement