Hive Sql Query To Fill Missing Date Values In Table With Nearest Values

I have spent days trying to figure out how to add missing dates with the nearest values in Hive with no luck. I need to use Hive SQL for this based on environment constraints. The

Solution 1:

Get next date using lead() function, calculate difference in days, get string of spaces with length = diff in days, split, use posexplode to generate rows, use position to add to date to get missing dates:

with mytable as (--Demo dataset, use your table instead of thisselect stack(10, --number of tuples'Peter',float(50000),'2021-05-24',
'Peter',float(50035),'2021-05-25',
'Peter',float(50035),'2021-05-26',
'Peter',float(50610),'2021-05-28',
'Peter',float(51710),'2021-06-01',
'Peter',float(53028.1),'2021-06-02',
'Peter',float(53916.1),'2021-06-03',
'Mary',float(50000),'2021-05-24',
'Mary',float(50035),'2021-05-25',
'Mary',float(53028.1),'2021-05-30'
) as (account_name,available_balance,Date_of_balance)
) --use your table instead of this CTEselect  account_name, available_balance, date_add(Date_of_balance,e.i) as Date_of_balance
from
( --Get next_date to generate date rangeselect account_name,available_balance,Date_of_balance,
       lead(Date_of_balance,1, Date_of_balance) over (partitionby account_name orderby Date_of_balance) next_date    
  from mytable d  --use your table
) s lateralviewouter posexplode(split(space(datediff(next_date,Date_of_balance)-1),'')) e as i,x --generate rowsorderby account_name desc, Date_of_balance --this is to have order of rows like in your Converted Table

Result:

account_nameavailable_balancedate_of_balancePeter500002021-05-24Peter500352021-05-25Peter500352021-05-26Peter500352021-05-27Peter506102021-05-28Peter506102021-05-29Peter506102021-05-30Peter506102021-05-31Peter517102021-06-01Peter53028.12021-06-02Peter53916.12021-06-03Mary500002021-05-24Mary500352021-05-25Mary500352021-05-26Mary500352021-05-27Mary500352021-05-28Mary500352021-05-29Mary53028.12021-05-30