Secure BigLake Data: Challenge Lab

export USER2=

-----------------------------------------------------------

gcloud services enable bigqueryconnection.googleapis.com
bq mk --connection --location=US --project_id=$DEVSHELL_PROJECT_ID \
--connection_type=CLOUD_RESOURCE user_data_connection
SERVICE_ACCOUNT_ID=$(bq show --format=json --connection $DEVSHELL_PROJECT_ID.US.user_data_connection | jq -r '.cloudResource.serviceAccountId')
gcloud projects add-iam-policy-binding $DEVSHELL_PROJECT_ID \
--member="serviceAccount:${SERVICE_ACCOUNT_ID}" \
--role='roles/storage.objectViewer'
bq mk online_shop
bq mkdef --connection_id=$DEVSHELL_PROJECT_ID.US.user_data_connection --source_format=CSV --autodetect=true \
gs://$DEVSHELL_PROJECT_ID-bucket/user-online-sessions.csv > mytable_def1
bq mk --table --external_table_definition=mytable_def1 \
online_shop.user_online_sessions
export USER=user:$USER2
gcloud projects remove-iam-policy-binding $DEVSHELL_PROJECT_ID --member=$USER --role='roles/storage.objectViewer'
bq query --use_legacy_sql=false \
"SELECT * EXCEPT(zip, latitude, ip_address, longitude)
FROM \`$DEVSHELL_PROJECT_ID.online_shop.user_online_sessions\`;"