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.
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.
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.
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.
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.
Run the following command in the SSH terminal:
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
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
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.
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:
Run the following command in your Google Cloud Shell terminal to make a new directory:
Change to that directory:
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:
This will open a new window, or tab, with the Google Cloud Shell editor:
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.
Dockerfile so it reads as follows:
FROM sqlpad/sqlpad:2.5.6 COPY docker-entrypoint / RUN chmod +x /docker-entrypoint
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:
#!/bin/sh exec node /usr/app/server/server.js --dir /var/lib/sqlpad --port 8080 $@
Google App Engine requires a configuration file known as
runtime: custom env: flex service: sqlpad manual_scaling: instances: 1
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.
app.yaml file to
cp app.yaml app_default.yaml
Then delete the service value from
app_default.yaml so it looks like this:
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.
Make sure you're inside the SQLPad directory we prepared in the previous step:
Deploy the default application.
gcloud app deploy app_default.yaml
Then delete the
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
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.
Navigate to the Google+ API page and click "Enable":
Navigate to the APIs & Services Credentials page in Google Cloud Console:
Next, click "Create credentials" and choose "OAuth client ID":
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
gcloud app browse -s sqlpad --no-launch-browser
The results of this command should resemble
The value for Authorized redirect URIs adds
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.
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:
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
This may take a few minutes:
gcloud app deploy
Once complete, your SQLPad login screen will look like this:
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:
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:
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:
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.
Open Google Cloud Shell and issue the following command:
gcloud app services delete sqlpad
SSH to your SAP HANA, express edition virtual machine. Then become the
sudo su - hxeadm
Then exit the hxeadm user:
Shut down the virtual machine:
sudo shutdown now
Your SSH connection through Google Cloud Console will issue an alert which you can safely dismiss: