I am new to Looker.
I have a partitioned BigQuery connection to Looker.
Due to this there is a required date filter on any query demanded, which prevents the source query for suggestions from functioning.
At first I found a work around by using a ‘sql_always_where’ in the explore, but then realized this was generating queries over all the data (which is big) to get suggestions.
I recently developed a dashboard from a derived query. At first I hard coded a where condition for the date field limiting it to the past few days, however, the final data would need to be at any time period in history. When I adjusted this date to the data start I realized it was very expensive to run. It needs to be run with different filters by different people so caching/persisting isn’t really an option.
To prevent the derived table query from querying over all the data, I followed the ‘Creating dynamic SQL derived tables with LookML and Liquid’ Lab to inject the dates into the derived query. While this works for the dates, my filters on other fields are no longer populating suggestions, and I cannot even force an entry.
Is there any way to get my suggestions back without having to hard code the initial time value in the derived query, which is significantly increasing the query data size?
And in general, is there any way to get suggestions to populate from a BigQuery partitioned table without having to scan the entire database?
Thanks!
LinkedIn
Twitter
