Google Cloud 데이터 분석 플랫폼 심층 분석 Deep Dive into Google Cloud Data Analytics Platform

1. 필요한 컬럼만 선택하기 (SELECT * EXCEPT) 1. Select Only Necessary Columns (SELECT * EXCEPT)

`SELECT *`는 비용과 성능 면에서 비효율적입니다. 필요한 컬럼만 명시적으로 선택하고, 제외할 컬럼이 더 적을 경우 `EXCEPT`를 사용하세요. `SELECT *` is inefficient in terms of cost and performance. Explicitly select only the columns you need, or use `EXCEPT` if there are fewer columns to exclude.

-- Anti-pattern
SELECT * FROM `dataset.table`;

-- Optimized
SELECT col1, col2, col3 FROM `dataset.table`;

-- When there are fewer columns to exclude
SELECT * EXCEPT (dim1, dim2) FROM `dataset.table`;

2. WHERE 절 조건 순서 최적화 2. Optimize WHERE Clause Condition Order

BigQuery는 WHERE 절의 조건 순서를 재정렬하지 않으므로, 가장 선택적인(가장 많은 데이터를 필터링하는) 조건을 앞에 두는 것이 성능에 유리합니다. BigQuery does not reorder conditions in the WHERE clause, so placing the most selective (filters the most data) condition first is beneficial for performance.

-- Anti-pattern: Expensive LIKE operation first
SELECT text FROM `stackoverflow.comments` WHERE text LIKE '%java%' AND user_display_name = 'anon';

-- Optimized: Apply the more selective filter first
SELECT text FROM `stackoverflow.comments` WHERE user_display_name = 'anon' AND text LIKE '%java%';

3. JOIN 전에 데이터 줄이기 3. Reduce Data Before JOIN

JOIN을 수행하기 전에 집계(aggregation)나 필터링을 통해 처리할 데이터 양을 미리 줄이면, 셔플되는 데이터가 감소하여 성능이 크게 향상됩니다. Reducing the amount of data to be processed through aggregation or filtering before performing a JOIN significantly improves performance by decreasing the data shuffled.

-- Anti-pattern: JOINing large tables first
SELECT u.id, COUNT(c.text)
FROM stackoverflow.users AS u
JOIN stackoverflow.comments AS c ON u.id = c.user_id
GROUP BY u.id;

-- Optimized: Aggregate the comments table first
SELECT u.id, c.comments_count
FROM stackoverflow.users AS u
JOIN (
  SELECT user_id, COUNT(text) AS comments_count
  FROM stackoverflow.comments
  GROUP BY user_id
) AS c ON u.id = c.user_id;

4. 근사 집계 함수 활용 4. Utilize Approximate Aggregation Functions

정확한 값이 필요하지 않은 경우, `COUNT(DISTINCT ...)` 대신 `APPROX_COUNT_DISTINCT(...)`와 같은 근사 함수를 사용하면 훨씬 빠른 성능을 얻을 수 있습니다. 결과는 일반적으로 실제 값의 1% 내외 오차를 가집니다. If an exact value is not required, using approximate functions like `APPROX_COUNT_DISTINCT(...)` instead of `COUNT(DISTINCT ...)` can yield much faster performance. The result is typically within a 1% margin of error of the actual value.

-- Standard function
SELECT dim1, COUNT(DISTINCT dim2) FROM `dataset.table` GROUP BY 1;

-- Optimized (using approximate function)
SELECT dim1, APPROX_COUNT_DISTINCT(dim2) FROM `dataset.table` GROUP BY 1;

History-Based Optimization

과거에 실행된 유사한 쿼리의 실행 계획과 성능 데이터를 학습하여, 후속 쿼리를 자동으로 최적화하는 자가 학습 기능입니다. 예를 들어, BigQuery는 과거 쿼리 실행에서 특정 JOIN 조건의 선택도(selectivity)가 낮다는 것을 학습하면, 다음 실행 시에는 해당 JOIN을 쿼리 계획의 뒷부분으로 미루어 처리할 데이터의 양을 미리 줄입니다. 이처럼 복잡한 다중 조인 쿼리에서도 애플리케이션이나 스키마 변경 없이 최대 100배의 쿼리 지연 시간 개선을 제공할 수 있습니다. 이 기능은 성능 향상이 확실할 때만 적용되며, 효과가 미미할 경우 자동으로 비활성화되는 안전장치를 포함한 폐쇄 루프 시스템(Closed-loop system)으로 작동합니다.This is a self-learning feature that learns from the execution plans and performance data of previously run similar queries to automatically optimize subsequent queries. For example, if BigQuery learns from past query executions that a particular JOIN condition has low selectivity, it will postpone that JOIN to a later part of the query plan in the next execution to reduce the amount of data processed beforehand. This can provide up to a 100x improvement in query latency for complex multi-join queries without any application or schema changes. This feature operates as a closed-loop system with a safety mechanism that applies it only when performance improvement is certain and automatically disables it if the effect is negligible.

Short Query Optimized Mode

1GB 미만을 스캔하는 작은(short) 쿼리들을 위해 설계된 최적화 모드입니다. 내부적인 작업 생성 오버헤드를 제거하고 SELECT 문의 결과를 인라인으로 제공하는 방식으로, 작은 쿼리의 실행 속도를 최대 50%까지 향상시킵니다. 이 기능은 BI 대시보드나 실시간 애플리케이션의 응답성을 높이는 데 특히 유용하며, 모든 BigQuery 클라이언트에서 자동으로 적용됩니다. `INFORMATION_SCHEMA.JOBS` 뷰를 통해 어떤 쿼리가 이 기능으로 가속화되었는지 확인할 수 있습니다.This is an optimization mode designed for small (short) queries that scan less than 1GB. By eliminating internal job creation overhead and providing the results of SELECT statements inline, it improves the execution speed of small queries by up to 50%. This feature is particularly useful for enhancing the responsiveness of BI dashboards and real-time applications and is automatically applied across all BigQuery clients. You can check which queries have been accelerated by this feature through the `INFORMATION_SCHEMA.JOBS` view.

CMETA (Columnar Metadata)

BigQuery는 내부 스토리지 파일(Capacitor)별로 각 컬럼의 메타데이터(최소/최대값, 고유값 수 등)를 CMETA라는 인덱스에 저장합니다. 쿼리 실행 시, BigQuery 옵티마이저는 WHERE 절의 필터 조건을 이 CMETA 인덱스와 비교하여, 조건에 맞지 않는 데이터 블록 전체를 읽지 않고 건너뜁니다. 이를 통해 불필요한 I/O를 원천적으로 차단하여 100GB에서 10TB에 이르는 대용량 테이블에서도 5배에서 10배까지 쿼리 실행 시간을 단축시킬 수 있습니다.BigQuery stores metadata for each column (min/max values, number of unique values, etc.) for each internal storage file (Capacitor) in an index called CMETA. During query execution, the BigQuery optimizer compares the filter conditions in the WHERE clause with this CMETA index and skips reading entire data blocks that do not meet the conditions. This fundamentally prevents unnecessary I/O, reducing query execution time by 5 to 10 times even for large tables ranging from 100GB to 10TB.

Fine-grained DML

`UPDATE`, `DELETE`, `MERGE`와 같은 DML 문의 실행을 최적화하는 기능입니다. 기존에는 DML 작업 시 변경이 필요한 데이터가 포함된 전체 파일 그룹을 다시 작성해야 했습니다. 하지만 이 기능을 활성화하면, BigQuery는 파일 그룹보다 더 세분화된 수준에서 변경을 수행하여 다시 써야 하는 데이터의 양을 최소화합니다. 이는 특히 소수의 행만 변경되는 트랜잭션성 데이터 관리에서 총 사용 슬롯과 쿼리 지연 시간을 크게 개선하는 효과를 가져옵니다.This feature optimizes the execution of DML statements like `UPDATE`, `DELETE`, and `MERGE`. Previously, DML operations required rewriting the entire file group containing the data that needed to be changed. However, with this feature enabled, BigQuery performs changes at a more granular level than the file group, minimizing the amount of data that needs to be rewritten. This is particularly effective in improving total slot usage and query latency for transactional data management where only a few rows are changed.

-- How to enable the feature
ALTER TABLE mydataset.mytable SET OPTIONS(enable_fine_grained_mutations = TRUE);

BigQuery Advanced Runtime (Preview)

쿼리 프로세서에 향상된 벡터화(Enhanced Vectorization) 기술을 적용하여 성능을 극대화합니다. 벡터화는 SIMD(Single Instruction, Multiple Data) 연산을 통해 여러 데이터 포인트를 한 번의 명령으로 동시에 처리하는 기술입니다. 이 런타임을 활성화하면 필터 평가, 데이터 인코딩/디코딩과 같은 쿼리 처리의 주요 측면이 최적화되어, SQL 스크립트 변경 없이도 고성능을 제공받을 수 있습니다.This applies Enhanced Vectorization technology to the query processor to maximize performance. Vectorization is a technique that processes multiple data points simultaneously with a single instruction through SIMD (Single Instruction, Multiple Data) operations. Activating this runtime optimizes key aspects of query processing such as filter evaluation and data encoding/decoding, providing high performance without any changes to SQL scripts.

실습 예제: 런타임에 예약 할당 변경하기 (Preview) Example: Changing Reservation Assignment at Runtime (Preview)

적절한 권한이 있는 사용자는 쿼리 실행 시 기본 예약을 재정의하여 다른 예약에서 쿼리를 실행할 수 있습니다. 이는 특정 고부하 쿼리를 별도의 대용량 예약에서 실행할 때 유용합니다. Users with appropriate permissions can override the default reservation at query time to run a query in a different reservation. This is useful for running specific high-load queries in a separate, larger reservation.

# Example using gcloud CLI
gcloud alpha bq query --use_legacy_sql=false \
--project_id=my-query-project \
--reservation_id=my-runtime-reservation \
'SELECT * FROM `my-data-project.mydataset.mytable` LIMIT 10'

실습 예제 1: 커스텀 데이터 마스킹 함수 생성 Example 1: Creating a Custom Data Masking Function

SQL UDF(사용자 정의 함수)를 사용하여 민감한 데이터를 특정 사용자 그룹에게 마스킹 처리하여 보여줄 수 있습니다. You can use a SQL UDF (User-Defined Function) to show masked sensitive data to specific user groups.

-- Create a function to mask social security numbers with 'X'
CREATE OR REPLACE FUNCTION mydataset.mask_ssn(ssn STRING)
RETURNS STRING
OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE.REGEXP_REPLACE(ssn, r'\d', 'X')
);

실습 예제 2: 하위 쿼리를 이용한 동적 행 수준 보안 Example 2: Dynamic Row-Level Security Using a Subquery

행 접근 정책에 하위 쿼리를 사용하여, 쿼리를 실행하는 사용자의 속성(예: 소속 지역)에 따라 접근 가능한 데이터를 동적으로 필터링할 수 있습니다. You can use a subquery in a row-access policy to dynamically filter accessible data based on the attributes of the user running the query (e.g., their region).

-- Filter data to show only the user's own region by referencing the sales_rep table
CREATE OR REPLACE ROW ACCESS POLICY sales_region_filter
ON mydataset.partner_spend
GRANT TO ('group:sales@example.com')
FILTER USING (
  region IN (
    SELECT sales_region
    FROM mydataset.sales_rep_mapping
    WHERE sales_rep_email = SESSION_USER()
  )
);

실습 예제 1: `ML.GENERATE_TEXT`로 텍스트 생성하기 Example 1: Generating Text with `ML.GENERATE_TEXT`

Vertex AI의 Gemini 모델에 프롬프트를 보내고, 그 결과를 SQL 쿼리 내에서 직접 받을 수 있습니다. You can send a prompt to Vertex AI's Gemini model and receive the result directly within a SQL query.

SELECT
  product_name,
  ml_generate_text_result AS marketing_copy
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.gemini_pro_model`,
    (SELECT 'Generate an exciting marketing slogan under 30 characters for this product: ' || product_name AS prompt, * FROM mydataset.products),
    STRUCT(0.8 AS temperature, 50 AS max_output_tokens)
  );

실습 예제 2: `AI.GENERATE_TABLE`로 비정형 텍스트에서 정보 추출하기 Example 2: Extracting Information from Unstructured Text with `AI.GENERATE_TABLE`

자유 형식의 텍스트 프롬프트에서 원하는 스키마를 가진 구조화된 테이블을 생성할 수 있습니다. 이는 로그 분석, 고객 리뷰 처리 등에서 매우 유용합니다. You can generate a structured table with a desired schema from a free-form text prompt. This is very useful for tasks like log analysis and customer review processing.

SELECT *
FROM
  AI.GENERATE_TABLE(MODEL `mydataset.gemini_model`,
    (
      SELECT
        'John Smith is a 20-year-old unmarried male residing at 1234 NW 45th St, Kirkland WA, 98033. He has two phone numbers, 123-123-1234 and 234-234-2345, and weighs 200.5 pounds.' AS prompt
    ),
    STRUCT('address STRING, age INT64, is_married BOOL, name STRING, phone_number ARRAY, weight_in_pounds FLOAT64' AS output_schema)
  );