BigQuery - TVF (Table-Valued Function) 테이블 반환 함수

BigQuery에서 테이블 반환 함수(Table-Valued Function, TVF)는 SQL을 이용하여 매개변수를 입력받아 동적으로 테이블 형식의 결과를 반환하는 기능이다. 이는 재사용 가능한 쿼리를 정의하거나 복잡한 데이터를 매개변수화하여 간편하게 처리할 수 있도록 해준다.

언뜻 보면 뷰와 같은 개념인 것 같지만, 뷰와 달리 매번 다른 매개변수를 입력해 동적으로 데이터를 처리할 수 있다는 장점이 있다.

 

 

 

 

 

1. TVF의 기본 개념

 

- TVF는 입력 매개변수를 받아 SQL 쿼리를 실행하여 테이블 형식의 데이터를 반환한다.

- 복잡한 쿼리를 여러 번 반복해서 작성하는 대신, TVF로 재사용 가능하게 만들어 간편하게 데이터를 조회할 수 있다.

- 매개변수화된 쿼리를 통해 특정 조건에 맞춘 데이터를 효율적으로 반환할 수 있다.

 

 

 

2. TVF 정의 방법

 

BigQuery에서 테이블 반환 함수를 정의하는 구문은 다음과 같다.

CREATE FUNCTION dataset_name.function_name(parameter_name DATA_TYPE)
RETURNS TABLE<column_name DATA_TYPE, ...>
AS (
  SELECT *
  FROM `project_id.dataset_name.table_name`
  WHERE column_name = parameter_name
);

- CREATE FUNCTION: 함수를 생성할 때 사용

- RETURNS TABLE<...>: 반환할 테이블의 컬럼과 데이터 타입을 정의

- AS (...): 함수의 본체로, 매개변수를 이용해 데이터를 필터링하거나 처리하는 SQL 쿼리를 작성

 

 

 

3. TVF 사용예시

 

2번 정의 방법으로 예시를 만들어보자

 

단일 매개변수 : 특정 고객 ID로 주문 정보를 검색하는 예시

CREATE FUNCTION my_dataset.get_customer_data(customer_id STRING)
RETURNS TABLE<customer_id STRING, order_id STRING, amount FLOAT64>
AS (
  SELECT customer_id, order_id, amount
  FROM `my_project.my_dataset.orders`
  WHERE customer_id = customer_id
);

- 이 함수는 customer_id를 입력받아, 해당 고객의 주문 정보를 반환한다.

 

 

사용

SELECT *
FROM my_dataset.get_customer_data('C12345');

- 이 쿼리는 customer_id가 'C12345'인 모든 주문 정보를 반환한다.

 

 

복수 매개변수 : 특정 날짜 범위의 데이터를 검색

CREATE FUNCTION my_dataset.get_sales_data(start_date DATE, end_date DATE)
RETURNS TABLE<sale_id STRING, sale_date DATE, amount FLOAT64>
AS (
  SELECT sale_id, sale_date, amount
  FROM `my_project.my_dataset.sales`
  WHERE sale_date BETWEEN start_date AND end_date
);

 

 

 

4. TVF의 장점

 

- 재사용성: 복잡한 쿼리를 간단히 함수로 정의하여 여러 곳에서 재사용할 수 있다.

- 매개변수화된 질의: 매개변수를 통해 다양한 조건으로 데이터를 조회할 수 있어, 코드 중복을 줄이고 유지보수가 용이하다.

- 동적 쿼리 실행: 동일한 쿼리를 여러 상황에 맞게 매번 다른 입력으로 실행할 수 있어 유연한 데이터 처리가 가능해진다.

 

 

 

 

5. 주의사항

 

- TVF는 데이터의 스키마가 미리 정의되어 있어야 합니다. 반환할 테이블의 컬럼과 데이터 타입을 정확히 명시해야 한다.

- TVF를 사용할 때 쿼리 성능을 고려해야 합니다. 특히 입력 매개변수에 따라 필터링하는 작업이므로 인덱싱이나 파티셔닝을 잘 활용하는 것이 중요하다.