# Google Colab에서는 Credential을 사용하지 않아도 된다. # 서비스 계정 없이 내 계정으로 사용자 인증 할 수 있다. fromgoogle.colabimportauthauth.authenticate_user()
In [22]:
sql=""" select store_number, any_value(store_location) store_location, item_number, any_value(item_description) item_description, date_trunc(date, MONTH) period, round(sum(sale_dollars), 2) sale_dollars, round(sum(volume_sold_liters), 2) volume_soold_liters from `bigquery-public-data.iowa_liquor_sales.sales` where date between DATE(2019, 1, 1) and DATE(2019, 12, 31) group by store_number, item_number, period"""df=client.query(sql).to_dataframe()df.head()
# 판매량 및 리터별 상위 5개 dfm=df[df['item_description'].isin(dagg['item_description'])].groupby(['item_description','period']).sum()di=dfm.reset_index()di.pivot(index='period',columns='item_description',values='volume_soold_liters').plot.bar(figsize=(12,5))
Out[46]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fd43d9993a0>
AWS S3, Google Storage 등의 외부 스토리지 서비스와 Google Play, Google Ads, Youtube 등의 구글 서비스 데이터와 연결 할 수 있다.
일정 주기 단위로 실행 할 수 있다.
3. 쿼리 예약
일정 주기별로 쿼리를 실행하고 결과를 지정한 테이블에 저장한다.
cron 작업, Airflow 등의 별도 도구 없이 즉시 실행이 가능해 편리하다.
편리한 Backfill 기능이 제공된다.
매개변수를 사용 할 수 있다.
단, 결제 계정이 등록되어 있어야 사용 할 수 있다.
3.1 예약된 쿼리 사용 방법 - 예약 생성
쿼리를 작성하고
툴바 > 일정 > 새로 예약된 쿼리 만들기를 클릭한다.
일정의 이름, 반복 빈도, 실행 일정, 저장 테이블을 설정한다.
저장한다.
3.2 예약된 쿼리 사용 방법 - 백필 실행
이전 날짜 기준에 맞춰 쿼리를 실행한다.
쿼리에 넣어둔 매개 변수 @run_date, @run_time이 해당 기준 일자에 맞춰 변한다
단순히 해당 일자의 데이터를 추가해 넣는 대신 덮어씌우기 위해서는 파티션 설정과 테이블 덮어씌우기 설정이 필요하다.
select
date(timestamp_micros(event_timestamp)) date,
item_id,
item_name,
count(distinct user_pseudo_id) user_count
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
unnest(items)
where
event_name = 'add_to_cart'
and _table_suffix = format_date('%Y%m%d', @run_date)
--예약된 쿼리를 위해서 사용하는 매개변수 / 실행하면 에러가 난다
group by 1, 2, 3
3.3 주의사항
실행 시간이 몇 분 딜레이 될 가능성 + 쿼리 실행 완료 시간이 일정치 않은 점 때문에 실행 순서를 정밀하게 정해둘 수 없다.
예약된 쿼리의 결과 테이블과 에어플로의 결과 등 다수 도구에서 다수 작업 결과물이 생길 경우 관리/운영이 어려워질 수 있다.
4. INFORMATION_SCHEMA
데이터 세트, 데이터 세트 내의 테이블 목록, 테이블의 메타 데이터 등 다양한 정보를 제공
보관 기간이 180일
예)
프로젝트의 모든 테이블 목록
테이블의 모든 컬럼 목록
최근 쿼리 조회 내역 (누가 얼마나 쿼리를 많이 날렸는지 모니터링 가능)
테이블 사용량 내역
-- 실행한 모든 JOB 의 기록
select *
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
-- temp dataset 안에 들어있는 모든 table
select
*
from temp.INFORMATION_SCHEMA.TABLES
5. Audit Logs
영구적으로 저장 가능
GCP 리소스에 대한 로그를 저장소에 라우팅하는 도구
지원하는 저장소
GCP Storage
GCP Pub/Sub
GCP BigQuery
실행 기록에서 methodName 선택 후 `일치하는 항목 표시` > 우측 작업 더보기의 `싱크 만들기`
변수 선언 후 공유 상태로 한 시퀀스에서 여러 문을 실행 (많은 쿼리를 절차에 따라서 실행한다!)
테이블 생성/삭제 등의 관리 태스크를 자동화
IF 및 WHILE과 같은 프로그래밍 구조를 사용하여 복잡한 로직 구현
BEGIN과 END를 사용해 블록을 나타낸다. 블록 내의 모든 문은 세미콜론으로 끝나야 한다.
1.1 DECLARE / SET
변수 선언과 할당
declare end_dt date;
declare period int64 default 5; -- 매개변수
set end_dt = date('2021-01-15');
select
date(timestamp_micros(event_timestamp)) date,
count(distinct user_pseudo_id) user_count
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
unnest(items)
where
event_name = 'add_to_cart'
AND _TABLE_SUFFIX BETWEEN
format_date('%Y%m%d', end_dt-period)
AND format_date('%Y%m%d', end_dt)
group by 1
1.2 EXECUTE IMMEDIATE
동적 SQL 실행을 위한 문
declare fill_period int64 default 3;
begin
declare max_date date;
execute immediate """
select max(date) from temp.add_to_cart
""" --문자열을 그대로 쿼리로 실행
into max_date; --쿼리 실행 결과를 변수에 할당
create temp table recent_data as
select
date(timestamp_micros(event_timestamp)) date,
count(distinct user_pseudo_id) user_count
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
unnest(items)
where
event_name = 'add_to_cart'
AND _TABLE_SUFFIX BETWEEN
format_date('%Y%m%d', max_date+1)
and format_date('%Y%m%d', max_date+fill_period+1) --max date 이후 3일
group by 1
;
select * from recent_data;
end;
declare fill_period int64 default 3;
begin
declare max_date date;
execute immediate """
select max(date) from temp.add_to_cart
"""
into max_date; --쿼리 실행 결과를 변수에 할당
create temp table recent_data as
select
date(timestamp_micros(event_timestamp)) date,
count(distinct user_pseudo_id) user_count
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
unnest(items)
where
event_name = 'add_to_cart'
AND _TABLE_SUFFIX BETWEEN
format_date('%Y%m%d', max_date+1)
and format_date('%Y%m%d', max_date+fill_period+1) --max date 이후 3일
group by 1
;
merge temp.add_to_cart as a -- 원본 테이블에 insert 또는 update
using recent_data as r
on a.date = r.date
when not matched then
insert(date, user_count)
values(date, user_count)
when matched then
update set user_count = r.user_count;
drop table recent_data;
select max(date) from temp.add_to_cart;
end;
1.3 IF / THEN / ELSEIF / ELSE / END IF
-- 필요한 변수를 선언한다.
DECLARE DIE1, DIE2, TOTAL INT64;
-- 결과를 넣을 문자열을 선언한다.
DECLARE RESULT STRING;
-- 두 개의 난수를 생성하고 변수를 설정한다. 둘의 합을 구한다.
SET (DIE1, DIE2) = (CAST(FLOOR(RAND()*6)+1 AS INT64), CAST(FLOOR(RAND()*6)+1 AS INT64));
SET TOTAL = DIE1 + DIE2;
-- if 블록으로 특별한 결과를 확인하고 결과를 설정한다.
-- if 블록에서는 BEGIN 또는 END문을 사용하지 않는다.
IF (DIE1 = 1 AND DIE2 = 1) THEN SET RESULT ='Snake eyes.';
ELSEIF (DIE1 = 6 AND DIE2 = 6) THEN SET RESULT ='Boxcars.';
ELSEIF (DIE1 = 4 AND DIE2 = 4) THEN SET RESULT ='Hard eight.';
ELSE SET RESULT ='Nothing special.';
END IF;
-- 결과를 사용자에게 반환한다.
SELECT FORMAT('You rolled %d and %d for a total of %d. %s',DIE1, DIE2, TOTAL, RESULT);
1.4 LOOP / END LOOP
LOOP는 BREAK (또는 LEAVE) 하지 않는 한 영원히 실행된다.
DECLARE I INT64 DEFAULT 0;
DECLARE R ARRAY<INT64> DEFAULT [];
LOOP
SET I = I + 1;
SET R = ARRAY_CONCAT(R, [I]);
IF I > 9 THEN LEAVE;
END IF;
END LOOP;
SELECT * FROM UNNEST(R);
1.5 WHILE / DO / END WHILE
DECLARE I INT64 DEFAULT 0;
DECLARE R ARRAY<INT64> DEFAULT [];
WHILE I < 10 DO
SET I = I+1;
SET R = ARRAY_CONCAT(R, [I]);
END WHILE;
SELECT * FROM UNNEST(R);
1.6 예외 처리
예외 처리는 BEGIN/END 블록으로 범위를 지정한다.
예외가 발생하면 EXCEPTION문을 바로 실행한다.
BEGIN
SELECT 1/0; -- 0으로 나누기 오류
EXCEPTION WHEN ERROR THEN
SELECT 'Whare are you even doing there.';
END
2. 저장 프로시져
다른 쿼리 또는 다른 저장 프로시져에서 호출 할 수 있는 문 (스크립트를 함수처럼 사용 할 수 있게 해준다!)
입력 인수를 사용하고 값을 출력으로 반환
CREATE OR REPLACE PROCEDURE temp.create_customer(name STRING) --매개변수 name 지정
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO temp.customers (customer_id, name)
VALUES(id, name);
SELECT FORMAT('Created customer %s (%s)', id, name);
END
▼
CALL temp.create_customer('foo')
3. UDF (사용자 정의 함수)
SQL 표현식 또는 자바스크립트 코드를 사용하는 사용자 정의 함수
영구 UDF 또는 임시 UDF로 정의 가능
-- array 안에 struct가 들어간 경우
SELECT
(select value.string_value from unnest(event_params) where key='page_title') page_title,
(select value.string_value from unnest(event_params) where key='page_location') page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
-- 매개변수로 지정하고 싶은 경우
create temp function get_value(_key string, _event_params any type) as (
(select value.string_value from unnest(_event_params) where key=_key)
);
SELECT
get_value('page_title', event_params) page_title,
get_value('page_location', event_params) page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
4. TVF (사용자 정의 테이블 함수)
테이블을 반환하는 사용자 정의 함수
뷰와 비슷하게 작동하되 매개변수 사용 가능한 장점 (뷰테이블인데 매개변수 사용 가능!)
CREATE OR REPLACE TABLE FUNCTION temp.names_by_year(y INT64)
AS
SELECT year, name, SUM(number) as total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = y
GROUP BY year, name
SELECT *
FROM
'dataset.orders_*'
WHERE
_TABLE_SUFFIX BETWEEN '1' and '4'
샤딩 테이블
테이블 이름이 yyyymmdd 형식의 suffix를 가지는 경우를 말한다.
yyyymmdd 형식의 일자가 파티션 구역이 된다.
즉, 일 단위 파티션 구역으로 사용한다.
하나의 테이블처럼 표시된다.
테이블 이름의 '문자열' 로 검색해야 한다.
create table temp.add_to_cart as (
select
date(timestamp_micros(event_timestamp)) date,
item_id,
item_name,
count(distinct user_pseudo_id) user_count
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
unnest(items)
where
event_name = 'add_to_cart'
AND _TABLE_SUFFIX BETWEEN '20210111' AND '20210115' --wildcard와 match되는 문자열을 넣어줘야 한다
group by 1, 2, 3
)
파티션 테이블
지정한 컬럼의 범위에 따라 데이터를 나눠 저장한 테이블이다.
파티션 구역 개수에 제한이 있다(4,000개)
관리자가 파티션 지정 조회를 강제 할 수 있다. (조회 용량을 줄이고, 퍼포먼스를 개선하기 위해)
컬럼 데이터 타입에 제한이 있다(정수, 날짜, 수집시간)
integer, date, datetime, timestamp 형식의 컬럼을 사용한다.
Cluster
테이블에 저장 될 때 지정한 컬럼들로 블록을 만들어 정렬해 저장한다.
각 블럭의 크기가 일정하지 않을 경우 사용한다.
예상 조회 비용이랑 Cluster 실제 과금 비용이 다를 수 있다. (실제 클러스터 블럭 사이즈를 감안하지 못하기 때문)
최소 블록 사이즈가 존재한다. 비용 산정 시 최소 크기 이상의 조회 비용으로 계산한다.
지정한 클러스터 컬럼의 순서가 where 절에 들어가야 한다. (조회 용량을 줄이고, 퍼포먼스를 개선하기 위해)
SELECT *
FROM
`bigquery-public-data.deps_dev_v1.Advisories`
WHERE
Source = 'GHSA'
and SourceId = 'GHSA-5684-g483-2249'
임의의 값을 반환한다. 이 경우 MIN 또는 MAX를 사용할 수도 있지만 ANY_VALUE는 정렬이 필요 없기 때문에 더 빠르다.
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(['apple', 'banana', 'pear']) as fruit;
ARRAY_AGG
ARRAY_AGG는 임의의 값을 가져와서 배열로 변환한다. 즉, UNNEST와 반대로 사용 할 수 있다.
SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
SELECT FORMAT('%T', ARRAY_AGG(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG는 ARRAY를 입력받아 모든 하위 요소를 연결해 단일 배열을 만든다.
SELECT [1, 2, 3] AS x
UNION ALL SELECT [4, 5]
UNION ALL SELECT [6]
SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg
FROM (
SELECT [1, 2, 3] AS x
UNION ALL SELECT [4, 5]
UNION ALL SELECT [6]
)
SELECT FORMAT('%T', ARRAY_CONCAT_AGG(x)) AS array_concat_agg
FROM (
SELECT [1, 2, 3] AS x
UNION ALL SELECT [4, 5]
UNION ALL SELECT [6]
)
STRING_AGG
STRING_AGG는 일련의 값을 단일 STRING (또는 BYTES) 값으로 연결한다. 개별 값에 대한 작업을 이후 읽을 수 있는 형식으로 반환할 때 자주 사용한다.
SELECT *
FROM UNNEST(['apple', NULL, 'pear', 'banana', 'pear']) AS fruit
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(['apple', NULL, 'pear', 'banana', 'pear']) AS fruit
COUNTIF
특정 불리언 표현과 일치하는 그룹의 행 수를 반환한다. 하위 쿼리나 추가 WHERE 절 없이 단순하게 집계 결과를 계산할 수 있다.
SELECT Department, COUNTIF(Cost > 25)
FROM Expenses
GROUP BY 1
SUM
SUM(IF(표현식, 참, 거짓))
SELECT Department, SUM(IF(Cost > 150, Cost, 0))
집계 분석 함수
with numbers as (
select *
from
unnest(generate_array(1, 100)) as num
)
select
num,
AVG(num) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) MovingAverage,
SUM(num) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal --Unbounded는 테이블의 끝을 의미
from
numbers
SELECT (1, 2) AS struct_col
SELECT STRUCT(1, 2) AS struct_col
SELECT STRUCT<INT64, INT64>(1, 2) AS struct_col
SELECT STRUCT(1 AS col_1, 2 AS col_2) AS struct_col
SELECT STRUCT<col_1 INT64, col_2 INT64>(1, 2) AS struct_col
행
struct_col.col_1
struct_col.col_2
1
1
2
# Struct 데이터 조회 방법
WITH data AS (
SELECT
STRUCT<col_1 INT64, col_2 INT64>(1, 2) AS struct_col
)
SELECT
struct_col.col_1,
struct_col.col_2
FROM
data
데이터 유형 - Array
배열 형식의 컬럼
하나의 컬럼, 하나의 행 안에 다수 행을 가지는 정보를 담을 수 있다.
# Array 만드는 방법
SELECT [1, 2] AS array_col
SELECT ARRAY[1, 2] AS array_col
SELECT ARRAY<INT64>[1, 2] AS array_col
행
array_col
1
1
2
# Array 데이터 조회 방법 (컬럼으로 선택)
WITH data AS (
SELECT ARRAY<INT64>[1, 2] AS array_col
)
SELECT
array_col[offset(0)] AS col_1,
array_col[ordinal(2)] AS col_2
FROM
data
행
col_1
col_2
1
1
2
- 배열 내 접근
- 배열의 N번째 값을 가져오고 싶은 경우 OFFSET, ORDINAL을 사용할 수 있다
- OFFSET: 0부터 시작
- ORDINAL: 1부터 시작
- 존재하지 않는 N을 지정하면 에러가 발생하는데, 이럴 경우 SAFE_를 앞에 붙여주면 (SAFE_OFFSET, SAFE_ORDINAL) 에러가 발생하지 않고 NULL이 return된다
# Array 데이터 조회 방법 (행으로 나눠 선택)
WITH data AS (
SELECT
'a' AS string_col,
ARRAY<INT64>[1, 2] AS array_col
)
SELECT
string_col,
array_item
FROM
data,
UNNEST(array_col) AS array_item
행
string_col
array_item
1
a
1
2
a
2
실습 - Array 안에 Struct 가 들어간 경우
빅쿼리에서 중첩 필드를 볼 때마다 다음과 같은 방법으로 직접 쿼리를 해봐도 동작하지 않는다.
SELECT
items.item_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
event_name = 'add_to_cart'
어떤 방식으로든 병합하거나 집계하지 않고는 중첩 필드에 액세스 할 수 없다.
SELECT
item_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
,unnest(items)
WHERE
event_name = 'add_to_cart'
--ARRAY_AGG는 임의의 값을 가져와서 배열로 변환한다. 즉, UNNEST와 반대로 사용할 수 있다--
SELECT
(select array_agg(item_id) from unnest(items)) item_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
event_name = 'add_to_cart'
SELECT
date(timestamp_micros(event_timestamp)) date,
item.item_id,
item.item_name,
count(distinct user_pseudo_id) user_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`,
unnest(items) as item
WHERE
event_name = 'add_to_cart'
GROUP BY 1, 2, 3
