Skip to Content

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.

Enjoy!

 

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Perry Sedlar

    Hi Lucia,

    I have HXE version 2 sps2 on a personal sandbox server, in general, I really like it.  The project I am on is still struggling to get the full licensed version of HANA on our servers, so having HXE up and running is helping me.

    I do have a question though, and this may not be the correct forum (if not please suggest whom I might contact).  I need to create HANA users programmatically.   I planned to create HANA Stored Procedures and call them from JAVA (using JDBC).  I have created a simple Stored Procedure in a new Schema I created that does a simple query (SELECT * FROM ….).  I can call this Stored Procedure from my Java code with no problem.

    I am trying to write a Stored Procedure to actually create users in HANA.  I am using this as a reference:  https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/20d5ddb075191014b594f7b11ff08ee2.html

    In HANA Studio, I can open the SQL Console and create new users with no problems.  For example if I have the following in the SQL Console it works great:

    CREATE USER 1380619314 PASSWORD Changemenow123 VALID FROM NOW UNTIL ‘2028-01-01′ SET PARAMETER CLIENT=’90’, TIME ZONE=’EST’, EMAIL ADDRESS=’perry.a.sedlar.ctr@mail.mil’;

    However, I have spent a couple days trying to create a Stored Procedure in HANA Studio and I keep getting syntax errors.  It seems to complain that USER is missplaced or incorrect.  I will paste the latest stored procedure attempt below.  I don’t understand what I need to do in the Stored Procedure to do the same thing I can do in the SQL Console with no problem.  :/

    Any suggestions?

     

    PROCEDURE “IDM_PROTOTYPE”.”companyx.aesip.idm.hana::SP_CREATE_HANA_USER” ( IN edipi VARCHAR(12), IN password VARCHAR(20),PROCEDURE “IDM_PROTOTYPE”.”companyx.aesip.idm.hana::SP_CREATE_HANA_USER” ( IN edipi VARCHAR(12), IN password VARCHAR(20),           IN valid_from VARCHAR(10), IN valid_until VARCHAR(10), IN client VARCHAR(2), IN time_zone VARCHAR(3), IN email VARCHAR(60) )  LANGUAGE SQLSCRIPT SQL SECURITY INVOKER  –DEFAULT SCHEMA IDM_PROTOTYPE –READS SQL DATA AS

    AS

    BEGIN/* *********************************************************************** ** **                                                                         **** PROCEDURE: “IDM_PROTOTYPE”.”companyx.aesip.idm.hana::SP_CREATE_HANA_USER”   **** DATE: 02/13/2018                                                        **** AUTHOR: Perry A. Sedlar                                                 **** Description:  Create a new HANA user                                    **** Input Parameters: edipi (user id)                                       ****                   password (initial password)                           ****                   valid_from                                            ****                   valid_to   e.g. 2028-01-01                            ****                   client                                                ****                   time_zone                                             ****                   email                                                 ****                                                                         ****                                                                         **************************************************************************** **/

    EXEC CREATE USER :edipi PASSWORD :password VALID FROM :valid_from UNTIL :valid_until SET PARAMETER CLIENT=:client TIME ZONE=:time_zone EMAIL ADDRESS=:email;
    END;

    (0) 

Leave a Reply