Connect a SQL client to HANA Express on Google Kubernetes Engine
In my previous blog post, I showed how to setup a Kubernetes cluster with three HXE containers running in single pods. As I continue to explore different possibilities with my new favorite toy, I thought I could document some extra steps to connect from an external, local SQL client.
Bind HXE’s ports to the host
I modified the YAML file described in the tutorial to expose the port from the pod onto the host. The host is the actual Virtual Machine with it’s own external IP, so this will allow me to use the port from outside. The assumption that you will not be running any other container that needs those ports and can cause a conflict still applies here. The trick is done by adding the key “hostPort” to the yaml file, followed by the port you will be exposing.
Why not services and NodePort? In a future blog post, let’s tame this beast first.
Enable TCP/IP traffic to your VMs
Go into the firewall rules and create a tag that allows TCP/IP outbound communications from the desired ports and any other responsible firewalling you consider. My instances will only hold the secret of life so I will not sweat it much here.
You need to create the node first and see which VM it got assigned to. Command kubectl describe pod will give you the name of the VM.
Using the burger on the left upper corner (yeah… burgers…), go get that VM.
You can identify where the pod has been deployed from the output of the describe command. The auto-generated names are not too friendly but it’s completely doable:
Click on that VM, edit and add the tag to the network.
Remember to scroll down and save.
Tell your database to use the external IP
We’ve seen this before when adding tenants in cloud environments. When HANA Studio or a SQL client knock on the HANA’s door, they get redirected to the proper port but the internal IP that is assigned to the VM. You need a command to tell the database to use the external IP.
Log in to the pod, log in to the database and cast the SQL spell
kubectl exec -it hxe-pod bash hdbsql -I 90 -d SYSTEMDB alter system alter configuration ('global.ini','SYSTEM')set ('public_hostname_resolution','map_localhost')='your external IP address’ with reconfigure;
Connect to the database
I’m using DBeaver for this but any SQL client that allows you to use a custom JDBC driver will do. Instructions on installing DBeaver are here.
Use port 39017 for the SYSTEMDB and 39041 for the tenant database, just like for Docker. For HANA Studio, you don’t need to worry about the ports.