Consuming Customer Specific Datasets from Data Sharing Partners using BigQuery

GSP1043 — Consuming Customer Specific Datasets from Data Sharing Partners using BigQuery

Estimated time: 20 minutes

# 📊 Consuming Customer Specific Datasets from Data Sharing Partners using BigQuery > ⚠️ **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 for educational purposes, hands-on practice, 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: GSP1043 PART 1 (PARTNER PROJECT) <<<${RESET}\n"

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 Part 2!${RESET}\n"

echo -e "${CYAN}${BOLD}⚠️  ATTENTION: USER CREDENTIALS REQUIRED ⚠️${RESET}"
read -p "Enter Data Publisher Username (Email): " PUB_USER
read -p "Enter Customer/Data Twin Username (Email): " TWIN_USER
echo ""

echo -e "${CYAN}${BOLD}[Orbit of Ops] Creating Destination Table (Top 10 Cities)...${RESET}"
bq query --use_legacy_sql=false --destination_table=${PROJECT_ID}:demo_dataset.authorized_table --replace --nouse_cache \
'SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY state_code ORDER BY area_land_meters DESC) AS cities_by_area FROM `bigquery-public-data.geo_us_boundaries.zip_codes`) cities WHERE cities_by_area <= 10 ORDER BY cities.state_code LIMIT 1000;' > /dev/null

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

jq --arg project_id "$PROJECT_ID" '.access += [
    { "dataset": { "dataset": { "datasetId": "demo_dataset", "projectId": $project_id }, "targetTypes": ["VIEWS"] } }
]' 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 Publisher & Twin...${RESET}"
cat <<EOF > policy.json
{
  "bindings": [
    {
      "members": [
        "user:$PUB_USER",
        "user:$TWIN_USER"
      ],
      "role": "roles/bigquery.dataViewer"
    }
  ]
}
EOF
bq set-iam-policy ${PROJECT_ID}:demo_dataset.authorized_table 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: GSP1043 PART 2 (PUBLISHER PROJECT) <<<${RESET}\n"

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

read -p "Enter the DATA SHARING PARTNER Project ID (From Part 1): " PARTNER_PROJECT_ID
read -p "Enter the CUSTOMER/DATA TWIN Username (Email): " TWIN_USER
echo ""

echo -e "${CYAN}${BOLD}[Orbit of Ops] Creating Authorized View for NY...${RESET}"
bq mk --use_legacy_sql=false \
--view "SELECT * FROM \`${PARTNER_PROJECT_ID}.demo_dataset.authorized_table\` WHERE state_code = 'NY' LIMIT 1000" \
${PUB_PROJECT_ID}:data_publisher_dataset.authorized_view || true

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Authorizing the View...${RESET}"
bq show --format=prettyjson ${PUB_PROJECT_ID}:data_publisher_dataset > temp_dataset.json

jq --arg pub_project "$PUB_PROJECT_ID" '.access += [{
  "view": { "datasetId": "data_publisher_dataset", "projectId": $pub_project, "tableId": "authorized_view" }
}]' temp_dataset.json > updated_dataset.json

bq update --source=updated_dataset.json ${PUB_PROJECT_ID}:data_publisher_dataset || true

echo -e "${CYAN}${BOLD}[Orbit of Ops] Granting Access to Customer...${RESET}"
cat <<EOF > policy.json
{"bindings": [{"members": ["user:$TWIN_USER"],"role": "roles/bigquery.dataViewer"}]}
EOF
bq set-iam-policy ${PUB_PROJECT_ID}:data_publisher_dataset.authorized_view policy.json || true

echo -e "\n${GREEN}${BOLD}✅ Part 2 Complete! Please click 'Check my progress' on Task 2!${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: GSP1043 PART 3 (CUSTOMER TWIN PROJECT) <<<${RESET}\n"

export TWIN_PROJECT_ID=$(gcloud config get-value project 2>/dev/null)

read -p "Enter the DATA PUBLISHER Project ID (From Part 2): " PUB_PROJECT_ID
echo ""

echo -e "${CYAN}${BOLD}[Orbit of Ops] Creating Cross-Project Join View...${RESET}"
bq mk --use_legacy_sql=false \
--view "SELECT cities.zip_code, cities.city, cities.state_code, customers.last_name, customers.first_name
FROM \`${TWIN_PROJECT_ID}.customer_dataset.customer_info\` as customers
JOIN \`${PUB_PROJECT_ID}.data_publisher_dataset.authorized_view\` as cities
ON cities.state_code = customers.state" \
${TWIN_PROJECT_ID}:customer_dataset.customer_table || true

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