템플릿 필터를 사용하면 날짜 필터에서 시작일과 종료일(각각 {% 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이 됩니다.
이 두 가지 사례 모두 잘못된 SQL이 발생하지 않도록 하려면 LookML에서 IFNULL 또는 COALESCE를 사용할 수 있습니다.
사용 사례
월별로 파티션을 나눈 열(BigQuery)
일부 BigQuery 데이터 세트에서 테이블은 월별로 정리되며 테이블 ID는 연/월 조합을 접미사로 포함합니다. 이러한 예시는 공개 BigQuery Wikipedia [fh-bigquery:wikipedia] 데이터 세트에 있으며 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 기준으로 자정을 넘는 경우 해당 로그 항목이 선택되도록 하는 것입니다.
다음 예시에서는 GitHub 정보의 일별 파티션이 있는 공개 [githubarchive:day] 데이터 세트를 사용합니다.
예시 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
}
}
이 SQL은 NULL이 날짜로 대체되면 실패하므로 Explore에 always_filter를 추가해야 합니다.
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개 행에 도달하는 윈도우 함수를 구현합니다. 그러나 날짜로 파티션을 나눈 테이블을 사용하는 경우 문제가 발생할 수 있습니다. 쿼리에 실제로 계산을 위해 추가 기록 테이블이 필요한 경우에도 쿼리를 실행할 테이블 집합을 먼저 지정해야 합니다.
해결 방법: 시작일이 날짜 필터에 지정된 날짜보다 이전 날짜가 되도록 합니다. 다음은 일주일을 더 연장하는 예입니다.
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에는 Presto에서 검색되는 YYYY-MM-DD 형식의 문자열인 파티션을 나눈 열 dt가 있습니다.
생성기가 처음 실행되면 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의 출력을 확인하여 테이블 파티션이 실제로 사용 중인지 확인할 수 있습니다(Explore 페이지의 데이터 탭에서 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는 파티션을 나눈 열만 검사하고 있음을 나타냅니다.