I am trying to convert GP to Hive migration logic but below statement giving the wrong output while execute query: output for GP : 2021-12-31 similar if we converted Hive query out put if Hive query : 2022-01-02 i could see the difference of the date. please help me. Thanks Answer You are subtracting interval 0 months and 1 day
Tag: hive
Hive regexp_extract numeric value from a string
I have a table as: I am trying to get the numeric values from the table. The expected output is A -> 123 / B -> 124 etc I am trying to do using regexp_extract Any suggestions please? Answer If the delimiters are fixed – ‘; ‘ between key-value pairs and ‘=’ between key and value, you can use str_to_map
QUALIFY equivalent in HIVE / SPARK SQL
I am trying to convert a Teradata SQL query into HIVE/Spark SQL equivalent. Is there any substitute for qualify along with count Answer Got it 🙂
How to retain the first record while using Window function LAG()?
I have a data which looks like – Actual Table – VIN Mode Status Start End ABC123456789 Mode 1 Waiting for Auth 01/01/2010 00:00:00 05/05/2014 14:54:54 ABC123456789 Mode 1 Waiting for URL 05/05/2014 14:54:54 05/13/2014 19:09:51 ABC123456789 Mode 1 Waiting for User 05/13/2014 19:09:51 11/13/2014 22:26:32 ABC123456789 Mode 1 Authorized 11/13/2014 22:26:32 11/13/2014 22:31:00 ABC123456789 Mode 1 Authorized 11/13/2014 22:31:00
find the consecutive values in impala
I have a data set below with ID, Date and Value. I want to flag the ID where three consecutive days has value 0. id date value 1 8/10/2021 1 1 8/11/2021 0 1 8/12/2021 0 1 8/13/2021 0 1 8/14/2021 5 2 8/10/2021 2 2 8/11/2021 3 2 8/12/2021 0 2 8/13/2021 0 2 8/14/2021 6 3 8/10/2021 3
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
SQL Rows to column but NOT transpose
We have a table regarding identification where data is as such: Customer Number Identification 101 passport-us 101 Licence 102 passport-uk 102 Licence 102 birth-cert 103 Licence-learner Thus one customer may have submitted multiple identification others in varying number/combination. Result we are after is : Customer Number Passport Licence Birth Certificate 101 passport-us licence 102 passport-uk licence birth-cert 103 licence-learner So
Extract year from timestamp in hive
I am writing the query to show the data entries for a specific year. Date is stored in dd/mm/yyyy hh:mm:ss.(Date TIMESTAMP – e.g. 12/2/2014 0:00:00). I am trying to display the two columns(name, orderdate) filtered by a specific year(year from orderdate). The requirement is to enter the specific year(2010 or 2020 etc) not the entire date. I tried using date_format()