I have a dataset as given in the link, DataSet I want to segregate the column “order_item_unit_status” as separate column and bring respective transaction amount for the same. Desired output is given below. Objective is to consolidate the txn_amt into respective categories and group them based on …
Tag: hiveql
Hive Union of two queries is giving compile error
I am not sure what is wrong with following hive query. However, it’s throwing error. I am able to run this queries individually. However, facing issues while running with union Error Answer Hive versions prior to 1.2.0 only support UNION ALL Workaround: Use UNION ALL instead of UNION, wrap all into subq…
Want to convert timestamp to date format in hive
want to convert this number ‘20210412070422’ to date format ‘2021-04-12’ in hive I am trying but this returns null value from_unixtime(unix_timestamp(eap_as_of_dt, ‘MM/dd/yyyy’)) Answer The best methoid is to do without unix_timestamp/from_unixtime if possible and in your c…
Can’t found the poroblem within this Hive Query [closed]
Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers. This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers. Closed las…
Hive Explode the Array of Struct key: value:
This is the below Hive Table And this is the data in the above table- Is there any way I can get the below output using HiveQL? I tried use explode() but I get result like that: Answer Use laterral view [outer] inline to get struct elements already etracted and use conditional aggregation to get values corres…
Compare two SQL tables and return count of rows with changes
I have two partitions from an SQL table containing num_key records. I need to compare and count changes in the February records versus the January records. SAMPLE DATA AND DESIRED RESULTS: ptn_dt = ‘2019-01-31’ (January) num_key active_indicator 111 true 112 false 113 false 114 false 115 true 116 …
How to grouby data in one column and distribute it in another column in HiveSQL?
I have the following data: CompanyID Department No of People Country 45390 HR 100 UK 45390 Service 250 UK 98712 Service 300 US 39284 Admin 142 Norway 85932 Admin 260 Germany I wish to know how many people belong to the same department from different countries? Required Output Department No of People Country H…
Why does Hive throw me an error while using Order by date?
I am trying to write a query In hive and I am seeing the following error. “Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Primitve …
Is there a way to parse csv string with escapings via HQL/SQL?
I have a problem parsing csv-formatted data that is stored in a Hive table column that is loaded into PostgreSQL DB afterwards. What I need to do is to retrieve some fields from there, however, if a comma is enquoted, it should be treated as a part of data to retrieve; on top of that, quotes can be escaped th…
How to get min and max from 7 columns in Hive Hue excluding zeros
I have a table which has 9 columns. Below is the structure of it I need the min and max of these columns for a row excluding zeros. Below is the required table structure If you see the columns min and max, min is minimum of 7 cols (col1 to col7) in a particular row excluding zero and max is