1

We launched a Cloud Composer cluster and want to use it to move data from Cloud SQL (Postgres) to BQ. I followed the notes about doing this mentioned at these two resources:

Google Cloud Composer and Google Cloud SQL

https://cloud.google.com/sql/docs/postgres/connect-kubernetes-engine

We launch a pod running the cloud_sql_proxy and launch a service to expose the pod. The problem is that Cloud Composer cannot see the service stating the error when attempting to use an ad-hoc query to test:

cloud not translate host name "sqlproxy-service" to address: Name or service not known"

Trying by the service IP address results in the page timing out.

The -instances passed to cloud_sql_proxy work when used in a local environment or cloud shell. The log files seem to indicate no connection is ever attempted

me@cloudshell:~ (my-proj)$ kubectl logs -l app=sqlproxy-service
me@2018/11/15 13:32:59 current FDs rlimit set to 1048576, wanted limit is 8500. Nothing to do here.

    2018/11/15 13:32:59 using credential file for authentication; email=my-service-account@service.iam.gserviceaccount.com
    2018/11/15 13:32:59 Listening on 0.0.0.0:5432 for my-proj:my-ds:my-db
    2018/11/15 13:32:59 Ready for new connections

I see a comment here https://stackoverflow.com/a/53307344/1181412 that possibly this isn't even supported?

Airflow

enter image description here

YAML

apiVersion: v1
kind: Service
metadata:
  name: sqlproxy-service
  namespace: default
  labels:
    app: sqlproxy
spec:
  ports:
  - port: 5432
    protocol: TCP
    targetPort: 5432
  selector:
    app: sqlproxy
  sessionAffinity: None
  type: ClusterIP
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: sqlproxy
  labels:
    app: sqlproxy
spec:
  selector:
    matchLabels:
      app: sqlproxy
  template:
    metadata:
      labels:
        app: sqlproxy
    spec:
      containers:
        - name: cloudsql-proxy
          ports:
          - containerPort: 5432
            protocol: TCP
          image: gcr.io/cloudsql-docker/gce-proxy:latest
          imagePullPolicy: Always
          command: ["/cloud_sql_proxy",
                    "-instances=my-proj:my-region:my-db=tcp:0.0.0.0:5432",
                    "-credential_file=/secrets/cloudsql/credentials.json"]
          securityContext:
            runAsUser: 2  # non-root user
            allowPrivilegeEscalation: false
          volumeMounts:
            - name: cloudsql-instance-credentials
              mountPath: /secrets/cloudsql
              readOnly: true
      volumes:
        - name: cloudsql-instance-credentials
          secret:
            secretName: cloudsql-instance-credentials
JohnB
  • 1,743
  • 5
  • 21
  • 40

1 Answers1

2

The information you found in the answer you linked is correct - ad-hoc queries from the Airflow web server to cluster-internal services within the Composer environment are not supported. This is because the web server runs on App Engine flex using its own separate network (not connected to the GKE cluster), which you can see in the Composer architecture diagram.

Since that is the case, your SQL proxy must be exposed on a public IP address for the Composer Airflow web server to connect to it. For any services/endpoints listening on RFC1918 addresses within the GKE cluster (i.e. not exposed on a public IP), you will need additional network configuration to accept external connections.

If this is a major blocker for you, consider running a self-managed Airflow web server. Since this web server would run in the same cluster as the SQL proxy you set up, there would no longer be any issues with name resolution.

hexacyanide
  • 88,222
  • 31
  • 159
  • 162
  • We went with a Cloud Scheduler job that instead moved the data to GCS for ingestion to BQ. But thanks for this information – JohnB Mar 22 '19 at 14:22