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 txn_date_key. (Basically pivoting based on order_item_unit_status column and bringing txn_amt respectively.) I used
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 subquery and apply distinct UNION is the same as
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 case it is possible. date() can be removed, string in yyyy-MM-dd format is compatible with date type: Result: Another efficient method using regexp_replace: If
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 last year. Improve this question EDIT:
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 corresponting to some keys
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 true ptn_dt = ‘2019-02-28’ (February) num_key active_indicator 111 true 112 false 113
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 HR 100 UK Service 250 UK 300
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 type DATE not supported in Value Boundary expression. I used the same query
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 themselves.
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