[Analytic Block] BigQuery Table Date Range (Deprecated)

[Dillon_Morrison]

This method has been Deprecated, this method only works with Legacy SQL, Standard SQL is recommended.

##About This Block

This block demonstrates how to effectively query time data in BigQuery, so that analysts can perform crucial time-related analyses (for example, events over time, events per time period, purchases per day, and so forth).

A best practice for time series data in BigQuery is to partition it by dates and store the partitions in individual files or tables. This makes it easy to add, remove and maintain datasets. The partitioned tables can be unioned together and effectively appear as a single table using the table wildcard functions TABLE_DATE_RANGE.

For example, click data would be stored in a series of tables named clicks_20140920, clicks_20140921, … clicks_20140926 and they would be queried with SQL like this:

SELECT ...
FROM (TABLE_DATE_RANGE(
	clicks_, 
	TIMESTAMP('2014-09-20'), 
	TIMESTAMP('2014-09-26')))
WHERE ...

Looker provides a practical and easy way to implement TABLE_DATE_RANGE through the {% table_date_range %} tag.

##Ideal Data Types
This pattern is specifically designed for date partitioned tables in BigQuery.

The {% table_date_range %} tag takes two parameters. The first parameter is the name of the LookML field to use as a filter. The second parameter is the common prefix for the partitioned tables.

##Expected Output
This Block allows you to easily query specific time periods in BigQuery, allowing for any time-based analysis, even for users who don’t understand how the tables are laid out under the hood.

This example explores the public data ‘githubarchive’, and has been set up to show the last 7 days of data. Any user viewing these results can change the date filter (to explore the last two weeks, say), and the query will be rewritten to query the appropriate tables automatically.

###Repositories created in the last 7 days.

##Try it Yourself
###How it’s Done:

First, create a date filter in the view:

  - filter: date_filter
    type: date

You can then use it in the {% table_date_range %} tag and in the always_filter of the explore.
###Try it Out:

- explore: githubarchive
  always_filter:
    date_filter: 2 days #This will be the default date range.

- view: githubarchive
  sql_table_name: |
      ( SELECT * FROM {% table_date_range date_filter githubarchive:day.events_ %})

  fields:
  - filter: date_filter #This date filter is used in the table_date_range tab and as an explore filter.
    type: date

#Below here put in measures and dimensions that are relevant for this view
#Example of measure
  - measure: event_count
    type: count
    drill_fields: [id, created_date, type, actor_name, repo_name]

#Example of dimensions
  - dimension: event_type
    sql: type

  - dimension_group: created_at
    type: time
    timeframes: [time, date, week, month]
    sql: created_at
...

###Further Analysis

There is a {% table_date_range_last %} parameter available, which works just like table date range, however it returns on the last date’s table. This is useful for referencing and joining in snapshot tables that are rebuilt daily.