SAP HANA Express Edition on Red Hat OpenShift Service on AWS

SAP HANA express edition is a streamlined version of the SAP HANA platform that enables developers to jump-start application development in the cloud or from a personal computer. With it, you can build and deploy modern applications that use up to 32GB of memory without incurring any license fees. SAP HANA express edition includes an in-memory data engine with advanced analytical data processing engines for business, text, spatial, and graph data, supporting multiple data models on a single copy of the data. The software license allows for both non-production and production use cases, enabling you to quickly prototype, demo, and deploy next-generation applications with SAP HANA express edition.

This is the second of two articles about deploying SAP HANA express edition in containers. The previous article describes deploying SAP HANA express edition in a container on Red Hat Enterprise Linux.

Kubernetes is a popular choice among many developers for application deployments. Many of these deployments can benefit from a turnkey application platform. Red Hat OpenShift Service on AWS (ROSA) is a managed OpenShift integration on AWS developed by Red Hat and jointly supported by AWS and Red Hat. In this article, we’ll demonstrate the use of SAP HANA express edition on ROSA, and how to connect to the database and perform analytics.

Prerequisites

You need the following resources:

• AWS account
• Red Hat account
• IAM user with permission to create and access a ROSA cluster (refer to the ROSA workshop for more detail)
• Install AWS CLI (aws)
• Install ROSA CLI (rosa)
• Install the OpenShift CLI (oc)
• The ROSA cluster created in the ROSA workshop
• Access to the Red Hat OpenShift web console
  

If you haven't done so already, complete the ROSA workshop. Once you've completed it, you have a ROSA cluster ready to use in this tutorial.

2. Connect to your ROSA cluster

Use the oc command to connect to your ROSA cluster:

$ oc login https://api.rosa-cluster.tel6.p1.openshiftapps.com:6443 \
--username cluster-admin --password TUpBJ-MqyRp-EkAIG-zgQRn
Login successful.
You have access to 100 projects, the list has been suppressed. You can list all projects with 'oc projects'
Using project "default".

Alternatively, you can connect to your ROSA cluster from the OpenShift Web UI using the cluster-admin login and password. Use the rosa command to get the location of the Web UI:

$ rosa describe cluster -c rosa-cluster | grep Console
Console URL: 
https://console-openshift-console.apps.rosa-cluster.tel6.p1.openshiftapps.com

3. Create deployment configuration files

Create a secret to fetch the container image:

$ oc create secret docker-registry docker-secret \
--docker-server=https://index.docker.io/v1/ \
--docker-username=example --docker-password=xxxxxxxxx \
--docker-email=example@redhat.com
Create a new file called hxe.yaml and open it for editing in your favorite text editor. In the Web UI, you can use the web text editor.
kind: ConfigMap
apiVersion: v1
metadata:
  creationTimestamp: 2018-01-18T19:14:38Z
  name: hxe-pass
data:
  password.json: |+
    {"master_password" : "HXEHana1"}
---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: persistent-vol-hxe
  labels:
    type: local
spec:
  capacity:
    storage: 150Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/data/hxe_pv"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: hxe-pvc
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 50Gi
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: hxe
  labels:
    name: hxe
spec:
  selector:
    matchLabels:
      run: hxe
      app: hxe
      role: master
      tier: backend
  replicas: 1
  template:
    metadata:
      labels:
        run: hxe
        app: hxe
        role: master
        tier: backend
    spec:
      initContainers:
        - name: install
          image: busybox
          command: [ 'sh', '-c', 'chown 12000:79 /hana/mounts' ]
          volumeMounts:
            - name: hxe-data
              mountPath: /hana/mounts
      volumes:
        - name: hxe-data
          persistentVolumeClaim:
             claimName: hxe-pvc
        - name: hxe-config
          configMap:
             name: hxe-pass
      imagePullSecrets:
      - name: docker-secret
      containers:
      - name: hxe-container
        securityContext:
          priviliged: true
        image: "saplabs/hanaexpress:2.00.061.00.20220519.1"
        ports:
          - containerPort: 39013
            name: port1
          - containerPort: 39015
            name: port2
          - containerPort: 39017
            name: port3
          - containerPort: 8090
            name: port4
          - containerPort: 39041
            name: port5
          - containerPort: 59013
            name: port6
        args: [ "--agree-to-sap-license", "--dont-check-system", "--passwords-url", "file:///hana/hxeconfig/password.json" ]
        volumeMounts:
          - name: hxe-data
            mountPath: /hana/mounts
          - name: hxe-config
            mountPath: /hana/hxeconfig
      - name: sqlpad-container
        image: "sqlpad/sqlpad:5.8.0"
        ports:
        - containerPort: 3000

Create a file named hxe-service.yaml containing this content:

$ cat hxe-service.yaml 
apiVersion: v1
kind: Service
metadata:
  name: hxe-connect
  labels:
    app: hxe
spec:
  type: ClusterIP
  ports:
  - port: 39013
    targetPort: 39013
    name: port1
  - port: 39015
    targetPort: 39015
    name: port2
  - port: 39017
    targetPort: 39017
    name: port3
  - port: 39041
    targetPort: 39041
    name: port5
  selector:
    app: hxe

Create a file named service.yaml containing this content:

$ cat service.yaml 
apiVersion: v1
kind: Service
metadata:
  name: sqlpad
  labels:
    app: hxe
spec:
  type: ClusterIP
  ports:
    - port: 3000
      targetPort: 3000
      protocol: TCP
      name: sqlpad
  selector:
    app: hxe

4. Deploy HANA express on your ROSA cluster

Use the following command to deploy the HANA express image in a pod, and then verify your deployment:

$ oc apply -f hxe.yaml                              
$ oc apply -f hxe-service.yaml 
service/hxe-connect created
$ oc apply -f sqlpad-service.yaml                                     
service/sqlpad created

Use the oc command to get the name of the pod. Make sure 2 of 2 are ready, and that  Status in Running.

$ oc get pods                                         
NAME                   READY   STATUS    RESTARTS   AGE
hxe-5cbb578679-5ppmh   2/2     Running   0          23h

Next, verify that the services you've defined are running. In the Web UI, select Services from the  Networking menu.

Use the oc describe command to get information on your pods:

$ oc describe pods

Name:         hxe-5cbb578679-5ppmh
Namespace:    default
Priority:     0
Node:         ip-10-0-207-169.us-west-2.compute.internal/10.0.207.169
Start Time:   Mon, 13 Feb 2023 18:04:32 -0800
Labels:       app=hxe
              pod-template-hash=5cbb578679
              role=master
              run=hxe
              tier=backend
Annotations:  k8s.ovn.org/pod-networks:
                {"default":{"ip_addresses":["10.128.2.32/23"],"mac_address":"0a:58:0a:80:02:20","gateway_ips":["10.128.2.1"],"ip_address":"10.128.2.32/23"...
              k8s.v1.cni.cncf.io/network-status:
                [{
                    "name": "ovn-kubernetes",
                    "interface": "eth0",
                    "ips": [
                        "10.128.2.32"
                    ],
                    "mac": "0a:58:0a:80:02:20",
                    "default": true,
                    "dns": {}
                }]
              k8s.v1.cni.cncf.io/networks-status:
                [{
                    "name": "ovn-kubernetes",
                    "interface": "eth0",
                    "ips": [
                        "10.128.2.32"
                    ],
                    "mac": "0a:58:0a:80:02:20",
                    "default": true,
                    "dns": {}
                }]
Status:       Running
IP:           10.128.2.32
IPs:
  IP:           10.128.2.32
Controlled By:  ReplicaSet/hxe-5cbb578679
Init Containers:
  install:
    Container ID:  cri-o://fe045c2baba442d840deb2f027669932ef3bec4b623db34fcb3edf9aea2bba63
    Image:         busybox
[...]

Using the name of the pod, enter the container:

$ oc exec -it hxe-5cbb578679-5ppmh bash

kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead.

Defaulted container "hxe-container" out of: hxe-container, sqlpad-container, install (init)

hxeadm@hxe-5cbb578679-5ppmh:/usr/sap/HXE/HDB90> 

From within the pod, run the HDB info command to verify that everything's running as expected:

hxeadm@hxe-5cbb578679-5ppmh:/usr/sap/HXE/HDB90> HDB info

USER          PID     PPID  %CPU        VSZ        RSS COMMAND
hxeadm       2073        0   0.0      21080       4100 bash
hxeadm       2122     2073   0.0      20284       3388  \_ /bin/sh /usr/sap/HXE/HDB90/HDB info
hxeadm       2157     2122   0.0      41428       3368      \_ ps fx -U hxeadm -o user:8,pid:8,ppid:8,pcpu:5,vsz:10,rss:10,args
hxeadm          1        0   0.0      21076       4068 /bin/bash /run_hana --agree-to-sap-license --dont-check-system --passwords-u
hxeadm        873        1   0.0     380748      52376 hdbdaemon
hxeadm        879      873   1.4    4654040    3468832  \_ hdbnameserver
hxeadm       1301      873   0.2     675056     172892  \_ hdbcompileserver
hxeadm       1302      873   0.3     726184     199884  \_ hdbpreprocessor
hxeadm       1334      873   0.3    1734704     625876  \_ hdbwebdispatcher
hxeadm       1443      873   2.5    4888336    3798060  \_ hdbindexserver -port 39040
hxeadm       1755      873   0.2    1582972     373372  \_ hdbdiserver
hxeadm        948        1   0.0     497072      36700 hdbrsutil  --start --port 39001 --volume 1 --volumesuffix mnt00001/hdb00001 
hxeadm       1488        1   0.0     497140      37372 hdbrsutil  --start --port 39040 --volume 2 --volumesuffix mnt00001/hdb00002.
hxeadm@hxe-5cbb578679-5ppmh:/usr/sap/HXE/HDB90> 

Use hdbsql to connect to the database and enable a document store for later use:

hxeadm@hxe-5cb[...]> hdbsql -i 90 -d systemdb -u SYSTEM -p HXEHana1

Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands          
       \q to quit                         
hdbsql SYSTEMDB=> select USER_NAME, USER_DEACTIVATED from SYS.USERS;
USER_NAME,USER_DEACTIVATED
"SYS","TRUE"
"SYSTEM","FALSE"
"_SYS_STATISTICS","FALSE"
"_SYS_TABLE_REPLICAS","TRUE"
"_SYS_EPM","TRUE"
"_SYS_REPO","TRUE"
"_SYS_SQL_ANALYZER","TRUE"
"_SYS_TASK","TRUE"
"_SYS_AFL","TRUE"
"_SYS_WORKLOAD_REPLAY","TRUE"
"_SYS_DATA_ANONYMIZATION","TRUE"
"_SYS_ADVISOR","TRUE"
"_SYS_PLAN_STABILITY","TRUE"
"_SYS_DI_TO","TRUE"
"_SYS_DI","TRUE"
"_SYS_DI_SU","TRUE"
"_SYS_DI_CATALOG","TRUE"
"_SYS_DI_BI_CATALOG","TRUE"
"_SYS_DI_CDS_CATALOG","TRUE"
"_SYS_DI_SEARCH_CATALOG","TRUE"
20 rows selected (overall time 30.560 msec; server time 317 usec)

Now you must verify your external IP address to connect to SAP HANA express edition. Get your external IP addresses from the SQLPAD service:

$ oc get services
NAME          TYPE           CLUSTER-IP      EXTERNAL-IP                            PORT(S)                                   AGE
hxe-connect   ClusterIP      172.30.38.85    <none>                                 39013/TCP,39015/TCP,39017/TCP,39041/TCP   3m58s
kubernetes    ClusterIP      172.30.0.1      <none>                                 443/TCP                                   3d
openshift     ExternalName   <none>          kubernetes.default.svc.cluster.local   <none>                                    3d
sqlpad        ClusterIP      172.30.95.223   <none>                                 3000/TCP                                  3m35s

5. Create a route

In the Web UI, select Routes from the Networking menu and create a new route for your sqlpad service.

Click the URL in the Location column of the Routes listing to access the SQLPAD app.

Alternatively, you can use the oc command to view the URL:

$ oc get route
NAME           HOST/PORT [...]                     
route-sqlpad   route-sqlpad-default.apps.rosa-cluster.tel6.p1.openshiftapps.com [...]

6. Log in to SAP HANA express edition

Navigate to the URL in a web browser, and click the Sign in button to create an administrator account.

Sign in to sqlpad using your new admin account, and use the Connections option in the top right corner to connect to your database. Click on New connection and fill in the details.

• Connection name: HANA
• Driver: SAP HANA
• Host/Server/IP Address: 172.30.38.85
• Port: 39041
• Database Username: SYSTEM
• Database Password: HXEHana1
• Tenant: HXE

Click on the connection to join the SAP HANA express edition service.

Paste this command in sqlpad:

select USER_NAME, USER_DEACTIVATED from SYS.USERS;

SAP HANA express edition on ROSA

SAP HANA express edition is available for free for application development, prototyping, and production use with up to 32 GB of RAM. SAP supports SAP HANA express edition in containers, and has supplied a SAP HANA Express container image on Dockerhub. In this tutorial, you've successfully started SAP HANA express edition on Red Hat OpenShift Service on AWS (ROSA), and you've connected to it from a containerized sqlpad web app.

Red Hat OpenShift is designed so developers can deploy apps on Kubernetes without needing to learn Kubernetes. Like the sqlpad web app, you can develop your own apps that connect to SAP HANA without having to worry about the infrastructure, and you can use the tools you want with a consistent user experience.