Google Cloud Composer and Google Cloud SQL

Question

What ways do we have available to connect to a Google Cloud SQL (MySQL) instance from the newly introduced Google Cloud Composer? The intention is to get data from a Cloud SQL instance into BigQuery (perhaps with an intermediary step through Cloud Storage).

1. Can the Cloud SQL proxy be exposed in some way on pods part the Kubernetes cluster hosting Composer?

2. If not can the Cloud SQL Proxy be brought in by using the Kubernetes Service Broker? -> https://cloud.google.com/kubernetes-engine/docs/concepts/add-on/service-broker

3. Should Airflow be used to schedule and call GCP API commands like 1) export mysql table to cloud storage 2) read mysql export into bigquery?

4. Perhaps there are other methods that I am missing to get this done

Answer 1

"The Cloud SQL Proxy provides secure access to your Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL." -Google CloudSQL-Proxy Docs

CloudSQL Proxy seems to be the recommended way to connect to CloudSQL above all others. So in Composer, as of release 1.6.1, we can create a new Kubernetes Pod to run the gcr.io/cloudsql-docker/gce-proxy:latest image, expose it through a service, then create a Connection in Composer to use in the operator.

To get set up:

• Follow Google's documentation

• Test the connection using info from Arik's Medium Post

  • Check that the pod was created kubectl get pods --all-namespaces

  • Check that the service was created kubectl get services --all-namespaces

  • Jump into a worker node kubectl --namespace=composer-1-6-1-airflow-1-10-1-<some-uid> exec -it airflow-worker-<some-uid> bash

    • Test mysql connection mysql -u composer -p --host <service-name>.default.svc.cluster.local

Notes:

• Composer now uses namespaces to organize pods

• Pods in different namespaces don't talk to each other unless you give them the full path <k8-service-name>.<k8-namespace-name>.svc.cluster.local

• Creating a new Composer Connection with the full path will enable successful connection