This repo demonstrates the configuration of a data ingestion process that connects a private Cloud Data Fusion instance to a private Cloud SQL instance.
- A pre-created project
- You need to have organization admin rights, and project owner privileges or work with privileged users to complete provisioning.
cd ~
mkdir repos
cd repos
git clone https://github.com/sqlmartini/cdf-psc.git
The Terraform in this section updates organization policies and enables Google APIs.
- Configure project you want to deploy to by running the following in Cloud Shell
export PROJECT_ID="enter your project id here"
cd ~/repos/cdf-psc/core-tf/scripts
source 1-config.sh
- Run Terraform for organization policy edits and enabling Google APIs
cd ~/repos/cdf-psc/foundations-tf
terraform init
terraform apply \
-var="project_id=${PROJECT_ID}" \
-auto-approve
Note: Wait till the provisioning completes (~5 minutes) before moving to the next section.
In this section, we will provision:
- User Managed Service Account and role grants
- Network, subnets, firewall rules
- Private IP allocation for Cloud Data Fusion
- Data Fusion instance and VPC peering connection
- BigQuery dataset
- Cloud SQL instance
- GCE SQL proxy VM with static private IP
- Paste this in Cloud Shell after editing the GCP region variable to match your nearest region-
cd ~/repos/cdf-psc/core-tf
PROJECT_NBR=`gcloud projects describe $PROJECT_ID | grep projectNumber | cut -d':' -f2 | tr -d "'" | xargs`
GCP_ACCOUNT_NAME=`gcloud auth list --filter=status:ACTIVE --format="value(account)"`
GCP_REGION="us-central1"
CDF_NAME="cdf1"
CDF_VERSION="BASIC"
CDF_RELEASE="6.10.0"
- Run the Terraform for provisioning the rest of the environment
terraform init
terraform apply \
-var="project_id=${PROJECT_ID}" \
-var="project_number=${PROJECT_NBR}" \
-var="gcp_account_name=${GCP_ACCOUNT_NAME}" \
-var="gcp_region=${GCP_REGION}" \
-var="cdf_name=${CDF_NAME}" \
-var="cdf_version=${CDF_VERSION}" \
-var="cdf_release=${CDF_RELEASE}" \
-auto-approve
Note: Takes ~20 minutes to complete.
mkdir ~/repos/cdf-psc/core-tf/database
cd ~/repos/cdf-psc/core-tf/database
curl -LJO https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak
cd ~/repos/cdf-psc/core-tf/scripts
source 2-cloudsql.sh
Use sed to find/replace in test-computeprofile.json to appropriately set the serviceAccount
cd ~/repos/cdf-psc/core-tf/profiles
sed -i "s/<PROJECT_ID>/$PROJECT_ID/g" test-computeprofile.json
Use sed to find/replace in test-cdap-data-pipeline.json to appropriately set the static IP address of the cloud sql proxy VM
cd ~/repos/cdf-psc/core-tf/
IP=$(terraform output -json | jq -r '.sql_proxy_ip.value')
cd ~/repos/cdf-psc/core-tf/pipelines
sed -i "s/<SQL-PROXY-IP>/$IP/g" test-cdap-data-pipeline.json
Run the shell script to deploy driver, compute profile, and pipeline to Cloud Data Fusion
cd ~/repos/cdf-psc/core-tf/scripts
source 3-datafusion.sh
TO-DO create instructions to do this or call by API