0

I have a view, which is formed from joining a table(around 25 TB) with another table(5 GB) 

CREATE VIEW view1 AS 
select columns(around 30 columns)
from table1
RIGHT JOIN table2
ON conditions1
where conditions2

When I run this select

select * 
from view1
where date='20170320'

It finishes in a couple of minutes. But this select

select * 
from view1
where date = from_unixtime(unix_timestamp(date_add(CURRENT_DATE, -1) ,'yyyy-MM-dd'), 'yyyyMMdd')

Takes forever to start(if it starts at all) and doesn't finish. There are no actual errors. It just waits until I get tired and decide to kill it.

Why is this happening?

Evgenii
  • 389
  • 3
  • 7
  • 21
  • Your table contains a string, so why are you comparing to a unix time? Does simply `select from_unixtime(unix_timestamp(date_add(CURRENT_DATE, -1) ,'yyyy-MM-dd'), 'yyyyMMdd')` take long? – OneCricketeer Jun 16 '17 at 12:55
  • @cri `select from_unixtime......` finishes in a few seconds. `comparing to a unix time` How should I do it then? – Evgenii Jun 16 '17 at 13:40
  • Nevermind, I forgot that `from_unixtime` returned strings – OneCricketeer Jun 16 '17 at 15:35
  • Possible duplicate of [Why partitions elimination does not happen for this query?](https://stackoverflow.com/questions/42376268/why-partitions-elimination-does-not-happen-for-this-query) – David דודו Markovitz Jun 16 '17 at 15:47

0 Answers0