Yeap, you're right ... View more

Alex,   Thank you again.   Subquery approach has been recommended to our team as a long term solution. However, for short-tem solution to avoid regression impact, using view with limited partitions has been selected.   If I remember correctly, in MySQL `table A` data can be limited by `ON Clause` before joining so that cadidates for join can be reduced.   Thank you for your valuable comment. Gatsby ... View more

Alex,   First of all, thank you very much for your explanation.   You're right. the second query selects partition in table A. And, I'm fully aware of the difference between the first one( using on clause ) and second on ( where clause ) like the way you explained.   The reason different variances were tried to find out ways to limit table A data before joinging two tables. ( yes, second query doesn't work this way ) In MySQL, table A could be limited by `ON clause`, but with Impala, I don't know how to do it.   Do you think using subquery is the best way?   Thank you Gatsby ... View more

Hi Henry,   I have a question for you and it is about `partition pruning` ( about pruning )   Let's say there are two tables A and B. And, each table is partitioned by yearweek. And, here is the query I'd like to run. ( Yes. I need to use left join to get result what I want ) SELECT * FROM A LEFT OUTER JOIN B ON A.account_id = B.account_id AND A.yearweek = 201710 and B.yearweek = 201710 Even this doesn't select specific partition in `table A`  SELECT * FROM A LEFT OUTER JOIN B ON A.account_id = B.account_id AND B.yearweek = 201710 WHERE A.yearweek = 201710   Like you said, `A.yearweek` = 201710 on `On clause` couldn't select partition yearweek=201710. This might be filter is applied from `Left to Right`. In order to select specific partition for `table A`. I used `dynamic partition` and updated query like this. SELECT * FROM (SELECT * FROM A WHERE yearweek = 201710) a LEFT OUTER JOIN B b ON a.account_id = b.account_id AND b.yearweek = 201710 Do you think this is best I can do? Or is there way to limit data for `table A` by using `On clause`?   And, is there any refrerence you would recommend for me to upderstand how JOIN works in Impala and Hive?   Thank you very much in advance.   Gatsby ... View more

From log history, I found out someone keep running 'invalidate metadata' without table name. 😞   Thank you for your comment Gatsby ... View more

yeap. you're right. I will take a look log. Thank you Gatsby ... View more

@gaurang   Today, I had some issue with slow quries. And, the issue was related to metadata Catalog Daemon caches.     How often do you make quries to that TABLE/VIEW ( I don't think your issue is related to VIEW )?   In my case, metadata for TABLE was reloaded very often because Catalog Daemon flushes out metadata.   Take a look your catalog daemon and check if TABLE metadata is cached.   Gatsby ... View more

@Lars Volker   I have a question for you. How long the metadata loaded from Hive metastore by Impala Catalog Daemon stay in memory?   I'm using Impala 2.7 ( KUDU ). It seems the metadata is flushed more often than before.   Is there any configuration for life cycle for metadata in catalog daemon has?   @gaurang I'm asking this question here because I guess @Lars Volker answer can help resolve your issue.   Thank you Gatsby ... View more

@Tim Armstrong   Thank you very much for your explanation. 🙂   Gatsby ... View more

FYI, `COMPUTE STATS` can run with first level partition. https://issues.cloudera.org/browse/IMPALA-1570 ... View more

@Lars Volker   I thought that way too.  Thank you for confirming it 🙂   Gatsby ... View more

@Lars Volker I have a question for you. Does VIEW still need to load metadata separately even if metadata of TABLE for VIEW is already loaded?   @gaurang which CDH/Impala are you using?     Thank you Gatsby ... View more

Thank you. Then, why does this say that Impala2.8/CDH5.10? http://www.cloudera.com/documentation/enterprise/release-notes/topics/impala_new_features.html#new_features_280 ... View more