View the original community article here
Overarching themes/questions:
Here is a list of common questions our users often need support with, along with troubleshooting steps to try out:
1. Why is my SQL query slow even when there are no queries queued on Looker?
-
Run against database (or SQL Runner): check if that runs fast to see if it is a database problem or looker problem
-
If the query runs slow in both SQL Runner and database, that means that the query itself is slow and that it is not specifically a Looker issue, but instead a database issue or query complexity issue
-
-
Explain in SQL can help to optimize the query
-
Check join logic, so we can make sure to avoid fanouts
-
Open up the generated SQL in SQL Runner to test your query
2. I have a part of a query, and I see there’s an error from this query. How can I find out which field is causing the error?
-
Start by making the query less complex:
-
Remove all filters and extra fields to isolate the field causing the error
-
If the query is pivoted, unpivot the field and then take a look at the generated SQL
-
-
Open up the generated SQL in SQL Runner to test your query/see a more detailed error message
-
Search the SQL database dialect docs for the functions we are using to ensure that we are using correct syntax
-
Check to see if the database name is included in the error surfaced on the explore.
-
If it is, we are looking at a SQL error and need to check our SQL
-
If it is not, we are looking at a LookML issue and need to check our LookML
-
-
Are we able to run the query in the database without error?
3. Why is Looker’s generated SQL different from the one I usually write?
-
Symmetric aggregates: They help to prevent miscalculating aggregates, but also add in a lot of complex looking code in the generated SQL
4. What does a particular SQL error mean? (function missing, data type mismatch, etc)
-
Is there a data type mismatch between what we have written versus the data types that are required for the function, per the database dialect documentation?
-
Are we using the correct function for the database dialect?
-
Google the error: there are lots of online forums that can provide context
-
Open up the generated SQL in SQL Runner to test your query
Specific/Common Errors:
So we just went over the overarching themes that we can run into when working with complex SQL. However, there are times when we might run into errors that are very specific to the query we are working with. Here are the top few errors we may run into, we use MySQL here as an example, the error messages from other SQL dialects can be very similar:
Error 1:
SQL Syntax Error: The MySQL database encountered an error while running this query.
(conn=95374054) Unknown column 'users.city' in 'field list'
So what does this error mean?
The database is throwing an error stating that the column we’re referencing in the `sql` parameter of a field isn’t being found. Therefore, there are a few things we can check for here:
Steps to troubleshoot:
-
Narrow down the field(s) the error is coming from and identify the table in the database that the error is coming from
-
Run a `SELECT * FROM [TABLE_NAME] LIMIT 5;` to view a list of columns in the table. Check if the column in question exists in your table.
Frequent causes of this error:
-
Typos: When manually typing table names into the `sql` parameter, we may make a typo
-
Data changed in the database: If column names change in a table in the database, that won’t automatically update the Looker view file that’s based on that table. We’ll need to manually change that in the Looker view file or generate a new view file with “create view from table”
-
Resource for if your data is changing frequently: https://community.looker.com/lookml-5/automatically-refresh-of-view-definition-based-on-updated-data-source-table-11011
Error 2:
SQL Syntax Error: (conn=95369153) FUNCTION demo_db.getdate does not exist
Explanation: This error is due to using the wrong function. As we can see in the SQL here - the `getdate()` function is not a recognized function in the MySQL database. The correct function to use in this case would be curdate()
`sql: curdate() ;;`
Because there are nuances in which SQL dialect is based on your database - it is a good idea to double check the functions and their properties when writing custom SQL!
Error 3:
SQL Error: "X is not a valid group by expression"
Explanation: What does error mean? It is likely that the fields we are added to our query is not aggregating (measure) across a group (dimension)
We would want to create a measure for the field that we are using:
Rather than doing the sum function in the SQL of the dimension - we would want to create a type sum measure instead
Error 4:
invalidQuery: No matching signature for operator >= for argument types: TIMESTAMP, DATE. Supported signature: ANY >= ANY at [34:11]
-
Here we want to make sure that the two date functions that we are comparing are the same DATATYPE (timestamp >= timestamp or date >= date or datetime >= datetime)
-
Oftentimes we can use the cast() to cast one of the fields to match the other fields data type
-
It is often helpful to take a look at the query to see which fields are type date or time field from which the error could be occurring and working up from there
-
A similar error can happen when we write “case when .. then” statement if the data type for all the “then” statements are not consistent
More common SQL errors troubleshooting tips:
Dealing with Non-Unique Primary Keys
Error: Column <name> must appear in the GROUP BY clause or be used in an aggregate function
SQL Error: "Syntax error at or near:"
SQL Error: relation "schema.table" does not exist"
SQL Error: "X is not a valid group by expression"
No Matching Signature for operator >= for argument types: TYPE, OTHER TYPE