Skip to Content

Enable your SAP HANA, express edition instance for SAP HANA Smart Data Access

test
0 %
Enable your SAP HANA, express edition instance for SAP HANA Smart Data Access
Details
// Explore More Tutorials

Enable your SAP HANA, express edition instance for SAP HANA Smart Data Access

10/15/2018

Enable your SAP HANA, express edition system for SAP HANA Smart Data Access.

You will learn

  • Create a SAP HANA Smart Data Access definition file for Amazon Athena
  • Enable the Script server
  • Enable the SAP HANA Smart Data Access safe Mode

SAP HANA Smart Data Access provide the ability to create remote sources and virtual tables.

For more information about SAP HANA Smart Data Access, you can check the online documentation.

But first, you need to activate the Script Server, which is disabled by default on new installations.

Once enabled, you will also need to activate the Safe Mode for ODBC connections.

The Safe Mode provides the capability to load ODBC drivers and execute ODBC calls from within the Script Server process and reduces potential issues with the Index Server caused by third-party ODBC drivers.

For more details, you can check the Safe Mode for ODBC Connections documentation.

Step 1: Switch to the ec2-user user

Connect to your SAP HANA, express edition instance using an SSH client as the ec2-user user.

The prompt should be:

ec2-user@hxehost:~>

If you are using an existing session and the prompt is hxeadm@hxehost:~> , then run the exit command to return to ec2-user.

Make sure the prompt is ec2-user@hxehost:~> before moving to the next step.

Log on to answer question
Step 2: Configure SAP HANA SDA for Amazon Athena

In order to best leverage the SAP HANA Smart Data Access, you will now add a dedicated configuration that will optimize the way queries will be executed between SAP HANA, express edition and Amazon Athena.

To learn more about it, you can check the SAP HANA Smart Data Access documentation from the SAP HANA Administration guide.

Run the following command to create the SAP HANA Smart Data Access configuration for Amazon Athena:

sudo vi /usr/sap/HXE/SYS/exe/hdb/config/property_athena.ini

Note: Below are a few useful vi keyboard combinations:

  • Enable the insert mode : ESC then I
  • Paste the clipboard content : CTRL+SHIFT+V
  • Exit and save vi: ESC then :wq!

Insert the following content then save and exit vi:

CAP_SUBQUERY : true
CAP_ORDERBY : true
CAP_JOINS : true
CAP_GROUPBY : true
CAP_AND : true
CAP_OR : true
CAP_TOP : false
CAP_LIMIT : true
CAP_SUBQUERY :  true
CAP_SUBQUERY_GROUPBY : true

FUNC_ABS : true
FUNC_ADD : true
FUNC_ADD_DAYS : DATE_ADD(DAY,$2,$1)
FUNC_ADD_MONTHS : DATE_ADD(MONTH,$2,$1)
FUNC_ADD_SECONDS : DATE_ADD(SECOND,$2,$1)
FUNC_ADD_YEARS : DATE_ADD(YEAR,$2,$1)
FUNC_ASCII : true
FUNC_ACOS : true
FUNC_ASIN : true
FUNC_ATAN : true
FUNC_TO_VARBINARY : false
FUNC_TO_VARCHAR : false
FUNC_TRIM_BOTH : TRIM($1)
FUNC_TRIM_LEADING : LTRIM($1)
FUNC_TRIM_TRAILING : RTRIM($1)
FUNC_UMINUS : false
FUNC_UPPER : true
FUNC_WEEKDAY : false

TYPE_TINYINT : TINYINT
TYPE_LONGBINARY : VARBINARY
TYPE_LONGCHAR : VARBINARY
TYPE_DATE : DATE
TYPE_TIME : TIME
TYPE_DATETIME : TIMESTAMP
TYPE_REAL : REAL
TYPE_SMALLINT : SMALLINT
TYPE_INT : INTEGER
TYPE_INTEGER : INTEGER
TYPE_FLOAT : DOUBLE
TYPE_CHAR : CHAR($PRECISION)
TYPE_BIGINT : DECIMAL(19,0)
TYPE_DECIMAL : DECIMAL($PRECISION,$SCALE)
TYPE_VARCHAR : VARCHAR($PRECISION)
TYPE_BINARY : VARBINARY
TYPE_VARBINARY : VARBINARY

PROP_USE_UNIX_DRIVER_MANAGER : true

Then, switch the file ownership back to hxeadm:

sudo chown hxeadm:sapsys /usr/sap/HXE/SYS/exe/hdb/config/property_athena.ini
Log on to answer question
Step 3: Switch to the hxeadm user

As the ODBC connection will be initialized by the SAP HANA, express edition process, it is important to configure the ODBC DSN as the hxeadm user.

Form your SSH session, execute the following command:

sudo su - hxeadm

The prompt should be:

hxeadm@hxehost:~>

Make sure the prompt is hxeadm@hxehost:~> before moving to the next step.

Log on to answer question
Step 4: Connect to the System Database

The Script Server activation for the HXE tenant can only be done from a connection to the System Database.

Start a HDBSQL session to connect to the System Database using:

hdbsql -i 90 -d SYSTEMDB -u system

You will be prompted for the database master password provided during the initialization.

The terminal prompt should become:

hdbsql SYSTEMDB=>
Log on to answer question
Step 5: Apply the configuration

You can now execute the following SQL commands to apply the required configuration:

alter database hxe add 'scriptserver';
alter system alter configuration ('indexserver.ini', 'SYSTEM') set ('smart_data_access', 'odbc_adapters_in_scriptserver') = 'all';

You can now quit the current HDBSQL session using \q.

Log on to answer question
Step 6: Restart your instance

Now, you will need to restart your SAP HANA, express edition for the change to be effective.

From the previous SSH session, run the following command:

HDB stop && HDB start

The restart process will take a few minutes to complete.

Although the database is running, the XS Advanced applications and services may still be loading. Check the progress by running the following command in the XS Command Line Interface.

xs-admin-login
xs apps | grep webide

When prompted, use the password provided during the SAP HANA initialization process (the master password).

Repeat the second command until you see STARTED and 1/1 for the webide applications.

Once started, you can run the following command to get the list of started processes:

HDB info

Provide an answer to the question below then click on Validate.

Paste the result from the last command.
×

Next Steps

Back to top