bigquery

BigQuery エキスパートエンジニアスキル

このスキルでは、BigQuery の開発に関する包括的なガイドを提供します。

1. bq コマンドラインツールの基本

1.1. クエリの実行

# Execute query with Standard SQL
bq query --use_legacy_sql=false 'SELECT * FROM `project.dataset.table` LIMIT 10'

# Output results in CSV format
bq query --use_legacy_sql=false --format=csv 'SELECT * FROM `project.dataset.table`'

# Dry run (cost estimation)
bq query --use_legacy_sql=false --dry_run 'SELECT * FROM `project.dataset.table`'

# Save results to table
bq query --use_legacy_sql=false --destination_table=project:dataset.result_table 'SELECT * FROM `project.dataset.table`'

1.2. テーブル操作

# List tables
bq ls project:dataset

# Check table schema
bq show --schema --format=prettyjson project:dataset.table

# Create table (from schema file)
bq mk --table project:dataset.table schema.json

# Create partitioned table
bq mk --table --time_partitioning_field=created_at project:dataset.table schema.json

# Create clustered table
bq mk --table --clustering_fields=user_id,category project:dataset.table schema.json

# Delete table
bq rm -t project:dataset.table

1.3. データの読み込み／エクスポート

# Load from CSV
bq load --source_format=CSV project:dataset.table gs://bucket/data.csv schema.json

# Load from JSON
bq load --source_format=NEWLINE_DELIMITED_JSON project:dataset.table gs://bucket/data.json

# Load from Parquet (auto-detect schema)
bq load --source_format=PARQUET --autodetect project:dataset.table gs://bucket/data.parquet

# Export to Cloud Storage
bq extract --destination_format=CSV project:dataset.table gs://bucket/export/*.csv

2. GoogleSQL の基本構文

2.1. SELECT ステートメント

-- Basic SELECT
SELECT
  column1,
  column2,
  COUNT(*) AS count
FROM
  `project.dataset.table`
WHERE
  date >= '2024-01-01'
GROUP BY
  column1, column2
HAVING
  COUNT(*) > 10
ORDER BY
  count DESC
LIMIT 100

2.2. よく使う関数

-- String functions
CONCAT(str1, str2)
LOWER(str), UPPER(str)
TRIM(str), LTRIM(str), RTRIM(str)
SUBSTR(str, start, length)
REGEXP_CONTAINS(str, r'pattern')
REGEXP_EXTRACT(str, r'pattern')
SPLIT(str, delimiter)

-- Date/time functions
CURRENT_DATE(), CURRENT_TIMESTAMP()
DATE(timestamp), TIMESTAMP(date)
DATE_ADD(date, INTERVAL 1 DAY)
DATE_DIFF(date1, date2, DAY)
FORMAT_DATE('%Y-%m-%d', date)
PARSE_DATE('%Y%m%d', str)
EXTRACT(YEAR FROM date)

-- Aggregate functions
COUNT(*), COUNT(DISTINCT column)
SUM(column), AVG(column)
MIN(column), MAX(column)
ARRAY_AGG(column)
STRING_AGG(column, ',')

-- Window functions
ROW_NUMBER() OVER (PARTITION BY col ORDER BY col2)
RANK() OVER (ORDER BY col DESC)
LAG(col, 1) OVER (ORDER BY date)
LEAD(col, 1) OVER (ORDER BY date)
SUM(col) OVER (PARTITION BY category)

2.3. JOIN 構文

-- INNER JOIN
SELECT a.*, b.column
FROM `project.dataset.table_a` AS a
INNER JOIN `project.dataset.table_b` AS b
  ON a.id = b.id

-- LEFT JOIN
SELECT a.*, b.column
FROM `project.dataset.table_a` AS a
LEFT JOIN `project.dataset.table_b` AS b
  ON a.id = b.id

-- CROSS JOIN (commonly used for array expansion)
SELECT *
FROM `project.dataset.table`,
UNNEST(array_column) AS element

2.4. CTE（共通テーブル式）

WITH
  base_data AS (
    SELECT *
    FROM `project.dataset.table`
    WHERE date >= '2024-01-01'
  ),
  aggregated AS (
    SELECT
      category,
      COUNT(*) AS count
    FROM base_data
    GROUP BY category
  )
SELECT *
FROM aggregated
ORDER BY count DESC

3. テーブル設計

3.1. パーティショニング

クエリのスキャン量を減らすため、日付でデータを分割します。

-- Create date-partitioned table
CREATE TABLE `project.dataset.partitioned_table`
PARTITION BY DATE(created_at)
AS SELECT * FROM `project.dataset.source_table`;

-- Integer partitioning
CREATE TABLE `project.dataset.int_partitioned`
PARTITION BY RANGE_BUCKET(user_id, GENERATE_ARRAY(0, 1000000, 10000))
AS SELECT * FROM source;

-- Require partition filter
CREATE TABLE `project.dataset.table`
PARTITION BY DATE(created_at)
OPTIONS (
  require_partition_filter = TRUE
);

3.2. クラスタリング

指定した列でデータを並べ替え、グループ化します。

-- Clustering table
CREATE TABLE `project.dataset.clustered_table`
PARTITION BY DATE(created_at)
CLUSTER BY user_id, category
AS SELECT * FROM source;

3.3. ベストプラクティス

• パーティショニングとクラスタリングを組み合わせます
• クエリで頻繁にフィルタリングされる列を選択します
• クラスタリング列は最大 4 列です
• カーディナリティの高い列を優先します

4. パフォーマンスの最適化

4.1. クエリの最適化

-- Avoid SELECT *
-- Bad
SELECT * FROM table;
-- Good
SELECT column1, column2 FROM table;

-- Leverage partition pruning
-- Bad (function applied to partition column)
WHERE DATE(created_at) = '2024-01-01'
-- Good
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

-- Use APPROX_ functions for estimates (faster)
SELECT APPROX_COUNT_DISTINCT(user_id) FROM table;

4.2. JOIN の最適化

-- Put smaller table on right side (broadcast JOIN)
SELECT *
FROM large_table
JOIN small_table ON large_table.id = small_table.id;

-- JOIN only needed columns
WITH filtered AS (
  SELECT id, needed_column FROM large_table WHERE condition
)
SELECT * FROM filtered JOIN other_table ON ...

4.3. スロット使用量の確認

-- Check job statistics
SELECT
  job_id,
  total_bytes_processed,
  total_slot_ms,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_sec
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY total_slot_ms DESC
LIMIT 10;

5. コスト管理

5.1. 料金モデル

• オンデマンド：スキャンしたデータ量に基づきます（$5/TB）
• 定額料金（エディション）：予約済みスロットに基づきます
• ストレージ：アクティブ $0.02/GB、長期保存 $0.01/GB

5.2. コスト削減のベストプラクティス

1. SELECT * を避けます
2. 必ずパーティションフィルターを使用します
3. クエリを実行する前にドライランでコストを確認します
4. マテリアライズドビューで繰り返し実行されるクエリを最適化します
5. BI Engine でダッシュボードのクエリを高速化します

5.3. カスタム割り当ての設定

-- Set query byte limit per project
-- Configure in Cloud Console or gcloud

6. データガバナンス

6.1. IAM ロール

• roles/bigquery.admin：すべての権限
• roles/bigquery.dataEditor：データの読み取り／書き込み
• roles/bigquery.dataViewer：データの読み取り専用
• roles/bigquery.jobUser：ジョブの実行
• roles/bigquery.user：データセットの一覧表示、ジョブの実行

6.2. 列レベルのセキュリティ

-- Apply policy tag
ALTER TABLE `project.dataset.table`
ALTER COLUMN sensitive_column
SET OPTIONS (policy_tags = ['projects/project/locations/us/taxonomies/123/policyTags/456']);

6.3. 行レベルのセキュリティ

-- Create row access policy
CREATE ROW ACCESS POLICY region_filter
ON `project.dataset.table`
GRANT TO ('user:[email protected]')
FILTER USING (region = 'APAC');

7. BigQuery ML

7.1. モデルの作成

-- Linear regression model
CREATE OR REPLACE MODEL `project.dataset.model`
OPTIONS (
  model_type = 'LINEAR_REG',
  input_label_cols = ['target']
) AS
SELECT feature1, feature2, target
FROM `project.dataset.training_data`;

-- Logistic regression model
CREATE OR REPLACE MODEL `project.dataset.classifier`
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['label']
) AS
SELECT * FROM training_data;

7.2. モデルの評価と予測

-- Model evaluation
SELECT * FROM ML.EVALUATE(MODEL `project.dataset.model`);

-- Prediction
SELECT *
FROM ML.PREDICT(
  MODEL `project.dataset.model`,
  (SELECT * FROM `project.dataset.new_data`)
);

8. 外部データソース

8.1. 外部テーブル

-- Reference Cloud Storage CSV as external table
CREATE EXTERNAL TABLE `project.dataset.external_table`
OPTIONS (
  format = 'CSV',
  uris = ['gs://bucket/path/*.csv'],
  skip_leading_rows = 1
);

-- Parquet external table
CREATE EXTERNAL TABLE `project.dataset.parquet_table`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://bucket/path/*.parquet']
);

8.2. フェデレーテッドクエリ

-- Connect to Cloud SQL
SELECT * FROM EXTERNAL_QUERY(
  'projects/project/locations/us/connections/connection_id',
  'SELECT * FROM mysql_table'
);

9. スケジュール済みクエリ

9.1. 設定例

-- Configure in Cloud Console or bq command
-- Run daily at 2 AM
bq query --use_legacy_sql=false \
  --schedule='every 24 hours' \
  --display_name='Daily aggregation' \
  --destination_table='project:dataset.daily_summary' \
  --replace \
  'SELECT DATE(created_at) as date, COUNT(*) as count FROM source GROUP BY 1'

10. 参考リンク

• 公式ドキュメント：https://cloud.google.com/bigquery/docs
• GoogleSQL リファレンス：https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
• 関数リファレンス：https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-all
• bq コマンドリファレンス：https://cloud.google.com/bigquery/docs/bq-command-line-tool
• BigQuery ML：https://cloud.google.com/bigquery/docs/bqml-introduction
• 料金：https://cloud.google.com/bigquery/pricing