I have a LookML that is powered by a big query view where the dataset that I am pulling from is partitioned on date.
Currently I have the view hardcoded to pull everything from 2017 onward, as shown below:
SELECT
*
FROM
project.dataset.mydataset
WHERE
date >= '2017-01-01'
The problem is that this is a 6.9 TB query and takes about 15 minutes to run in the Looker Explorer.
Is it possible to change the date partition I have in the view to a parameter depending on the date range that is selected in the explorer?
For example, say the current date is 8/6/2021:
I want to pull the last 7 days in the explorer -
I would like the SQL code in the lookml view to update to this:
SELECT
*
FROM
project.dataset.mydataset
WHERE date >= ‘2021-07-31’
This would minimize the size and time of the pull drastically.
Hi, I’m still new to Looker, but you should be able to do this when you set up the Explore in your model using sql_always_where if you are using a native derived table.
explore: mydataset {
label: "Last 7 Days"
sql_always_where: ((( mydataset.date ) >= ((DATE_ADD(CURRENT_DATE(), INTERVAL -6 DAY))) AND ( mydataset.date ) < ((DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL -6 DAY), INTERVAL 7 DAY)))));;
As a tip, I suggest adding the filter onto the Explore and then from the black Data bar, select SQL (defaults on Results) and copy/paste it into the sql_always_where because there are a lot of parentheses going on.
Hi, I would suggest you use a templated filter if you need to use a derived table, something like:
derived_table: {
sql: SELECT
*
FROM
project.dataset.mydataset
WHERE {% condition date %} date {% endcondition %} ;;
}
filter: date {
type: date
}
Or similarly using date_start and date_end filters.
Maybe a simpler way would be to create a view directly from your table:
view: my_view {
sql_table_name: project.dataset.mydataset ;;
......
}
And if you need to have the date to always be >= 2017-01-01, you can set this inside the explore:
explore: my_view {
sql_always_where: date >= "2017-01-01" ;;
}
This will further help reduce query costs by not needing to select * for every query.
Hi!, is there any way to use a parameter like a date selector to filter with an sql_always_where or always_filter within a model?
I have a problem similar to this, I need to filter always by the partition date but the filter will depend on the dates that the user need to see.. So what I want is the user to select the dates from a parameter and the use that value to filter the partition date with a sql_always_where in the model
Hi!, is there any way to use a parameter like a date selector to filter with an sql_always_where or always_filter within a model?
I have a problem similar to this, I need to filter always by the partition date but the filter will depend on the dates that the user need to see.. So what I want is the user to select the dates from a parameter and the use that value to filter the partition date with a sql_always_where in the model
Hi Flaviacamila,
You can use Looker - Templated filters to include an input as part of SQL filter, the syntax used is Looker - Liquid
Regards,
Leo