Using sql runner to test derived tables

To learn about SQL Runner, see this document page and choose release 4.16 or higher.

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:

Old LookML

    - view: user_order_facts
      derived_table:
        sql: |
          SELECT
          o.user_id AS user_id,
          COUNT(*) AS lifetime_items,
          ROUND(AVG(oi.sale_price), 2) AS average_item_price,
          ROUND(SUM(oi.sale_price), 2) AS lifetime_revenue
          FROM 
             (select id, 
             order_id, 
             sale_price,
             from order_items 
             where sale_price>20) oi
          left outer join orders o
            ON oi.order_id = o.id
          GROUP BY user_id
        indexes: [user_id]

New LookML

derived_table: {
  sql: SELECT
    o.user_id AS user_id,
    COUNT(*) AS lifetime_items,
    ROUND(AVG(oi.sale_price), 2) AS average_item_price,
    ROUND(SUM(oi.sale_price), 2) AS lifetime_revenue
    FROM
       (select id,
       order_id,
       sale_price,
       from order_items
       where sale_price>20) oi
    left outer join orders o
      ON oi.order_id = o.id
    GROUP BY user_id
     ;;
  indexes: ["user_id"]
}

When I queried it, I received 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:

073c5010fdd5dffb3fb4d23be252a4b8f4a1589c.png

Running this query gives us the same error as before:
61cea27be2763544585dddb26d3906b95c291cfc.png

So we know the error is coming from something in our derived table SQL syntax.

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.

Indeed, when I comment out everything but

(select id, 
        order_id, 
        sale_price,
from order_items 
where sale_price>20) 

I see that this simpler query is still causing the error. Looking closely, I notice that there is an extra comma after sale_price that is causing the errors.

Removing this enables the whole query to run successfully so we know that this was driving our explore to have 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 cause the error, gradually add back in parts of your original query the query to find what part of the original query caused the error.

0 1 1,543
1 REPLY 1

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.

Top Labels in this Space
Top Solution Authors