Analytics as a Service for Data Sharing Partners

GSP1042 — Analytics as a Service for Data Sharing Partners

Estimated time: 25 minutes

# 📊 Analytics as a Service for Data Sharing Partners > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created to help you understand why each step works. Attempt the lab yourself first and use this guide as a learning aid rather than as a shortcut to skip the underlying concepts. This guide is intended for educational purposes, hands-on practice, and Google Cloud certification preparation. It is not affiliated with, endorsed by, or officially supported by Google, Google

# ==============================================================================
# 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: GSP1042 PART 1 (PARTNER PROJECT) <<<${RESET}\n"

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Auto-fetching Project ID...${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 "✅ Partner Project ID: ${GREEN}$PROJECT_ID${RESET}\n"
echo -e "${RED}${BOLD}📌 SAVE THIS PROJECT ID! You will need it for Parts 2 and 3!${RESET}\n"

echo -e "${CYAN}${BOLD}⚠️  ATTENTION: CUSTOMER CREDENTIALS REQUIRED ⚠️${RESET}"
read -p "Enter Customer A Username (Email): " CUSTOMER_A_EMAIL
read -p "Enter Customer B Username (Email): " CUSTOMER_B_EMAIL
echo ""

echo -e "${CYAN}${BOLD}[Orbit of Ops] Task 1: Creating Authorized Views (TX & CA)...${RESET}"
bq mk --use_legacy_sql=false --view \
"SELECT * FROM \`bigquery-public-data.geo_us_boundaries.zip_codes\` WHERE state_code='TX' LIMIT 4000" \
$PROJECT_ID:demo_dataset.authorized_view_a || true

bq mk --use_legacy_sql=false --view \
"SELECT * FROM \`bigquery-public-data.geo_us_boundaries.zip_codes\` WHERE state_code='CA' LIMIT 4000" \
$PROJECT_ID:demo_dataset.authorized_view_b || true

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Task 2: Assigning IAM Permissions to the Views (Dataset Level)...${RESET}"
bq show --format=prettyjson $PROJECT_ID:demo_dataset > dataset_metadata.json

jq --arg project_id "$PROJECT_ID" '.access += [
    { "view": { "datasetId": "demo_dataset", "projectId": $project_id, "tableId": "authorized_view_a" } },
    { "view": { "datasetId": "demo_dataset", "projectId": $project_id, "tableId": "authorized_view_b" } }
]' 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] Task 3: Granting BigQuery Data Viewer to Customers...${RESET}"
cat <<EOF > policy_a.json
{"bindings": [{"members": ["user:$CUSTOMER_A_EMAIL"],"role": "roles/bigquery.dataViewer"}]}
EOF
bq set-iam-policy $PROJECT_ID:demo_dataset.authorized_view_a policy_a.json || true

cat <<EOF > policy_b.json
{"bindings": [{"members": ["user:$CUSTOMER_B_EMAIL"],"role": "roles/bigquery.dataViewer"}]}
EOF
bq set-iam-policy $PROJECT_ID:demo_dataset.authorized_view_b policy_b.json || true

echo -e "\n${GREEN}${BOLD}✅ Part 1 Complete. Please click 'Check my progress' on Tasks 1, 2, & 3!${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: GSP1042 PART 2 (CUSTOMER A) <<<${RESET}\n"

read -p "Enter the DATA SHARING PARTNER Project ID: " PARTNER_PROJECT_ID
export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)

echo -e "\n${CYAN}${BOLD}[Orbit of Ops] Creating Customer A View...${RESET}"
bq query --use_legacy_sql=false \
"CREATE OR REPLACE VIEW \`${PROJECT_ID}.customer_a_dataset.customer_a_table\` AS
SELECT geos.zip_code, geos.city, cust.last_name, cust.first_name
FROM \`${PROJECT_ID}.customer_a_dataset.customer_info\` AS cust
JOIN \`${PARTNER_PROJECT_ID}.demo_dataset.authorized_view_a\` AS geos
ON geos.zip_code = cust.postal_code;" || true

echo -e "\n${GREEN}${BOLD}✅ Part 2 Complete!${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: GSP1042 PART 3 (CUSTOMER B) <<<${RESET}\n"

read -p "Enter the DATA SHARING PARTNER Project ID: " PARTNER_PROJECT_ID
export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)

echo -e "\n${CYAN}${BOLD}[Orbit of Ops] Creating Customer B View...${RESET}"
bq query --use_legacy_sql=false \
"CREATE OR REPLACE VIEW \`${PROJECT_ID}.customer_b_dataset.customer_b_table\` AS
SELECT geos.zip_code, geos.city, cust.last_name, cust.first_name
FROM \`${PROJECT_ID}.customer_b_dataset.customer_info\` AS cust
JOIN \`${PARTNER_PROJECT_ID}.demo_dataset.authorized_view_b\` AS geos
ON geos.zip_code = cust.postal_code;" || true

echo -e "\n${GREEN}${BOLD}✅ Part 3 Complete!${RESET}"