Use SAP HANA, express edition to help Thomas get started with help from other developers in the community using Kubernetes, text analytics, graph and geospatial.
You will learn
- How to easily deploy SAP HANA, express edition and SQLPAD from Docker into Google Kubernetes Cluster
- How to configure persistency and services in Kubernetes for the two containers in the same pod
- How to access the database from SQLPAD
- Explore advanced analytics features in SAP HANA, including the document store, geospatial, graph and linguistic text search functions
Details
Let us help our fellow developer Thomas to learn SAP HANA. On his journey he will find other developers of the community with similar interests, explore who is learning from whom and who are close by. To make it fun and useful we do this all with SAP HANA express edition.
This tutorial will use Google Kubernetes Engine to deploy SAP HANA, express edition and an SQL client. If you do not want to use this method, you can check other available options to download or install SAP HANA, express edition.
How do we help Thomas?
Like most developers, Thomas wants to stay on top of the latest technologies. The first step is to get started with tutorials, like this one. The second step he wants to take is connect with other developers and experts in the SAP Community to share experiences and learn together.
Fellow developers from all around the world connect daily to exchange information. We will find out if they share our interest for SAP HANA and related topics by using text analytics on their opinions in the community.
Thanks to the multiple engines in SAP HANA, we will combine text analytics with graph analytics to find out how community members are connected.
Finally, we will use the geospatial capabilities in SAP HANA to find out developers closer to Thomas’ location in Munich.
Step 1: Log in to the communityThis tutorial has validations to keep track of completion and make sure you are on track after important steps.
Sign in or register by clicking on the person icon in the top right corner. If you are registering for the first time, all you need is an email address or social media account.
![Log in to Community](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/zoomlogin.gif)
Use your email address or social media.
![Log in to Community](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/community.png)
This tutorial works either with the free trial account in Google Cloud Platform or with the paid tier.
Additional options to download or install SAP HANA, express edition for free are listed at
developers.sap.com
. The options forDatabase Server Only
and an SQL client likeDBeaver
, Visual Studio.
If you decide to use your own instance, mark the first 9 steps as done, and continue to step 10.
If you have not already, follow the steps to sign in to Google Cloud Platform. Even if you are eligible for the free trial, you will be required to enter credit card details for validation.
![Accept terms](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/cc2.png)
DoneLog on to answer questionStep 3: Create a clusterHere is more information on how the free trial works and how to disable billing.
Navigate to Kubernetes Engine -> Clusters.
![Navigate to Clusters](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/1n.png)
Click Create a Cluster.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/1.png)
Leave Standard Cluster marked.
![Navigate to Clusters](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/std.png)
Change the amount of nodes to 1 and choose the configuration with 4 vCPUs
and 15 GB memory.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/2.png)
Click Advanced edit.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/3.png)
Change the image type to Ubuntu and the book disk type to SSD persistent disk.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/4.png)
Click Save.
Review the options. Then Click create.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/6.png)
DoneLog on to answer questionStep 4: Connect to the clusterWhat is going on?
You are creating a Kubernetes cluster with computing capacity for SAP HANA, express edition and a web SQL client, SQLPAD, in a single node. The images for these containers will be pulled from the public
Docker
repository and store. The containers will be connected to each other and have some ports exposed to the internet.
Deployment takes a couple of minutes. Refresh the browser periodically and wait until the cluster is ready.
Once the cluster has been created successfully, click Connect.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/8.png)
Click Run in cloud shell.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/9.png)
Once the console is open and the command has been copied, press Enter.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/10.png)
Copy the following command into the command line, and press Enter to execute:
sshCopytouch hxe.yaml
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/11.png)
Execute the following command:
sshCopyedit hxe.yaml
This will open a new tab with an editor. Agree to open in editor.
![Create a cluster](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/20.png)
Use the copy function in the top right corner for the file below and paste it into the open file:
textCopykind: 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:
storageClassName: manual
capacity:
storage: 150Gi
accessModes:
- ReadWriteOnce
hostPath:
path: "/data/hxe_pv"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: hxe-pvc
spec:
storageClassName: manual
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
image: "store/saplabs/hanaexpress:2.00.033.00.20180925.2"
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"
ports:
- containerPort: 3000
---
apiVersion: v1
kind: Service
metadata:
name: hxe-connect
labels:
app: hxe
spec:
type: LoadBalancer
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
---
apiVersion: v1
kind: Service
metadata:
name: sqlpad
labels:
app: hxe
spec:
type: LoadBalancer
ports:
- port: 3000
targetPort: 3000
protocol: TCP
name: sqlpad
selector:
app: hxe
The file will be automatically saved.
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/30.png)
You will create a secret with your credentials to log in to Docker and pull the images. If you do not yet have an account, create one by signing up with Docker.
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/docker.png)
Go back to the Cloud Shell. You will use the console below the editor.
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/cloudshell.png)
Use your registered email address, account ID and password in Docker to replace the placeholders in the command below:
textCopykubectl create secret docker-registry docker-secret --docker-server=https://index.docker.io/v1/ --docker-username=<<USER_NAME>> --docker-password=<<PASSWORD>> --docker-email=<<EMAIL>>
You should get a message saying secret docker-secret
created.
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/12.png)
You will now use the deployment file (hxe.yaml
) to create a variety of artifacts. Use the following command to create the artifacts based on the yaml
file.
kubectl create -f hxe.yaml
You should get confirmation that the deployment, persistency and additional artifacts have been created.
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/13.png)
You can check the progress of the creation with the following command:
sshCopykubectl describe pods
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/14.png)
Give it a couple of minutes for the database to start. Here is some reading you can do in the meantime:
What is going on?
Kubernetes is an open source platform to manage containerized workloads and services. In this case, you have used two containers, one for SAP HANA, express edition and the other for SQLPAD. SAP HANA needs persistency and you want that persistency to remain accessible even if the container disappears.
This is why you created a persistent volume and attached a portion of it to a container using a claim. With the proper mechanisms, this would also allow you to restore the log and data files in the database from an upgraded version of the Docker container.
You also want SAP HANA to have some ports accessible from outside the Kubernetes environment. In Kubernetes language, your containers are deployed in a pod. Physically, in this example, these are the two Docker containers in a virtual machine. Within the pod, containers can connect to each other but you want to be able to access SQLPAD from the external network.
Services provide a mechanism to define a policy to access the pod from the outside world and expose specific ports for each of the applications. For example, SAP HANA express edition will be listening to JDBC connections on port 39041 while SQLPAD will be listening for web requests on port 3000
Use the following command to get the name of the pod that has been created:
sshCopykubectl get pods
Use the name of the pod in the command below to access the database:
sshCopykubectl exec -it <<POD>> bash
For example:
![Saved file](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/15.png)
Congratulations! You are now connected to your instance of SAP HANA, express edition.
DoneLog on to answer questionStep 8: Log in and configure the databaseYou will now perform some configurations on your database for the advanced analytic features to work.
First import the file with data for the document store using the following command:
sshCopy wget -O ./work/json.csv https://raw.githubusercontent.com/SAPDocuments/Tutorials/master/tutorials/gcp-hxe-evt-gke-2/json.csv
![SQL command](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/17.png)
Log in to the SQL console using the following command:
sqlCopyhdbsql -i 90 -d systemdb -u SYSTEM -p HXEHana1
Use the following command to enable the document store:
sqlCopyalter database HXE add 'docstore';
Use quit
to exit the SQL command line and exit
.
![SQL command](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/16.png)
Finally, type exit to leave the container.
![SQL command](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/18.png)
Use the following command to get the services that expose your connections to the database and the SQL client:
textCopykubectl get services
Copy the external IP address from the previous command for the service called sqlpad
.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/110.png)
Open a new tab. Paste the IP address followed by port 3000.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/32.png)
Hit Enter and click on Sign up.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/23.png)
Use the following credentials to set up the administration access:
Password | |
---|---|
admin@email | HanaRocks |
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/24.png)
Repeat the credentials to log in.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/50.png)
Click admin > Connections
on the right-upper corner.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/60.png)
Click New Connection.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/7.png)
Call it HANA
and choose the SAP HANA driver.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/80.png)
Go back to the cloud console. Copy the external IP address for the service hxe-connect
.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/90.png)
Paste it into the Host Server IP address
and complete the rest of the fields to access your tenant database.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/100.png)
Click Save. Then click New Query.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/25.png)
You should see the schemata on the left side panel indicating the connection has been successful.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/26.png)
DoneLog on to answer questionStep 10: NoSQL time! Create a JSON document storeErrors? Make sure you are using the external IP address for SAP HANA and that there are no spaces before or after the IP address or port.
In SQLPAD, statements need to be executed one by one. You can do this by selecting the statement first and then pressing run.
Create a collection first using the following command:
sqlCopycreate collection hints;
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/33.png)
You are using the SYSTEM user and its schema for convenience purposes. The recommended approach for productive databases is to create development users and deactivate the SYSTEM user. More information in the security guide
Use the following statement to import data into your document store:
sqlCopyIMPORT FROM CSV FILE '/usr/sap/HXE/HDB90/work/json.csv' INTO system.hints WITH batch 2 threads 10 RECORD DELIMITED BY '\n' FIELD DELIMITED BY ',' optionally enclosed by '""' ERROR LOG '/usr/sap/HXE/HDB90/work/load.err';
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/a1.png)
What is going on?
Document store allows you to store all of the information related to the same record in the same document. These documents do not have a predefined format or number of fields like a table.
Here is a sample document you inserted in the document store with the import command:
Document stores do not have tables or schemas, they use collections and documents. Documents in the same collection may have different structures and data types. This is particularly useful when relationships across documents are not too relevant and data structure needs to be flexible. For example, data for user accounts where fields like the phone number may not be entered and may not be stored at all. In this same scenario, there is no need for foreign keys and relations between the user records. This type of database is also referred to as
NoSQL
because data operations are not performed using SQL in other platforms. However, SAP HANA uses SQL for CRUD operations in JSON document store. For more information about the document store in SAP HANA, refer to the help
Use the following statement to complete the validation below:
sqlCopy select TO_NVARCHAR("hint"), TO_NVARCHAR("office") from hints where "name" = 'Maria';
Where is Maria located?MadridWaldorfBrazilDenmarkSubmit Answer×Log on to answer questionFree resources, like this tutorial, are a great way to get started. People in the community with more experience and willing to help are a great complement to a learning journey.
You will select the people whose experience is 2 years or more. You will also move those records into a columnar table so you can perform advanced analytics only available in the columnar store.
Create the columnar table first:
sqlCopycreate column table "DEVS"
(
"DEVNAME" nvarchar(100) PRIMARY KEY,
"LEARNS_FROM" nvarchar(100),
"HINT_TEXT" text FAST PREPROCESS OFF ASYNC,
"CITY" nvarchar(100),
"LON_LAT" nvarchar(200)
);
Note the columnar table has a text index on the field
HINT_TEXT
.
Insert the data from the documents store into the columnar table, filtering out people with tenure lower than 1 year:
Copy
insert into "DEVS"
select "name", "learns_from", "hint", "office", , "geolocation"
from hints where to_bigint("tenure") > 1
Count the inserted records in the new columnar table:
sql
Copy
select count(*) from "DEVS";
Insert the result of the previous SQL command in the box below to complete the following validation:
Submit Answer
×
Log on to answer questionStep 12: Use linguistic text search to find developers who can help
There are plenty of different ways of working with SAP HANA: some are interested in the analytics, some keep it running smoothly through system and database administration, and others use it to create data-driven applications. You are looking for those people who like to develop applications.
You will use a linguistic text search to find out who has said anything related developing applications.
SQL
Copy
select "DEVNAME", TO_NVARCHAR("HINT_TEXT"), "LEARNS_FROM"
from "DEVS"
where contains(hint_text, 'develop', linguistic)
Notice how the linguistic search brings all records containing the verb develop
as a stem.
![Connect to sqlpad](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/34.png)
Other functions such as fuzzy search, text mining and sentiment analysis can be applied using the text engine in SAP HANA. To learn more about linguistic and other types of search, visit the official documentation
You will use these results to create a table to show who learns from who. This table will be used to create a graph workspace. Create it using the following SQL statement:
sql
Copy
create column table learning_relation (
"ID" int generated always as identity(start with 10 increment by 1) unique not null,
"SOURCE" NVARCHAR(100) not null,
"TARGET" NVARCHAR(100) not null
);
Insert the records into the new table:
sql
Copy
insert into learning_relation
(source, target)
select learns_from, devname from devs
How many records were inserted into the new table?
Submit Answer
×
Log on to answer questionStep 13: Use Graph to find out who learns from who
Now that you have a table with learning relations and developers in the community, you can find out how these people are related to each other. One of the ways to represent a network of people is by using a graph database.
In SAP HANA, graphs are represented by vertices (in this example, developers) and edges (the connections between them, taken from the field learns_from
).
![Graph](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/a3.png)
Find more information about the graph data model in the SAP HANA reference and how to apply to text analytics.
Create a graph workspace to define a graph in terms of tables and columns:
sql
Copy
CREATE GRAPH WORKSPACE "HANA_GRAPH"
edge table "LEARNING_RELATION"
SOURCE COLUMN "SOURCE"
TARGET COLUMN "TARGET"
KEY COLUMN "ID"
VERTEX TABLE "DEVS"
KEY COLUMN "DEVNAME";
There are some known algorithms to apply on a graph. One of them is the strongly connected components
. As this is a directed graph (the learns_from
establishes has a direction from one node to the other), you can establish an index with the most strongly connected members.
In this example, you will find out who are the developers more people are connected to by learning from them either directly or indirectly.
One of the methods to execute calculations on graph workspaces is through a calculation node. These can be created both graphically in SAP Web IDE for SAP HANA and using SQL and an XML definition.
sql
Copy
CREATE CALCULATION SCENARIO "HANA_GRAPH_CS" USING '
<?xml version="1.0"?>
<cubeSchema version="2" operation="createCalculationScenario" defaultLanguage="en">
<calculationScenario schema="SYSTEM" name="HANA_GRAPH_CS">
<calculationViews>
<graph name="cs_node" defaultViewFlag="true" schema="SYSTEM" workspace="HANA_GRAPH" action="GET_STRONGLY_CONNECTED_COMPONENTS">
<expression>
</expression>
<viewAttributes>
<viewAttribute name="DEVNAME" datatype="string"/>
<viewAttribute name="COMPONENT" datatype="int"/>
</viewAttributes>
</graph>
</calculationViews>
</calculationScenario>
</cubeSchema>
' WITH PARAMETERS ('EXPOSE_NODE'=('cs_node', 'HANA_GRAPH_CS'));
You can now use the calculation scenario. The highest index returned by the algorithm indicates the strongest connected component.
sql
Copy
SELECT * FROM "HANA_GRAPH_CS" ORDER BY "COMPONENT" DESC;
Submit Answer
×
Log on to answer questionStep 14: Find the closest geographical location
So far, you have found the developers with enough years of experience, interest in developing applications and strongly connected. Find out who is closer to Thomas so they can meet at the next community event.
Thomas is located in Munich, Germany. The geolocation is longitude: 11.569299 latitude: 48.145130
![Geolocation](https://developers.sap.com/tutorials/hxe-k8s-advanced-analytics/_jcr_content.github-proxy.1544319430.file/geo.png)
Use the following query to calculate distance to Thomas’ location:
sql
Copy
select devname, st_geomFromText( 'Point( 11.569299 48.145130 )', 4326).st_distance(st_geomFromtext( devs.lon_lat, 4326), 'meter') / 1000 as DISTANCE_KM
from "DEVS"
where contains(hint_text, 'develop', linguistic)
order by distance_km asc
Submit Answer
×
Log on to answer questionStep 15: Who helps me get started?
Congratulations! Here is how you can get started with any topic in SAP HANA and more:
developers.sap.com
: Just like this tutorial, there are plenty of free downloads and missions to help you with different topics. You can learn new topics like machine learning, more about geospatial or switch to a full SAP HANA, express edition image with XS Advanced, to create cloud native applications with micro-services- The community: Fellow developers write about their experiences and recommendations in blog posts and many are willing to help and answer questions in the Q&A
- Community events: You can also check the closest events to meet other developers in the events section in the community.