Connecting to Cloud SQL

[sthomp]

I wanted to leave a note here to update setup.py (or possibly provide more examples of setup.py?) with instructions on connecting to Cloud SQL. I hope this will save someone a few hours (days? :))

Connecting to Cloud SQL requires use of the Cloud SQL Proxy which needs to be installed via setup.py. I thought this would be as straight forward as:

CUSTOM_COMMANDS = [
    ['echo', 'Custom command worked!'],
    ['wget', 'https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64'],
    ['mv', 'cloud_sql_proxy.linux.amd64', 'cloud_sql_proxy'],
    ['chmod', '+x', 'cloud_sql_proxy'],
    ['cloud_sql_proxy', '-dir=/cloudsql -instances=my-project:us-central1:mysql-instance=tcp:3307', '&']
]

But it turns out the ampersand & required to run the proxy in the background will cause distutils to freeze and thus cause the entire job to freeze. In this state, the job needs to be manually cancelled or it will just sit idle indefinitely. This is due to the underlying subprocess.Popen(..., shell=False).

To fix this I added another parameter to RunCustomCommand so that shell=True could be passed in. And then called RunCustomCommand separately as follows:

self.RunCustomCommand('./cloud_sql_proxy -dir=/cloudsql -instances=my-project:us-central1:mysql-instance=tcp:3307 &', True)

Update:

Seems I jumped the gun on coming up with a solution to this problem. The core of the issue was more so related to the line p.communicate() and p.returncode in setup.py which will block and wait for the sql proxy to return.

I've managed to get this all working in a local container by having a separate subprocess command in python:

subprocess.Popen(./cloud_sql_proxy -dir=/cloudsql -instances=my-project:us-central1:mysql-instance=tcp:3307, shell=True, preexec_fn=os.setpgrp)

However running this on dataflow causes easy_install to stall and puts the rest of the dataflow job in a hung state

[silviulica]

It seems the core issue is that we are trying to capture the output of the process started. We do that for the sake of being able to debug what is going on since this output will show up in the cloud logging UI (worker logs). In this case we really want to disconnect the background process from the rest. We will experiment with a solution and document it here. It should be along these lines I think: http://stackoverflow.com/questions/1196074/how-to-start-a-background-process-in-python

[sthomp]

After talking with @robertwb a bit it seems like this could be part of a larger issue of 'how do I setup a worker environment with all the necessary dependencies'. One thought he mentioned was being able to run your job in a dockerized environment. This would be a nice way to give stronger guarantees that things that work in a dev environment will also work in production. But maybe more of a long term solution :)

[silviulica]

Robert is absolutely right. I am trying to find a solution here given the current reality :-)

[brodin]

@sthomp Did you ever solved using Cloud SQL together with dataflow? I am experiencing the same issues as you at the moment. If you did get it to work, could you post some snippets of your config.py or some other valuable parts of your implementation?

[aaltay]

@sthomp @brodin Have you find a solution to your problem? If not could you describe what is the current problem?

[brodin]

@aaltay When I realised that Cloud SQL was not available as a sink/source I started integrating as a transform in a DoFn. However connecting to Cloud SQL is not an easy task in Python since you need to run the cloud_sql_proxy. The only working solution I have reached at the moment is to connect via static and public IP together with username/password and unlock the entire ip-range on my cloud sql instance (0.0.0.0.0/0). I see two potential, better, solutions:

• Java have solved this with a socket factory; maybe one could build something similar in python?
• From the config.py or some other configuration be able to invoke and start a cloud_sql_proxy instance in which you can connect to in the dataflow (as suggested by @sthomp )

[aaltay]

Thank you @brodin

• For the socket factory option, I would encouraging asking on cloudsql team support channels. They will be more qualified to answer.
• Running custom commands as part of setup.py is possible in Dataflow. Have you tried this and ran into an issue?

[Somtom]

@sthomp @brodin has this problem been solved? Currently also trying to implement an CloudSQL ETL job with Python Dataflow. It runs locally with the proxy but I can't get it running with Dataflow runner

[Somtom]

If anyones has similar problems: In this Stackoverflow post you can find my current workaround which uses the idea of @brodin with additional SSL certificates

[brodin]

@Somtom I still use the 0.0.0.0.0/0 without additional security, but will soon convert the solution to use java/scala and then I can use the socket factory.

[sernle]

Thank you @brodin

• For the socket factory option, I would encouraging asking on cloudsql team support channels. They will be more qualified to answer.
• Running custom commands as part of setup.py is possible in Dataflow. Have you tried this and ran into an issue?

@aaltay Not sure why you close the issue. The issue is still there. And people have tried to run it with setup.py and failed which sthomp already stated when he created the issue.

[smittysmee]

@sernle - A potential solution would be to leverage the Private IP access and VPC private service access.
https://cloud.google.com/sql/docs/mysql/configure-private-ip
https://cloud.google.com/sql/docs/mysql/configure-private-services-access

[sernle]

@sernle - A potential solution would be to leverage the Private IP access and VPC private service access.
https://cloud.google.com/sql/docs/mysql/configure-private-ip
https://cloud.google.com/sql/docs/mysql/configure-private-services-access

Yeah, I did something similar. However, depending on projects, you may need to stuck with Python and Cloud SQL. And it is not really nice experience.

[jccatrinck]

I created a example using Cloud SQL Proxy inside the Dataflow worker container, connecting from the Python pipeline using Unix Sockets without need for SSL or IP authorization.

So the pipeline is able to connect to one or multiple Cloud SQL instances.

https://github.com/jccatrinck/dataflow-cloud-sql-python

There is a screenshot showing the log output showing the database tables as example.