テンプレート フィルタを使用すると、日付フィルタで開始日と終了日（それぞれ {% date_start date_filter %} と {% date_end date_filter %}）を選択して、日付を参照できます。このページでは、ユースケースの例とそれを達成するための手順について説明します。
構文の説明
以下の構文はほとんどの言語で機能しますが、特定の言語には特定の使用例があります。例:-
BigQuery では、
TABLE_DATE_RANGEやTABLE_QUERYなどのテーブル ワイルドカード関数を扱うときにきめ細かい制御が可能なため、日付フィルタを指定する際に{% table_date_range prefix date_filter %}を使用するだけでは不十分です。 -
Hadoop では、列の型（
string、date）や形式（YYYY-MM-DD）に関係なく、日付パーティション分割列を操作できます。
使用上の注意
-
date_filterに値が指定されていない場合、{% date_start date_filter %}と{% date_end date_filter %}はどちらもNULLと評価されます。 -
オープンエンドの
date_filter（before 2016-01-01やafter 2016-01-01など）の場合、{% date_start date_filter %}フィルタまたは{% date_end date_filter %}フィルタのいずれかがNULLになります。
この 2 つのケースがいずれも無効な SQL にならないようにするには、LookML で IFNULL または COALESCE を使用します。
ユースケースの例
月単位のパーティション分割列（BigQuery）
一部の BigQuery データセットでは、テーブルが月別に整理され、テーブル ID に年と月の組み合わせが接尾辞として付加されます。この例は、次のデータセットにあります。このデータセットには、pagecounts_201601、pagecounts_201602、pagecounts_201603 などの名前のテーブルが多数含まれています。
例 1: always_filter に依存する LookML
次の派生テーブルは、TABLE_QUERY([dataset], [expr]) を使用してクエリを実行する適切なテーブルセットを取得します。
view: pagecounts {
derived_table: {
sql: SELECT * FROM
TABLE_QUERY([fh-bigquery:wikipedia],
"length(table_id) = 17 AND
table_id >= CONCAT( 'pagecounts_' , STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') ) AND
table_id <= CONCAT('pagecounts_' , STRFTIME_UTC_USEC({% date_end date_filter %},'%Y%m') )";
)
;;
}
filter: date_filter {
type: date
}
}
式のコードに関する注意事項:
-
table_idは、データセット内のテーブルの名前を参照します。 -
length(table_id) = 17は、pagecounts_201407_en_top64kのような名前を持つ他のテーブルを無視します。 -
STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m')は、開始日のYYYYmm部分のみを出力します。
NULL は date_filter の部分で置き換えられます。これを回避するには、Explore に always_filter が必要です。
explore: pagecounts {
always_filter: {
filters: [date_filter: "2 months ago"]
}
}
{% date_start date_filter %} は NULL と評価されるため、データセット内の最も古い日付より前の日付のフィルタでは失敗します。
例 2: always_filter に依存しない LookML
COALESCE または IFNULL を使用して、クエリを実行するデフォルトのテーブルセットをエンコードすることもできます。次の例では、過去 2 か月が使用されます。
-
下限:
COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH')) -
上限:
COALESCE({% date_end date_filter %},CURRENT_TIMESTAMP())
view: pagecounts {
derived_table: {
sql: SELECT * FROM
TABLE_QUERY([fh-bigquery:wikipedia],
"length(table_id) = 17 AND
table_id >= CONCAT( 'pagecounts_'; , STRFTIME_UTC_USEC(COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH')),'%Y%m') ) AND
table_id <= CONCAT( 'pagecounts_' , STRFTIME_UTC_USEC(COALESCE({% date_end date_filter %},CURRENT_TIMESTAMP()),'%Y%m') )"
)
;;
}
filter: date_filter {
type: date
}
}
米国のタイムゾーンでクエリする場合、ログファイルは UTC になります（BigQuery）
東部または太平洋のタイムゾーンでクエリを実行している場合でも、Looker のログファイルが UTC に保存されることがあります。この問題により、ログファイルがクエリのローカル タイムゾーンで明日の日付にすでにロールされている場合で、データが欠落する問題が発生する可能性があります。
この問題を解決するには、日付フィルタの終了日に日付を追加して、UTC の午前 0 時を過ぎてもそれらのログエントリが取得されるようにします。
次の例では、一般公開の [githubarchive:day] データセットを使用しています。このデータセットには、GitHub 情報の日別のパーティションが含まれています。
例 1: always_filter に依存する LookML
view: githubarchive {
derived_table: {
sql: SELECT * FROM
TABLE_DATE_RANGE([githubarchive:day.],
{% date_start date_filter %},
DATE_ADD({% date_end date_filter %},1,"DAY")
)
;;
}
filter: date_filter {
type: date
}
}
NULL を日付に置き換えるとこの SQL が失敗するため、always_filter を Explore に追加する必要があります。
explore: githubarchive {
always_filter: {
filters: [date_filter: "2 days ago"]
}
}
例 2: always_filter に依存しない LookML
この例では、デフォルトの期間が LookML でエンコードされます。COALESCE が unknown 型を返していたため、最終的に SQL を機能させるために IFNULL を使用する必要がありました。
-
下限:
IFNULL({% date_start date_filter %},CURRENT_DATE()) -
上限:
IFNULL({% date_end date_filter %},CURRENT_DATE())+ 1 日
view: githubarchive {
derived_table: {
sql: SELECT * FROM
TABLE_DATE_RANGE([githubarchive:day.],
IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()),
DATE_ADD(IFNULL({% date_end date_filter %},CURRENT_TIMESTAMP()),1,"DAY")
)
;;
}
filter: date_filter {
type: date
}
}
末尾の N 日間のウィンドウ関数（BigQuery）
特定の分析を行う際に、過去の期間にわたってデータを集計することがあります。このオペレーションを SQL で実行するには、通常、日付別に一意のテーブルの n 行前まで遡るウィンドウ関数を実装します。ただし、日付で分割されたテーブルを使用する場合のジレンマがあります。クエリが計算のために追加の履歴テーブルを必要とする場合でも、まず、クエリを実行するテーブルのセットを指定する必要があります。
解決策: 開始日を、日付フィルタで指定された日付よりも前に設定できるようにします。さらに 1 週間遡る例を以下に示します。
view: githubarchive {
derived_table: {
sql: SELECT y._date,
y.foo,
y.bar
FROM (
SELECT _date,
SUM(foo) OVER (ORDER BY _date RANGE BETWEEN x PRECEDING AND CURRENT ROW),
COUNT(DISTINCT(bar)) OVER (ORDER BY _date RANGE BETWEEN x PRECEDING AND CURRENT ROW)
FROM (
SELECT _date,
foo,
bar
FROM TABLE_DATE_RANGE([something:something_else.], DATE_ADD(IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()), -7, "DAY"), IFNULL({% date_end date_filter %},CURRENT_TIMESTAMP()))
) x
) y
WHERE {% condition date_filter %} y._date {% endcondition %};;
}
filter: date_filter {
type: date
}
}
ユーザーがクエリに最初に指定した期間に結果セットを切り詰める WHERE 制約を指定しているため、追加の SELECT ステートメントが必要になります。
「YYYY-MM-DD」形式の文字列により日付で分割されたテーブル（Presto）
Hadoop テーブルでは、パーティション分割列を使用して、頻繁に検索される列（特に日付）の検索時間を短縮することが一般的です。日付列の形式は任意に指定できますが、YYYY-MM-DD と YYYYMMDD が最も一般的です。日付列の型は、文字列、日付、または数値にできます。
この例では、Hive テーブル table_part_by_yyyy_mm_dd にパーティション分割列 dt（YYYY-MM-DD 形式の文字列）があり、これが Presto で検索されています。
生成ツールを初めて実行すると、LookML は次のようになります。
view: table_part_by_yyyy_mm_dd {
sql_table_name: hive.taxi. table_part_by_yyyy_mm_dd ;;
suggestions: no
dimension: dt {
type: string
sql: ${TABLE}.dt ;;
}
}
次の両方の例の式のコードに関する注意事項:
-
date_startとdate_endの出力はtype: timestampになります。 -
date_format( <expr>, '%Y-%m-%d')は、タイムスタンプを文字列に変換して適切な形式にするために使用されます。 -
coalesceは、before 2010-01-01やafter 2012-12-31などのフィルタが入力された場合に NULL を処理するためのものです。 -
これは Presto 言語コードであるため、Hive は形式設定文字列（
yyyy-MM-dd）にいくつかの違いがあり、date_formatは NULL 値を受け入れることができないため、coalesceはなんらかのデフォルトに並べ替えられてここに移動します。
例 1: 共通のテーブル式を使用してテーブルをフィルタする LookML
この例では、派生テーブルを使用してテーブルをフィルタします。
view: table_part_by_yyyy_mm_dd {
# sql_table_name: hive.taxi. table_part_by_yyyy_mm_dd
suggestions: no
derived_table: {
sql: SELECT * FROM hive.taxi. table_part_by_yyyy_mm_dd
WHERE ( coalesce( dt >= date_format({% date_start date_filter %}, '%Y-%m-%d'), TRUE) )
AND ( coalesce( dt <= date_format({% date_end date_filter %}, '%Y-%m-%d'), TRUE) )
;;
}
filter: date_filter {
type: date
}
dimension: dt {
type: string
sql: ${TABLE}.dt ;;
}
}
通常、パーティション分割テーブルはテーブル全体をスキャンするには時間がかかりすぎる（および、クラスタ リソースを大量に消費する）ため、このビューの Explore にもデフォルトのフィルタを使用することをおすすめします。
explore: table_part_by_yyyy_mm_dd {
always_filter: {
filters: [date_filter: "2013-01"]
}
}
例 2: 述語で直接フィルタリングする LookML
この例では、テーブルに対して述語フィルタリングを直接実行しています。サブクエリや共通テーブル式は使用していません。
view: table_part_by_yyyy_mm_dd {
sql_table_name: hive.taxi.table_part_by_yyyy_mm_dd ;;
filter: date_filter {
type: date
sql: ( coalesce( ${dt} >= date_format({% date_start date_filter %}, '%Y-%m-%d'), TRUE) )
AND ( coalesce( ${dt} <= date_format({% date_end date_filter %}, '%Y-%m-%'), TRUE) );;
}
dimension: dt {
type: string
sql: ${TABLE}.dt ;;
}
}
この LookML によって生成されたクエリについて SQL Runner の EXPLAIN の出力をチェックすることで、テーブル パーティションが実際に使用されていることを確認でき（データ探索ページの [Data] タブの [SQL] セクションをクリックするとアクセスできます）、以下のように表示されます。
output[table_part_by_yyyy_mm_dd.count] => [count:bigint]
table_part_by_yyyy_mm_dd.count := count
TopN[500 by (count DESC_NULLS_LAST)] => [count:bigint]
Aggregate(FINAL) => [count:bigint]
count := "count"("count_4")
RemoteExchange[GATHER] => count_4:bigint
Aggregate(PARTIAL) => [count_4:bigint]
count_4 := "count"(*)
Filter[(COALESCE(("dt" >= CAST('2013-04-01' AS VARCHAR)), true) AND COALESCE(("dt" <= CAST('2016-08-01' AS VARCHAR)), true))] => [dt:varchar]
TableScan[hive:hive:taxi: table_part_by_yyyy_mm_dd, originalConstraint = (COALESCE(("dt" >= CAST('2013-04-01' AS VARCHAR)), true) AND COALESCE(("dt" <= CAST('2016-08-01' AS VARCHAR)), true))] => [dt:varchar]
LAYOUT: hive dt := HiveColumnHandle{clientId=hive, name=dt, hiveType=string, hiveColumnIndex=-1, partitionKey=true}
:: [[2013-04-01, 2013-12-31]]
partitionKey=true と表示されたパーティション キーの範囲は、パーティション分割列のみをスキャンしていることを示します。