[인프런 빅쿼리 빠짝스터디 4주차] 알아두면 언젠가 쓸모 있는 유용한 기능들

이상해씨·2024년 11월 14일

BigQuery sql 빅쿼리 빠짝스터디 인프런

SQL

목록 보기

20/22

유용한 기능들

스케쥴 쿼리
절차적 언어
VIEW
UDF
JSON 함수
Gemini를 사용한 빅쿼리

1. 스케쥴 쿼리

지속적, 또는 일정 시간에 쿼리를 사용해야 하는 경우.
연산이 오래 걸려 (3분 이상) 미리 BigQuery Table로 저장하는 경우에 사용
BigQuery Data Transfer Service API를 사용

2. 절차적 언어

쿼리문에서 절차형 프로그래밍을 적용할 때 활용
변수선언, 조건문, 반복문.

1. 변수선언

declare : 변수타입, 값 정의
set : 변수에 값을 할당

declear a INT;
SET a=13;

2. 조건문(if, while)

while문
예시

label_1: LOOP
  WHILE x < 1 DO
    IF y < 1 THEN
      CONTINUE label_1;
    ELSE
      BREAK label_1;
  END WHILE;
END LOOP label_1

3. 동적 쿼리 실행
EXCUTE IMMEDIATE

쿼리결과를 인자로 넣어 다시 실행하고 싶을 때.
특정값을 Table에서 SELECT할 수 있음.
사용자 입력 혹은 외부 변수를 사용하여 쿼리를 작성할 때 사용.
가변적인 것을 이용하고 싶을 때!

4. 오류
raise 기능!

3. VIEW

Table에 데이터를 저장하지 않고 쿼리를 저장
복잡한 쿼리를 단순화하거나 쿼리 변경이 잦을 경우, 쿼리를 재사용하고 싶은 경우

1. 생성법

1) 쿼리 실행 후, 뷰 저장

2) CREATE문 사용

기본문법

CREATE OR REPLACE VIEW AS
SELECT
  col
FROM table

예시

CREATE OR REPLACE VIEW `my_project.my_dataset.my_view` AS
SELECT name, age, department
FROM `my_project.my_dataset.employees`
WHERE age > 30;


-- `my_project.my_dataset.my_view` 경로에 저장됨.

2. WITH과 VIEW의 차이

VIEW는 재사용 가능, 다양한 쿼리에서 반복적으로 사용되는 복잡한 쿼리를 저장. DB에 저장됨. 속도가 상대적으로 느림.
WITH은 하나의 쿼리 내에서 사용.

4. UDF(User Defined Function)

사용자 정의 함수
반복 작업, 공용함수, SQL로 실행이 어려운 함수의 경우 사용
SQL, JS로 UDF를 생성할 수 있음. JS는 속도 이슈가 있음.

1. 임시 UDF : 쿼리문에서만 UDF
2. 영구 UDF : 데이터셋에 저장되는 UDF

UDF 참고 사이트

1) bqutil
https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/README.md

bqutil.fn.함수이름으로 실행

SELECT
	bqutil.fn.t_test([1.2,3.0,4.4], [3.5,4.7,8.4]) AS test_outcome

2) BigFunctions

upload_table_to_gsheet, upload_to_gsheet, 각종 Transform geo data 함수, array 함수 등 각종 함수가 존재...

5. JSON 함수

JSON : value, key로 구성된 데이터
json을 사용하면 특정 데이터를 추출하는 데 용이하다.
JSON_QUERY (JSON형태로 데이터 반환), JSON_QUERY_ARRAY, JSON_VALUE (스칼라로 반환), JSON_VALUE_ARRAY 등의 함수가 존재
JSON으로 반환하면 데이터에 항상 ''가 존재, 스칼라는 ''없음
$: root, 배열 사용
JSON path Finder(https://jsonpathfinder.com/) 를 사용하여 json 형태로 쉽게 표현할 수 있다.