Question

Date Parameter In a LookML View

  • 6 August 2021
  • 2 replies
  • 87 views

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.

 

 

 

 

 

 

 

 

 

 

 


2 replies

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.

Reply