Perform Predictive Data Analysis in BigQuery
Solution for Perform Predictive Data Analysis in BigQuery. 1 lab: GSP374. Fast copy-paste commands for Google Cloud.
GSP374 — Perform Predictive Data Analysis in BigQuery: Challenge Lab
Estimated time: 15 minutes
# 🚀 Perform Predictive Data Analysis in BigQuery: Challenge Lab > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created for educational purposes, hands-on practice, and Google Cloud certification preparation. This guide is designed to help learners understand BigQuery, BigQuery ML, predictive data analysis, and machine learning workflows through practical exercises. Always attempt the lab yourself first and follow Google Cloud Skills Boost / Qwiklabs Terms of Service. T
#!/bin/bash
# ==============================================================================
# ORBIT OF OPS - MASTER SCRIPT: PREDICTIVE DATA ANALYSIS IN BIGQUERY (GSP374)
# ==============================================================================
GREEN='\e[1;32m'
CYAN='\e[1;36m'
YELLOW='\e[1;33m'
BLUE='\e[1;34m'
MAGENTA='\e[1;35m'
WHITE='\e[1;37m'
RED='\e[1;31m'
RESET='\e[0m'
BOLD='\e[1m'
clear
echo -e "${CYAN}${BOLD}"
cat << "EOF"
____ _ _ _ __ ___
/ __ \ | | (_) | / _| / _ \
| | | |_ __| |__ _| |_ ___ | |_ | | | |_ __ ___
| | | | '__| '_ \| | __| / _ \ | _| | | | | '_ \/ __|
| |__| | | | |_) | | |_ | (_) || | | |_| | |_) \__ \
\____/|_| |_.__/|_|\__| \___/ |_| \___/| .__/|___/
| |
|_|
EOF
echo -e "${RESET}"
echo -e "${BLUE}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${BLUE}${BOLD}║ 🚀 MASTER SCRIPT: BQML SOCCER ANALYTICS (GSP374) ║${RESET}"
echo -e "${BLUE}${BOLD}║ 🌐 BROUGHT TO YOU BY ORBIT OF OPS ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"
echo -e "${YELLOW}${BOLD}[Orbit of Ops] Auto-fetching Project...${RESET}"
export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
if [[ -z "$PROJECT_ID" ]]; then
export PROJECT_ID=$DEVSHELL_PROJECT_ID
fi
echo -e "✅ Project ID: ${GREEN}$PROJECT_ID${RESET}\n"
echo -e "${MAGENTA}${BOLD}⚠️ PLEASE ENTER THE EXACT VALUES FROM YOUR LAB MANUAL: ⚠️${RESET}\n"
read -p "1. Task 1 - Table name for events.json (e.g., events): " EVENT_TABLE
read -p "2. Task 1 - Table name for tags2name.csv (e.g., tags2name): " TAG_TABLE
read -p "3. Task 3 - X-axis goal mouth length (e.g., 100): " X_GOAL_MOUTH
read -p "4. Task 3 - Y-axis goal mouth length (e.g., 50): " Y_GOAL_MOUTH
read -p "5. Task 3 - X-axis length (e.g., 105): " X_LENGTH
read -p "6. Task 3 - Y-axis length (e.g., 68): " Y_LENGTH
read -p "7. Task 4 - Y-axis half (e.g., 34): " Y_HALF
read -p "8. Task 4 - Function 1 Name (e.g., shot_distance_to_goal): " FUNC_1
read -p "9. Task 4 - Function 2 Name (e.g., shot_angle_to_goal): " FUNC_2
read -p "10. Task 4 - ML Model Name (e.g., expected_goals_model): " MODEL_NAME
echo -e "\n${BLUE}${BOLD}[Orbit of Ops] Task 1: Loading Datasets into BigQuery...${RESET}"
bq mk --dataset ${PROJECT_ID}:soccer 2>/dev/null || true
bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect soccer.${EVENT_TABLE} gs://spls/bq-soccer-analytics/events.json
bq load --source_format=CSV --autodetect soccer.${TAG_TABLE} gs://spls/bq-soccer-analytics/tags2name.csv
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON soccer.competitions gs://spls/bq-soccer-analytics/competitions.json
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON soccer.matches gs://spls/bq-soccer-analytics/matches.json
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON soccer.teams gs://spls/bq-soccer-analytics/teams.json
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON soccer.players gs://spls/bq-soccer-analytics/players.json
echo -e "\n${BLUE}${BOLD}[Orbit of Ops] Task 2: Analyzing Penalty Kick Success Rates...${RESET}"
cat << EOF > task2.sql
SELECT
playerId,
(Players.firstName || ' ' || Players.lastName) AS playerName,
COUNT(id) AS numPKAtt,
SUM(IF(101 IN UNNEST(tags.id), 1, 0)) AS numPKGoals,
SAFE_DIVIDE(
SUM(IF(101 IN UNNEST(tags.id), 1, 0)),
COUNT(id)
) AS PKSuccessRate
FROM
\`soccer.${EVENT_TABLE}\` Events
LEFT JOIN
\`soccer.players\` Players ON Events.playerId = Players.wyId
WHERE
eventName = 'Free Kick' AND subEventName = 'Penalty'
GROUP BY
playerId, playerName
HAVING
numPkAtt >= 5
ORDER BY
PKSuccessRate DESC, numPKAtt DESC;
EOF
bq query --use_legacy_sql=false < task2.sql
echo -e "\n${BLUE}${BOLD}[Orbit of Ops] Task 3: Analyzing Shot Distances...${RESET}"
cat << EOF > task3.sql
WITH Shots AS (
SELECT
*,
(101 IN UNNEST(tags.id)) AS isGoal,
SQRT(
POW(( ${X_GOAL_MOUTH} - positions[ORDINAL(1)].x) * ${X_LENGTH} / 100, 2) +
POW(( ${Y_GOAL_MOUTH} - positions[ORDINAL(1)].y) * ${Y_LENGTH} / 100, 2)
) AS shotDistance
FROM
\`soccer.${EVENT_TABLE}\`
WHERE
eventName = 'Shot' OR
(eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty'))
)
SELECT
ROUND(shotDistance, 0) AS ShotDistRound0,
COUNT(*) AS numShots,
SUM(IF(isGoal, 1, 0)) AS numGoals,
AVG(IF(isGoal, 1, 0)) AS goalPct
FROM Shots
WHERE shotDistance <= 50
GROUP BY ShotDistRound0
ORDER BY ShotDistRound0;
EOF
bq query --use_legacy_sql=false < task3.sql
echo -e "\n${BLUE}${BOLD}[Orbit of Ops] Task 4: Creating UDFs and Training BQML Model...${RESET}"
echo -e "${YELLOW}⏳ NOTE: Model training will take 2-3 minutes. Do not close this window!${RESET}"
cat << EOF > task4_and_model.sql
CREATE OR REPLACE FUNCTION \`soccer.${FUNC_1}\`(x INT64, y INT64)
RETURNS FLOAT64
AS (
SQRT(
POW((${X_GOAL_MOUTH} - x) * ${X_LENGTH} / 100, 2) +
POW((${Y_GOAL_MOUTH} - y) * ${Y_LENGTH} / 100, 2)
)
);
CREATE OR REPLACE FUNCTION \`soccer.${FUNC_2}\`(x INT64, y INT64)
RETURNS FLOAT64
AS (
SAFE.ACOS(
SAFE_DIVIDE(
(
(POW(${X_LENGTH} - (x * ${X_LENGTH} / 100), 2) + POW(${Y_HALF} + (7.32 / 2) - (y * ${Y_LENGTH} / 100), 2)) +
(POW(${X_LENGTH} - (x * ${X_LENGTH} / 100), 2) + POW(${Y_HALF} - (7.32 / 2) - (y * ${Y_LENGTH} / 100), 2)) -
POW(7.32, 2)
),
(2 *
SQRT(POW(${X_LENGTH} - (x * ${X_LENGTH} / 100), 2) + POW(${Y_HALF} + 7.32 / 2 - (y * ${Y_LENGTH} / 100), 2)) *
SQRT(POW(${X_LENGTH} - (x * ${X_LENGTH} / 100), 2) + POW(${Y_HALF} - 7.32 / 2 - (y * ${Y_LENGTH} / 100), 2))
)
)
) * 180 / ACOS(-1)
);
CREATE OR REPLACE MODEL \`soccer.${MODEL_NAME}\`
OPTIONS(
model_type = 'LOGISTIC_REG',
input_label_cols = ['isGoal']
) AS
SELECT
Events.subEventName AS shotType,
(101 IN UNNEST(Events.tags.id)) AS isGoal,
\`soccer.${FUNC_1}\`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotDistance,
\`soccer.${FUNC_2}\`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotAngle
FROM
\`soccer.${EVENT_TABLE}\` Events
LEFT JOIN \`soccer.matches\` Matches ON Events.matchId = Matches.wyId
LEFT JOIN \`soccer.competitions\` Competitions ON Matches.competitionId = Competitions.wyId
WHERE
Competitions.name != 'World Cup' AND
(
eventName = 'Shot' OR
(eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty'))
) AND
\`soccer.${FUNC_2}\`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) IS NOT NULL;
EOF
bq query --use_legacy_sql=false < task4_and_model.sql
echo -e "\n${BLUE}${BOLD}[Orbit of Ops] Task 5: Making Predictions Using the New Model...${RESET}"
cat << EOF > task5.sql
SELECT
predicted_isGoal_probs[ORDINAL(1)].prob AS predictedGoalProb,
* EXCEPT (predicted_isGoal, predicted_isGoal_probs)
FROM
ML.PREDICT(
MODEL \`soccer.${MODEL_NAME}\`,
(
SELECT
Events.playerId,
(Players.firstName || ' ' || Players.lastName) AS playerName,
Teams.name AS teamName,
CAST(Matches.dateutc AS DATE) AS matchDate,
Matches.label AS match,
CAST((CASE
WHEN Events.matchPeriod = '1H' THEN 0
WHEN Events.matchPeriod = '2H' THEN 45
WHEN Events.matchPeriod = 'E1' THEN 90
WHEN Events.matchPeriod = 'E2' THEN 105
ELSE 120
END) + CEILING(Events.eventSec / 60) AS INT64) AS matchMinute,
Events.subEventName AS shotType,
(101 IN UNNEST(Events.tags.id)) AS isGoal,
\`soccer.${FUNC_1}\`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotDistance,
\`soccer.${FUNC_2}\`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotAngle
FROM
\`soccer.${EVENT_TABLE}\` Events
LEFT JOIN \`soccer.matches\` Matches ON Events.matchId = Matches.wyId
LEFT JOIN \`soccer.competitions\` Competitions ON Matches.competitionId = Competitions.wyId
LEFT JOIN \`soccer.players\` Players ON Events.playerId = Players.wyId
LEFT JOIN \`soccer.teams\` Teams ON Events.teamId = Teams.wyId
WHERE
Competitions.name = 'World Cup' AND
(
eventName = 'Shot' OR
(eventName = 'Free Kick' AND subEventName IN ('Free kick shot'))
) AND
(101 IN UNNEST(Events.tags.id))
)
)
ORDER BY predictedGoalProb;
EOF
bq query --use_legacy_sql=false < task5.sql
echo -e "\n${MAGENTA}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${MAGENTA}${BOLD}║ 🎉 AUTOMATION COMPLETED SUCCESSFULLY 🎉 ║${RESET}"
echo -e "${MAGENTA}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}"
echo -e "${WHITE}${BOLD}You can now check all your progress in the lab manual!${RESET}"