Date Parameter In a LookML View

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 -

52c3741d-8152-4554-bb17-04caa3d366e8.png

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.

0 4 2,003
4 REPLIES 4

rallaking
Participant I
5eddb320-1b31-4832-a952-492d00afdd78.png

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

leobardor
Participant V

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

Top Labels in this Space
Top Solution Authors