How to extract last 7 days of rows from the max date in shell

Question

I'm passing max(pay_date) to a variable Max_date in Shell from Hive table. The datatype of pay_date field is Date.

I want to extract 7 days of pay_date from Max_date of pay_date from the table.

I used below script to get...

#!/bin/bash
Max_date=$(hive -e "select max(pay_date) from dbname.tablename;")

hive -e "select pay_date from dbname.tablename where pay_date >= date_sub(\"$Max_date\",7);"

It's not giving me any output.

I'm stuck with passing a variable which has date value and use that in date_sub function for last 7 days of rows.

Please let me know if I'm missing some absolute basics.

I bet if you were to echo the Max_date variable, you'd see a lot more output than just a single value. — OneCricketeer, Dec 22 '15 at 13:50

Durga Viswanath Gadiraju · Accepted Answer · 2015-12-22T13:57:21.527

1

You can run query like this:

hive -e "select o1.order_date from orders o1 join (select max(order_date) order_date from orders) o2 on 1=1 where o1.order_date >= date_sub(o2.order_date, 7);

Also you can use this as part of shell script:

max_date=$(hive -e "select max(order_date) from orders" 2>/dev/null)

hive -e "select order_date from orders where order_date >= date_sub('$max_date', 7);"

edited Dec 22 '15 at 13:57

answered Dec 22 '15 at 13:41

Durga Viswanath Gadiraju

3,896
2
14
21

1

Why can't you do a cross join like normal? – OneCricketeer Dec 22 '15 at 13:52
@cricket_007 I did not get your question – Durga Viswanath Gadiraju Dec 22 '15 at 13:58
1

`Join a, b on 1=1` is the same as using `select a, b` – OneCricketeer Dec 22 '15 at 14:00
link is not related to hive, there are some differences – Durga Viswanath Gadiraju Dec 22 '15 at 14:05

How to extract last 7 days of rows from the max date in shell

1 Answers1