2

I'm trying to connect my Google Colaboratory Notebook to the Google Cloud SQL Instance I have, but I can't. My instance is only accessed with Cloud SQL Proxy, and I have no whitelisted any ip. What would be the steps to connect to it? I have also tried to download cloud_proxy_sql, but the problem is I can't give it execution permissions to the file...

Do you have any suggestion about this?

EyLuismi
  • 21
  • 2
  • 2
  • What errors have occurred when you have attempted to connect? – LordWilmore Mar 06 '18 at 10:29
  • It just timeout... I don't know if I have done it the right way. The only successful way I had was making a curl to a service which answers my ip and giving me the ip so I could whitelist it, but of course I can't whitelist that ip to my database for security reasons – EyLuismi Mar 06 '18 at 10:51
  • I'm not in a position to test this, but it might be possible to run the proxy as the argument to https://software.clapper.org/daemonize/. – Danek Duvall Feb 10 '21 at 01:27

3 Answers3

4

Here's an example: https://colab.research.google.com/drive/1SQCvWqCUAkqVHjfaEuLTBMBzxVdl7Q9h

A few important points:

  1. chmod +x will make the downloaded proxy executable.
  2. You'll want to authenticate using google.colab.auth. This sets the environment variable GOOGLE_APPLICATION_CREDENTIALS needed by the proxy.

Then, adjust the command-line invocation based on your proxy and database settings:

!./cloud_sql_proxy --instances=my-project:my-region:my-instance
Bob Smith
  • 36,107
  • 11
  • 98
  • 91
  • Thanks, I finally could get the proxy to connect, I was doing exactly the same, like word by word instead of the data of instances of course, but I needed to delete first the anterior cloud_proxy_sql file. The thing is, while I keep the connection open, I can't do anything else... I mean, the connection looks like working, but I can execute a simple query SQL in python... :S because if I close this, it closes de proxy, and It cant be after this, because it will be "paused" until closed – EyLuismi Mar 07 '18 at 11:28
  • Same here. The proxy starts and connects perfectly, but as it has to stay active to listen to incoming connections, the other cells in the book can't start running. – Patrick M. Nov 07 '18 at 08:52
  • Running cloud_sql_proxy in a detached screen session made it work. The notebook can now continue executing other cells. First install screen: `!apt update && apt install -y screen`. Then run a detached screen with the command like so, `!screen -S test -d -m {proxy-command}`. – Sid Dec 18 '18 at 17:46
1

Bob Smith's answer is partially correct. The problem is as other uses have pointed out, the notebook can't run other cells while the proxy is running.

We can workaround this by running cloud_sql_proxy in a detached screen.

  1. Install dependency

    # install screen
!apt update && apt install -y screen

  2. Then we can start the proxy.

    # run a detached screen with the proxy command
!screen -S test -d -m "./cloud_sql_proxy --instances=my-project:my-region:my-instance"

Now the notebook can now continue executing other cells.

Remember to kill the proxy when you are done

# kill proxy
!screen -X -S test quit
Sid
  • 139
  • 1
  • 9
0

Found the solution here: How to connect datalab with Google Cloud SQL?

As colab notebooks seem to be monothread, you have to run the proxy in one notebook and the queries in another

Patrick M.
  • 750
  • 3
  • 10
  • 21