Share Data Using Google Data Cloud

GSP375 — Share Data using Google Data Cloud: Challenge Lab

Estimated time: 30 minutes

# 📊 Data Sharing Between Partner and Customer Using BigQuery: Challenge Lab > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created to help you understand Google Cloud services and real-world data sharing architectures. It is intended solely for educational purposes, hands-on learning, and Google Cloud certification preparation. Attempt the challenge yourself before using this guide. This walkthrough is **not affiliated with or endorsed by Google, Google Cloud, Google C

# ==============================================================================
# Color Variables & Branding
# ==============================================================================
GREEN='\e[1;32m'
CYAN='\e[1;36m'
YELLOW='\e[1;33m'
MAGENTA='\e[1;35m'
RED='\e[1;31m'
RESET='\e[0m'
BOLD='\e[1m'

clear
echo -e "${CYAN}${BOLD}"
cat << "EOF"
  ____       _     _ _            __    ___            
 / __ \     | |   (_) |          / _|  / _ \           
| |  | |_ __| |__  _| |_   ___  | |_  | | | |_ __  ___ 
| |  | | '__| '_ \| | __| / _ \ |  _| | | | | '_ \/ __|
| |__| | |  | |_) | | |_ | (_) || |   | |_| | |_) \__ \
 \____/|_|  |_.__/|_|\__| \___/ |_|    \___/| .__/|___/
                                            | |        
                                            |_|        
EOF
echo -e "${RESET}"
echo -e "${MAGENTA}${BOLD}>>> ORBIT OF OPS: GSP375 PART 1 (PARTNER PROJECT) <<<${RESET}\n"

export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
echo -e "✅ Partner Project ID: ${GREEN}$PROJECT_ID${RESET}\n"
echo -e "${RED}${BOLD}📌 SAVE THIS PROJECT ID! You will need it for Part 2!${RESET}\n"

echo -e "${CYAN}${BOLD}⚠️  ATTENTION: VARIABLES FROM LAB INSTRUCTIONS REQUIRED ⚠️${RESET}"
read -p "Enter the Partner View Name (e.g., authorized_view_gk77): " PARTNER_VIEW
read -p "Enter the Customer Username Email (e.g., [email protected]): " CUSTOMER_EMAIL
echo ""

echo -e "${CYAN}${BOLD}[Orbit of Ops] Task 1: Creating Partner Authorized View...${RESET}"
bq mk --use_legacy_sql=false --view \
"SELECT * FROM \`bigquery-public-data.geo_us_boundaries.zip_codes\`;" \
$PROJECT_ID:demo_dataset.$PARTNER_VIEW || true

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Authorizing the View in the Dataset...${RESET}"
bq show --format=prettyjson $PROJECT_ID:demo_dataset > dataset_metadata.json

jq --arg project_id "$PROJECT_ID" --arg view_name "$PARTNER_VIEW" '.access += [
    { "view": { "datasetId": "demo_dataset", "projectId": $project_id, "tableId": $view_name } }
]' dataset_metadata.json > updated_dataset_metadata.json

bq update --source updated_dataset_metadata.json $PROJECT_ID:demo_dataset || true

echo -e "${CYAN}${BOLD}[Orbit of Ops] Granting BigQuery Data Viewer to Customer...${RESET}"
cat <<EOF > policy.json
{"bindings": [{"members": ["user:$CUSTOMER_EMAIL"],"role": "roles/bigquery.dataViewer"}]}
EOF
bq set-iam-policy $PROJECT_ID:demo_dataset.$PARTNER_VIEW policy.json || true

echo -e "\n${GREEN}${BOLD}✅ Part 1 Complete. Please click 'Check my progress' on Task 1!${RESET}"

GREEN='\e[1;32m'
CYAN='\e[1;36m'
YELLOW='\e[1;33m'
MAGENTA='\e[1;35m'
RESET='\e[0m'
BOLD='\e[1m'

echo -e "\n${MAGENTA}${BOLD}>>> ORBIT OF OPS: GSP375 PART 2 (CUSTOMER PROJECT) <<<${RESET}\n"

export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
echo -e "✅ Customer Project ID: ${GREEN}$PROJECT_ID${RESET}\n"

echo -e "${CYAN}${BOLD}⚠️  ATTENTION: VARIABLES FROM LAB INSTRUCTIONS REQUIRED ⚠️${RESET}"
read -p "Enter the DATA SHARING PARTNER Project ID (From Part 1): " PARTNER_PROJECT_ID
read -p "Enter the Partner View Name used in Task 1 (e.g., authorized_view_gk77): " PARTNER_VIEW
read -p "Enter the Customer View Name for Task 3 (e.g., customer_authorized_view_hpdr): " CUSTOMER_VIEW
read -p "Enter the Data Sharing Partner Username Email (e.g., [email protected]): " PARTNER_EMAIL
echo ""

echo -e "${CYAN}${BOLD}[Orbit of Ops] Task 2: Updating Customer Data Table...${RESET}"
bq query --use_legacy_sql=false \
"UPDATE \`${PROJECT_ID}.customer_dataset.customer_info\` cust
SET cust.county=vw.county
FROM \`${PARTNER_PROJECT_ID}.demo_dataset.${PARTNER_VIEW}\` vw
WHERE vw.zip_code=cust.postal_code;" || true

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Task 3: Creating Customer Authorized View...${RESET}"
bq mk --use_legacy_sql=false --view \
"SELECT county,COUNT(1) AS Count FROM \`${PROJECT_ID}.customer_dataset.customer_info\` cust GROUP BY county HAVING county is not null" \
$PROJECT_ID:customer_dataset.$CUSTOMER_VIEW || true

echo -e "${CYAN}${BOLD}[Orbit of Ops] Authorizing the View in the Dataset...${RESET}"
bq show --format=prettyjson $PROJECT_ID:customer_dataset > dataset_metadata.json

jq --arg project_id "$PROJECT_ID" --arg view_name "$CUSTOMER_VIEW" '.access += [
    { "view": { "datasetId": "customer_dataset", "projectId": $project_id, "tableId": $view_name } }
]' dataset_metadata.json > updated_dataset_metadata.json

bq update --source updated_dataset_metadata.json $PROJECT_ID:customer_dataset || true

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Granting BigQuery Data Viewer to Partner...${RESET}"
cat <<EOF > policy.json
{"bindings": [{"members": ["user:$PARTNER_EMAIL"],"role": "roles/bigquery.dataViewer"}]}
EOF
bq set-iam-policy $PROJECT_ID:customer_dataset.$CUSTOMER_VIEW policy.json || true

echo -e "\n${GREEN}${BOLD}✅ Part 2 Complete! Please click 'Check my progress' on Tasks 2 & 3!${RESET}"