View the original community article here
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:
- Navigate to the Production SQL of the derived table, and see if there is any use of
.SQL_TABLE_NAME
.- If so, this PDT is possibly a dependency of a PDT that is still building.
- 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
- 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
. - 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 theShow 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 hittingRebuild and Rerun
from an explore. You should now see a query in Admin/Queries with a status ofBuilding 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.
- In this case, we would expect to see the PDT table in the DB's scratch schema, but no entry in
This content is subject to limited support.