Bigquery with Grafana 規劃小記

20260328

Bigquery with Grafana 規劃小記

今天來寫 Bigquery with Grafana 規劃

目的: 使用 Grafana 呈現 Bigquery 查詢的結果

目前規劃想法

• 使用 Cloud Armor 搭配 Load Balancer 達成加密連線與白名單過濾

• Grafana 目前使用 GCE 方式運作, 使用獨立 Service Account 來作為此 GCE default account , 只給予 BigQuery Job User 與要請求單位的 BigQuery Viewer 權限, 使用這樣的方式達成無金鑰驗證方式

• Grafana GCE 沒有配置外部 IP, 透過 Cloud NAT 方式來進行套件更新, 管理員則透過 IAP 方式進行存取

• 刪除預設網段, 建立自訂網段與Subnet

與 Gemini CLI 合作建立部署的 script

以下提供參考 deploy_grafana_alb.sh

#!/bin/bash

# 終止執行，如果發生任何錯誤

set -e

# ==========================================

# 參數設定區 (請依照您的實際環境修改)

# ==========================================

# 您的 Grafana 專案 ID

PROJECT_GRAFANA="your-grafana-project-id"

# 目標 BigQuery 帳單專案 (可以設定多個，請以空白分隔或換行)

TARGET_BILLING_PROJECTS=(

    "your-billing-project-a"

    "your-billing-project-b"

    # "your-billing-project-c"

)

# 資源名稱與設定

SA_NAME="grafana-bq-reader"

SA_EMAIL="${SA_NAME}@${PROJECT_GRAFANA}.iam.gserviceaccount.com"

ZONE="asia-east1-b" # 請選擇適合您的可用區

REGION="asia-east1"

VM_NAME="grafana-server"

IG_NAME="grafana-ig"

NETWORK_TAG="grafana-tag"

STATIC_IP_NAME="grafana-lb-ip"

ARMOR_POLICY_NAME="grafana-whitelist"

# 網路與基礎設施設定

VPC_NAME="grafana-vpc"

SUBNET_NAME="grafana-subnet"

SUBNET_RANGE="10.10.0.0/24"

ROUTER_NAME="grafana-router"

NAT_NAME="grafana-nat"

# 您的網域名稱，請替換成實際使用的網域 (例如: grafana.yourdomain.com)

DOMAIN_NAME="grafana.yourdomain.com"

# 您的白名單 IP，請替換成實際 IP (例如: 203.0.113.50/32)

# 支援多個 IP，請以逗號分隔 (例如: "IP1/32,IP2/24")

WHITELIST_IPS="YOUR_IP_ADDRESS/32"

# ==========================================

# 防呆檢查

# ==========================================

if [ "$PROJECT_GRAFANA" = "your-grafana-project-id" ] || \

   [ "$WHITELIST_IPS" = "YOUR_IP_ADDRESS/32" ] || \

   [ "${TARGET_BILLING_PROJECTS[0]}" = "your-billing-project-a" ]; then

    echo "❌ 錯誤：請先使用文字編輯器開啟此腳本，修改「參數設定區」的預設變數！"

    echo "請特別注意填寫正確的 PROJECT_GRAFANA、TARGET_BILLING_PROJECTS 與 WHITELIST_IPS。"

    exit 1

fi

echo "=========================================="

echo "🚀 開始執行 Grafana + ALB + Cloud Armor 自動化部署"

echo "專案 ID: $PROJECT_GRAFANA"

echo "=========================================="

# 設定 gcloud 預設專案

gcloud config set project $PROJECT_GRAFANA

echo -e "\n[階段零] 檢查並啟用必要的 GCP API..."

echo "👉 啟用 Compute Engine API 與 Certificate Manager API (可能需要幾分鐘)..."

gcloud services enable compute.googleapis.com

gcloud services enable certificatemanager.googleapis.com

echo -e "\n[階段零點五] 清理預設 VPC (Default VPC)..."

echo "👉 嘗試刪除預設 VPC 相關之防火牆規則 (若存在)..."

gcloud compute firewall-rules list --filter="network:default" --format="value(name)" > /tmp/default_fw_rules.txt || true

if [ -s /tmp/default_fw_rules.txt ]; then

    cat /tmp/default_fw_rules.txt | xargs -r gcloud compute firewall-rules delete --quiet || true

fi

rm -f /tmp/default_fw_rules.txt || true

echo "👉 嘗試刪除預設 VPC 網路 (包含預設子網路)..."

gcloud compute networks delete default --quiet || true

echo -e "\n[階段一] IAM 與服務帳戶 (Service Account) 設定"

echo "👉 1. 在 Grafana 專案建立專屬 SA..."

# 取得目前日期

CREATE_DATE=$(date +"%Y-%m-%d")

# 建立 SA (若已存在會報錯，可略過)

gcloud iam service-accounts create $SA_NAME \

    --description="Service Account for Grafana to read BigQuery (Created by Gemini on $CREATE_DATE)" \

    --display-name="Grafana BQ Reader" || true

echo "等待 10 秒讓 IAM 權限傳播..."

sleep 10

echo "👉 2. 授予 BigQuery Job User 角色..."

gcloud projects add-iam-policy-binding $PROJECT_GRAFANA \

    --member="serviceAccount:${SA_EMAIL}" \

    --role="roles/bigquery.jobUser"

echo "👉 3. 授予跨專案 BigQuery Data Viewer 角色..."

for project in "${TARGET_BILLING_PROJECTS[@]}"; do

    echo "  - 設定專案: $project"

    gcloud projects add-iam-policy-binding "$project" \

        --member="serviceAccount:${SA_EMAIL}" \

        --role="roles/bigquery.dataViewer"

done

echo -e "\n🔍 正在查詢 $ZONE 區域內可用的 E2 系列機器規格..."

gcloud compute machine-types list --zones=$ZONE --filter="name ~ '^e2-'" --format="table(name, vcpus, memoryMb)"

echo ""

echo "💡 常見規格價格參考 (依區域與折扣可能有所不同)："

echo "  - e2-micro (2 vCPU, 1GB RAM)    : 約 \$7 USD/月 (適用於極小流量)"

echo "  - e2-small (2 vCPU, 2GB RAM)    : 約 \$13 USD/月 (適合個人小專案)"

echo "  - e2-medium (2 vCPU, 4GB RAM)   : 約 \$25 USD/月 (建議的最小正式環境配置)"

echo "  - e2-standard-2 (2 vCPU, 8GB RAM): 約 \$49 USD/月 (適合企業級正式環境)"

echo ""

printf "請輸入您要使用的機器規格 [直接按 Enter 預設使用 e2-medium]: "

read VM_MACHINE_TYPE

VM_MACHINE_TYPE=${VM_MACHINE_TYPE:-e2-medium}

echo "✅ 已選擇機器規格: $VM_MACHINE_TYPE"

echo -e "\n🔍 正在設定硬碟 (Disk)..."

printf "請輸入開機磁碟大小 (GB) [直接按 Enter 預設使用 20]: "

read DISK_SIZE

DISK_SIZE=${DISK_SIZE:-20}

echo "💡 磁碟類型參考："

echo "  - pd-standard : 標準永久磁碟 (最便宜)"

echo "  - pd-balanced : 平衡永久磁碟 (建議，效能與價格平衡)"

echo "  - pd-ssd      : SSD 永久磁碟 (效能最高)"

printf "請輸入磁碟類型 [直接按 Enter 預設使用 pd-balanced]: "

read DISK_TYPE

DISK_TYPE=${DISK_TYPE:-pd-balanced}

echo "✅ 已選擇磁碟: ${DISK_SIZE}GB, 類型: $DISK_TYPE"

echo -e "\n🔍 正在設定作業系統 (OS Image)..."

echo "請選擇您要使用的作業系統："

echo "  1) Debian 12 (預設)"

echo "  2) Ubuntu 22.04 LTS"

echo "  3) Ubuntu 24.04 LTS"

echo "  4) Oracle Linux 9"

printf "請輸入選項 [1-4, 直接按 Enter 預設使用 1]: "

read OS_CHOICE

case $OS_CHOICE in

    2)

        IMAGE_PROJECT="ubuntu-os-cloud"

        IMAGE_FAMILY="ubuntu-2204-lts"

        echo "✅ 已選擇 OS: Ubuntu 22.04 LTS"

        STARTUP_SCRIPT="#!/bin/bash

exec > /var/log/grafana_install.log 2>&1

echo \"開始安裝 Grafana (Ubuntu 22.04)...\"

export DEBIAN_FRONTEND=noninteractive

apt-get update

apt-get install -y apt-transport-https software-properties-common wget

echo \"正在安裝 Google Cloud Ops Agent...\"

curl -sSO https://dl.google.com/cloudagents/add-google-cloud-ops-agent-repo.sh

bash add-google-cloud-ops-agent-repo.sh --also-install

mkdir -p /etc/apt/keyrings/

wget -q -O - https://apt.grafana.com/gpg.key | gpg --dearmor | tee /etc/apt/keyrings/grafana.gpg > /dev/null

echo \"deb [signed-by=/etc/apt/keyrings/grafana.gpg] https://apt.grafana.com stable main\" | tee /etc/apt/sources.list.d/grafana.list

apt-get update

apt-get install -y grafana

echo \"設定 Grafana...\"

sed -i \"s/;domain = localhost/domain = ${DOMAIN_NAME}/\" /etc/grafana/grafana.ini

sed -i \"s|;root_url = %(protocol)s://%(domain)s:%(http_port)s/|root_url = https://${DOMAIN_NAME}/|\" /etc/grafana/grafana.ini

sed -i \"s/;cookie_secure = false/cookie_secure = true/\" /etc/grafana/grafana.ini

systemctl daemon-reload

systemctl enable grafana-server

systemctl start grafana-server

echo \"Grafana 安裝與設定完成！\"

"

        ;;

    3)

        IMAGE_PROJECT="ubuntu-os-cloud"

        IMAGE_FAMILY="ubuntu-2404-lts-amd64"

        echo "✅ 已選擇 OS: Ubuntu 24.04 LTS"

        STARTUP_SCRIPT="#!/bin/bash

exec > /var/log/grafana_install.log 2>&1

echo \"開始安裝 Grafana (Ubuntu 24.04)...\"

export DEBIAN_FRONTEND=noninteractive

apt-get update

apt-get install -y apt-transport-https software-properties-common wget

echo \"正在安裝 Google Cloud Ops Agent...\"

curl -sSO https://dl.google.com/cloudagents/add-google-cloud-ops-agent-repo.sh

bash add-google-cloud-ops-agent-repo.sh --also-install

mkdir -p /etc/apt/keyrings/

wget -q -O - https://apt.grafana.com/gpg.key | gpg --dearmor | tee /etc/apt/keyrings/grafana.gpg > /dev/null

echo \"deb [signed-by=/etc/apt/keyrings/grafana.gpg] https://apt.grafana.com stable main\" | tee /etc/apt/sources.list.d/grafana.list

apt-get update

apt-get install -y grafana

echo \"設定 Grafana...\"

sed -i \"s/;domain = localhost/domain = ${DOMAIN_NAME}/\" /etc/grafana/grafana.ini

sed -i \"s|;root_url = %(protocol)s://%(domain)s:%(http_port)s/|root_url = https://${DOMAIN_NAME}/|\" /etc/grafana/grafana.ini

sed -i \"s/;cookie_secure = false/cookie_secure = true/\" /etc/grafana/grafana.ini

systemctl daemon-reload

systemctl enable grafana-server

systemctl start grafana-server

echo \"Grafana 安裝與設定完成！\"

"

        ;;

    4)

        IMAGE_PROJECT="oracle-os-cloud"

        IMAGE_FAMILY="oracle-linux-9"

        echo "✅ 已選擇 OS: Oracle Linux 9"

        STARTUP_SCRIPT="#!/bin/bash

exec > /var/log/grafana_install.log 2>&1

echo \"開始安裝 Grafana (Oracle Linux 9)...\"

echo \"正在安裝 Google Cloud Ops Agent...\"

curl -sSO https://dl.google.com/cloudagents/add-google-cloud-ops-agent-repo.sh

bash add-google-cloud-ops-agent-repo.sh --also-install

yum install -y https://dl.grafana.com/enterprise/release/grafana-enterprise-10.4.1-1.x86_64.rpm

echo \"設定 Grafana...\"

sed -i \"s/;domain = localhost/domain = ${DOMAIN_NAME}/\" /etc/grafana/grafana.ini

sed -i \"s|;root_url = %(protocol)s://%(domain)s:%(http_port)s/|root_url = https://${DOMAIN_NAME}/|\" /etc/grafana/grafana.ini

sed -i \"s/;cookie_secure = false/cookie_secure = true/\" /etc/grafana/grafana.ini

systemctl daemon-reload

systemctl enable grafana-server

systemctl start grafana-server

echo \"Grafana 安裝與設定完成！\"

"

        ;;

    *)

        IMAGE_PROJECT="debian-cloud"

        IMAGE_FAMILY="debian-12"

        echo "✅ 已選擇 OS: Debian 12 (預設)"

        STARTUP_SCRIPT="#!/bin/bash

exec > /var/log/grafana_install.log 2>&1

echo \"開始安裝 Grafana (Debian 12)...\"

export DEBIAN_FRONTEND=noninteractive

apt-get update

apt-get install -y apt-transport-https software-properties-common wget

echo \"正在安裝 Google Cloud Ops Agent...\"

curl -sSO https://dl.google.com/cloudagents/add-google-cloud-ops-agent-repo.sh

bash add-google-cloud-ops-agent-repo.sh --also-install

mkdir -p /etc/apt/keyrings/

wget -q -O - https://apt.grafana.com/gpg.key | gpg --dearmor | tee /etc/apt/keyrings/grafana.gpg > /dev/null

echo \"deb [signed-by=/etc/apt/keyrings/grafana.gpg] https://apt.grafana.com stable main\" | tee /etc/apt/sources.list.d/grafana.list

apt-get update

apt-get install -y grafana

echo \"設定 Grafana...\"

sed -i \"s/;domain = localhost/domain = ${DOMAIN_NAME}/\" /etc/grafana/grafana.ini

sed -i \"s|;root_url = %(protocol)s://%(domain)s:%(http_port)s/|root_url = https://${DOMAIN_NAME}/|\" /etc/grafana/grafana.ini

sed -i \"s/;cookie_secure = false/cookie_secure = true/\" /etc/grafana/grafana.ini

systemctl daemon-reload

systemctl enable grafana-server

systemctl start grafana-server

echo \"Grafana 安裝與設定完成！\"

"

        ;;

esac

echo -e "\n[階段二] 自訂 VPC 網路與 Cloud NAT 設定"

echo "👉 1. 建立自訂 VPC 網路 ($VPC_NAME)..."

gcloud compute networks create $VPC_NAME --subnet-mode=custom

echo "👉 2. 建立自訂子網路 ($SUBNET_NAME)..."

gcloud compute networks subnets create $SUBNET_NAME \

    --network=$VPC_NAME \

    --region=$REGION \

    --range=$SUBNET_RANGE

echo "👉 3. 建立 Cloud Router ($ROUTER_NAME)..."

gcloud compute routers create $ROUTER_NAME \

    --network=$VPC_NAME \

    --region=$REGION

echo "👉 4. 建立 Cloud NAT ($NAT_NAME)..."

gcloud compute routers nats create $NAT_NAME \

    --router=$ROUTER_NAME \

    --region=$REGION \

    --auto-allocate-nat-external-ips \

    --nat-all-subnet-ip-ranges

echo -e "\n[階段三] GCE 虛擬機器與執行個體群組"

echo "👉 1. 建立 GCE 執行個體 (無外部 IP，透過 startup-script 自動安裝 Grafana)..."

gcloud compute instances create $VM_NAME \

    --description="Grafana Server VM (Created by Gemini on $CREATE_DATE)" \

    --zone=$ZONE \

    --machine-type=$VM_MACHINE_TYPE \

    --image-project=$IMAGE_PROJECT \

    --image-family=$IMAGE_FAMILY \

    --boot-disk-size=$DISK_SIZE \

    --boot-disk-type=$DISK_TYPE \

    --network=$VPC_NAME \

    --subnet=$SUBNET_NAME \

    --no-address \

    --service-account=${SA_EMAIL} \

    --scopes=https://www.googleapis.com/auth/cloud-platform \

    --tags=$NETWORK_TAG \

    --metadata=startup-script="$STARTUP_SCRIPT"

echo "👉 2. 建立非代管執行個體群組並加入 VM..."

gcloud compute instance-groups unmanaged create $IG_NAME \

    --description="Grafana Instance Group (Created by Gemini on $CREATE_DATE)" \

    --zone=$ZONE

gcloud compute instance-groups unmanaged add-instances $IG_NAME \

    --zone=$ZONE \

    --instances=$VM_NAME

echo "👉 3. 設定執行個體群組 Named Port..."

gcloud compute instance-groups unmanaged set-named-ports $IG_NAME \

    --named-ports=http:3000 \

    --zone=$ZONE

echo -e "\n[階段四] VPC 防火牆設定"

echo "👉 1. 建立防火牆規則以允許 GCP LB 健康檢查連線..."

gcloud compute firewall-rules create allow-gcp-lb-to-grafana \

    --description="Allow GCP Load Balancer health checks to Grafana (Created by Gemini on $CREATE_DATE)" \

    --direction=INGRESS \

    --priority=1000 \

    --network=$VPC_NAME \

    --action=ALLOW \

    --rules=tcp:3000 \

    --source-ranges=130.211.0.0/22,35.191.0.0/16 \

    --target-tags=$NETWORK_TAG

echo "👉 2. 建立防火牆規則以允許透過 IAP 安全 SSH 連線..."

gcloud compute firewall-rules create allow-iap-ssh-to-grafana \

    --description="Allow IAP SSH access to internal Grafana VM (Created by Gemini on $CREATE_DATE)" \

    --direction=INGRESS \

    --priority=1000 \

    --network=$VPC_NAME \

    --action=ALLOW \

    --rules=tcp:22 \

    --source-ranges=35.235.240.0/20 \

    --target-tags=$NETWORK_TAG

echo -e "\n[階段五] 保留靜態 IP"

echo "👉 保留 GCP 全域靜態外部 IP..."

gcloud compute addresses create $STATIC_IP_NAME \

    --description="Static IP for Grafana ALB (Created by Gemini on $CREATE_DATE)" \

    --network-tier=PREMIUM \

    --global || true

LB_IP=$(gcloud compute addresses describe $STATIC_IP_NAME --global --format="value(address)")

echo "✅ 獲得靜態 IP: $LB_IP"

echo "⚠️ 請立即前往 Cloudflare (或其他 DNS 提供商)，將 $DOMAIN_NAME 的 A 紀錄指向 $LB_IP (若使用 Cloudflare 請設為 DNS Only 灰色雲朵)"

printf "請在完成 DNS 設定後，按 Enter 鍵繼續..."

read

echo -e "\n[階段六] Cloud Armor 白名單設定"

echo "👉 1. 建立 Cloud Armor 政策 (預設拒絕)..."

gcloud compute security-policies create $ARMOR_POLICY_NAME \

    --description="Whitelist for Grafana (Created by Gemini on $CREATE_DATE)" || true

# 更改預設規則為 403 拒絕

gcloud compute security-policies rules update 2147483647 \

    --security-policy=$ARMOR_POLICY_NAME \

    --action="deny-403"

echo "👉 2. 新增白名單規則 (允許 $WHITELIST_IPS)..."

gcloud compute security-policies rules create 1000 \

    --description="Allow specific IPs to access Grafana (Created by Gemini on $CREATE_DATE)" \

    --security-policy=$ARMOR_POLICY_NAME \

    --src-ip-ranges="$WHITELIST_IPS" \

    --action="allow" || true

echo -e "\n[階段七] Application Load Balancer (ALB) 與 SSL 設定"

echo "👉 1. 建立健康檢查..."

gcloud compute health-checks create http grafana-hc \

    --description="Health check for Grafana backend (Created by Gemini on $CREATE_DATE)" \

    --port=3000 \

    --request-path="/api/health" || true

echo "👉 2. 建立後端服務並套用 Cloud Armor..."

gcloud compute backend-services create grafana-backend \

    --description="Backend service for Grafana ALB (Created by Gemini on $CREATE_DATE)" \

    --load-balancing-scheme=EXTERNAL \

    --protocol=HTTP \

    --port-name=http \

    --health-checks=grafana-hc \

    --global || true

gcloud compute backend-services update grafana-backend \

    --security-policy=$ARMOR_POLICY_NAME \

    --global

echo "👉 3. 將執行個體群組加入後端服務..."

gcloud compute backend-services add-backend grafana-backend \

    --instance-group=$IG_NAME \

    --instance-group-zone=$ZONE \

    --global || true

echo "👉 4. 建立 URL Map..."

gcloud compute url-maps create grafana-url-map \

    --description="URL Map for Grafana ALB (Created by Gemini on $CREATE_DATE)" \

    --default-service=grafana-backend || true

echo "👉 5. 建立 Google 代管 SSL 憑證..."

gcloud compute ssl-certificates create grafana-cert \

    --description="Managed SSL Certificate for Grafana (Created by Gemini on $CREATE_DATE)" \

    --domains=$DOMAIN_NAME || true

echo "👉 6. 建立 Target HTTPS Proxy..."

gcloud compute target-https-proxies create grafana-https-proxy \

    --description="Target HTTPS Proxy for Grafana (Created by Gemini on $CREATE_DATE)" \

    --url-map=grafana-url-map \

    --ssl-certificates=grafana-cert || true

echo "👉 7. 建立全域轉送規則 (Forwarding Rule)..."

gcloud compute forwarding-rules create grafana-https-rule \

    --description="Global forwarding rule for Grafana ALB (Created by Gemini on $CREATE_DATE)" \

    --load-balancing-scheme=EXTERNAL \

    --network-tier=PREMIUM \

    --address=$STATIC_IP_NAME \

    --global \

    --target-https-proxy=grafana-https-proxy \

    --ports=443 || true

echo "=========================================="

echo "✅ 基礎設施部署腳本執行完畢！"

echo "Grafana 與 Ops Agent 正在背景自動安裝與設定 (約需 2-3 分鐘)。"

echo ""

echo "🔐 Grafana 登入資訊："

echo "   - 預設帳號：admin"

echo "   - 預設密碼：admin"

echo "   ⚠️ 安全提醒：首次登入後，系統會要求您立即變更密碼，請務必設定強密碼。"

echo ""

echo "後續檢查步驟："

echo "1. 若需確認安裝進度，可透過 IAP SSH 進入內部 VM ($VM_NAME) 執行："

echo "   gcloud compute ssh $VM_NAME --zone=$ZONE --tunnel-through-iap --command=\"tail -f /var/log/grafana_install.log\""

echo "2. 憑證核發通常需要 15~30 分鐘，請耐心等待。"

echo "3. 等待完成後，即可開啟瀏覽器存取: https://$DOMAIN_NAME"

echo "=========================================="

現在真的是進入 人與 AI 合作時代

~ enjoy it

References

• Gemini CLI