[Biguqery] 뉴비를 위한 빅쿼리 User Defined Function(사용자 정의 함수) 설명 및 쿼리 사용법

DBMS 들은 대부분 UDF 기능을 가지고 있는데, 알아두면 프로시저와 같이 유용하게 쓰일 수 있다! 

꼭 Bigquery가 아니어도 되지만, 이번 포스팅에선 빅쿼리를 기준으로 설명하도록 한다.

 

0. Function 함수란 ? 

일단, 함수라는 기본 개념은 y=f(x) 인데, x에다 원하는 값을 넣으면 출력되는 y값이 있도록 만든 것이다. 

프로그래밍에서도, DB에서도 똑같은 개념으로 쓰인다. 

값을 넣으면 내가 정의한 처리를 거쳐 결과가 나오게끔 설정해주는 것. 그게 함수이다. 

즉 사용자 정의 함수를 정의한다는 것은 내가 스스로 저 네모박스를 만들어주는 것! 

 

*왜 쓰나요?

1.기니까...

. 내가 막 ~~~~~~~ a라는 값을 입력하면 +2를 하고 -3을 하고 x10을하고 루트를 씌우고 이런 과정을 해야하는데 매번 써줄 수 없다. 그런데 저 과정을 cal 이라는 이름의 함수로 저장해둔다면 이렇게 처리할 수 있다. 

SELECT ABS(SQRT(a+2-3*10/2))+1232x2131232325967
#로 써야하는걸 
SELECT cal(a)

 

2.반복작업에 유리 

SELECT ABS(SQRT(a+2-3*10/2))+1232x2131232325967

 

만약 내가 이 쿼리로 30개의 테이블을 만들었다고 하자. 만약에 이걸 

SELECT ABS(SQRT(a+2-3*10/2))+1232x999999999999

 

로 바꾸고 싶다면..? 30개의 테이블을 다 바꿔(REPLACE)주어야한다. 

 

하지만 만약 내가 저걸 함수 cal(a) 로 써놨다면 

함수를 정의한 쿼리만 바꿔주면 모든 테이블의 cal(a)값이 아래 수식으로 바뀌는 것이다. 

 

그렇다면, 이제 직접 함수를 정의해보고 사용해보자. 이런저런 옵션값을 사용할 수는 있지만 생성과 사용 자체는 별로 어렵지 않다. 

1. 함수, 어떻게 만들까? 

빅쿼리에서 함수는 이렇게 정의하면 된다. 

CREATE FUNCTION `your_project.your_dataset.your_function_name`(param1 INT64, param2 INT64)
RETURNS INT64 AS (
  param1 + param2
);

 

한줄한줄 뜯어보면 다음과 같다. 

CREATE FUNCTION `your_project.your_dataset.your_function_name`(param1 INT64, param2 INT64)
-- 함수 생성 `프로젝트ID.데이터셋.정의할함수명`(변수1 변수타입,변수2 변수타입)

RETURNS INT64 AS (
  param1 + param2
);

-- 위에서 입력한 변수들로 param1 + param2 연산을해서 INT64형식으로 뱉어주세용~

 

한번에 이해될거라 생각하는데, 정의할 함수명과 INPUT 변수들을 정의해주고, 결과값으로 나와야하는 값이 어떤 연산을 거쳐야 하는지 괄호 안에 적어주면 된다. 

 

param1+param2 에는 자바스크립트 코드도 사용 가능하고, SQL 문법도 사용 가능하다. 

 

2. 사용법

만들었으니 한번 사용해보자 ! 

SELECT `your_project.your_dataset.your_function_name`(param1, param2);

 

위에서 만든 Function을 입력하고 변수값에 넣을 것 (위에서 내가 변수 타입을 int로 정의했으니 테이블에서 int 형식의 컬럼이 될수도 있고 그냥 숫자가 될 수도 있을 것이다.)을 입력해주면 된다. 

 

그러면 param1+param2 값이 출력되는 것을 확인할 수 있다. 

 

 

 

엥?? 이렇게 쉽다고 할 정도로 별거 없다. Python Def와 똑같은... 하지만 이 간단한 기능은 때에따라 정말 파워풀하게 쓰이기 때문에 숙지해두면 반드시 유용하게 쓰일 때가 있을 것이다. (많이 쓴다 실제로) 

 

3. 현업에서의 사용 예시 

실제 현업에서는 어떻게 사용했을까. 

 

1.복잡한 문자열에서 특정 문자열만 뽑아내기 (긴거 치기 귀찮아) 

전 회사에서 컨플루언스 회의록 제목을 보고 어떤 JIRA와 연결되어있는 회의인지 찾는 쿼리를 작성하는 것이 필요했다. 타이틀은 늘 같은 규칙으로 작성되기 때문에 정규식으로 처리할 수 있었는데,  그때그떄 마다 복잡한 정규식을 치기가 너무 귀찮은 나머지 EXTRACT_TITLE이라는 함수를 만들어

 SELECT EXTRACT_TITLE(meeting_title) AS work

 

로 처리했었던 적이 있다. 

 

2.메인 국가 처리하기 (반복 작업 귀찮아)

 

우리 회사는 현재 여러가지 모바일 게임을 서비스하는 게임 회사이다. (이직했어옹) 

그러다보니 담당 게임마다 메인으로 보는 국가들이 다르고 이 국가 조건이 한 20~30개의 테이블에 들어가있다. 

근데 그럼... 메인으로 보는 국가가 하나 추가되면 이 30개의 테이블 프로시저를 다 고쳐야하잖아요..??? 

이것은 칼퇴를 지향하는 올바른 직장인의 마인드셋으로 절대 할 수 없는 일이다. 

 

그래서 이를 방지하고자 함수로 처리해주었다. 

 SELECT main_country(country) AS country

CASE WHEN 'KR' THEN 'KR' 
     WHEN 'JP' THEN 'JP' 
  ELSE 'ETC' END

 

이렇게 함수를 만들어주고 나중에 어~ 중국도 메인국가로 보고싶어요 하면 WHEN 'CN' THEN 'CN' 으로 한줄 추가해주면 끝나는 것이다. 

 

 

---

 

연차가 쌓여가면서 기본이라고 말하는 기능들을 잘 사용하는 것이 엄청 파워풀하다는 것을 점점 깨닫고있다. 

기본은 정말 쉽지만 어떻게 사용하고 활용할지에 대해 고민하며 더 좋은 효율을 내는 데이터분석가가 되도록 하자!