Thinking Notes: Google Cloud

Google Cloud（GCP）は、個人でも完全無料～実質無料で試すことができる。

無料試用枠（初回アカウント登録特典）
- 特典内容: 90日間有効な$300分の無料クレジットがもらえる。
- 条件: 初めてGoogle Cloudのアカウントを開設する場合。
- ポイント: 登録時にクレジットカードの入力が必要だけど、勝手に有料プランへ自動移行されて課金されることはない。有料アカウントへ手動でアップグレードしない限り、無料枠を使い切ったら停止する仕組み。
常時無料枠（Always Free）無料試用期間が終わった後も、規定の範囲内ならずーっと無料で使い続けられるリソースがたくさんある。

サービス名	常時無料の範囲（毎月）	主な使い道
Compute Engine (VM)	e2-micro インスタンス 1台 (米国リージョン)	小さなWebサーバーやBotの稼働
Cloud Run	200万リクエスト/月	Dockerコンテナを使ったAPIやWebアプリ
Cloud Functions	200万回の呼び出し/月	イベント駆動型の軽いプログラム実行
Cloud Storage (GCS)	5GBの標準ストレージ	画像やデータの保存
BigQuery	1TB分のクエリ処理、10GBのストレージ	SQLを使ったデータ分析の練習

Cloud Storage（データレイク役割）

CSV、JSON、画像、ログファイルなど、あらゆる生のデータを「とりあえずそのまま貯めておく」場所。
料金がすごく安くて容量を気にせず置けるのが強み。

BigQuery（データウェアハウス役割）

Cloud Storage にあるCSVやParquetファイルを読み込んでテーブル化したり、直接取り込んで（ロードして）標準的なSQLで爆速分析・抽出する場所。
テラバイト級の巨大データでも数秒で抽出できるのが強み。

（GCSへの）データの読み込み方法

CSVからの読み込み

特に自社の業務システムから出力した顧客データや、外部のサービスからダウンロードした売上データなどはCSV形式が多いから、GCSにCSVをアップロードして分析するケースは一般的。

ただ、実務で膨大なデータ（何千万行・何億行）を扱う場合は、CSVよりも圧縮率が高くて高速に読み込めるParquet（パルケ）やJSONという形式に変換してGCSに置くことも多い。

Google Analytics（GA4）や AdSense との連携

GA4やAdSenseのデータをBigQueryに送ってSQLで分析するのは、マーケティング分析やWebメディア運営の現場で超定番の使われ方。

Google Analytics (GA4) の場合
- GCSを挟まずに「GA4 ➔ BigQuery」へ直接連携（エクスポート）する標準機能が用意されている。
- ボタンひとつで設定できて、毎日自動でWebサイトの閲覧ログ（生のイベントデータ）がBigQueryに転送される。しかも標準の枠内なら転送自体は無料。
AdSense や Google 広告の場合
- 「BigQuery Data Transfer Service」というGoogle Cloud内の定期転送機能を使ったり、APIを経由してBigQueryやGCSにデータを送る。
- 広告のインプレッション数や収益データを自動で取得して、BigQuery上でGA4のアクセスデータと「JOIN（結合）」させることで、「どのページ・どの検索ワードから来たユーザーが一番収益を生んでいるか」をSQLで一発分析できるようになる。

データ元	連携ルート	よく使われる形式
業務データ / CSV等	CSVデータ ➔ GCS ➔ BigQuery	CSV / Parquet
Google Analytics (GA4)	GA4 ➔ 直接 BigQuery	自動テーブル化
AdSense / Google Ads	API / 転送機能 ➔ BigQuery (またはGCS)	JSON / 自動テーブル化

お試しでやってみた内容

まずは無料登録: $300分のクレジットをもらってアカウントを作る。
予算アラートを設定する: 万が一の請求を防ぐために、「10,000円を超えたら通知する」みたいな予算アラート（Budget Alert）を最初に設定しておく。
AdSenseのレポートをcsv形式でダウンロードしてくる。
Cloud Storage (GCS) にアップロードする。
BigQueryからGCS上の外部テーブルを参照して、BigQuery Console上でSQLを叩いて集計
Google Colabからの認証、BigQuery接続、Pythonでの集計とグラフ可視化

GCSの準備

Cloud Storage（GCS）にバケットを作ってCSVをアップロード
1. Google Cloud コンソールの左上メニュー（☰）から「Cloud Storage」➔「バケット」を選択。
2. 上部にある「＋作成」をクリック。
3. バケット名を入力（世界で一意の任意名、例: my-first-bucket-hoge-1234）。
4. ロケーションタイプは「Region」、ロケーションは「asia-northeast1 (東京)」を選択して、下部にある「作成」を押す。
5. 作成されたバケット画面が開くので、「ファイルをアップロード」を押して用意したCSVファイルをアップロード。
6. アップロード完了後、CSVのファイル名をクリックして「GCS URI」（gs://バケット名/ファイル名.csv のような文字列）をコピーしておく。
BigQueryにデータセットを作成
1. 左上メニューから「BigQuery」を選択。
2. エクスプローラ欄にある自分のプロジェクト名（project-id）の右横にある「︙（3つの点）」をクリック ➔ 「データセットを作成」を選択。
3. データセット ID を入力（例: sample_dataset）。
4. ロケーションタイプを「asia-northeast1 (東京)」に合わせたら、一番下の「データセットを作成」を押す。
GCSのCSVからBigQueryテーブルを作成
1. 今作成したデータセット名（sample_dataset）の右横にある「︙」をクリック ➔ 「テーブルを作成」を選択。
2. 以下の通りに設定入力：
  - ソース: 「ソース」で Google Cloud Storage を選択。
  - GCS バケットからファイルを選択: 先ほどコピーした GCS URI（gs://...）を貼り付け。
  - ファイル形式: CSV を選択。
  - テーブル: 作成するテーブル名を入力（例: test_table）。
  - スキーマ: 「自動検出（Auto detect）」にチェックを入れる（列名やデータ型を自動で判断してくれる）。
  - だがAdSenseレポートの場合は、半角スペースやカッコがあってデフォルトの自動検出のままだとうまくいかないので、
    1. 下の方にある「詳細オプション（Advanced options）」をクリックして展開する。
    2. 「文字マップ（Character map）」という項目を探して、初期値から「V2」に変更する。
    3. V2にすると、BigQueryが自動的に使える記号に置換してくれる。
    4. csvの側で列名を変更するならv2にせずともよい。
3. 画面下部の「テーブルを作成」をクリックすれば完了！

バケット（Bucket）とは？

データをしまっておくための、最上位のフォルダのようなもの。Cloud Storage（GCS）では、ファイル（CSVや画像など）を直接クラウド上にぽんと置くことはできなくて、「必ずバケットという箱の中に保存する」というルールがある。

バケット名の特徴と決め方

世界中でたったひとつ（ユニーク）の名前にする必要がある
- Google Cloudを使っている世界中の誰とも被らない名前にする必要がある。
- 例えば my-bucket や test みたいな名前はすでに誰かが使っているからエラーになっちゃう。
おすすめの命名パターン
- 自分の名前や日付、ランダムな数字を組み合わせると被りにくくて作りやすい。
- 例：hoge-data-202609 や my-sample-bucket-hoge-99

パソコンで例えると、

GCS全体 ➔ パソコンのハードディスク
バケット ➔ Cドライブ直下のメインフォルダ
ファイル ➔ その中に入れるCSVデータや画像

バケット名さえ決まれば、その中にCSVファイルをどんどん放り込めるようになる。

データセットIDとは？

BigQueryのなかで「複数のテーブルをまとめておくためのグループ名（フォルダ名）」のこと。「バケット」がCloud Storage側の道具箱だとしたら、「データセット」はBigQuery側の道具箱（データベース）みたいなイメージ。

プロジェクト ID: クラウド全体の契約単位
データセット ID: 関連するテーブルを束ねるフォルダ（スキーマ）
テーブル名: 実際のCSVデータが入った表

データセットIDの決め方ルール

バケット名とは違って世界中で被らないようにする必要はなくて、自分のプロジェクトの中で被らなければよい。
使える文字: 半角の英数字とアンダースコア（_）
おすすめの名前: sample_dataset や test_db など、分かりやすい小文字の英数字で作れば大丈夫。
SQLを書くときも、プロジェクトID.データセットID.テーブル名という形で指定してデータを呼び出すことになる。

BigQueryでSQLを使ってデータ抽出・集計

SQLの実行手順
- 作成したテーブル名（test_table）をクリック。
- 画面上部にある「クエリ」➔「新しいタブ」をクリック。
- エディタ領域（黒い画面）にSQLが入力できるようになる。
まずは試してほしい基本のSQL
- まずは全件取得して中身を確認するSQL（プロジェクトID.データセットID.テーブル名の部分は適宜書き換える）

SELECT * 
FROM `プロジェクトID.sample_dataset.test_table` 
LIMIT 10;

-- DATE型（日付型）のdate（yyyymmdd）カラムをyyyymmにする 
FORMAT_DATE('%Y%m', date) AS yyyymm,  -- 例: 202609
-- もしハイフンを入れたいなら FORMAT_DATE('%Y-%m', date)

縦持ちを横持ちにするならPIVOT

-- Max(CASE WHEN..としなくてもいい）
SELECT *
FROM (
  -- ① 元となる集計データを用意（前処理）
  SELECT 
    site,
    FORMAT_DATE('%Y%m', date) AS yyyymm,
    `Estimated earnings _JPY_` AS revenue
  FROM `プロジェクトID.sample_dataset.test_table`
)
-- ② PIVOTで yyyymm の値を横軸の列に展開する
PIVOT(
  SUM(revenue) FOR yyyymm IN ('202607', '202608', '202609')
)
ORDER BY site;

「毎月新しい年月が増えるたびに IN ('202601', '202602'...) のコードすら書き換えたくない」っていう場合は、BigQueryの動的SQL（Dynamic SQL）を使う

ただし、BigQueryでDECLAREやSETを使った手続き型スクリプト（複数行の処理）を実行すると、ステップごとの処理結果が別々のタブ（ステートメント）として出力される仕様になっている。なのでこれの実行結果を見るときは、一番最後のステートメントを選択する必要がある。

-- 存在する yyyymm をカンマ区切りの文字列として動的に生成して変数に入れる
DECLARE dynamic_months STRING;

SET dynamic_months = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", FORMAT_DATE('%Y%m', date), "'"), ',' ORDER BY CONCAT("'", FORMAT_DATE('%Y%m', date), "'"))
  FROM `プロジェクトID.sample_dataset.test_table`
);

-- 動的にPIVOTクエリを作成して実行
EXECUTE IMMEDIATE FORMAT("""
  SELECT *
  FROM (
    SELECT site, FORMAT_DATE('%%Y%%m', date) AS yyyymm, `Estimated earnings _JPY_` AS revenue
    FROM `プロジェクトID.sample_dataset.test_table`
  )
  PIVOT(
    SUM(revenue) FOR yyyymm IN (%s)
  )
  ORDER BY site;
""", dynamic_months);

Google Colabからの認証、BigQuery接続

from google.colab import auth
from google.cloud import bigquery
import pandas as pd

# 1. Googleアカウントの認証（ポップアップが出るので許可するだけ）
auth.authenticate_user()

# 2. BigQueryクライアントの作成
project_id = '適宜指定'
client = bigquery.Client(project=project_id)

# 3. SQLの実行とPandas DataFrameへの格納
sql = """
SELECT site, SUM(`Estimated earnings _JPY_`) AS total_revenue
FROM `プロジェクトID.sample_dataset.test_table`
GROUP BY site
"""

# 結果を直接PandasのDataFrameとして取得
df = client.query(sql).to_dataframe()

# データ表示
print(df.head())

実務の運用で難しくなる3つのポイント

データパイプラインの自動化（ETL / ELT）
- 個人でお試し: 手動でCSVをGCSにアップロードしてBigQuery画面から取り込み。
- 実務での運用: 毎日・毎時間にバッチ処理やストリーミングで自動転送させる（Cloud Composer/AirflowやCloud Pub/Subなどのツールを活用）。
データクレンジングと例外処理
- 個人でお試し: キレイなCSVを1回読み込ませる。
- 実務での運用: 「途中でデータ型が変わった」「日付形式が崩れている」「欠損値がある」「二重送信された」といったエラーへの自動検知とリカバリ設計が必要。
大規模データでのコスト・パフォーマンス管理
- 個人でお試し: 数千〜数万行のデータ（クエリ実行も数MB〜数GBでほぼ無料）。
- 実務での運用: 数十億行（数TB〜数PB）のデータ。適当に SELECT * を叩くと1回のクエリで数千円〜数万円吹っ飛ぶリスクがあるため、パーティショニングやクラスタリングの設計が必須になる。

BigQueryとBigtable

BigQuery（データウェアハウス）: 大量の過去データをまとめて集計・分析する（SQLでOLAP処理）。
Bigtable（NoSQLデータベース）: ミリ秒単位の超高速な読み書きが必要なリアルタイム処理（IoTのセンサーデータ受信やソーシャルメディアのログなど）。

Thinking Notes

ページ

2026年9月15日火曜日

Google Cloud