BigQuery Scheduled Query Open Source
BigQuery + Open Source
BigQuery Scheduled Query Open Source Contribution GitHub Archive Data Pipeline ETL Dashboard Looker Studio Community Metrics Contributor Analytics Production
| Metric | SQL Aggregation | Insight | Dashboard |
|---|---|---|---|
| Daily Commits | COUNT WHERE type=PushEvent | Development activity trend | Line chart |
| PR Merge Rate | Merged / Total PRs | Review efficiency | Gauge |
| Issue Response Time | AVG(first_comment - created) | Community responsiveness | Metric card |
| New Contributors | COUNT DISTINCT new actors | Community growth | Bar chart |
| Star Growth | COUNT WatchEvent per day | Project popularity | Area chart |
| Top Contributors | COUNT events GROUP BY actor | Key contributors | Leaderboard |
Scheduled Query Setup
=== BigQuery Scheduled Query ===
SQL Query — Daily GitHub Contribution Summary
CREATE OR REPLACE TABLE `project.dataset.daily_contributions`
PARTITION BY date
AS
SELECT
DATE(created_at) AS date,
repo.name AS repo_name,
type AS event_type,
COUNT(*) AS event_count,
COUNT(DISTINCT actor.login) AS unique_contributors
FROM `githubarchive.day.20*`
WHERE
_TABLE_SUFFIX = FORMAT_DATE('%y%m%d', @run_date)
AND repo.name IN ('kubernetes/kubernetes', 'pytorch/pytorch', 'facebook/react')
GROUP BY date, repo_name, event_type
ORDER BY date DESC, event_count DESC;
Schedule via CLI:
bq mk --transfer_config \
--project_id=my-project \
--data_source=scheduled_query \
--target_dataset=github_analytics \
--display_name="Daily GitHub Contributions" \
--schedule="every 24 hours" \เนื้อหาเกี่ยวข้อง — Eleventy Static Event Driven Design
--params='{
"query": "SELECT ... FROM githubarchive.day.* ...",
"destination_table_name_template": "daily_contributions_{run_date}",
"write_disposition": "WRITE_TRUNCATE"
}'
Terraform:
resource "google_bigquery_data_transfer_config" "github_daily" {
display_name = "Daily GitHub Contributions"
แนะนำเพิ่มเติม — สัญญาณเทรดรายวัน XM Signal
data_source_id = "scheduled_query"
schedule = "every 24 hours"
destination_dataset_id = google_bigquery_dataset.github.dataset_id
params = {
query = file("queries/daily_contributions.sql")
destination_table_name_template = "daily_contributions"
write_disposition = "WRITE_APPEND"
}
}
from dataclasses import dataclass
@dataclass
class ScheduleConfig:
name: str
schedule: str
query_type: str
destination: str
cost_estimate: str
configs = [
เนื้อหาเกี่ยวข้อง — แนะนำให้อ่าน C# MAUI Container Orchestration
ScheduleConfig("Daily Contributions", "every 24 hours", "Aggregate events by repo/type",
"daily_contributions", "~$0.05/day"),
ScheduleConfig("Weekly Summary", "every week on Monday 08:00", "Weekly rollup metrics",
"weekly_summary", "~$0.10/week"),
ScheduleConfig("Contributor Leaderboard", "every 24 hours", "Top contributors ranked",
"contributor_leaderboard", "~$0.03/day"),
ScheduleConfig("PR Metrics", "every 6 hours", "PR open/merge/close rates",
"pr_metrics", "~$0.08/day"),
ScheduleConfig("Issue Health", "every 12 hours", "Issue response and resolution time",
"issue_health", "~$0.04/day"),
]
แนะนำเพิ่มเติม — คอร์สเทรด Forex ที่ iCafeForex
GitHub Analytics Queries
=== GitHub Archive Analytics ===
Top Contributors Query
SELECT
actor.login AS contributor,
COUNT(*) AS total_events,
COUNTIF(type = 'PushEvent') AS commits,
COUNTIF(type = 'PullRequestEvent') AS pull_requests,
COUNTIF(type = 'IssuesEvent') AS issues,
COUNTIF(type = 'PullRequestReviewEvent') AS reviews,
เนื้อหาเกี่ยวข้อง — บทความที่เกี่ยวข้อง: Header PHP คืออะไร? คู่มือฉบับสมบูรณ์ 2026 สำหรับนักพัฒนาเว็บไซต์
MIN(created_at) AS first_contribution,
MAX(created_at) AS last_contribution
FROM `githubarchive.day.20*`
WHERE repo.name = 'kubernetes/kubernetes'
AND _TABLE_SUFFIX BETWEEN '240101' AND '241231'
GROUP BY contributor
ORDER BY total_events DESC
LIMIT 50;
Community Health Score
WITH metrics AS (
SELECT
DATE(created_at) AS date,
COUNTIF(type = 'PushEvent') AS pushes,
COUNTIF(type = 'PullRequestEvent') AS prs,
COUNTIF(type = 'IssuesEvent') AS issues,
COUNT(DISTINCT actor.login) AS contributors
FROM `githubarchive.day.20*`
WHERE repo.name = @repo_name
GROUP BY date
)
SELECT
date,
pushes, prs, issues, contributors,
(pushes * 1 + prs * 3 + issues * 2 + contributors * 5) AS health_score
เนื้อหาเกี่ยวข้อง — แนะนำให้อ่าน WordPress WooCommerce GreenOps Sustainability ลด Carbon Footprint สำหรับ E-C…
FROM metrics
ORDER BY date DESC;
@dataclass
class RepoMetric:
repo: str
daily_commits: int
weekly_prs: int
open_issues: int
contributors_30d: int
health_score: int
trend: str
repos = [
RepoMetric("kubernetes/kubernetes", 85, 120, 2500, 450, 92, "Stable"),
RepoMetric("pytorch/pytorch", 65, 95, 1800, 380, 88, "Growing"),
RepoMetric("facebook/react", 25, 40, 800, 150, 75, "Stable"),
RepoMetric("golang/go", 30, 50, 6000, 200, 70, "Stable"),
RepoMetric("rust-lang/rust", 45, 80, 3500, 300, 85, "Growing"),
]
เคล็ดลับ
- Partition: ใช้ Partition Filter ทุก Query ลด Cost 50-90%
- MERGE: ใช้ MERGE Statement สำหรับ Upsert ป้องกัน Duplicate
- Terraform: จัดการ Scheduled Query ด้วย Terraform เป็น Code
- Alert: ตั้ง Email Notification เมื่อ Query Fail
- Budget: ตั้ง Budget Alert ป้องกันค่าใช้จ่ายเกิน
BigQuery Scheduled Query คืออะไร
ตั้งเวลารัน SQL อัตโนมัติ ทุกชั่วโมง วัน สัปดาห์ Destination Table ETL Console CLI Terraform @run_time @run_date Email Notification Service Account
