In this CodeLab we will launch a new instance of SAP HANA, express edition with XS Advanced, the application development platform for SAP HANA based on Cloud Foundry. We will also securely deploy the Open Source query tool, SQLPad, on Google App Engine, and connect it to SAP HANA.

What You'll Learn

SAP HANA, express edition with XS Advanced must be deployed into a Google Cloud Platform project.

Creating a Project

To create a project you must first log in to the Google Cloud Platform Console with your Google Account.

If you are logging in for the first time you will need to accept the terms of service:

Now create a new project by navigating to the project creation form:

Click "Create" to complete this step.

Selecting a Project

Click the "Select a project" button in the Google Cloud Platform Console header:

Choose your project from the list:

Cloud Launcher allows us to launch software on automated infrastructure. We can use it to launch SAP HANA, express edition with XS Advanced. In Cloud Launcher this is known as SAP HANA, express edition (server + applications). The Launcher will create the appropriate virtual machine, disk, and network configuration.

Use the search bar in Google Cloud Platform Console to easily get started. Search for "SAP HANA Launcher" and choose "SAP HANA, express edition (server + applications)" from the list:

This will give us cost estimates and information about what we're going to launch. Choose SAP HANA, express edition (server + applications) and click "Launch on Compute Engine":

Fill in the form and make the right choices for your deployment model. The most important choices are:

Click "Deploy" and watch Cloud Launcher complete the SAP HANA, express edition (server + applications) deployment.

SSH into your new SAP HANA, express edition (server + applications) instance by clicking the SSH button when the launcher is complete:

A new window will open with an SSH connection to your new SAP HANA, express edition (server + applications) virtual machine. Once you're connected, use the following command to get the installation script started:

Create the SAP HANA Virtual Machine Default User

sudo su - hxeadm

You will be prompted to create a new master password. This password will be applied to all of the default users created during installation, including the database administration users.

Follow the prompts to complete the setup process.

  1. When asked if you need a proxy server to access the internet, choose "no."
  2. When asked if you want to wait for XSA configuration to finish, choose "no" so the process completes in background. If the connection to the web SSH console is lost the configuration won't be interrupted.
  3. When asked to proceed with configuration choose "yes."

Wait until the command prompt is available again. You can follow its progress by running the following command in the terminal:

As SAP HANA Virtual Machine hxeadm User

watch cat /usr/sap/HXE/home/xsa_config_status

Wait until the status switches from in progress to success:

Press Ctrl+C to return to the terminal.

The instance is now ready to be used. Several administration users have been created:

Navigate to your virtual machine instances in Google Cloud Compute Engine:

Note the External IP for your SAP HANA, express edition with XS Advanced instance:

We will use this IP address in future steps to connect to SAP HANA, express edition with XS Advanced.

For this CodeLab we will use both SAP Web IDE for SAP HANA and SQLPad.

You can perform administrative tasks on SAP HANA XS Advanced using the command line.

To log in and list applications and their status in the SAP space, such as the SAP Web IDE for SAP HANA or the XS Advanced Cockpit:

xs login
xs apps

To log in directly as the admin, XSA_ADMIN, the following command is available only from the command line in the server:

xs-admin-login

For example, to find out the URL for the SAP Web IDE for SAP HANA and the status of the application:

You can use user XSA_DEV to connect to SAP Web IDE for SAP HANA. This will only work if you have renamed the hosts file. If you cannot or do not have permissions to rename the hosts file in the computer you are using to access the XS Advanced applications, you can complete the next step.

You have the option to rename the default domain used with XS Advanced applications to access the database explorer or the SAP Web IDE to the external IP address in your Virtual Machine.

Set the IP address to static

Use the edit button and click on the pencil next to the default network interface to edit its values

Click on the arrow to Create an IP address

And assign a name to the IP address

Scroll down and save the settings for the your virtual machine. Back in the compute engine, take note of the new static external IP address

Edit the controller parameters

Use the SSH button to open a new console. Enter the following command

sudo vi /hana/shared/HXE/global/hdb/custom/config/xscontroller.ini

Use the arrow keys to move to the lines to be edited. Press the letter i to start editing and replace hxehost with your external IP address:

Use Esc + "wq" to save your changes and exit.

Stop and rename the database

Use the following commands to stop the database and create a new file called pwd.xml

sudo su - hxeadm
HDB stop
Exit
vi pwd.xml

Press the letter i to insert text and copy the content below. Replace HXEHana1 with the password you originally set for hxeadm when configuring the instance:

<?xml version="1.0" encoding="UTF-8"?>
<Passwords>
<password><![CDATA[Password123]]></password>
<source_password><![CDATA[Password123]]></source_password>
<system_user_password><![CDATA[HXEHana1]]></system_user_password>
</Passwords>

Use Esc + "wq" to save and exit the editor.

Replace the IP_ADDRESS in the following command with your external IP address and paste it in the command line:

cat ./pwd.xml | sudo /hana/shared/HXE/hdblcm/hdblcm --action=rename_system  \
  --skip_hostagent_calls \
  --certificates_hostmap=hxehost=IP_ADDRESS \
  --xs_domain_name=IP_ADDRESS \
  --read_password_from_stdin=xml -b

Once the rename has finished successfully the database will be started for you.

Every server has a simple SQL command line you can use to perform basic administration tasks. Use the following command to connect to the SYSTEMDB database

hdbsql -i 90 -d SYSTEMDB -u SYSTEM

You will be prompted for the password. Use the master database password that you specified at the beginning of the script.

You can perform a very simple SQL test

SELECT * from dummy;

We will use Google Cloud Shell to configure SQLPad for deployment on Google App Engine.

SQLPad is an Open Source, database independent query editor and runner. It works with many databases including SAP HANA.

Launch Google Cloud Shell

Click the Google Cloud Shell icon in the Google Cloud Console header.

Once you launch Google Cloud Shell you should see a terminal on the lower portion of the window:

Make a Directory and Files for SQLPad

Run the following command in your Google Cloud Shell terminal to make a new directory:

mkdir sqlpad-on-gae

Change to that directory:

cd sqlpad-on-gae

Create the files we will need to deploy SQLPad on Google App Engine:

touch Dockerfile docker-entrypoint app.yaml

Open the Google Cloud Shell Code Editor in this directory:

edit .

This will open a new window, or tab, with the Google Cloud Shell editor:

Create the Dockerfile

We will deploy SQLPad on the Google App Engine flexible environment. This environment allows us to deploy our own container and get all the benefits of a fully managed application platform. To start we need a container.

Thankfully SQLPad publishes containers on Docker Hub so we can use them as the basis of our Google App Engine deployment.

Edit your Dockerfile so it reads as follows:

Dockerfile

FROM sqlpad/sqlpad:2.6.0
COPY docker-entrypoint /
RUN chmod +x /docker-entrypoint

Create a docker-entrypoint

The default docker-entrypoint for SQLPad assumes the application will be listening on port 3000, however, Google App Engine expects your application to listen on port 8080. We need to make a copy which conforms to Google App Engine's expectations:

docker-entrypoint

#!/bin/sh

exec node /usr/app/server/server.js  --dir /var/lib/sqlpad --port 8080 $@

Create an app.yaml configuration

Google App Engine requires a configuration file known as app.yaml.

app.yaml

runtime: custom
env: flex
service: sqlpad
manual_scaling:
  instances: 1

The runtime and env parameters are set to deploy a custom runtime in the Google App Engine flexible environment.

We want to specify the service name as sqlpad and instruct Google App Engine to deploy a single instance. The reason we want just one instance is because this lightweight deployment of SQLPad has a built-in data store running inside the container and that method doesn't scale beyond a single instance.

If this is your first Google App Engine deployment you must first deploy a default app. Follow these steps:

Copy your app.yaml file to app_default.yaml:

cp app.yaml app_default.yaml

Then delete the service value from app_default.yaml so it looks like this:

app_default.yaml

runtime: custom
env: flex
manual_scaling:
  instances: 1

To deploy SQLPad on Google App Engine we will use the gcloud command from inside the directory we prepared SQLPad to deploy from.

Change to the SQLPad directory

Make sure you're inside the SQLPad directory we prepared in the previous step:

cd ~/sqlpad-on-gae

If this is your first Google App Engine deployment you must first deploy a default app. Follow these steps:

Deploy the default application.

gcloud app deploy app_default.yaml

You'll be asked to choose a region where you want your App Engine application located. Select a region close to your location, such as [9] us-central if you're in the U.S.

The deployment will take several minutes to complete. When prompted:
Do you want to continue (Y/n)? Click "Y" to continue each time you are prompted.

Then delete the app_default.yaml file:

rm app_default.yaml

Deploying SQLPad

Now we can deploy SQLPad. This may take a few minutes:

gcloud app deploy

Follow the prompts and reply in the affirmative:

When your deploy completes successfully you will see the following output:

Deployed service [sqlpad] to [https://sqlpad-dot-PROJECT_ID.appspot.com]

You can stream logs from the command line by running:
  $ gcloud app logs tail -s sqlpad

To view your application in the web browser run:
  $ gcloud app browse -s sqlpad

Load SQLPad in the Browser

To load SQLPad we need the URL which can be found by running the following command:

gcloud app browse -s sqlpad --no-launch-browser

Copy the output of this command into a new tab to open it. You should see a sign in screen like this:

Instead of allowing anyone to sign up and sign in to our SQLPad instance we will require a Google account using Google Identity Platform's OAuth 2.0 for Server-side Web Apps.

Enable the Google+ API

Navigate to the APIs & Services Library page in the Google Cloud Console.

Search for and click the Google+ API:

And click "Enable":

Create OAuth client ID Credentials

Navigate to the APIs & Services Credentials page in Google Cloud Console:

Next, click "Create credentials" and choose "OAuth client ID":

If this is your first OAuth client ID you will have to follow the next step:

You will be informed that you must create a OAuth consent screen. Click on that button and fill the form in. You can supply any value for the Product name shown to users field and click "Save":

Next it will open a form to configure your OAuth client ID. Choose "Web application" from the list:

This will expand the form to configure your server-side web application OAuth client ID. Start by choosing a name for the application. I chose sqlpad:

The value for Authorized JavaScript origins will be the URL of your SQLPad service on Google App Engine. You can discover that URL using the following command on Google Cloud Shell:

gcloud app browse -s sqlpad --no-launch-browser

The results of this command should resemble https://sqlpad-dot-PROJECT_ID.appspot.com.

The value for Authorized redirect URIs adds /auth/google/callback to the end of the Authorized Javascript origins URI. For example: https://sqlpad-dot-PROJECT_ID.appspot.com/auth/google/callback

Finally, click "Create":

When your OAuth client is created Google Cloud Console will present a popup with your client ID and client secret. We will need these to configure SQLPad properly.

Configure SQLPad as an OAuth Client

Update the Dockerfile in Google Cloud Shell to set environment variables for the client ID, client secret, and public URL. We also want to disable authentication by username and password. Here is what your Dockerfile will look like:

Dockerfile

FROM sqlpad/sqlpad:2.6.0
COPY docker-entrypoint /
RUN chmod +x /docker-entrypoint

ENV GOOGLE_CLIENT_ID [YOUR_CLIENT_ID].apps.googleusercontent.com
ENV GOOGLE_CLIENT_SECRET [YOUR_CLIENT_SECRET]

ENV PUBLIC_URL https://sqlpad-dot-[YOUR_PROJECT_ID].appspot.com
ENV DISABLE_USERPASS_AUTH true

Re-Deploy SQLPad

This may take a few minutes:

gcloud app deploy

Once complete, your SQLPad login screen will look like this:

Log In

Click the "Log in with Google" button on SQLPad. This will ask you to authenticate with a Google account. Choose the account that works for you:

Create a New Connection

First, click the "New connection" button:

Next, name your connection and choose the SAP HANA Database Driver:

Now we need a few pieces of information from prior steps:

Here's what the new connection form looks like when it's filled out:

Test your New Connection

Once you have filled out the new connection form it's time to test it. Click the "Test" button. If your settings are correct you'll see a "Test successful" alert in the bottom right corner of the window:

Save your New Connection

Now click the "Save" button. You will see the new connection in the Connections list:

Click on the "New Query" button:

Then, type an SQL query and press "Run" to see the results:

Open https://hxehost:53075 (or https://YOUR_IP_ADDRESS:53075, if you have renamed the XS Advanced domain) in a browser (preferably, Chrome in incognito mode). Log in as XSA_DEV and use the master password you set up during the configuration of the VM:

Click on the icon for Database explorer

Click yes

Use the following parameters to connect to the tenant database:

Once the database is added, you can use the SQL console to perform the rest of the exercises. Use the + sign to connect as a new user

What We've Covered