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
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.
How to join single table with multiple columns in Hive?
I have 2 tables: one has exchange rates (to GBP), the other has various amount columns with different currencies. I am joining these two tables and display all amount values in GBP, but I get nulls …
Calculate time difference between two columns of string type in hive without changing the data type string
I am trying to calculate the time difference between two columns of a row which are of string data type. If the time difference between them is less than 2 hours then select the first column of that row else if the time difference is greater than 2 hours then select the second column of that row. It can be
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,