Bigquery Procedure 소개

Bigquery Procedure 소개

Bigquery procedure 는 쿼리문을 함수의 형태로 만들어, parameter 입력을 통해 쿼리문의 재사용성을 높이는 개념이다. 코드 공통화가 있다면, 쿼리 공통화라고 생각해도 좋지 않을까.

Bigquery Procedure

• Bigquery 내에서 함수의 개념처럼 쿼리문 자체를 parameter를 받아 쿼리를 날리는 개념
• UDF function과는 다름
• UDF function :  쿼리 내에서 SUM, MAX 등과 같은 기능 함수를 유저가 정의해서 사용하는 개념
• Bigquery procedure : 
  • CALL dataset.GetEmployeeHierarchy(target_employee_id, employee_hierarchy);
  • 미리 정의한 쿼리문을 파라미터에 따라 실행하는 방식

Bigquery Procedure 적용 방법

• create procedure

•  procedure_argument : procedure에 들어갈 parameter 를 입력
  • IN 인수에 대한 변수 또는 값 표현식을 지정
  • OUT 인수는 프로시져가 시작될 때 NULL로 초기화 / OUT 인수에 대한 변수를 지정해야 함
  • INOUT 인수에 대한 변수를 지정 / INOUT 인수는 프로시져 본문에서 변수로 참조되고 새 값이 할당될 수 있음
  • 파라미터는 in, out, inout 으로 구분하여 입력
  • IN 은 인수가 프로시져에 대한 입력에만 해당
  • OUT은 인수가 프로시져의 출력값
  • INOUT은 인수가 프로시져의 입력과 출력에 모두 해당
  • IN , OUT 및 INOUT도 지정되지 않은 경우 해당 인수는 IN 인수로 간주
• statement_list : 쿼리문
• procedure_option_list : strict_mode=True → 자체 구문 검사, 오류 검사
• procedure 생성

• procedure 호출
• CALL [procedure()] 로 호출

Bigquery Procedure 예시

1. 변수의 단순 연산

• 결과 : 8

2. Employee 상속

• 아래 데이터와 같이 employee 에게 할당된 manager의 정보가 있다고 하자

• 문제 : 아래 procedure는 무슨 역할을 하는 걸까?

• target_employee_id 의 manager_id를 담고, 해당 manager_id의 manager_id를 계속 추적하면서 리스트에 담는 구조
• call 하여 실행

• 결과

Bigquery Procedure 장점 & 단점

장점

• 쿼리의 재사용성을 높일 수 있음
• 즉, 코드를 공통화하듯 쿼리를 공통화하여 사용할 수 있음
• 프로시져는 함수의 개념이므로, 프로시져를 재귀적으로 호출이 가능함

단점

• procedure 내 reference table을 파라미터로 받을 수 없다는 제약으로 공통화가 어려울 수 있음
• 코드별로 데이터 테이블이 나눠있어 불가능 하지만, 통합테이블로 한 테이블에서 코드를 받아와 사용하는 경우 활용성 높음
• 단순 쿼리 실행에서는 Procedure가 비효율적일수 있음
• 단순 쿼리문을 procedure 화하여 여러개 만들경우, 선형적으로 비용(시간, 저장공간)이 증가할 수 있음

References

• https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language?hl=ko#create_function_statement
• https://cloud.google.com/blog/products/data-analytics/command-and-control-now-easier-in-bigquery-with-scripting-and-stored-procedures