Using SQL Runner to check errors in derived tables
In Looker, a derived table is a query whose output is treated as a table in the database.
For example, you might have a database table called orders that has many columns. You want to compute some customer-level aggregate metrics, such as how many orders each customer has placed or when each customer placed their first order. Using either a native derived table or a SQL-based derived table, you can create a new database table named customer_order_summary that includes these metrics.
You can then work with the customer_order_summary derived table as if it were any other table in the database.
Native derived tables and SQL-based derived tables
To create a derived table in your Looker project, use the derived_table parameter under a view parameter. Inside the derived_table parameter, you can define the query for the derived table in one of two ways:
For a native derived table, you define the derived table with a LookML-based query.
For a SQL-based derived table, you define the derived table with a SQL query.
Here are more resources about Derived tables in Looker:
Using sql runner to test derived tables
If you see an error coming from a derived table, you can determine the cause of the error by copying the SQL statement into SQL Runner and gradually narrow down the query to identify the problem code. For example, let’s say I have a derived table defined like this:
LookML
When I queried it, I got this message:
temp derived_table user_order_facts creation failed: SQL Error in CREATE TABLE as SELECT: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
To track down where the issue is coming from, we can open the query in SQL Runner by clicking the SQL tab and selecting “open in SQL Runner.” We now have the query open in SQL Runner and can start troubleshooting:
If your derived table is persisted or exists on an a dialect that creates temporary tables, start by commenting out any create statements.
Also, SQL Runner can only run one select statement at once so comment out any extra queries. This leaves us with:
From here, we can start commenting out sections until we narrow it down to a few lines of code. For example, in this case the error message mentions the syntax issue being near the ‘from order_items’ so I might start by checking the subquery.
I see that this simpler query is still generating an error. Upon closer inspection, I see that the extra comma after sale_price is causing the errors.
Removing this allows the entire query to run successfully, so we know that this was causing our explore to generate an error. We can now adjust our derived table view to reflect this change and see that the explore returns without error.
If your simpler query does not generate the error, gradually add back in parts of your original query to find what part of the original query caused the error.
Resources: Using sql runner to test derived tables
