The query below outputs 1642575.0. But I only want 1642575 (just the number without the decimal and the zero following it). The number of delimited values in the field varies. The only constant is that there’s always only one number with a decimal. I was trying to write a regexp function to extract the …
Tag: hiveql
SQL (HUE) : Is there any way to convert 24 hrs time into 12 hrs AM / PM format with hours buckets
I have table A which contains column time stored as timestamp datatype. Table A: Contains time column in HH:MM:SS in 24 hrs format. Answer Please use below code. Replace now() with time for your query. Explanation – firstly i am checking if hour is >12. If yes, deducting 12 to get the hour. Then sett…
Is there a best way to join multiple tables
Can some one please help in joining/merging the table like below. I know how to do if the department columns (depart_1, depart_2, depart_3) are in one table. but not able to achieve this scenario as they are in different tables. I have almost 100 fields like department, so little concern about performance as …
User Defined Column name in select statement in Hivesql
I need to create user defined column name like below Postgresql query into HiveSql. Could you please help me on this. Answer Use backticks: But it is not possible to preserve case due to Hive limitation. Resulted column name will be in lower case: total customers See this answer: https://stackoverflow.com/a/5…
Find Unique Count Postgresql query into hivesql
I want to get unique customer counts. I have reference of postgresql query. Could you please convert this query into HiveSql Answer Use case expressions: One more method for counting distinct is size(collect_set()):
Convert Postgresql into HiveSql
How do I convert below mentioned postgresql query into HiveSql Answer Use CASE expressions like this: Also in Hive version >= 1.3 you can use quarter function:
insert extra rows in query result sql
Given a table with entries at irregular time stamps, “breaks” must be inserted at regular 5 min intervals ( the data associated can / will be NULL ). I was thinking of getting the start time, making a subquery that has a window function and adds 5 min intervals to the start time – but I only…
Hive SQL cast string as timestamp without losing the milliseconds
I have string data in the form 2020-10-21 12:49:27.090 I want to cast it as a timestamp. When I do this: select cast(column_name as timestamp) as column_name from table_name all of the milliseconds are dropped, like this: 2020-10-21 12:49:27 I also tried this: select cast(date_format(column_name,’yyyy-M…
Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns
I have a dataset with booking hotels. date_in has format “yyyy-MM-dd”. I need select top 10 the most visited hotel by month. I get the following error: Error: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only …
How to UPDATE a value in hive table?
I have a flag column in Hive table that I want to update after some processing. I have tried using hive and impala using the below query but it didn’t work, and got that it needs to be a kudu table …