To learn about SQL Runner, see this document page and choose release 4.16 or higher.
A good way to gain an idea of a query's performance is to know how many rows the query will return. Looker will only return 5000 rows in the browser, since browsers have limitations on the amount of data that can be rendered. Knowing the total number of rows a query will return is particularly useful when it returns more than 5000 rows.
There are two key ways to do figure out the number of rows in a given query:
Using SQL Runner
SQL Runner can be used to quickly calculate the number of rows returned by a specific query. When you add fields and filters to an exploration, Looker begins to auto-generate SQL for that query. You can access the SQL for any query by clicking on the SQL tab.
After clicking on the SQL tab, click the Open in SQL Runner button on the bottom right hand corner to view this query in SQL Runner.
In order to count the total number of rows returned by this query, select COUNT(*)
from this query by placing the SQL for this query as a subselect in the FROM
clause, like so:
SELECT COUNT(*)
FROM
(--query here--)
This might look something like:
This will count all the rows returned by the query. Just hit 'Run' and you will see the number of rows
Using LookML
The number of rows returned by a query can also be calculated using a Looker field. However, this approach is also much less flexible that using SQL Runner.
In this case, a measure can be created to calculate the number of rows returned by a query. This measure would be of type: count_distinct
and would count the dimension that serves as the "grouping" dimension in the query. This will give you the number of distinct values of this dimension, which is equivalent to the number of rows returned by a query that groups by dimension.
In order to use this measure, run a query that contains only this measure.
If you have one dimension in the query, you would count this dimension. This might look something like:
- measure: count_of_created_date
type: count_distinct
sql: ${created_date}
This will count the number of rows returned by a query grouped only by the dimension created_date
.
If you have more than one dimension in the Look, you can create a similar count_distinct
measure that concatenates these dimensions together, and then counts that. This would look something like:
- measure: count_of_created_date_and_age_tier
type: count_distinct
sql: CONCAT(${created_date}, ${age_tier})
Have fun!
Cheers,
Alejandro aka Freedom Looker