高度な分析 — 地理空間・近似集計・ノートブック

地理空間分析の基礎 — GEOGRAPHY 型と ST_ 関数

BigQuery の GEOGRAPHY 型は、地球表面上の点集合（点・線・多角形、およびそれらの集合）を表します。座標系は WGS84 の準拠楕円体で、辺は平面の直線ではなく球面測地線（geodesic edge）として扱われます。GEOGRAPHY 列を持つテーブルを作ると、各行が 1 つの「空間フィーチャ」（ジオメトリ + 属性）になり、テーブル全体が空間フィーチャの集合になります。

点を作る基本関数は ST_GEOGPOINT(longitude, latitude) です。経度が先、緯度が後という引数順序に注意してください。多くの地図サービスは緯度を先に表示する慣習があるため、取り違えやすいポイントです。

WKT（Well-Known Text）や GeoJSON との相互変換には ST_GEOGFROMTEXT / ST_ASTEXT、ST_GEOGFROMGEOJSON / ST_ASGEOJSON を使います。

SELECT
  ST_ASTEXT(ST_GEOGPOINT(-73.9855, 40.7580)) AS wkt,
  ST_ASGEOJSON(ST_GEOGPOINT(-73.9855, 40.7580)) AS geojson;
-- wkt: POINT(-73.9855 40.758)
-- geojson: {"type":"Point","coordinates":[-73.9855,40.758]}

-- 処理: 数十 KB 程度（実測は dry run で確認）
SELECT
  name,
  ST_DISTANCE(
    ST_GEOGPOINT(longitude, latitude),
    ST_GEOGPOINT(-73.9855, 40.7580)
  ) AS distance_m
FROM `bigquery-public-data.new_york.citibike_stations`
ORDER BY distance_m
LIMIT 5;

-- 処理: 数 MB 程度（county_geom はポリゴンなので points より大きい。dry run で確認）
SELECT
  c.county_name,
  COUNT(*) AS station_count
FROM `bigquery-public-data.new_york.citibike_stations` AS s
JOIN `bigquery-public-data.geo_us_boundaries.counties` AS c
  ON ST_CONTAINS(c.county_geom, ST_GEOGPOINT(s.longitude, s.latitude))
WHERE c.state_fips_code = '36'
GROUP BY c.county_name
ORDER BY station_count DESC;

近似集計と HLL++ スケッチ — 精度とコストのトレードオフ

APPROX_COUNT_DISTINCT(expression) は COUNT(DISTINCT expression) の近似値を返します。統計的な推定値であり厳密な値と一致するとは限りませんが、大量の入力に対しては COUNT(DISTINCT) より高速に処理できます。他にも近似分位数を求める APPROX_QUANTILES、頻度上位を求める APPROX_TOP_COUNT / APPROX_TOP_SUM があります。

裏側で使われているのが HLL++（HyperLogLog++）というカーディナリティ推定アルゴリズムです。BigQuery では HLL_COUNT.INIT でスケッチ（BYTES 型の圧縮表現）を作り、HLL_COUNT.MERGE / HLL_COUNT.MERGE_PARTIAL でスケッチ同士を合成し、HLL_COUNT.EXTRACT で単一スケッチからカーディナリティを取り出します。MERGE は集約関数で最終的な件数（INT64）を返すのに対し、MERGE_PARTIAL は合成後の新しいスケッチ（BYTES）を返す点が異なります。

精度（precision）は 10〜24 の範囲で指定でき、既定値は 15 です。精度を上げるほどストレージ消費と誤差のトレードオフが変わります。例えば精度 10 はスケッチ 1 個あたり約 1 KiB で 95% 信頼区間の誤差が ±6.50% ですが、既定の精度 15 では約 32 KiB で ±1.15% まで縮まります。

-- 処理: 数十 MB 程度（bikeid 列のみをスキャン。実測は dry run で確認）
SELECT
  COUNT(DISTINCT bikeid) AS exact_count,
  APPROX_COUNT_DISTINCT(bikeid) AS approx_count,
  ROUND(
    ABS(APPROX_COUNT_DISTINCT(bikeid) - COUNT(DISTINCT bikeid))
      / COUNT(DISTINCT bikeid) * 100, 3
  ) AS error_pct
FROM `bigquery-public-data.new_york.citibike_trips`;

bq mk --location=US --dataset PROJECT_ID:handson_ch15

-- 処理: 数十 MB 程度（starttime と bikeid のみをスキャン）
CREATE TABLE `PROJECT_ID.handson_ch15.daily_bike_sketches` AS
SELECT
  DATE(starttime) AS ride_date,
  HLL_COUNT.INIT(bikeid) AS bike_sketch
FROM `bigquery-public-data.new_york.citibike_trips`
GROUP BY ride_date;

-- 処理: 1 MB 未満（daily_bike_sketches の該当行だけをスキャン）
SELECT HLL_COUNT.MERGE(bike_sketch) AS unique_bikes_jan2015
FROM `PROJECT_ID.handson_ch15.daily_bike_sketches`
WHERE ride_date BETWEEN '2015-01-01' AND '2015-01-31';

bq rm -r -f -d PROJECT_ID:handson_ch15

BigQuery Studio ノートブックと BigQuery DataFrames — Python × BigQuery

BigQuery Studio のノートブックは Colab Enterprise 上で動作し、SQL セルと Python セルを 1 つのインターフェースで組み合わせられます。ノートブックの実行には Compute Engine の VM が割り当てられる「ランタイム」が必要で、複数のノートブックが同じランタイムを共有できますが、各ランタイムは 1 人のユーザー専用です。ノートブックのスロット使用量は Cloud Billing レポートで goog-bq-feature-type ラベルの値 BQ_STUDIO_NOTEBOOK を使って追跡できます。

ノートブックの中心的な価値がBigQuery DataFrames（bigframes.pandas）です。pandas 互換の API を追加設定なしで使え、実際の計算はローカルではなく BigQuery のクエリエンジン側で実行されます。

import bigframes.pandas as bpd

bpd.options.bigquery.project = "your_gcp_project_id"
bpd.options.bigquery.ordering_mode = "partial"

df = bpd.read_gbq("bigquery-public-data.ml_datasets.penguins")
df.peek()

# pandas と同じ書き方だが、計算は BigQuery 側で実行される
average_body_mass = df["body_mass_g"].mean()

ordering_mode は "partial"（効率重視、行の順序は明示的にソートするまで保証されない）と "strict"（既定値、pandas 互換性重視）を選べます。df.peek() は結果を効率的にプレビューするための専用メソッドです。

ノートブックや DataFrames を使わない、より薄い選択肢がクライアントライブラリです。Python では google-cloud-bigquery パッケージを使い、クエリを投げて結果を受け取るだけの最小構成を書けます。

from google.cloud import bigquery

client = bigquery.Client()
results = client.query_and_wait(
    "SELECT name, num_bikes_available "
    "FROM `bigquery-public-data.new_york.citibike_stations` "
    "WHERE num_bikes_available > 30"
)
for row in results:
    print(f"{row.name}: {row.num_bikes_available}")

DataFrames が「pandas ライクな変換をクエリに翻訳する」レイヤーであるのに対し、クライアントライブラリは「SQL 文字列を投げて結果を受け取る」だけの薄いレイヤーです。既存のアプリケーションに組み込むならクライアントライブラリ、データ探索や特徴量エンジニアリングを対話的に進めるならノートブック + DataFrames、という使い分けになります。

つまずきポイント

理解度チェック

まとめと次章へ

• GEOGRAPHY 型は WGS84 上の点・線・多角形を表し、ST_GEOGPOINT(経度, 緯度) を起点に ST_DISTANCE・ST_DWITHIN・ST_INTERSECTS・ST_CONTAINS を使い分けて最寄り検索・エリア集計を組み立てる
• APPROX_COUNT_DISTINCT と HLL++ スケッチは、正確な COUNT(DISTINCT) と引き換えに速度とストレージ効率を得る近似集計の手段。precision で誤差とコストを調整できる
• HLL スケッチは事前集計して MERGE で再集計できる点が本質。日次スケッチを保存しておけば任意の期間集計を生ログの再スキャンなしで求められる
• BigQuery Studio ノートブックと BigQuery DataFrames は SQL と Python を橋渡しする仕組みだが、Compute Engine の VM ランタイムを使うため課金プロジェクトが前提
• 次章ではセキュリティとガバナンス（IAM・行列レベルセキュリティ・監査ログ）を扱い、ここまで作ってきたデータ基盤を安全に運用する視点を補います

• 地理空間データの扱い方
• 地理空間分析を始める
• 地理空間関数リファレンス
• 近似集計関数リファレンス
• HyperLogLog++ 関数リファレンス
• スケッチによる近似分析
• ノートブックの概要
• BigQuery DataFrames クイックスタート
• BigQuery サンドボックス