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.
SAP HANA, express edition with XS Advanced must be deployed into a Google Cloud Platform 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.
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:
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.
Wait until the command prompt is available again. You can follow its progress by running the following command in the terminal:
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:
SYSTEMto perform database administration tasks
XSA_DEVto log in to SAP Web IDE for SAP HANA as a developer
XSA_ADMINto perform administration tasks in for Extended Application Services, advanced model (XS Advanced) platform
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:
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.
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
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
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.
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
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.
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.
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.
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.6.0 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
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  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
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 APIs & Services Library page in the Google Cloud Console.
Search for and click the Google+ API:
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.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
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: