Skip to content
Advertisement

Tag: hiveql

SQL Summing columns based on date key

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

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

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

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

Advertisement