Skip to Content

Help Thomas Get Started with SAP HANA Cloud

Use SAP HANA Cloud to help Thomas get started with help from other developers in the community using in-database text analytics, graph algorithms and geospatial processing.
You will learn
  • How to start with your own free SAP HANA Cloud instance in the SAP Business Technology Platform
  • How to leverage SAP Business Application Studio to develop with advanced analytics features in SAP HANA Cloud, including JSON table, geospatial, graph and text search functions
jung-thomasThomas JungJanuary 4, 2022
Created by
Lsubatin
October 1, 2019
Contributors
jung-thomas
Lsubatin

Ready to explore SAP HANA Cloud? As a fun exercise, you can first help our fictional developer, Thomas, work with other developers in the community to create his own database schema on SAP HANA Cloud to use text, graph and geospatial processing inside the database.

Get to know SAP HANA Cloud

SAP HANA Cloud is a complete database and application development platform. It lets you use advanced data processing capabilities — text, spatial, predictive, and more — to pull insights from all types of data.

By combining in-memory storage with columnar store, data operations are performed faster than in a traditional database with a disk-based storage. SAP HANA is also translytical, which means that developers can perform both transactional and analytical operations from the same structure, in real time, and without creating additional copies of the data such as materialized views.

This tutorial is based on an SAP HANA Cloud instance that you get in a free SAP BTP trial account or SAP BTP free tier.

If you do not want to use this method, you can check other available options to download or install SAP HANA, express edition, and corresponding tutorials.

How do we help Thomas?

Like most developers, Thomas wants to stay on top of the latest technologies. His first step is to get started with free tutorials, like this one. The second step is to connect with other developers and experts in the community to share knowledge and learn together.

Fellow developers from all around the world connect daily to exchange information. And we are going to find out if they share Thomas’ interest for SAP HANA Cloud and related topics by using text processing on their opinions in the community.

Thanks to the multiple engines in SAP HANA Cloud, we will also combine text processing with graph algorithms to find out how community members are connected.

Finally, we will use the geospatial capabilities in SAP HANA Cloud to find developers closer to Thomas’ location in Munich.

How do we help Thomas
  • Step 1

    This tutorial uses validations to track completion and make sure you are all set after finishing important steps.

    1. Sign in or register by clicking on the person icon in the top right corner. If you’re registering for the first time, all you need is an email address or social media account.

      Log in to Community
    2. Use your email address or social media account.

      Log in to Community
  • Step 2

    While the free tier is the go-to option for individuals and enterprises wanting to try out SAP BTP, it is not yet available in all regions of the world. Therefore we suggest you start by trying to use the Free Tier, but if it is unavailable to you; you can always still use the Free Trial for SAP HANA Cloud access. However both options are described in the following tutorial group. If you are completely new to SAP BTP and have neither a Free Trial nor a Free Tier account, you should go through this tutorial group in order to get started: Set Up an SAP BTP Account for Tutorials. Otherwise you can skip ahead to the next step.

  • Step 3

    Now that you have an SAP BTP account (either Free Tier or Free Trial) you need to add SAP HANA Cloud to it. While both free environments have access to SAP HANA Cloud, neither create an SAP HANA Cloud database instance by default. If you’ve never setup SAP HANA Cloud in your SAP BTP Account, you must follow the steps in the list tutorial group in order to get started: Provision an Instance of SAP HANA Cloud, SAP HANA Database.

    1. Complete the tutorial steps in Provision an Instance of SAP HANA Cloud, SAP HANA Database This wizard used in the above tutorial will walk you through the process of creating an SAP HANA Cloud instance. Just one note as you go through this guided tour: Make sure that in the “Advanced Settings” part of the setup, that you select “Allow all IP addresses” in the “Connections” setting. This setting will allow you to develop against your SAP HANA Cloud using a variety of external development tools.

      Allow All IP addresses
    2. After completing the previous step, you should now have a new SAP HANA Cloud instance created in the SAP BTP trial or free tier.

      HANA Cloud Instance
    3. Once the SAP HANA Cloud instance is created, take note of the admin user needed to connect to the database. This will be needed in subsequent steps in this tutorial.

    4. Finally it is important to take note that the SAP HANA Cloud instance in both the free tier and free trial shuts down at the end of each day automatically to save costs from unused systems. Therefore you must return to this SAP HANA Cloud administration screen each day you want to use SAP HANA Cloud and choose to start the system from the Action menu. If you forget to restart the instance, you will receive HANA connection errors whenever you try to interact with it in later steps.

      HANA Cloud stopped
    5. As an optional step if you are completely new to the SAP HANA Cloud environment, you might want to consider also going through this tutorial: Tools to Manage and Access the SAP HANA Cloud, SAP HANA Database in order to familiarize yourself with the various tools that can be used to manage and develop with SAP HANA Cloud.

  • Step 4
    1. Set Up SAP Business Application Studio for development (if you’ve never used SAP Business Application Studio) by following this tutorial

    2. Creating SAP Business Application Studio Dev Space - Dev spaces are like isolated virtual machines in the cloud that can be quickly spun-up. Each dev space type contains tailored tools and pre-installed run-times for a target scenario such as SAP Fiori or mobile development. This simplifies and saves time in setting up the development environment as there’s no need to install anything or upgrade; letting developers focus on their business domain, anytime, anywhere. Go to your SAP Business Technology Platform subaccount and click the Services -> Instances and Subscriptions option.

      Subscriptions
    3. Locate the SAP Business Application Studio entry and click Go to Application

      Go To Application
    4. Choose Create Dev Space. Please NOTE: In the SAP BTP trial and free tier you are limited to only two Dev Spaces and only one can be active at a time. If you have performed other tutorials, you might already have reached your maximum. In that case you might have to delete one of the other dev spaces in order to continue with this tutorial.

      Create Dev Space
    5. Enter HANA for your dev space name and choose SAP HANA Native Application as the kind of application you are creating.

      Choose Dev Space type
    6. The Dev space will automatically be configured with the most common tools you need for the type of application you choose. However you can also choose additional, optional extensions. For example you might want to add the optional SAP HANA Performance Tools to your HANA Dev Space.

      Dev Space Extensions
    7. Once all selections are completed, press Create Dev Space

      Create Dev Space
    8. The Dev Space will then begin starting and the process will take a minute or so as your cloud environment is being created

      Dev Space Creating
    9. Once the Dev Space reaches the green status of RUNNING, you can click on the name of the Dev Space and it will load into the editor within your browser

      Load Dev Space
    10. You’ll be redirected to your newly created SAP Business Application Studio Dev Space. We recommend you bookmark this URL so it’s easier for you to access this dev space of your SAP Business Application Studio in the future

      New Dev Space
  • Step 5

    Before we create our SAP HANA project, we want to do a few more one-time configuration steps to prepare the Dev Space

    1. In the left side of the Business Application Studio click on the Cloud Foundry targets icon

      CF Not Set
    2. Now in the Cloud Foundry Targets window you can expand either Service or Applications and then click on the Logon icon to continue the configuration process

      Login Required
    3. The command window will then open at the top of the SAP Business Application Studio. The first input will prompt you for the API endpoint

      API Endpoint
    4. The default value proposed is likely the correct value, but if you need to confirm; the value can be found in the SAP BTP cockpit at the Subaccount level

      API Endpoint at Subaccount Level
    5. Press Enter to confirm your input of the API endpoint. The next input field will ask you for the email address you used to create your SAP BTP trial account

      Email
    6. The next input will ask you for your SAP BTP trial account password

      Password
    7. The next input will ask you for your Organization. In most situations you will have a single choice. But like the API endpoint earlier, if you need to confirm the correct value it will be displayed in the top navigation of the SAP BTP cockpit

      Organization
    8. The final input will ask you for your Space. If you choose the endpoint API and Organization correctly, then you should have a single option of dev

      Space
    9. Upon completion of all the inputs, you should see that the Organization and Space have been set and you will see any service instances or application instances from the target space.

      Org and Space Set Correctly
  • Step 6

    You can now clone an existing GitHub repository into your workspace. This repository contains the artifacts to create a schema and a document store and add data into it.

    1. Return to the Explorer view and choose Clone Repository from the SAP Business Application Studio Welcome screen

      Clone From Git
    2. Enter the following URL and press Enter. https://github.com/SAP-samples/hana-cf-get-started-trial

      Confirm Clone
    3. If asked for the project location, choose the projects folder and then press Select Repository Location

      Choose Repository Location
    4. The clone log will be shown in the bottom of the screen. Upon completion, please press the Open button in the dialog in the bottom right.

      Complete Clone

    At any time if you get stuck, you can view the completed solution in GitHub as well at this URL: Project Solution

  • Step 7

    The cloned repository contains files with design-time definitions to create artifacts in the database using the SAP HANA Deployment Infrastructure, or HDI for short. HDI provides a service that enables you to deploy database development artifacts to so-called containers and to build run-time database objects. You can think of HANA container as an isolation that contains a single database schema for all objects.

    Artifacts in the current project will be translated into a physical database schema and into a JSON document store.

    1. Expand the SAP HANA PROJECTS window and press the Bind button

      Bind
    2. In the dialog that appears near the top of the screen, choose the Create a new service instance

      Create New Service Instance
    3. Accept the default service instance name

      Default Name
    4. Press Enter. It will take a minute or so to complete the creation

      Create Service
    5. Once created, return to the SAP HANA PROJECTS view. Press the Deploy button.

      Press Deploy

      What’s going on?

      The design-time definitions will be deployed by HANA Deployment Infrastructure - a service layer in SAP HANA.

      With HDI the physical artifacts will be created and managed by technical users in corresponding container. You would normally only deploy database artifacts into these containers through design-time definitions, as the physical build of database artifacts is the role of HDI.

      Later in this exercise, you will create some more database objects in the database directly for learning purposes. But the recommendation is to use HDI and build database objects by modeling design-time artifacts and their deployment to HDI containers.

      You will see the build log in the console. Wait until the build has finished successfully.

      Deploy Log
    6. Press the Open HDI container button.

      Open HDI container

      The database explorer will start loading in a new browser tab. If asked to add a database connection, click No.

    7. You can see your container with a schema and the table COMMUNITY. They were generated from definitions in the cloned repository.

      Database Explorer Tables
  • Step 8
    1. Open an SQL console.

      SAP Web IDE
    2. Paste the following statements to insert new JSON documents into your table and run

      run
      the statements.

      The statements may be marked with errors by the editor. You can ignore the errors.

      SQL
      Copy
      insert into "COMMUNITY" (DATA)  values('{"name" : "Sol" , "hint" :"I love using SAP HANA to develop applications", "learns_from" :"Sam", "office" :"Toronto", "tenure" :17, "geolocation" : "Point( -79.380186 43.647944 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Sam", "hint" :"I like developing in different languages and SQLScript", "learns_from" :"Sol", "office" :"Walldorf", "tenure" :3, "geolocation" : "Point( 8.636789 49.29487 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Jose", "hint" :"I use SAP Cloud platform to deploy cloud-native applications", "learns_from" :"Sol", "office" :"Palo Alto", "tenure" :5, "geolocation" : "Point( -122.146603 37.398989 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Charlotte", "hint" :"Developing apps with SAP HANA has been a game changer. I used to need several databases, now I only need one", "learns_from" :"Sam", "office" :"Australia", "tenure" :6, "geolocation" : "Point( 151.209092 -33.834509 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Maria", "hint" :"I am a coder. In my country, we say developing with SAP HANA is muito legal", "learns_from" :"Charlotte", "office" :"Sao Leopoldo", "tenure" :3, "geolocation" : "Point( -51.148393 -29.796256 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Wei", "hint" :"System administrator here, excited to learn you technologies", "learns_from" :"Sam", "office" :"Beijing", "tenure" :12, "geolocation" : "Point( 121.601862 31.20235 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Hiroshi", "hint" :"I developed many applications with both HANA and SQL Anywhere. I like both", "learns_from" :"Sol", "office" :"Fukuoka", "tenure" :8, "geolocation" : "Point( 130.399091 33.592314 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Saanvi", "hint" :"Developing apps from bangalore to the world", "learns_from" :"Sol", "office" :"Bangalore", "tenure" :7, "geolocation" : "Point( 77.637116 12.972402 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Rick", "hint" :"My team plays with databases regularly. HANA is one of the favorites", "learns_from" :"Maria", "office" :"Irving", "tenure" :11, "geolocation" : "Point( -96.938460 32.873744 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Ann", "hint" :"I like meeting other fellow coders", "learns_from" :"Casey", "office" :"San Ramon", "tenure" :1, "geolocation" : "Point( -121.961661 37.766586 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Hugo", "hint" :"I had never developed such cool apps before", "learns_from" :"Maria", "office" :"Monterrey", "tenure" :2, "geolocation" : "Point( -100.353643 25.64757 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Sofia", "hint" :"I connected SAP Analytics Cloud to HANA", "learns_from" :"Hiroshi", "office" :"Copenhagen", "tenure" :1, "geolocation" : "Point( 12.589387 55.710640 )" }');
      insert into "COMMUNITY" (DATA) values('{"name" :"Muhammed", "hint" :"I used to prefer Excel spreadsheets but Lumira changed that for me", "learns_from" :"Charlotte", "office" :"Ra anana", "tenure" :11, "geolocation" : "Point( 34.882402 32.201905 )" }');
      

      You should get success messages as in the following example:

      SAP Web IDE

      What’s going on?

      Tables with JSON columns allow 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 typical relational table.

      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 it stores Not-only Structured data. SAP HANA uses SQL for CRUD operations in JSON columns.

    3. The following statement demonstrates a use of the JSON Object Expression in the select statement. Run this statement to complete the validation below:

      SQL
      Copy
      SELECT *
      	FROM JSON_TABLE(COMMUNITY.DATA, '$'
      	COLUMNS
          (
              LOCATION NVARCHAR(200) PATH '$.office',
              NAME NVARCHAR(200) PATH '$.name'
          )
      	) AS JT where NAME = 'Maria'
      

      You can clear the statements before entering new ones in SQL console (recommended).
      Alternatively select (highlight) the statements you want to execute.

    Where is Maria located?

  • Step 9

    Free resources, like this tutorial, are a great way to get started. People in the community with more experience are often willing to help you on your learning journey. For our developer, Thomas, choosing people with more experience means that he can get up to speed quickly.

    You will select people whose experience is 2 years or more. You’ll also need to move those records into a columnar table so that you can perform advanced analytics that are only available in the columnar store.

    1. Create the columnar table first by returning to the SAP Business Application Studio editor view and creating a file named DEVS.hdbtable in the db/src folder.

      DEVS table

      Here is the content for this file.

      SQL
      Copy
      column table "DEVS"(
        "DEVNAME" nvarchar(100) PRIMARY KEY,
        "LEARNS_FROM" nvarchar(100),
        "HINT_TEXT" nvarchar(1000),
        "CITY" nvarchar(100),
        "LON_LAT" nvarchar(200)
      )
      

      Note column table definition in the statement

      SAP HANA creates columnar tables by default. The column keyword is optional, but is used in the example to remind about the native column-based storage of tables in SAP HANA.

    2. Save you content and press Deploy in the SAP HANA PROJECTS window.

      Upon completion of the build, return to the database explorer view of your HDI container and refresh the Tables selection. You should see the new table listed under TABLES:

      SAP Web IDE
    3. Insert the data from the documents store into the columnar table, filtering out community members with tenure below 1 year:

      SQL
      Copy
      insert into DEVS
      SELECT NAME, LEARNS_FROM, HINT, OFFICE, GEOLOCATION
      	FROM JSON_TABLE(COMMUNITY.DATA, '$'
      	COLUMNS
          (
              LOCATION NVARCHAR(200) PATH '$.office',
              NAME NVARCHAR(200) PATH '$.name',
              LEARNS_FROM NVARCHAR(200) PATH '$.learns_from',
              HINT NVARCHAR(200) PATH '$.hint',
              OFFICE NVARCHAR(200) PATH '$.office',
              GEOLOCATION NVARCHAR(200) PATH '$.geolocation',
              TENURE NVARCHAR(30) PATH '$.tenure'
          )
      	) AS JT where to_bigint(TENURE) > 1
      
    4. 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:

  • Step 10

    There are plenty of different ways to work with SAP HANA. Some developers are interested in its analytics, some keep it running smoothly through system and database administration, and others use it to create data-driven applications. In order to help Thomas, you’ll need to look for people who like to develop applications.

    You’ll use a contains text search to find out who has said anything related to developing applications.

    SQL
    Copy
    select DEVNAME, TO_NVARCHAR(HINT_TEXT) as "testimony", LEARNS_FROM
    from DEVS
    where contains(HINT_TEXT, '%develop%')
    
    Connect to SQLpad

    You will use these results to create a table (as a new hdbtable artifact as you did earlier) to show who learns from whom. This table will be used to create a graph workspace.

    Create it using the following syntax in a new hdbtable file – remember to deploy your db module again:

    SQL
    Copy
    column table LEARNING_RELATION(
    	ID int NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1),
    	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?

  • Step 11

    Now that you have a table populated with learning relationships and expert 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

    Find more information about the graph data model in the SAP HANA reference.

    Create a graph workspace to define a graph in terms of tables and columns by returning to the SAP Business Application Studio editor and creating a file with the extension hdbgraphworkspace (and be sure to Deploy again):

    SQL
    Copy
    graph workspace HANA_GRAPH
      edge table LEARNING_RELATION
        source column SOURCE
        target column TARGET
        key column ID
      vertex table DEVS
        key column DEVNAME
    

    You can preview the graph by navigating into Graph Workspaces, selecting the graph you have just created and choosing View Graph.

    Graph

    Here’s the graph:

    Graph
  • Step 12

    So far, you’ve found the most connected developers with two or more years of experience, plus an interest in developing applications. Now find out who is closest to Thomas, so that they can meet him at the next community event.

    Thomas is located in Munich, Germany. The geolocation of the city is:

    Longitude11.57548
    Latitude 48.13702
    Geolocation

    Use the following query to calculate distance to Thomas’ location:

    SQL
    Copy
    select DEVNAME,
    round(st_geomFromText('Point( 11.57548 48.13702 )', 4326).st_distance(st_geomFromtext(LON_LAT, 4326), 'kilometer'),0) as DISTANCE_KM
    from DEVS
    where contains(HINT_TEXT, '%develop%')
    order by DISTANCE_KM
    

    Who is in the closest office?

  • Step 13

    Congratulations on helping Thomas find and collaborate with other developers!

    If you are ready to explore more features with your own local copy of SAP HANA, you can also download SAP HANA, express edition. SAP HANA, express editions is free up to 32 GB of RAM, even for productive use. You can:

    Or you can continue to use the free SAP HANA Cloud trial as a part of your overall SAP BTP trial or free tier.

    Here’s how you can get started with any developer-focused topic in SAP HANA and more:

Back to top