Wednesday, 14 March 2018

Connect a SQL client to HANA Express on Google Kubernetes Engine

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


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.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Learning, SAP HANA Guides

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.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Learning, SAP HANA Guides

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.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Learning, SAP HANA Guides

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:

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Learning, SAP HANA Guides

Click on that VM, edit and add the tag to the network.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Learning, SAP HANA Guides

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.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Learning, SAP HANA Guides

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.

No comments:

Post a Comment