In this CodeLab, you will configure your SAP HANA, express edition instance to connect to Google BiqQuery through a Smart Data Adapter.

Log in as a sudoer user or open a new web SSH console. Run the following commands to install the dependencies needed to compile the unixODBC drivers.

sudo SUSEConnect -p PackageHub/12.2/x86_64
sudo SUSEConnect -p sle-sdk/12.2/x86_64
sudo SUSEConnect -p sle-module-toolchain/12/x86_64
sudo zypper clean
sudo zypper refresh

sudo zypper install --type pattern Basis-Devel

Use command curl -O to download the file into the /tmp directory

cd /tmp
curl -O http://www.unixodbc.org/unixODBC-2.3.6.tar.gz 

Once the download has finished, unzip the file into /tmp using the following command. Replace the name of the file with the file previously downloaded.

tar -xvzf unixODBC-2.3.6.tar.gz

The user executing this section needs to be in the sudoers list.

Switch to the directory created by the extraction of the unixODBC package.

cd ./unixODBC-2.3.6

Run the following command to create the makefile necessary for building and installation of the unixODBC driver

./configure

After the dependencies have been checked and provided you do not have any error messages, execute the following command:

make

Finally, perform the installation with the following command

sudo make install

Download the driver into the tmp directory in the virtual machine:

cd /tmp
curl -O https://storage.googleapis.com/simba-bq-release/odbc/SimbaODBCDriverforGoogleBigQuery64-2.1.6.1006.tar.gz

This is an example of what the download looks like in an SAP HANA, express edition virtual machine

Uncompress the file

Switch to hxeadm.

Unzip the file into the home for the administrator user in the operating system.

sudo su - hxeadm
tar -xvzf /tmp/SimbaODBCDriverforGoogleBigQuery64-2.1.6.1006.tar.gz --directory=/usr/sap/HXE/home

Example for SAP HANA, express edition

Use the text editor vi to configure the parameters for the driver in one of the files that have just been extracted. These files should all be in the home for the database administrator in the operating system, SIDadm.

cd ~

Edit the file simba.googlebigqueryodbc.ini in the directory you have just extracted, ~/simba/googlebigqueryodbc/lib/64/ within the home directory for user hxeadm.

vi ~/simba/googlebigqueryodbc/lib/64/simba.googlebigqueryodbc.ini

Set ErrorMessagesPath to the path where the directory ErrorMessages was extracted. This will be a concatenation of the home directory for hxeadm and ~/simba/googlebigqueryodbc/ErrorMessages/

Change the DriverManagerEncoding parameter to UTF-16 and uncomment the ODBCInstLib=libodbcinst.so.2 value.

You can copy the following file for the default SAP HANA, express edition instance

# To use this INI file, replace $(INSTALLDIR) with the
# directory the tarball was extracted to.
[Driver]
DriverManagerEncoding=UTF-16
ErrorMessagesPath=/usr/sap/HXE/home/simba/googlebigqueryodbc/ErrorMessages
LogLevel=1
LogPath=/tmp
ClientId=977385342095.apps.googleusercontent.com
ClientSecret=wbER7576mc_1YOII0dGk7jEE
#   unixODBC
ODBCInstLib=libodbcinst.so.2

For example, in SAP HANA, express edition:

You will need the location for the directories containing the libraries for the drivers you have installed.

Logged in as hxeadm, create or modify the file .customer.sh in the home directory using a text editor such as vi

vi ~/.customer.sh

Add the following values to the file:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/sap/XHE/home/simba/googlebigqueryodbc/lib/64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

From the cloud console for your PROJECT_ID, go to IAM and admin, service accounts and create a new service account to use to connect SAP HANA to Google BigQuery

Specify the name of the account, set the permissions BigQuery Data Viewer and BigQuery User, and choose to furnish a P12 key:

Save the key in p12 format to your computer or in another secure place.

Now that the service account has been created, upload the key to your server. You can use the upload tool in the web SSH console

Choose the .P12 file from your local computer. It will be uploaded to the Google account user home directory.

Copy it from your home directory to the home directory of your GCP default user with the following command as hxeadm:

cp /home/<<GCP_USERNAME>>/<<KEYFILE>>.p12 ~/key.p12

You can now use your service account and drivers to configure the connection. Create the file /hana/shared/HXE/HDB90/.odbc.ini using vi. ("90" in "HDB90" is the default instance number. Replace 90 in the appropriate HDBXX directory if you have a different instance number.)

vi /hana/shared/HXE/HDB90/.odbc.ini

Add the following entry, replacing the values appropriately.

[GoogleBQ]
Driver=/usr/sap/HXE/home/simba/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so
SQLDialect=1
OAuthMechanism=0
Email=SERVICE_ACCOUNT_NAME@PROJECT_ID.iam.gserviceaccount.com
KeyFilePath=/usr/sap/HXE/home/key.p12
Catalog=PROJECT_ID

The PROJECT_ID needs to be replaced with your project id:

The SERVICE_ACCOUNT_NAME should also be changed to the first part of the email address for the Service Account you recently created:

An SAP HANA system contains an instance of a database, called SYSTEMDB, that administers the one or many tenants in the same instance. Only tenant databases can make full use of the advanced analytics capabilities.

Logged in as hxeadm, use the following command to log in to the database

hdbsql -i 90 -d systemdb -u SYSTEM

When prompted, enter the master password you set up during the installation script. Enter the following commands to enable the indexserver in the tenant database and the connection to BigQuery:

hdbsql SYSTEMDB=> ALTER DATABASE HXE ADD 'scriptserver';
hdbsql SYSTEMDB=> ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('smart_data_access', 'odbc_adapters_in_scriptserver') = 'bigquery';

After each command you'll see a message like this:

0 rows affected (overall time 11.591 msec; server time 9222 usec)

For example:

Then issue quit ("q") command to exit hdbsql and return to the system command prompt:

hdbsql SYSTEMDB=> q

Use command HDB stop to stop the database.

Stopping the database will take several seconds. Wait until you see the command prompt again before proceeding.

hxeadm@hxehost:/usr/sap/HXE/home> HDB stop
hdbdaemon will wait maximal 300 seconds for NewDB services finishing.
Stopping instance using:
 /usr/sap/HXE/SYS/exe/hdb/sapcontrol -prot NI_HTTP -nr 90 -function Stop 
40028.06.2018 12:23:02 Stop OK
Waiting for stopped instance using:
 /usr/sap/HXE/SYS/exe/hdb/sapcontrol -prot NI_HTTP -nr 90 -function WaitforStopped 600
228.06.2018 12:23:40 Wait for StoppedOK hdbdaemon is stopped.
hxeadm@hxehost:/usr/sap/HXE/home>

Restart the virtual machine to apply the changes by selecting Compute Engine > VM Instances in the Google Cloud Console to first Stop then Start the instance.