question Re: When querying a VIEW, query planning takes a long time in Support Questions

When querying a VIEW, query planning takes a long time

gaurang — Fri, 16 Sep 2022 10:59:01 GMT

Hello -

As we are recomputing data everyday, I need remove old data and load new data everyday. We create our parquet data files through Map Reduce. So in order to reach ZERO downtime during switching yesterday's data with today's data, I came up with the idea of having a fixed VIEW and then after batch processing issue a ALTER VIEW statement to change the underlying table.

first time - CREATE VIEW table_view AS SELECT * from table_0130

daily - ALTER VIEW table_view AS SELECT * from table_0131

Most of our queries worked well. The response time did degrade slightly but nothing alarming. But for few BIG JOIN queries, the response time went from 2-3 secs to 2-3 mins.

On further digging into query profile, I found that the query planning is taking 2+ mins. Why would it take so much time? The VIEW is a simple one, just a SELECT *. Any impala conf settings that can resolve this?

I appreciate any help, pointers regarding this issue.

Querying VIEW

    Planner Timeline: 2m17s
       - Analysis finished: 2s588ms (2s588ms)
       - Equivalence classes computed: 1m16s (1m13s)
       - Single node plan created: 2m17s (1m1s)
       - Distributed plan created: 2m17s (223.64ms)
       - Lineage info computed: 2m17s (2.6ms)
       - Planning finished: 2m17s (9.974ms)
    Query Timeline: 2m31s
       - Start execution: 53.597us (53.597us)
       - Planning finished: 2m26s (2m26s)
       - Ready to start remote fragments: 2m26s (63.364ms)
       - Remote fragments started: 2m31s (4s442ms)
       - Cancelled: 2m31s (5.567ms)
       - Rows available: 2m31s (35.971ms)
       - Unregister query: 2m31s (118.833us)

Querying TABLE (directly)

    Planner Timeline: 55.334ms
       - Analysis finished: 21.430ms (21.430ms)
       - Equivalence classes computed: 22.938ms (1.507ms)
       - Single node plan created: 47.813ms (24.875ms)
       - Distributed plan created: 51.913ms (4.99ms)
       - Lineage info computed: 52.394ms (481.757us)
       - Planning finished: 55.334ms (2.939ms)
    Query Timeline: 1s036ms
       - Start execution: 45.736us (45.736us)
       - Planning finished: 125.378ms (125.332ms)
       - Ready to start remote fragments: 129.281ms (3.902ms)
       - Remote fragments started: 478.56ms (348.775ms)
       - Rows available: 882.741ms (404.685ms)
       - First row fetched: 982.468ms (99.727ms)
       - Unregister query: 998.825ms (16.356ms)

Re: When querying a VIEW, query planning takes a long time

Lars Volker — Tue, 31 Jan 2017 20:37:15 GMT

Hi gaurang,

Does the long query time only occur the first time you query a view after altering it? In that case if could be related to metadata loading for the underlying table. How many partitions and files do these tables typically have? You could also check the log files for anything suspicious.

Cheers, Lars

Re: When querying a VIEW, query planning takes a long time

thewayofthinkin — Tue, 31 Jan 2017 23:46:43 GMT

@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

Re: When querying a VIEW, query planning takes a long time

Lars Volker — Wed, 01 Feb 2017 00:01:48 GMT

@thewayofthinkin - No, the view should get expanded and then the resulting query will access the already loaded metadata of the table. From the order of the queries it looked like the view may be queried first, then the table, which could explain why the former took much longer than the latter.

Re: When querying a VIEW, query planning takes a long time

thewayofthinkin — Wed, 01 Feb 2017 00:12:23 GMT

@Lars Volker

I thought that way too.

Thank you for confirming it 🙂

Gatsby

Re: When querying a VIEW, query planning takes a long time

gaurang — Wed, 01 Feb 2017 01:38:58 GMT

Hi Lars-

No. Everytime I fire the query, it takes this long. I haven't checked logs yet. Went through the query profile, and found that query planning is what killing me. Will check logs to see if I can find anything.

Thanks for your response.

Regards,

Gaurang

Re: When querying a VIEW, query planning takes a long time

Lars Volker — Wed, 01 Feb 2017 01:40:11 GMT

Hi guarang,

Can you post the version of CDH and Impala you're using?

Thanks, Lars

Re: When querying a VIEW, query planning takes a long time

gaurang — Wed, 01 Feb 2017 01:42:06 GMT

Hey Gatsby - I'm using impalad version 2.3.0-cdh5.5.1

Re: When querying a VIEW, query planning takes a long time

thewayofthinkin — Wed, 01 Feb 2017 20:44:22 GMT

@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

Re: When querying a VIEW, query planning takes a long time

thewayofthinkin — Wed, 01 Feb 2017 22:09:43 GMT

@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

Re: When querying a VIEW, query planning takes a long time

Lars Volker — Thu, 02 Feb 2017 14:00:03 GMT

@gaurang - I suspect you may be hitting IMPALA-4242. Can you reduce the number of columns you're querying?

Re: When querying a VIEW, query planning takes a long time

Lars Volker — Thu, 02 Feb 2017 14:02:30 GMT

@thewayofthinkin - I don't know for sure, but I don't think metadata is flushed periodically. There also don't seem to be any configuration options of catalogd around metadata caching. Instead, the catalog should flush metadata when requested by "invalidate metadata" or by "refresh" or when a DDL statement makes changes to a table's metadata. Such changes should show up in the logfiles however.

Re: When querying a VIEW, query planning takes a long time

thewayofthinkin — Thu, 02 Feb 2017 17:26:45 GMT

yeap. you're right. I will take a look log.

Thank you
Gatsby

Re: When querying a VIEW, query planning takes a long time

gaurang — Thu, 02 Feb 2017 19:27:44 GMT

Hey Iv@c - I think you are right. The table beneath has 700+ columns. The moment I created a VIEW selecting just 30 columns, the response time became same as what I get querying directly the table (2-3 secs).
In my case though, I cannot select a set of columns, because the VIEW needs to be queries across the application. Maybe when we get a chance to change the front-end queries, we will try to implement this. Create different views for different reports, selecting just required columns from the underlying table.

Re: When querying a VIEW, query planning takes a long time

alex.behm — Fri, 03 Feb 2017 06:07:39 GMT

@gaurang would you be open to sharing your CREATE TABLEs, CREATE VIEW and the query that has slow planning time? No need for the data, just that should be sufficient for us to understand better what's going on.

Like Lars said, you are probably hitting IMPALA-4242 which explains the slow equivalence class computation, but I'd also like to understand the slow single-node planning time.

Thanks!