테이블 종류
- 와일드 카드 테이블
하나의 FROM 구문으로 다수의 테이블을 한 번에 읽을 수 있는 것.
아래 쿼리와 같이 테이블 이름과 표현식이 일치하는 경우, 모든 테이블을 와일드 카드 문자를 통해 하나의 테이블처럼 조회할 수 있다.
SELECT * FROM '<데이터 세트>.<테이블-prefix>*' WHERE 조건-
Sharding
yyyymmdd 형식의 suffix를 가진 다수의 테이블.
ex) <테이블 이름>_(92)라고 되어있는 테이블이 있다면 92개의 테이블을 하나의 테이블 이름 문자열로 검색할 수 있고, 날짜 별로 테이블이 나누어져 있다. -
Partition
지정한 컬럼의 범위에 따라 데이터를 나눠 저장한 테이블.
샤딩과의 차이점은 파티션은 하나의 테이블을 나눠 저장한 것이고, 샤딩보다 속도가 빠르다.
파티션 구역 개수에 제한이 있다.(4000개)
컬럼 데이터 타입에 제한이 있다.(정수, 날짜, 수집시간)
파티션 지정 조회를 강제할 수 있다. -
Cluster
지정한 컬럼들로 블록을 만들어 정렬해 저장.
파티션과의 차이점은 다수의 컬럼을 지정할 수 있고, 들어가는 컬럼의 타입도 중요하지x
각 블럭의 크기가 일정하지 않은 경우 사용.
그리고 지정한 클러스터의 컬럼 순서가 where조건에 그대로 들어가야 된다.
-> 지정한 클러스터 순서대로 정렬이 미리 되어 있기 때문.
(순서를 지켜서 사용하지 않을 경우 클러스터를 사용하는 장점이 사라진다.)
데이터 업데이트에 따라 자동으로 정렬된다.
조회 범위는 조정되지만 조회 비용 미리보기에 반영되지x
테이블 파티션
파티션에 사용할 수 있는 컬럼은 date, integer, datetime, timestamp가 있다.
날짜로 파티션을 나눈 테이블이 일반적인 사용 사례이다.
특정 기간 동안의 레코드에만 관심이 있는 경우 WHERE 조건에 대해 행을 비교하기 위해 매번 전체 데이터 세트를 스캔하는 것은 낭비이다.
ex)
- 작년의 모든 거래
- 지난 7일 동안의 모든 방문자 상호작용
- 지난 달에 판매된 모든 제품
이전 쿼리에서와 같이 전체 데이터 세트를 스캔하고 날짜 필드를 필터링하는 대신 이제 날짜로 파티션을 나눈 테이블을 설정
#standardSQL
CREATE OR REPLACE TABLE ecommerce.partition_by_day
PARTITION BY date_formatted
OPTIONS(
description="a table partitioned by date"
) AS
SELECT DISTINCT
PARSE_DATE("%Y%m%d", date) AS date_formatted,
fullvisitorId
FROM `data-to-insights.ecommerce.all_sessions_raw`date를 기준으로 파티션을 나눈다
파티션을 나눈 테이블로 데이터 처리
아래 쿼리를 실행하고 처리할 총 바이트를 확인.
#standardSQL
SELECT *
FROM `data-to-insights.ecommerce.partition_by_day`
WHERE date_formatted = '2016-08-01'쿼리 결과에 JOB INFORMATION을 보면
25KB가 처리된다.
아래 쿼리를 실행하고 처리할 총 바이트를 확인.
#standardSQL
SELECT *
FROM `data-to-insights.ecommerce.partition_by_day`
WHERE date_formatted = '2018-07-08'
0 바이트
쿼리 엔진은 쿼리를 실행하기 전에 존재하는 날짜 파티션이 있는지 알고 있기 때문에 나오는 결과이다.
테이블 파티션(Auto-expiring)
공개 데이터세트 탐색
GSOD NOAA 데이터세트 검색
#standardSQL
CREATE OR REPLACE TABLE ecommerce.days_with_rain
PARTITION BY date
OPTIONS (
partition_expiration_days=60,
description="weather stations with precipitation, partitioned by day"
) AS
SELECT
DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
(SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names
prcp
FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather
WHERE prcp < 99.9 -- Filter unknown values
AND prcp > 0 -- Filter
AND CAST(_TABLE_SUFFIX AS int64) >= 2018- 테이블 이름: ecommerce.days_with_rain
- 날짜 필드를 PARTITION BY로 사용
- OPTIONS의 경우 partition_expiration_days = 60을 지정
- 테이블 설명 추가 = "강수량이 있는 기상 관측소, 요일별로 분할됨"
파티션 만료가 작동하는지 확인
과거 60일부터 오늘까지의 데이터만 저장하고 있는지 확인하려면 DATE_DIFF 쿼리를 실행하여 설정된 파티션의 수명을 볼 수 있는 쿼리를 실행
#standardSQL
# avg monthly precipitation
SELECT
AVG(prcp) AS average,
station_name,
date,
CURRENT_DATE() AS today,
DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age,
EXTRACT(MONTH FROM date) AS month
FROM ecommerce.days_with_rain
WHERE station_name = 'WAKAYAMA' #Japan
GROUP BY station_name, date, today, month, partition_age
ORDER BY date DESC; # most recent days first일본 와카야마 의 NOAA 기상 관측소의 평균 강우량을 추적하는 쿼리
현재 날짜는 22.8.4이다 60일동안의 데이터만 저장되고 있는 것을 확인할 수 있다.
가장 오래된 파티션을 먼저 표시하도록 ORDER BY 절 업데이트
#standardSQL
# avg monthly precipitation
SELECT
AVG(prcp) AS average,
station_name,
date,
CURRENT_DATE() AS today,
DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age,
EXTRACT(MONTH FROM date) AS month
FROM ecommerce.days_with_rain
WHERE station_name = 'WAKAYAMA' #Japan
GROUP BY station_name, date, today, month, partition_age
ORDER BY partition_age DESC
