LinkedIn
Twitter
Sometimes, you need to connect Looker to your database but you are not a data engineer, so it is handy to have a guided step by step. the sources are the Looker SSH tunnel doc and some google cloud SQL doc
In your console, on in compute engine and press create instance:
Select the region closest to your database, select E2
In machine type, press preset and select shared-core and e2-micro
In boot disk, press change:
Select standard permanent disk
Then press select.
Then in identity and api access, select allow full access to all cloud api
Then press create instance. Now next to the ssh button below connect, press open in browser window
Press authorize
Now enter the following command:
Download the Cloud SQL Auth Proxy:
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.7.0/cloud-sql-proxy.linux.amd64
Make the Cloud SQL Auth Proxy executable:
chmod +x cloud-sql-proxy
Now create a group called looker:
sudo groupadd looker
Create user looker and its home directory:
sudo useradd -m -g looker looker
Switch to the looker user:
sudo su - looker
Create the .ssh directory:
mkdir ~/.ssh
Set permissions:
chmod 700 ~/.ssh
Change to the .ssh directory:
cd ~/.ssh
Create the authorized_keys file:
touch authorized_keys
Set permissions:
chmod 600 authorized_keys
Now, in your looker instance, In the Connections page in the Admin section of Looker, select the SSH Server tab.
Then select Add Server. Looker displays the Add SSH Server page:
Add a name to your server!
Press download key, open file and copy the content.
Go back to the command line and edit the authortized_keys by entering the following command:
Nano authorized_keys
Now copy the key you downloaded from your looker instance. It should only be on one line
Then press f2 to save and confirm pressing Y, and press enter to confirm
Then type exit to go back to your user
Exit
We now need to add the ssh-rsa to your server's list of accepted algorithms. Here's how. We need to Edit your sshd_config file:
Sudo nano /etc/ssh/sshd_config
And add the following at the end of the file
HostKeyAlgorithms +ssh-rsa
PubKeyAcceptedAlgorithms +ssh-rsa
Press f2 to save and confirm with Y
now , on your cloud SQL instance, get the instance connection name which should have the format projectID:region:instanceID
And back into the command line, type the following and replace the connection_name with yours:
./cloud-sql-proxy --private-ip projectID:region:instanceID
When the Cloud SQL Auth Proxy starts successfully, a message similar to the following appears in the SSH window:
Listening on 127.0.0.1:3306 for myInstance
Ready for new connections
Now go back to your looker instance,
For the ip address, you select the public ip address of your vm instance:
You should have this, press test and request fingerprint
Press accept and save
Dont press add connection as it redirects to the old UI
But click on the connection menu and press add connection
Now enter the details of your database, for the host put localhost and port 3306
In optional settings enable ssh tunnel, select your tunnel and press add tunnel.
Add localhost and port 3306
Press save and the test!
And hopefully you will the success sign!
