In this CodeLab we will launch a new instance of SAP HANA, express edition, and securely deploy the Open Source query tool, SQLPad on Google App Engine.

What You'll Learn

SAP HANA Express must be deployed into a Google Cloud Platform project. If you already have a project skip this step.

If you don't have a project you can create one in this step.

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 automatic infrastructure. We can use it to launch SAP HANA, express edition. The Launcher will create the appropriate virtual machine, disk, and network configuration. We can use the search bar in Google Cloud Platform Console to easily get started. Search for "SAP HANA Launcher" and choose SAP HANA, express edition from the list:

This will give us cost estimates and information about what we're going to launch. Click the "Launch on Compute Engine" button:

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 deployment.

Follow the first-time setup instructions.

Change the Administrator Password

SSH into your new SAP HANA, express edition 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 virtual machine. Once you're connected, update the administrator password with the following command:

sudo su - hxeadm

Your new administrator password will require:

Follow the prompts to complete the setup process:

SAP HANA, express edition will complete its setup and you'll know it's finished when your prompt returns after the following message:

*** Congratulations! SAP HANA, express edition 2.0 is configured. ***
See https://www.sap.com/developer/tutorials/hxe-ua-getting-started-vm.html to get started.

Check Database Status

Run the following command in the SSH terminal:

HDB info

You should see output which resembles the following:

USER       PID  PPID %CPU    VSZ   RSS COMMAND
hxeadm   11336 11335  0.0  16600  6280 -bash
hxeadm   13121 11336 33.3  13640  3428  \_ /bin/sh /usr/sap/HXE/HDB90/HDB info
hxeadm   13152 13121  0.0  37132  2976      \_ ps fx -U hxeadm -o user,pid,ppid,pcpu,vsz,rss,args
hxeadm    2684     1  0.0  21712  3044 sapstart pf=/usr/sap/HXE/SYS/profile/HXE_HDB90_hxehost
hxeadm    2692  2684  0.0 211980 56992  \_ /usr/sap/HXE/HDB90/hxehost/trace/hdb.sapHXE_HDB90 -d -nw -f /usr/sap/HXE
hxeadm    2708  2692  8.4 3174868 2653060      \_ hdbnameserver
hxeadm    3489  2692  0.9 1080720 324576      \_ hdbcompileserver
hxeadm    3622  2692  7.6 3156996 2487036      \_ hdbindexserver -port 39003
hxeadm    4822  2692  0.8 1090816 320260      \_ hdbdiserver
hxeadm    4824  2692  0.9 1284796 543788      \_ hdbwebdispatcher
hxeadm    2251     1  0.0 494972 33604 /usr/sap/HXE/HDB90/exe/sapstartsrv pf=/usr/sap/HXE/SYS/profile/HXE_HDB90_hxe

At this point SAP HANA, express edition is up and running.

Check that you're logged in as the hxeadm user by running the command whoami:

hxeadm@hxehost:/usr/sap/HXE/HDB90> whoami
hxeadm

If you don't get an answer of hxeadm, make sure you switch to that user:

sudo su - hxeadm

Change the password for the hxeadm user:

passwd

You'll see the following output when the password is changed successfully:

passwd: password updated successfully

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

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

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

For this CodeLab we will use SQLPad which is the most available option.

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.5.6
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

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 Google+ API page 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.5.6
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:

Deleting SQLPad and stopping the SAP HANA, express edition virtual machine will help control costs. You can always turn SAP HANA, express edition back on and re-deploy SQLPad later.

Deleting SQLPad

Open Google Cloud Shell and issue the following command:

gcloud app services delete sqlpad

Shutting Down SAP HANA, express edition

SSH to your SAP HANA, express edition virtual machine. Then become the hxeadm user:

sudo su - hxeadm

Stop HDB:

HDB stop

Then exit the hxeadm user:

exit

Shut down the virtual machine:

sudo shutdown now

Your SSH connection through Google Cloud Console will issue an alert which you can safely dismiss:

What We've Covered