빅쿼리 익스터널 테이블
외부 데이터 소스 연결
목차
개요
빅쿼리의 익스터널 테이블은 빅쿼리 스토리지에 저장되지 않고 외부 데이터 소스에서 직접 쿼리할 수 있는 테이블입니다. 데이터를 빅쿼리로 로드하지 않고도 SQL을 사용해 외부 데이터를 분석할 수 있습니다.
지원되는 외부 데이터 소스
1. Google Cloud Storage (GCS)
- CSV, JSON, Avro, Parquet, ORC 파일 지원
- 가장 일반적으로 사용되는 소스
2. Google Drive
- CSV, JSON, Avro, Parquet, ORC, Google Sheets
- 소규모 데이터셋에 적합
3. Google Cloud Bigtable
- NoSQL 데이터베이스
- 실시간 분석에 적합
4. Google Cloud SQL
- MySQL, PostgreSQL 인스턴스
- 페더레이션 쿼리로 접근
5. Google Cloud Spanner
- 글로벌 분산 관계형 데이터베이스
장점
1. 비용 효율성
- 데이터 저장 비용 절약
- 스토리지 요금 부담 없음
2. 실시간 데이터 접근
- 데이터 로드 과정 불필요
- 최신 데이터에 즉시 접근
3. 유연성
- 기존 데이터 파이프라인 유지
- 여러 소스 데이터 통합 쿼리
4. 간편한 설정
- 테이블 정의만으로 쉽게 생성
- 스키마 자동 감지 지원
단점
1. 성능 제한
- 네트워크 레이턴시
- 외부 소스의 처리 속도에 의존
2. 기능 제약
- 클러스터링, 파티셔닝 미지원
- DML 작업 제한적
3. 쿼리 비용
- 스캔하는 데이터양에 따른 과금
- 압축 효과 제한적
생성 방법
1. 콘솔에서 생성
-- CSV 파일 기반 익스터널 테이블
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.external_table`
OPTIONS (
format = 'CSV',
uris = ['gs://bucket-name/file.csv'],
skip_leading_rows = 1
);
2. Parquet 파일 기반
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.sales_external`
OPTIONS (
format = 'PARQUET',
uris = ['gs://my-bucket/sales/*.parquet']
);
3. 복합 소스 테이블
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.multi_source_table`
OPTIONS (
format = 'CSV',
uris = [
'gs://bucket1/data/*.csv',
'gs://bucket2/archive/*.csv'
],
skip_leading_rows = 1,
allow_jagged_rows = false,
allow_quoted_newlines = true
);
스키마 정의
1. 자동 스키마 감지
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.auto_schema_table`
OPTIONS (
format = 'PARQUET',
uris = ['gs://bucket/data.parquet']
-- 스키마 자동 감지됨
);
2. 명시적 스키마 정의
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.explicit_schema_table`
(
id INT64,
name STRING,
created_date DATE,
amount NUMERIC
)
OPTIONS (
format = 'CSV',
uris = ['gs://bucket/data.csv'],
skip_leading_rows = 1
);
실제 사용 예제
1. Cloud Storage CSV 데이터 분석
-- 익스터널 테이블 생성
CREATE OR REPLACE EXTERNAL TABLE `my-project.analytics.sales_external`
(
order_id STRING,
customer_id STRING,
product_name STRING,
quantity INT64,
price NUMERIC,
order_date DATE
)
OPTIONS (
format = 'CSV',
uris = ['gs://sales-data-bucket/orders/*.csv'],
skip_leading_rows = 1
);
-- 데이터 분석 쿼리
SELECT
product_name,
COUNT(*) as order_count,
SUM(quantity * price) as total_revenue
FROM `my-project.analytics.sales_external`
WHERE order_date >= '2024-01-01'
GROUP BY product_name
ORDER BY total_revenue DESC;
2. JSON 로그 데이터 분석
-- JSON 익스터널 테이블
CREATE OR REPLACE EXTERNAL TABLE `project.logs.app_logs_external`
OPTIONS (
format = 'NEWLINE_DELIMITED_JSON',
uris = ['gs://log-bucket/app-logs/*.json']
);
-- 로그 분석
SELECT
JSON_EXTRACT_SCALAR(data, '$.level') as log_level,
JSON_EXTRACT_SCALAR(data, '$.timestamp') as timestamp,
JSON_EXTRACT_SCALAR(data, '$.message') as message
FROM `project.logs.app_logs_external`
WHERE JSON_EXTRACT_SCALAR(data, '$.level') = 'ERROR'
ORDER BY timestamp DESC
LIMIT 100;
3. 페더레이션 쿼리 - Cloud SQL 연동
-- Cloud SQL 외부 데이터 소스 생성 (CLI)
bq mk --connection --display_name="MySQL Connection" \
--connection_type=CLOUD_SQL \
--properties='{"instanceId":"project:region:instance","database":"mydb","type":"MYSQL"}' \
--connection_credential='{"username":"user","password":"pass"}' \
project.region.my_mysql_connection
-- 익스터널 테이블 생성
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.mysql_users`
OPTIONS (
format = 'CLOUD_SQL',
uris = ['projects/project/locations/region/connections/my_mysql_connection/tables/users']
);
성능 최적화
1. 파일 형식 최적화
-- Parquet 형식 사용 (권장)
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.optimized_table`
OPTIONS (
format = 'PARQUET',
uris = ['gs://bucket/optimized/*.parquet']
);
2. 파일 크기 최적화
- 개별 파일 크기: 100MB - 10GB
- 너무 작은 파일은 성능 저하
- 압축 파일 사용 권장
3. 와일드카드 패턴 활용
-- 날짜별 파티셔닝된 파일 구조
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.daily_data`
OPTIONS (
format = 'PARQUET',
uris = ['gs://bucket/year=*/month=*/day=*/*.parquet']
);
-- 특정 기간 데이터만 쿼리
SELECT *
FROM `project.dataset.daily_data`
WHERE _FILE_NAME LIKE '%year=2024/month=01%';
비용 최적화 방법
1. 프로젝션 최적화
-- 필요한 컬럼만 선택
SELECT product_id, sales_amount
FROM `project.dataset.sales_external`
-- SELECT * 지양
2. 필터 조건 최적화
-- 조기 필터링으로 스캔량 감소
SELECT *
FROM `project.dataset.logs_external`
WHERE _FILE_NAME LIKE '%2024-01%' -- 파일 레벨 필터
AND log_level = 'ERROR' -- 데이터 레벨 필터
3. 결과 캐싱 활용
-- 결과를 영구 테이블로 저장
CREATE TABLE `project.dataset.processed_data` AS
SELECT
product_category,
SUM(sales_amount) as total_sales
FROM `project.dataset.sales_external`
WHERE sales_date >= '2024-01-01'
GROUP BY product_category;
모니터링 및 관리
1. 테이블 메타데이터 확인
-- 익스터널 테이블 정보 조회
SELECT
table_name,
table_type,
creation_time
FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_type = 'EXTERNAL';
2. 쿼리 성능 모니터링
-- 쿼리 히스토리에서 익스터널 테이블 쿼리 분석
SELECT
job_id,
query,
total_bytes_processed,
total_slot_ms,
creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE query LIKE '%external_table%'
ORDER BY creation_time DESC;
제한사항 및 주의사항
1. 기능 제한사항
- DML 작업 (INSERT, UPDATE, DELETE) 제한
- 클러스터링, 파티셔닝 미지원
- 복잡한 조인 연산 성능 저하
2. 데이터 일관성
- 외부 소스의 데이터 변경사항 즉시 반영
- 트랜잭션 일관성 보장 어려움
- 동시성 제어 제한적
3. 보안 고려사항
- IAM 권한 관리 중요
- 외부 소스 접근 권한 설정
- 데이터 암호화 정책 준수
베스트 프랙티스
1. 데이터 구조화
# 권장 디렉토리 구조
gs://bucket/
├── year=2024/
│ ├── month=01/
│ │ ├── day=01/
│ │ │ └── data.parquet
│ │ └── day=02/
│ └── month=02/
2. 스키마 진화 관리
-- 스키마 변경에 대비한 설계
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.flexible_table`
(
id STRING,
data JSON, -- 유연한 JSON 형식 사용
created_at TIMESTAMP
)
OPTIONS (
format = 'NEWLINE_DELIMITED_JSON',
uris = ['gs://bucket/data/*.json']
);
3. 하이브리드 접근법
-- 자주 사용되는 데이터는 내부 테이블로 복사
CREATE TABLE `project.dataset.frequent_data` AS
SELECT *
FROM `project.dataset.external_table`
WHERE usage_frequency = 'HIGH';
-- 히스토리 데이터는 익스터널 테이블로 유지
SELECT *
FROM `project.dataset.external_historical_data`
WHERE date_partition >= '2023-01-01';
결론
빅쿼리 익스터널 테이블은 외부 데이터 소스를 직접 쿼리할 수 있는 강력한 기능입니다. 적절한 파일 형식 선택, 최적화된 쿼리 작성, 그리고 비용 효율적인 운영을 통해 데이터 분석의 유연성과 효율성을 크게 향상시킬 수 있습니다.