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
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.
Run the following command to create the makefile necessary for building and installation of the unixODBC driver
After the dependencies have been checked and provided you do not have any error messages, execute the following command:
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-220.127.116.116.tar.gz
This is an example of what the download looks like in an SAP HANA, express edition virtual machine
Unzip the file into the home for the administrator user in the operating system.
sudo su - hxeadm tar -xvzf /tmp/SimbaODBCDriverforGoogleBigQuery64-18.104.22.1686.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.
Edit the file
simba.googlebigqueryodbc.ini in the directory you have just extracted,
/simba/googlebigqueryodbc/lib/64/ within the home directory for user
Set ErrorMessagesPath to the path where the directory ErrorMessages was extracted. This will be a concatenation of the home directory for hxeadm and ~
Change the DriverManagerEncoding parameter to UTF-16 and uncomment the
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
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
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.)
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
PROJECT_ID needs to be replaced with your project id:
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)
Then issue quit ("q") command to exit hdbsql and return to the system command prompt:
hdbsql SYSTEMDB=> q
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.