Troubleshooting PDT Explore queries get sent straight to 'enqueued'

[jamesnestler]

Knowledge Drop

Last tested: Sep 28, 2020

 

The Problem:

Users may report difficulty getting a PDT to build, specifically that a PDT query will go straight to queue, even though few other queries are running and the PDT is not the child of another PDT that is still building.

You'll want to verify that this is the scenario you are in by doing the following:

1. Navigate to the Production SQL of the derived table, and see if there is any use of .SQL_TABLE_NAME .
   1. If so, this PDT is possibly a dependency of a PDT that is still building.
2. Navigate to Admin/Queries and in another tab open that connection's settings; verify that running queries are fewer than the connection limit and PDT queries are fewer than the Max PDT Connections setting
3. With the Admin/Queries page open, navigate to an explore that utilizes the PDT in question (ideally, only uses that PDT), hit the gear icon and select Rebuild Derived Tables and Re-Run .
4. Tab back to Admin/Queries -- is the query from 2 marked as enqueued even though no or few other queries are running? If so, this card can help!

Troubleshooting:

• Verify that Looker can create PDTs on that connection: In Admin/Connections, hit test next to the connection. Do PDT related tests pass?
  • If not, troubleshoot the errors there, this card does not apply. If PDT tests do pass, proceed through this article.
• Verify that the PDT SQL can run: From the explore, open the query in SQL Runner, and trim out the CREATE TABLE AS SQL, as well as the outer query generated from the explore. Now, try running that raw PDT query; does it resolve or error out?
  • If it errors, this may be your problem.
  • Looker should handle SQL errors gracefully, so proceed through the rest of the card, and if this does indeed prove to be the problem, next steps would be to contact help.looker.com to file a bug.
• Trigger the PDT rebuild again, and see if it is running in the DB: From the explore, hit rebuild and rerun , then in SQL Runner hit the gear icon > Show Processes . This will list all currently running queries in the database. You may need to grab the SQL Text from the Show Processes query and compare to the derived table SQL. If context comments are enabled, you can optionally use the history id in the context comment to confirm or deny.
  • If the PDT query is running, then we are in a situation where Looker's Admin panel may be incorrect. The explore/dashboard queries should complete after the PDT table builds.
  • Note: Some databases do not support viewing currently running jobs from SQL Runner. If you don't see the button, you are in this case.
• See if the PDT table currently exists in the DB: From SQL Runner, hit Refresh Schemas and Tables with the connection in question selected. Change the schema to the scratch schema, and ctrl+f for the PDT table name from the outer explore query.
  • If we see an entry, we may be in a situation where the DB was able to successfully build the table, but Looker never received a success message from the DB.
  • Edit the PDT's sql parameter, and add a comment to trigger a change the the PDT name generated by Looker. Then trigger a build of the PDT by hitting Rebuild and Rerun from an explore. You should now see a query in Admin/Queries with a status of Building PDT

Why? (Anecdotes and Hypothesis):

Please do not take any of the below as gospel. Each will require additional investigation before confirming as an explanation. These are situations we have anecdotally seen to cause this behavior.

• The instance may be having memory issues. Grep for acquiring an instance in logs.
  • In this case, we would not expect the PDT query alone to be getting sent straight to enqueued. Other queries should exhibit the same behavior, although it may be intermittent.
• Looker may have experienced a temporary loss of connectivity to the Database, after the PDT finished building in the database but before the DB was able to communicate this back to Looker.
  • In this case, we would expect to see the PDT table in the DB's scratch schema, but no entry in active_derived_tables. The Reaper should resolve this issue for them next time it runs, however we can use the solution in Troubleshooting step 4 to resolve this manually.

This content is subject to limited support.